Re: Buffer sizing

Hi Bill, Buffer waits occurr if several threads want to access the same page at a time. ( i.e. one user wants to read the first row of a page, a second one wants to read the third row of the same page. The second one has to wait for the buffer until the first user will release the buffer. This is not a DB locking problem. If some users read different adjacent rows of a table this might cause a lot of buffer waits. The same problem could arise if you have too many cleaner threads, because they access your buffers, too. But be carefull: The number of buffer waits is not as interesting as the amount of time you spent for the buffer waits ! Next, foreground writes occurr if there are no more free pages inside your LRUs. If a sqlexec-thread wants to load a new page into your buffer, then it will first look, if there is a free buffer in the LRUs. The sqlexec-thread will look at all the "unlocked" LRU Free Queues ( page cleaner threads temporary place a lock on the queues during their sync operation ) and will try to find a free page. If the sqlexec-thread can't find a free page it will do a foreground write operation. Several circumstances might cause foreground writes. + page cleaner threads cannot perform the sync operation very quick. ( Slow I/O subsystem ) + Someone modifies your buffer pool in a very short time ( INSERT INTO a_table SELECT some_data FROM a_cached_table ) + Heavy activity after a long period of idle time. ( Cleaner threads calculate their snooze-time depending on the last time spent for the sync operation ). A few foreground writes per day do not mean a real problem. Bill Weaver wrote: > > I've been reading the Performance and Tuning manual and have some questions > regarding the amount of buffers to use. I'm running ODS 7.22 on a system with a > Gig of memory and a 4K page size. According to the manual on page 2-29, it says > that buffers should be set between 20-25% of physical memory. Ours is currently > set at 15,000 which is low according to the recommendation. Our read/write buffer > utilization is 95+% and 90+% respectively which would seem to indicate the we do > not need the excess buffers. However, bufwaits continues to climb (it's about > 1.15% of the read + write pages total) and we occassionaly have foreground writes > (right now there have been 2 today). I have 19 dbspaces, 1 chunk per space, > running kaio, 25 LRUs, 18 cleaners and min/max dirty set at 7 and 3 so we're > flushing almost constantly (currently 72.8% of writing is being done by the > cleaners in non-checkpoint times). Checkpoint duration is at 5 minutes and run > usually between 1-3 seconds. The disks/controller doesn't seem to be saturated > yet so it doesn't appear the bottleneck is there. Why is bufwaits climbing and > why do I get these foreground writes when it appears that plenty of buffers are > available? Do I need to up buffers to the recommended levels? Do I need more > lrus or cleaners? Am I missing something? Do I really even have a problem? > > By the way, we run a separate instance of Informix on this box also. However, it > is for development so it is very scaled down in terms of the resources allocated > to it. There are no other applications running on the box. BTW, if there is no customers complain, you do not have a performance problem. Why should you tune your system if you will not get paid for it ? The Informix Performance Guide is a guideline how to configure an OnLine instance on a dedicated database server. It's very good and I would suggest that you reread the chapter concerning the LRUS and CLEANERS parameters. ( Set LRUS to the number of CPU-vp's, if you have more than 4 CPU-vp's. Do you really have 25 CPU-vp's ? Okay, you should have 25 LRUS if you need 25 CLEANERS. But 25 CLEANERS would be neccessary if you could write data to 25 disks concurrently. Did you make this test ? I saw a SEQUENT machine with 80 disks, but I could write to only six disks concurrently ( bus problem ). Any comments ? Bye Stefan