Re: Performance Problem with many buffer wait
In article <380EA750.7E5DD882@netvigator.com>, Grace Kan
<hykan@netvigator.com> writes
>Hi all informix expert,
>
> I am now using Informix Online 7.30, and I experience a lot of
>buffer wait, that is a B flag in first position in onstat -u, which make
>my informix database extremely slow.
>
>c4ad7d24 B--PR-- 50686 hwchung ttyud c22403a8 0 1 1238
>64
>
> So why this happen and how to avoid it. And one more question,
>why not the onstat -u show lock wait ( L flag in the first position)
>rather than buffer wait, as user thread getting a buffer by obtain a
>share lock or exclusive lock on that buffer.
>
> Looking forward to have your reply
>
What hardware / Operating System are you using.
Which Operating System?
How many CPUS in teh machine?
How much memory in the machine?
How many disks? or what size?
What does onstat -d and onstat -D give?
>Best Regards,
>Grace
>ps. attached is onstat -c and onstat -p
>
>#**************************************************************************
>
>#
># INFORMIX SOFTWARE, INC.
>#
># Title: onconfig.01
># Description: INFORMIX-OnLine Configuration Parameters
>#
>#**************************************************************************
>
># Root Dbspace Configuration
>
>ROOTNAME rootdbs # Root dbspace name
>ROOTPATH /dev/rootdcs.1 # Path for device containing root>dbspace
>ROOTOFFSET 0 # Offset of root dbspace into device
>(Kbytes)
>ROOTSIZE 204800 # 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 10240 # Physical log file size (Kbytes)>
Can you move the physical log to a separate dbspace on it's own disk?
># Logical Log Configuration
>
>LOGFILES 94 # Number of logical log files
>LOGSIZE 4000 # Logical log size (Kbytes)>
Same for logical log files...
># Diagnostics
>
>MSGPATH /usr2/informix7/co2dcs.log # System message log file
>path
>CONSOLE /dev/console # System console message path>ALARMPROGRAM /usr2/informix7/log_full.sh # Alarm program path
>
># System Archive Tape Device
>
>TAPEDEV /dev/rmt/0m # Tape device path
>TAPEBLK 16 # Tape block size (Kbytes)
>TAPESIZE 8192000 # Maximum amount of data to put on tape
>(Kbytes)>
!! 8Gb tape, sounds like a big instance.
># Log Archive Tape Device
>
>LTAPEDEV /dev/rmt/0m # Log tape device path
>LTAPEBLK 16 # Log tape block size (Kbytes)
>LTAPESIZE 8192000 # Max amount of data to put on log tape
>(Kbytes)>
Logical log tape 8Gb! Definitely a big box!
># Optical
>
>STAGEBLOB # INFORMIX-OnLine/Optical staging area
>
># System Configuration
>
>SERVERNUM 6 # Unique id corresponding to a OnLine>instance
>DBSERVERNAME co2dcs # Name of default database server
>DBSERVERALIASES # List of alternate dbservernames
>NETTYPE ipcshm,1,512,CPU # Configure poll thread(s) for nettype
?? 1 CPU with 512 connections?
>NETTYPE soctcp,1,128,NET # Configure poll thread(s) for nettype
>DEADLOCK_TIMEOUT 60 # Max time to wait of lock in>distributed env.
>RESIDENT 1 # Forced residency flag (Yes = 1, No =
>0)
>
>MULTIPROCESSOR 1 # 0 for single-processor, 1 for>multi-processor
>NUMCPUVPS 2 # Number of user (cpu) vps
2 CPUS? Change NETTYPE for ipcshm to
NETTYPE ipcshm,2,512,CPU
>SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps>to one
>
>NOAGE 0 # Process aging
Set to 1.
>AFF_SPROC 0 # Affinity start processor
>AFF_NPROCS 0 # Affinity number of processors>
Set to
AFF_SPROC=1 and AFF_NPROCS=2
Reboot online
if this fails in online.log with
"nt enough processors for affinity"
set
AFF_SPROC=0 and AFF_NPROCS=2
Most of the time the first CPU is CPU 1, sometimes it is CPU 0!
># Shared Memory Parameters
>
>LOCKS 200000 # Maximum number of locks
>BUFFERS 20000 # Maximum number of shared buffers Increase to if you have enough memory. Allocate as much as you can to
buffers without running out of memory and swapping.
>NUMAIOVPS 11 # Number of IO vps Check if you can use KAIO for all chunks, if so set this to 1.
>PHYSBUFF 128 # Physical log buffer size (Kbytes)
>LOGBUFF 128 # Logical log buffer size (Kbytes) Sounds like either a big instance or buffered logging. Do you use
logging? If so are all database using unbuffered logging?
>LOGSMAX 100 # Maximum number of logical log files
>CLEANERS 8 # Number of buffer cleaner processes
>SHMBASE 0x0 # Shared memory base address
>SHMVIRTSIZE 163840 # initial virtual shared memory segment>size
>SHMADD 30000 # Size of new shared memory segments
>(Kbytes) Again seem like a big instance.
>SHMTOTAL 0 # Total shared memory (Kbytes).
>0=>unlimited
>CKPTINTVL 300 # Check point interval (in sec)
>LRUS 16 # Number of LRU queues
>LRU_MAX_DIRTY 5 # LRU percent dirty begin cleaning limit
>
>LRU_MIN_DIRTY 2 # 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 128 # 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 a