bufwaits and RA_PAGES

Colleagues, we are using ODS 7.23UC3 on ReliantUnix 5.43C40 and HP-UX 10.20. One day i wondered about 3.2 Mio bufwaits in 13 days when i made an onstat -p.This means the DB waits for approx. 3 buffers per second. Now i assumed, that the DB is waiting for data and changed the RA_PAGES and RA_THRESHOLD, which were set to the default values 8/4. The new values are 2048/2044. As before RA-pgsused is within optimum range, i.e. ixda-RA +idx-RA +da-RA =~ RA-pgsused. Till now i could not find any negative effects caused by this changes. I cant say much about the bufwaits now, because i have to wait for about 13 days to compare the values. But now i'm in doubt if my theory, that the DB is waiting for data, is correct. Does bufwaits not much more mean we are running out of buffers? Here the onstat -p of the original configuration: INFORMIX-OnLine Version 7.23.UC3 -- On-Line -- Up 13 days 02:13:06 -- 264752 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 26987274 40054626 852915167 96.84 5888621 11755533 29804841 80.24 isamtot open start read write rewrite delete commit rollbk 589959413 2603707 5067771 524455727 3889689 1386607 3999533 1493788 178 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 304 0 0 228782.89 66313.98 3241 7502 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 3187033 676 1077706719 0 0 1689 633612 4344 ixda-RA idx-RA da-RA RA-pgsused lchwaits 7838508 2349058 7277041 17457824 133653 Here our onconfig: # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /dev/online_root # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 2059700 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirroredroot MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS rootdbs # Location (dbspace) of physical log PHYSFILE 40000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 12 # Number of logical log files LOGSIZE 10240 # Logical log size (Kbytes) # Diagnostics MSGPATH /home/informix/online.log # System message log file path CONSOLE /dev/null # System console message path ALARMPROGRAM /opt/lib/informix/etc/log_full.sh # Alarm program path # System Archive Tape Device TAPEDEV /dev/null # Tape device path TAPEBLK 16 # Tape block size (Kbytes) TAPESIZE 10000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV dummy # Log tape device path LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 20000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB ,1 # INFORMIX-OnLine/Optical staging area # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLineinstance DBSERVERNAME rm8a03 # Name of default database server NETTYPE tlitcp,1,100,NET # Override sqlhosts nettype parameters DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 3 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto 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 100000 # Maximum number of shared buffers NUMAIOVPS 2 # Number of IO vps PHYSBUFF 1024 # Physical log buffer size (Kbytes) LOGBUFF 1024 # Logical log buffer size (Kbytes)LOGSMAX 80 # Maximum number of logical log files CLEANERS 6 # Number of buffer cleaner processes SHMBASE 0x20000000 # Shared memory base address SHMVIRTSIZE 40000 # initial virtual shared memory segmentsize SHMADD 8192 # Size of new shared memory segments (Kbytes) SHMTOTAL 520000 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 6 # Number of LRU queues LRU_MAX_DIRTY 10 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 5 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water markpercentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) OFF_RECVRY_THREADS 10 # Default number of offline workerthreads ON_RECVRY_THREADS 1 # Default number of online workerthreads # 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 /opt/lib/informix/etc/dr.lostfound # DR lost+found filepath # Read Ahead Variables RA_PAGES 2048 # Number of pages to attempt to readahead RA_THRESHOLD 2044 # Number of pages left before next group DBSPACETEMP tmp1 # 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 1 # 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 dumpsfor # 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:

BUFWAITS can have three causes: 1) Severe buffer starvation (ie need lots more buffers) 2) Excessive readahead, or RA_PAGES & RA_THRESHOLD too large or close together 3) Too few LRUS (most commmon cause!) Your new RA parameters are IMMENSE, unless your storage farm us made up of several older clerks with pencil and keyboard ;-) you cannot need 2K read ahead, chances are that will push the bufwaits higher! To determine if your BUFWAITS is reasonable given your load (some waits are unavoidable as many processes may just need the same buffers at the same time and so will have to wait) calculate the BUFWAIT Ratio (BR): BR = (bufwaits / (dskreads + bufwrits)) * 100.00 A reasonable BR value is under 10% with the ideal at 7% or less. Anything over 10% is usually death. For the onstat -p you posted the formula gives: BR = (3187033 / (40054626 + 29804841)) * 100.00 = 4.56% Which is excellent! No worries mate. At least not on this one. Note that NOAGE is very important on HP as HP/UX is aggressive about aging long running processes meaning that before long your oninits are running at a very low priority! Unless nothing else is running on that machine this can severely affect performance. FWIW. [SNIP] > On this special machine the DB resides on normal disks, but usually we > use RAID5 - would this > make any difference? Singleton disks will have slower overall response time and require more readahead than any striped array. BTW I STRONGLY recommend AGAINST using RAID5 for Informix database disks (my reasons are elsewhere in the CDI archives search for it) and I recommend instead RAID10 first choice followed by RAID 3 or RAID 4. If you are using singleton drives, are you using Informix mirroring for redundancy? Good idea! Art S. Kagel

OK Art - sounds like you've got a good little formula, but you've got me confused. In your formula you say to use dskreads, but when you plug the values here, you used pagereads, which can make a big difference. Which one is right? Thx... In article <3805FBB1.D906C7A5@bloomberg.net>, kagel@bloomberg.net wrote: > BUFWAITS can have three causes: > > 1) Severe buffer starvation (ie need lots more buffers) > 2) Excessive readahead, or RA_PAGES & RA_THRESHOLD too large or close > together > 3) Too few LRUS (most commmon cause!) > > Your new RA parameters are IMMENSE, unless your storage farm us made up of > several older clerks with pencil and keyboard ;-) you cannot need 2K read > ahead, chances are that will push the bufwaits higher! To determine if > your BUFWAITS is reasonable given your load (some waits are unavoidable as > many processes may just need the same buffers at the same time and so will > have to wait) calculate the BUFWAIT Ratio (BR): > > BR = (bufwaits / (dskreads + bufwrits)) * 100.00 > > A reasonable BR value is under 10% with the ideal at 7% or less. Anything > over 10% is usually death. For the onstat -p you posted the formula > gives: > > BR = (3187033 / (40054626 + 29804841)) * 100.00 = 4.56% > > Which is excellent! No worries mate. At least not on this one. > > Note that NOAGE is very important on HP as HP/UX is aggressive about aging > long running processes meaning that before long your oninits are running > at a very low priority! Unless nothing else is running on that machine > this can severely affect performance. FWIW. > > [SNIP] > > On this special machine the DB resides on normal disks, but usually we > > use RAID5 - would this > > make any difference? > > Singleton disks will have slower overall response time and require more > readahead than any striped array. BTW I STRONGLY recommend AGAINST using > RAID5 for Informix database disks (my reasons are elsewhere in the CDI > archives search for it) and I recommend instead RAID10 first choice > followed by RAID 3 or RAID 4. If you are using singleton drives, are you > using Informix mirroring for redundancy? Good idea! > > Art S. Kagel > Sent via Deja.com http://www.deja.com/ Before you buy.

OOPS typo. The correct value is pagreads NOT dskreads. Sorry all. Art S. Kagel ktroxel@my-deja.com wrote: > > OK Art - sounds like you've got a good little formula, but you've got > me confused. In your formula you say to use dskreads, but when you > plug the values here, you used pagereads, which can make a big > difference. Which one is right? > > Thx... > In article <3805FBB1.D906C7A5@bloomberg.net>, > kagel@bloomberg.net wrote: > > BUFWAITS can have three causes: > > > > 1) Severe buffer starvation (ie need lots more buffers) > > 2) Excessive readahead, or RA_PAGES & RA_THRESHOLD too large or close > > together > > 3) Too few LRUS (most commmon cause!) > > > > Your new RA parameters are IMMENSE, unless your storage farm us made > up of > > several older clerks with pencil and keyboard ;-) you cannot need 2K > read > > ahead, chances are that will push the bufwaits higher! To determine > if > > your BUFWAITS is reasonable given your load (some waits are > unavoidable as > > many processes may just need the same buffers at the same time and so > will > > have to wait) calculate the BUFWAIT Ratio (BR): > > > > BR = (bufwaits / (dskreads + bufwrits)) * 100.00 > > > > A reasonable BR value is under 10% with the ideal at 7% or less. > Anything > > over 10% is usually death. For the onstat -p you posted the formula > > gives: > > > > BR = (3187033 / (40054626 + 29804841)) * 100.00 = 4.56% > > > > Which is excellent! No worries mate. At least not on this one. > > > > Note that NOAGE is very important on HP as HP/UX is aggressive about > aging > > long running processes meaning that before long your oninits are > running > > at a very low priority! Unless nothing else is running on that > machine > > this can severely affect performance. FWIW. > > > > [SNIP] > > > On this special machine the DB resides on normal disks, but usually > we > > > use RAID5 - would this > > > make any difference? > > > > Singleton disks will have slower overall response time and require > more > > readahead than any striped array. BTW I STRONGLY recommend AGAINST > using > > RAID5 for Informix database disks (my reasons are elsewhere in the > CDI > > archives search for it) and I recommend instead RAID10 first choice > > followed by RAID 3 or RAID 4. If you are using singleton drives, are > you > > using Informix mirroring for redundancy? Good idea! > > > > Art S. Kagel > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.

Art, thanks for your reply > Your new RA parameters are IMMENSE, I agree with you, BUT take a look at my new onstat -p: INFORMIX-OnLine Version 7.23.UC3 -- On-Line -- Up 6 days 17:12:05 -- 266024 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 14853249 21452721 440733276 96.63 3003612 6038936 15158192 80.18 isamtot open start read write rewrite delete commit rollbk 276228144 1307835 2550587 243151708 1978592 709893 2025501 760813 96 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 300 0 0 122651.72 32464.29 1682 3848 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 595469 361 504643120 0 0 797 324649 2319 ixda-RA idx-RA da-RA RA-pgsused lchwaits 5358224 975963 5029247 11357347 93803 > For the onstat -p you posted the formula > gives: > > BR = (3187033 / (40054626 + 29804841)) * 100.00 = 4.56% > > Which is excellent! No worries mate. At least not on this one. > My new BR=1.63% .... the bufwaits decreased significantly ... what is your comment? All onstat -p parameters still seem to be ok to me, just the lchwaits seem to have increased. Possibly the BR improved although the immense values of the RA-parameters, because our indices also are immense and scanned quite often. On this machine we have a 2GB table with a 250MB index. So i assumed, that a 4MB read ahead is nothing compared to the index size .... this is why i chose those immense values. And this is just a small test configuration, the biggest index at one customer is 1.2GB. Unfortunately i have no access to those machines now - so i cannot check the situation there. (BTW: we dont have real problems with the DB performance because the application which uses the DB is so slow, that you dont notice even disastrous misconfigurations. For example i saw a BR=11% yesterday ...but the customer never protested ... ) > BTW I STRONGLY recommend AGAINST using > RAID5 for Informix database disks I know, i know ... but i have no influence on that. Neither on hardware nor on the database structure. But i changed the RA-parameters on a machine with RAID5 also .. the result was the same .. new BR~=2% . Wolf D. Pichler Remove the uppercase words from my e-mail address to reply.