Re: high buffer wait ratio

miyaki wrote: >My database engine is maainly serving OLTP type of >transaction and some minor DSS type of batch >processing. > >The total physical memory is 2GB and 4CPU. > >Here's some of the info requested by you guys. > > 1) 0 "zero" FG writes LRU vs. Chunk writes? > 6) Checkpoint was at 6-9 seconds during peak hours > (where 600 of OLTP transactions running concurrently > with 2 DSS type of batch jobs.) The average checkpoint > is at 3-5 seconds. 6-9 seconds is definitely noticable and painful. If you put the CKPTINTVL back to the default 300, then it may reduce that - but I'm assuming that LRU writes aren't doing anything for that to hold. If 300 still manages to fill it to the point of LRU writes, then you won't see much difference. > 8) onstat -F | head -7 > Fg Writes LRU Writes Chunk Writes > 0 3867446 38905 Oh there it is :) OK that's a helluva lot of LRU writing going on. The only way to bring down your checkpoint duration is to make the LRU writes pedal harder. Or spread the load better on your disks. Ask questions about this... >11) We have been using the RA_PAGES and RA_THRESHOLD >value since day one until now. Using the following >formula, I can get 99.9% constantly. Do I really need >to reduce the values? > >(RA-pgsused / (ixda-RA + idx-RA + da-RA))*100 Welll, an RA hit-rate of 99.9% is indeed good, but then again, I'll bet you will still get a 99% hitrate when you reduce the size of this. The counter-argument to large RA reads is: what *could* the engine be doing instead of reading very large read ahead buffers? If the RA hit rate can still be very high using smaller sizes, then clearly the engine will do less RA work in between doing other important work like .... LRU writes, for example. Any RA hitrate > 95% is good. You decide for yourself. I'd certainly be reducing until I see some noticable change in that percentage (eg a change of 0.1% and that means you'll be approaching the point where a change in RA sizes is actually noticable. >Some of the suggestion pointing to increase the number >of BUFFER. Ain't increasing the BUFFER will also >prolong the checkpoint time which is bad for OLTP type >of database? Juggling the LRU config parameters can keep a lid on this. However, you have 1,2 for the LRU params, and with version 7.31 you cannot go any lower. However, the load balance on your disks makes an enormous impact on checkpoint, LRU write and general SQL performance. Can you give us a bit of detail about your disk system? - size of disks - how many disks - any RAID/mirroring - highly cached disk array? and the money question: how many chunks on each disk, and what is the output from onstat -g iof ? Please correlate your chunks with the disks they are on.