Spin locks bringing system to a crawl

[Jay]

Hi,

I'm having a major decrease in performance, and I'm wondering if the number
of spin locks (725 entries in onstat -g spi of "fast mutex, bhash") are
contributing to this.

Enviroment
Informix 7.24 (I know we must upgrade, but unfortunatly it's not my descion
to as when), AIX 4.3.3, 4 CPUs, 1GB RAM, 8 disks no mirror or stripes and
volitile tables fragmented by round robin across 4 disks (indexes for these
tables are on seperate a disk).

I am really hoping someone can help me!

Thanks in advance

J.

# Root Dbspace Configuration

ROOTNAME rootdbs # Root dbspace name
ROOTPATH /dev/prod2_rootdbs # Path for device containing root
dbspace
ROOTOFFSET 0 # Offset of root dbspace into device
(Kbytes)
ROOTSIZE 1024000 # Size of root dbspace (Kbytes)

# Disk Mirroring Configuration Parameters

MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing mirrored root
MIRROROFFSET 0 # Offset into mirrored device (Kbytes)

# Physical Log Configuration

PHYSDBS rootdbs # Location (dbspace) of physical log
PHYSFILE 80000 # Physical log file size (Kbytes)

# Logical Log Configuration

LOGFILES 6 # Number of logical log files
LOGSIZE 45000 # Logical log size (Kbytes)

# Diagnostics

MSGPATH /isgprod2/inf7dev/online.log # System message log file path
CONSOLE /isgprod2/inf7dev/console.log # System console message path
ALARMPROGRAM /isgprod2/inf7dev/etc/no_log.sh # Alarm program path

# System Archive Tape Device

TAPEDEV /dev/rmt1 # Tape device path
TAPEBLK 512 # Tape block size (Kbytes)
TAPESIZE 20971520 #Maximum amount of data to put on tape
(kbytes)

# Log Archive Tape Device

LTAPEDEV /dev/null # Log tape device path
LTAPEBLK 512 # Log tape block size (Kbytes)
LTAPESIZE 12582412 # Max amount of data to put on log tape
(Kbytes)

# Optical

STAGEBLOB # INFORMIX-OnLine/Optical staging area

# System Configuration

SERVERNUM 0 # Unique id corresponding to a OnLine
instance
DBSERVERNAME aixprod2_on # Name of default database server
DBSERVERALIASES rt_aixprod2_on # List of alternate dbservernames
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed
env.
RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)

MULTIPROCESSOR 1 # 0 for single-processor, 1 for
multi-processor
NUMCPUVPS 4 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to
one

NOAGE 0 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors

# Shared Memory Parameters

LOCKS 10000 # Maximum number of locks
#BUFFERS 8000 # Maximum number of shared buffers
BUFFERS 100000
NUMAIOVPS 6 # Number of IO vps
PHYSBUFF 64 # Physical log buffer size (Kbytes)
LOGBUFF 64 # Logical log buffer size (Kbytes)
LOGSMAX 6 # Maximum number of logical log files
CLEANERS 6 # Number of buffer cleaner processes
SHMBASE 0x30000000 # Shared memory base address
SHMVIRTSIZE 98304 # initial virtual shared memory segment size
SHMADD 16384 # Size of new shared memory segments
(Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 32 # Number of LRU queues
LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water mark
percentage
LTXEHWM 60 # Long transaction high water mark
(exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 64 # Stack size (Kbytes)

# System Page Size
# BUFFSIZE - OnLine no longer supports this configuration parameter.
# To determine the page size used by OnLine on your platform
# see the last line of output from the command, 'onstat -b'.


# Recovery Variables
# OFF_RECVRY_THREADS:
# Number of parallel worker threads during fast recovery or an offline
restore.
# ON_RECVRY_THREADS:
# Number of parallel worker threads during an online restore.

OFF_RECVRY_THREADS 10 # Default number of offline worker
threads
ON_RECVRY_THREADS 1 # Default number of online worker threads

# Data Replication Variables
# DRAUTO: 0 manual, 1 retain type, 2 reverse type
DRAUTO 0 # DR automatic switchover
DRINTERVAL 30 # DR max time between DR buffer flushes (in
sec)
DRTIMEOUT 30 # DR network timeout (in sec)
DRLOSTFOUND /isgprod2/inf7dev/etc/dr.lostfound # DR lost+found file path

# CDR Variables
CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes)
CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional)
CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue
(Kbytes)

# Backup/Restore variables
BAR_ACT_LOG /tmp/bar_act.log
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 10
BAR_XFER_BUF_SIZE 31

# Read Ahead Variables
RA_PAGES 32 # Number of pages to attempt to read ahead
RA_THRESHOLD 30 # Number of pages left before next group

# DBSPACETEMP:
# OnLine equivalent of DBTEMP for SE. This is the list of dbspaces
# that the OnLine SQL Engine will use to create temp tables etc.
# If specified it must be a colon separated list of dbspaces that exist
# when the OnLine system is brought online. If not specified, or if
# all dbspaces specified are invalid, various ad hoc queries will create
# temporary files in /tmp instead.


DBSPACETEMP tempdbs1:tempdbs2:tempdbs3 # Default temp dbspaces
#DBSPACETEMP # Default temp dbspaces

# DUMP*:
# The following parameters control the type of diagnostics information which
# is preserved when an unanticipated error condition (assertion failure)
occurs
# during OnLine operations.
# For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.

DUMPDIR /tmp # Preserve diagnostics in this directory
DUMPSHMEM 0 # Dump a copy of shared memory
DUMPGCORE 0 # Dump a core image using 'gcore'
DUMPCORE 0 # Dump a core image (Warning:this aborts
OnLine)
DUMPCNT 1 # Number of shared memory or gcore dumps for
# a single user's session

FILLFACTOR 90 # Fill factor for building indexes

# method for OnLine to use when determining current time
USEOSTIME 0 # 0: use internal time(fast), 1: get time
from OS(slow)

# Parallel Database Queries (pdq)
MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
DS_MAX_QUERIES # Maximum number of decision support queries
DS_TOTAL_MEMORY 88000
DS_MAX_SCANS 1048576 # Maximum number of decision support scans
DATASKIP off # List of dbspaces to skip

# OPTCOMPIND
# 0 => Nested loop joins will be preferred (where
# possible) over sortmerge joins and hash joins.
# 1 => If the transaction isolation mode is not
# "repeatable read", optimizer behaves as in (2)
# below. Otherwise it behaves as in (0) above.
# 2 => Use costs regardless of the transaction isolation
# mode. Nested loop joins are not necessarily
# preferred. Optimizer bases its decision purely
# on costs.
OPTCOMPIND 2 # To hint the optimizer

ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 =
ABORT, 2 = WAIT
LBU_PRESERVE 0 # Preserve last log for log backup
OPCACHEMAX 0 # Maximum optical cache size (Kbytes)

# HETERO_COMMIT (Gateway participation in distributed transactions)
# 1 => Heterogeneous Commit is enabled
# 0 (or any other value) => Heterogeneous Commit is disabled
HETERO_COMMIT 0
NETTYPE ipcshm,4,50,CPU # Configure poll thread(s) for nettype
TBLSPACE_STATS 1


Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
586414 727492 88058443 99.33 39541 195853 424115 90.68

isamtot open start read write rewrite delete commit
rollbk
72386297 3379824 4975815 50048799 77538 31055 0 135 0

ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 8599.68 880.63 35 72

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
76288 0 21457153 0 0 22 5340 21910

ixda-RA idx-RA da-RA RA-pgsused lchwaits
219672 129 3072 222797 8817994


Spin locks with waits:

Num Waits Num Loops Avg Loop/Wait Name
360422 14834376 41.16 mtcb sleeping_lock
38347 191020 4.98 mtcb mutex_list_lock
10 47 4.70 class cl_lock, class = 0
1174704 6863005 5.84 vproc vp_lock, id = 1
1068068 5527923 5.18 vproc vp_lock, id = 3
910435 4660890 5.12 vproc vp_lock, id = 4
873934 4271230 4.89 vproc vp_lock, id = 5
20826 255286 12.26 mutex lock, name = rstcb
2 2 1.00 mutex lock, name = timestmp
4638 22682 4.89 mutex lock, name = alttbl
2 9 4.50 mutex lock, name = dbs_partn
119177 1961494 16.46 mutex lock, name = txlk[19]
54 195 3.61 mutex lock, name = txlk[12]
7631 60539 7.93 mutex lock, name = ddh chain
105225 487859 4.64 mutex lock, name = sbpool
39 112 2.87 mutex lock, name = bbpool
6338 37915 5.98 mutex lock, name = aioshcb
1 7 7.00 mutex lock, name = gfile
4 45 11.25 mutex lock, name = flushp-0
4 34 8.50 mutex lock, name = flushp-1
3 3 1.00 mutex lock, name = flushp-2
1 1 1.00 mutex lock, name = flushp-3
3 27 9.00 mutex lock, name = flushp-4
3 9 3.00 mutex lock, name = flushp-5
10 113 11.30 mutex lock, name = kaiobbpool
2595 14576 5.62 mutex lock, name = dbs_partn
4196 52257 12.45 mutex lock, name = dbs_partn
25 75 3.00 mutex lock, name = pt_100003
1 4 4.00 mutex lock, name = pt_17
140895 825452 5.86 mutex lock, name = pt_5000a0
12129 50419 4.16 mutex lock, name = pt_200014
1 1 1.00 mutex lock, name = pt_800014
1 2 2.00 mutex lock, name = pt_500099
1 7 7.00 mutex lock, name = pt_40000c
20577 77210 3.75 mutex lock, name = scbthd
15651 66824 4.27 mutex lock, name = sqtcb
6 105 17.50 tcb lock, tid = 11
5 157 31.40 tcb lock, tid = 26
1 199 199.00 tcb lock, tid = 28
1 59 59.00 tcb lock, tid = 30
38249 1517078 39.66 tcb lock, tid = 8767413
226 10804 47.81 pool po_lock, name = global
87315 713872 8.18 pool po_lock, name = mt
16368593 557532226 34.06 pool po_lock, name = 65
1 69 69.00 fast mutex, bhash[31369]
1 6 6.00 fast mutex, bhash[31372]
1 96 96.00 fast mutex, bhash[31373]
21 238 11.33 fast mutex, bhash[31374]
8 49 6.12 fast mutex, bhash[31375]
19 230 12.11 fast mutex, bhash[31376]
6 16 2.67 fast mutex, bhash[31377]
1 7 7.00 fast mutex, bhash[31617]
1 8 8.00 fast mutex, bhash[31618]
1 112 112.00 fast mutex, bhash[31620]
7 105 15.00 fast mutex, bhash[31621]
......(714 more entries clipped)
52 249 4.79 fast mutex, lru-0
42 716 17.05 fast mutex, lru-2
36 122 3.39 fast mutex, lru-4
35 120 3.43 fast mutex, lru-6
31 119 3.84 fast mutex, lru-8
31 150 4.84 fast mutex, lru-10
1 1 1.00 fast mutex, lru-11
26 85 3.27 fast mutex, lru-12
1 2 2.00 fast mutex, lru-13
19 75 3.95 fast mutex, lru-14
26 87 3.35 fast mutex, lru-16
34 1174 34.53 fast mutex, lru-18
28 84 3.00 fast mutex, lru-20
44 685 15.57 fast mutex, lru-22
1 1 1.00 fast mutex, lru-23
36 478 13.28 fast mutex, lru-24
44 180 4.09 fast mutex, lru-26
26 597 22.96 fast mutex, lru-28
2 2 1.00 fast mutex, lru-29
34 228 6.71 fast mutex, lru-30
35 194 5.54 fast mutex, lru-32
3 15 5.00 fast mutex, lru-33
41 194 4.73 fast mutex, lru-34
2 14 7.00 fast mutex, lru-35
34 116 3.41 fast mutex, lru-36
5 42 8.40 fast mutex, lru-37
38 592 15.58 fast mutex, lru-38
1 2 2.00 fast mutex, lru-39
42 153 3.64 fast mutex, lru-40
34 174 5.12 fast mutex, lru-42
37 99 2.68 fast mutex, lru-44
48 165 3.44 fast mutex, lru-46
28 97 3.46 fast mutex, lru-48
31 112 3.61 fast mutex, lru-50
43 149 3.47 fast mutex, lru-52
44 202 4.59 fast mutex, lru-54
42 201 4.79 fast mutex, lru-56
30 151 5.03 fast mutex, lru-58
37 116 3.14 fast mutex, lru-60
48 158 3.29 fast mutex, lru-62
1 1 1.00 fast mutex, bf[3348]
1 44 44.00 fast mutex, bf[11460]
1 44 44.00 fast mutex, bf[13238]
1 1 1.00 fast mutex, bf[15941]
1 1 1.00 fast mutex, bf[17050]
1 1 1.00 fast mutex, bf[20776]
7 49 7.00 fast mutex, bf[32080]
62063 780973 12.58 fast mutex, bf[32369]
4 176 44.00 fast mutex, bf[35513]
1 1 1.00 fast mutex, bf[40486]
1 39 39.00 fast mutex, bf[51066]
16 846 52.88 fast mutex, bf[53422]
26 492 18.92 fast mutex, bf[74967]
122159 2555142 20.92 fast mutex, bf[75595]
113441 4622047 40.74 fast mutex, bf[83747]
34236 515948 15.07 fast mutex, bf[85107]
1 1 1.00 fast mutex, bf[91699]
2 15 7.50 fast mutex, bf[92268]
1 53 53.00 fast mutex, bf[94211]
1 23 23.00 fast mutex, bf[95886]
6 90 15.00 fast mutex, bf[96245]
7 20 2.86 fast mutex, flush

[Art S. Kagel]

On Mon, 21 Jul 2003 10:01:28 -0400, Jay wrote:

You're getting some moderate LRU contention (BR ~7% & some LRU mutex spins

>15), try increasing LRUS and CLEANERS to 48. RA_THRESHOLD is too high

even for JBOD disk farm, try dropping that to 8 or 12 from 30.

See if that helps.

Art S. Kagel

PS: I know it's pain but please do not use 'NOSPAM' style email addresses.

> Hi,
>
> I'm having a major decrease in performance, and I'm wondering if the
> number of spin locks (725 entries in onstat -g spi of "fast mutex,
> bhash") are contributing to this.
>
> Enviroment
> Informix 7.24 (I know we must upgrade, but unfortunatly it's not my
> descion to as when), AIX 4.3.3, 4 CPUs, 1GB RAM, 8 disks no mirror or
> stripes and volitile tables fragmented by round robin across 4 disks
> (indexes for these tables are on seperate a disk).
>
> I am really hoping someone can help me!
>
> Thanks in advance
>
> J.

<SNIP>

[Michael Mueller]

Jay,

Your spin locks look fine. The most critical number is Avg Loop/Wait
which should be low. That you see a long list of spin locks is normal
for a large and heavily loaded system.

The profile shows a high number of sequential scans. This might be worth
looking into if it increases too fast with time. But I doubt that this
is your main problem either.

Can you be more specific about your "decrease in performance"? How high
is your cpu and disk load (sar -u, -d on most systems)? What are the
symptoms that bother you?

Michael

Jay wrote:

> Hi,
>
> I'm having a major decrease in performance, and I'm wondering if the number
> of spin locks (725 entries in onstat -g spi of "fast mutex, bhash") are
> contributing to this.
>
> Enviroment
> Informix 7.24 (I know we must upgrade, but unfortunatly it's not my descion
> to as when), AIX 4.3.3, 4 CPUs, 1GB RAM, 8 disks no mirror or stripes and
> volitile tables fragmented by round robin across 4 disks (indexes for these
> tables are on seperate a disk).
>
> I am really hoping someone can help me!
>
> Thanks in advance
>
> J.
>
> # Root Dbspace Configuration
>
> ROOTNAME rootdbs # Root dbspace name
> ROOTPATH /dev/prod2_rootdbs # Path for device containing root
> dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device
> (Kbytes)
> ROOTSIZE 1024000 # Size of root dbspace (Kbytes)
>
> # Disk Mirroring Configuration Parameters
>
> MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
> MIRRORPATH # Path for device containing mirrored root
> MIRROROFFSET 0 # Offset into mirrored device (Kbytes)
>
> # Physical Log Configuration
>
> PHYSDBS rootdbs # Location (dbspace) of physical log
> PHYSFILE 80000 # Physical log file size (Kbytes)
>
> # Logical Log Configuration
>
> LOGFILES 6 # Number of logical log files
> LOGSIZE 45000 # Logical log size (Kbytes)
>
> # Diagnostics
>
> MSGPATH /isgprod2/inf7dev/online.log # System message log file path
> CONSOLE /isgprod2/inf7dev/console.log # System console message path
> ALARMPROGRAM /isgprod2/inf7dev/etc/no_log.sh # Alarm program path
>
> # System Archive Tape Device
>
> TAPEDEV /dev/rmt1 # Tape device path
> TAPEBLK 512 # Tape block size (Kbytes)
> TAPESIZE 20971520 #Maximum amount of data to put on tape
> (kbytes)
>
> # Log Archive Tape Device
>
> LTAPEDEV /dev/null # Log tape device path
> LTAPEBLK 512 # Log tape block size (Kbytes)
> LTAPESIZE 12582412 # Max amount of data to put on log tape
> (Kbytes)
>
> # Optical
>
> STAGEBLOB # INFORMIX-OnLine/Optical staging area
>
> # System Configuration
>
> SERVERNUM 0 # Unique id corresponding to a OnLine
> instance
> DBSERVERNAME aixprod2_on # Name of default database server
> DBSERVERALIASES rt_aixprod2_on # List of alternate dbservernames
> DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed
> env.
> RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)
>
> MULTIPROCESSOR 1 # 0 for single-processor, 1 for
> multi-processor
> NUMCPUVPS 4 # Number of user (cpu) vps
> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to
> one
>
> NOAGE 0 # Process aging
> AFF_SPROC 0 # Affinity start processor
> AFF_NPROCS 0 # Affinity number of processors
>
> # Shared Memory Parameters
>
> LOCKS 10000 # Maximum number of locks
> #BUFFERS 8000 # Maximum number of shared buffers
> BUFFERS 100000
> NUMAIOVPS 6 # Number of IO vps
> PHYSBUFF 64 # Physical log buffer size (Kbytes)
> LOGBUFF 64 # Logical log buffer size (Kbytes)
> LOGSMAX 6 # Maximum number of logical log files
> CLEANERS 6 # Number of buffer cleaner processes
> SHMBASE 0x30000000 # Shared memory base address
> SHMVIRTSIZE 98304 # initial virtual shared memory segment size
> SHMADD 16384 # Size of new shared memory segments
> (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> LRUS 32 # Number of LRU queues
> LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
> LTXHWM 50 # Long transaction high water mark
> percentage
> LTXEHWM 60 # Long transaction high water mark
> (exclusive)
> TXTIMEOUT 0x12c # Transaction timeout (in sec)
> STACKSIZE 64 # Stack size (Kbytes)
>
> # System Page Size
> # BUFFSIZE - OnLine no longer supports this configuration parameter.
> # To determine the page size used by OnLine on your platform
> # see the last line of output from the command, 'onstat -b'.
>
>
> # Recovery Variables
> # OFF_RECVRY_THREADS:
> # Number of parallel worker threads during fast recovery or an offline
> restore.
> # ON_RECVRY_THREADS:
> # Number of parallel worker threads during an online restore.
>
> OFF_RECVRY_THREADS 10 # Default number of offline worker
> threads
> ON_RECVRY_THREADS 1 # Default number of online worker threads
>
> # Data Replication Variables
> # DRAUTO: 0 manual, 1 retain type, 2 reverse type
> DRAUTO 0 # DR automatic switchover
> DRINTERVAL 30 # DR max time between DR buffer flushes (in
> sec)
> DRTIMEOUT 30 # DR network timeout (in sec)
> DRLOSTFOUND /isgprod2/inf7dev/etc/dr.lostfound # DR lost+found file path
>
> # CDR Variables
> CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes)
> CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional)
> CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
> CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue
> (Kbytes)
>
> # Backup/Restore variables
> BAR_ACT_LOG /tmp/bar_act.log
> BAR_MAX_BACKUP 0
> BAR_RETRY 1
> BAR_NB_XPORT_COUNT 10
> BAR_XFER_BUF_SIZE 31
>
> # Read Ahead Variables
> RA_PAGES 32 # Number of pages to attempt to read ahead
> RA_THRESHOLD 30 # Number of pages left before next group
>
> # DBSPACETEMP:
> # OnLine equivalent of DBTEMP for SE. This is the list of dbspaces
> # that the OnLine SQL Engine will use to create temp tables etc.
> # If specified it must be a colon separated list of dbspaces that exist
> # when the OnLine system is brought online. If not specified, or if
> # all dbspaces specified are invalid, various ad hoc queries will create
> # temporary files in /tmp instead.
>
>
> DBSPACETEMP tempdbs1:tempdbs2:tempdbs3 # Default temp dbspaces
> #DBSPACETEMP # Default temp dbspaces
>
> # DUMP*:
> # The following parameters control the type of diagnostics information which
> # is preserved when an unanticipated error condition (assertion failure)
> occurs
> # during OnLine operations.
> # For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.
>
> DUMPDIR /tmp # Preserve diagnostics in this directory
> DUMPSHMEM 0 # Dump a copy of shared memory
> DUMPGCORE 0 # Dump a core image using 'gcore'
> DUMPCORE 0 # Dump a core image (Warning:this aborts
> OnLine)
> DUMPCNT 1 # Number of shared memory or gcore dumps for
> # a single user's session
>
> FILLFACTOR 90 # Fill factor for building indexes
>
> # method for OnLine to use when determining current time
> USEOSTIME 0 # 0: use internal time(fast), 1: get time
> from OS(slow)
>
> # Parallel Database Queries (pdq)
> MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
> DS_MAX_QUERIES # Maximum number of decision support queries
> DS_TOTAL_MEMORY 88000
> DS_MAX_SCANS 1048576 # Maximum number of decision support scans
> DATASKIP off # List of dbspaces to skip
>
> # OPTCOMPIND
> # 0 => Nested loop joins will be preferred (where
> # possible) over sortmerge joins and hash joins.
> # 1 => If the transaction isolation mode is not
> # "repeatable read", optimizer behaves as in (2)
> # below. Otherwise it behaves as in (0) above.
> # 2 => Use costs regardless of the transaction isolation
> # mode. Nested loop joins are not necessarily
> # preferred. Optimizer bases its decision purely
> # on costs.
> OPTCOMPIND 2 # To hint the optimizer
>
> ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 =
> ABORT, 2 = WAIT
> LBU_PRESERVE 0 # Preserve last log for log backup
> OPCACHEMAX 0 # Maximum optical cache size (Kbytes)
>
> # HETERO_COMMIT (Gateway participation in distributed transactions)
> # 1 => Heterogeneous Commit is enabled
> # 0 (or any other value) => Heterogeneous Commit is disabled
> HETERO_COMMIT 0
> NETTYPE ipcshm,4,50,CPU # Configure poll thread(s) for nettype
> TBLSPACE_STATS 1
>
>
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 586414 727492 88058443 99.33 39541 195853 424115 90.68
>
> isamtot open start read write rewrite delete commit
> rollbk
> 72386297 3379824 4975815 50048799 77538 31055 0 135 0
>
> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> 0 0 0 8599.68 880.63 35 72
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 76288 0 21457153 0 0 22 5340 21910
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 219672 129 3072 222797 8817994
>
>
> Spin locks with waits:
>
> Num Waits Num Loops Avg Loop/Wait Name
> 360422 14834376 41.16 mtcb sleeping_lock
> 38347 191020 4.98 mtcb mutex_list_lock
> 10 47 4.70 class cl_lock, class = 0
> 1174704 6863005 5.84 vproc vp_lock, id = 1
> 1068068 5527923 5.18 vproc vp_lock, id = 3
> 910435 4660890 5.12 vproc vp_lock, id = 4
> 873934 4271230 4.89 vproc vp_lock, id = 5
> 20826 255286 12.26 mutex lock, name = rstcb
> 2 2 1.00 mutex lock, name = timestmp
> 4638 22682 4.89 mutex lock, name = alttbl
> 2 9 4.50 mutex lock, name = dbs_partn
> 119177 1961494 16.46 mutex lock, name = txlk[19]
> 54 195 3.61 mutex lock, name = txlk[12]
> 7631 60539 7.93 mutex lock, name = ddh chain
> 105225 487859 4.64 mutex lock, name = sbpool
> 39 112 2.87 mutex lock, name = bbpool
> 6338 37915 5.98 mutex lock, name = aioshcb
> 1 7 7.00 mutex lock, name = gfile
> 4 45 11.25 mutex lock, name = flushp-0
> 4 34 8.50 mutex lock, name = flushp-1
> 3 3 1.00 mutex lock, name = flushp-2
> 1 1 1.00 mutex lock, name = flushp-3
> 3 27 9.00 mutex lock, name = flushp-4
> 3 9 3.00 mutex lock, name = flushp-5
> 10 113 11.30 mutex lock, name = kaiobbpool
> 2595 14576 5.62 mutex lock, name = dbs_partn
> 4196 52257 12.45 mutex lock, name = dbs_partn
> 25 75 3.00 mutex lock, name = pt_100003
> 1 4 4.00 mutex lock, name = pt_17
> 140895 825452 5.86 mutex lock, name = pt_5000a0
> 12129 50419 4.16 mutex lock, name = pt_200014
> 1 1 1.00 mutex lock, name = pt_800014
> 1 2 2.00 mutex lock, name = pt_500099
> 1 7 7.00 mutex lock, name = pt_40000c
> 20577 77210 3.75 mutex lock, name = scbthd
> 15651 66824 4.27 mutex lock, name = sqtcb
> 6 105 17.50 tcb lock, tid = 11
> 5 157 31.40 tcb lock, tid = 26
> 1 199 199.00 tcb lock, tid = 28
> 1 59 59.00 tcb lock, tid = 30
> 38249 1517078 39.66 tcb lock, tid = 8767413
> 226 10804 47.81 pool po_lock, name = global
> 87315 713872 8.18 pool po_lock, name = mt
> 16368593 557532226 34.06 pool po_lock, name = 65
> 1 69 69.00 fast mutex, bhash[31369]
> 1 6 6.00 fast mutex, bhash[31372]
> 1 96 96.00 fast mutex, bhash[31373]
> 21 238 11.33 fast mutex, bhash[31374]
> 8 49 6.12 fast mutex, bhash[31375]
> 19 230 12.11 fast mutex, bhash[31376]
> 6 16 2.67 fast mutex, bhash[31377]
> 1 7 7.00 fast mutex, bhash[31617]
> 1 8 8.00 fast mutex, bhash[31618]
> 1 112 112.00 fast mutex, bhash[31620]
> 7 105 15.00 fast mutex, bhash[31621]
> .....(714 more entries clipped)
> 52 249 4.79 fast mutex, lru-0
> 42 716 17.05 fast mutex, lru-2
> 36 122 3.39 fast mutex, lru-4
> 35 120 3.43 fast mutex, lru-6
> 31 119 3.84 fast mutex, lru-8
> 31 150 4.84 fast mutex, lru-10
> 1 1 1.00 fast mutex, lru-11
> 26 85 3.27 fast mutex, lru-12
> 1 2 2.00 fast mutex, lru-13
> 19 75 3.95 fast mutex, lru-14
> 26 87 3.35 fast mutex, lru-16
> 34 1174 34.53 fast mutex, lru-18
> 28 84 3.00 fast mutex, lru-20
> 44 685 15.57 fast mutex, lru-22
> 1 1 1.00 fast mutex, lru-23
> 36 478 13.28 fast mutex, lru-24
> 44 180 4.09 fast mutex, lru-26
> 26 597 22.96 fast mutex, lru-28
> 2 2 1.00 fast mutex, lru-29
> 34 228 6.71 fast mutex, lru-30
> 35 194 5.54 fast mutex, lru-32
> 3 15 5.00 fast mutex, lru-33
> 41 194 4.73 fast mutex, lru-34
> 2 14 7.00 fast mutex, lru-35
> 34 116 3.41 fast mutex, lru-36
> 5 42 8.40 fast mutex, lru-37
> 38 592 15.58 fast mutex, lru-38
> 1 2 2.00 fast mutex, lru-39
> 42 153 3.64 fast mutex, lru-40
> 34 174 5.12 fast mutex, lru-42
> 37 99 2.68 fast mutex, lru-44
> 48 165 3.44 fast mutex, lru-46
> 28 97 3.46 fast mutex, lru-48
> 31 112 3.61 fast mutex, lru-50
> 43 149 3.47 fast mutex, lru-52
> 44 202 4.59 fast mutex, lru-54
> 42 201 4.79 fast mutex, lru-56
> 30 151 5.03 fast mutex, lru-58
> 37 116 3.14 fast mutex, lru-60
> 48 158 3.29 fast mutex, lru-62
> 1 1 1.00 fast mutex, bf[3348]
> 1 44 44.00 fast mutex, bf[11460]
> 1 44 44.00 fast mutex, bf[13238]
> 1 1 1.00 fast mutex, bf[15941]
> 1 1 1.00 fast mutex, bf[17050]
> 1 1 1.00 fast mutex, bf[20776]
> 7 49 7.00 fast mutex, bf[32080]
> 62063 780973 12.58 fast mutex, bf[32369]
> 4 176 44.00 fast mutex, bf[35513]
> 1 1 1.00 fast mutex, bf[40486]
> 1 39 39.00 fast mutex, bf[51066]
> 16 846 52.88 fast mutex, bf[53422]
> 26 492 18.92 fast mutex, bf[74967]
> 122159 2555142 20.92 fast mutex, bf[75595]
> 113441 4622047 40.74 fast mutex, bf[83747]
> 34236 515948 15.07 fast mutex, bf[85107]
> 1 1 1.00 fast mutex, bf[91699]
> 2 15 7.50 fast mutex, bf[92268]
> 1 53 53.00 fast mutex, bf[94211]
> 1 23 23.00 fast mutex, bf[95886]
> 6 90 15.00 fast mutex, bf[96245]
> 7 20 2.86 fast mutex, flush
>
>
>
>
>
>

--

=== Michael Mueller ==================
Tel. + 49 8171 63600
Fax. + 49 8171 63615
Web: [url]http://www.mm.kay-mueller.de[/url]
[url]http://www.planets.kay-mueller.de[/url]
======================================

sending to informix-list