RE: Too many Locks
What makes you think it's locks? You might want to first identify your
performance problem. Are you bottlenecked on some OS resource? Check your
sar statistics when you are expierencing this performance problem. sar -u
for CPU, sar -d for disk and sar -w for memory(swapping).
If you don't see any problems there, you can run this SQL against the
sysmaster database(is the sysmaster database there in 7.24?) to identify
users waiting on locks:
select dbsname,
b.tabname,
rowidr,
keynum,
e.txt type,
d.sid owner,
hex(d.address) ownrstcb,
g.username ownname,
j.pid ownpid,
f.sid waiter,
hex(f.address) waitrstcb,
h.username waitname,
i.pid waitpid
from syslcktab a,
systabnames b,
systxptab c,
sysrstcb d,
sysscblst g,
flags_text e,
sysrstcb f , sysscblst h, syssessions i, syssessions j
where a.partnum = b.partnum
and a.owner = c.address
and c.owner = d.address
and a.wtlist = f.address
and d.sid = g.sid
and e.tabname = 'syslcktab'
and e.flags = a.type
and f.sid = h.sid
and h.sid = i.sid
and d.sid = j.sid
into temp A;
select
tabname,
type[1,4],
owner,
ownname ,
waiter,
waitname
from A;
Regards,
Bill Dare
> -----Original Message-----
> From: Jay [SMTP:remove:jay@td.ca]
> Sent: Sunday, July 20, 2003 1:44 PM
> To: informix-list@iiug.org
> Subject: Too many Locks
>
> Hi,
>
> I'm having a major decrease in performance, and I'm wondering if the
> number
> of spin locks 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