SEO

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

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>