Informix

Hi Dennis,

A session waiting on a buffer simply means that it is waiting on a free spot in the bufferpool pages.  That could be because it is waiting for a dirty page to be written to disk, or just searching the LRU queue to find an empty slot to use.  Waiting on a buffer is completely normal, but if the waiting is excessive that it may impact performance.  Art Kagel recommends calculating the buffer wait ratio - use onstat -p, or onstat -g buf and calculate bufwaits/(pagreads + bufwrits) *100.  You would want to see a value less than 7%.

As with many things, reducing the buffer waits may be a combination of various parameters and there is no simple answer.  You can increase the number of buffers (BUFFERPOOL), and increasing the number of LRUs will help.  The buffers are distributed over the number of LRUs and if you have lots of buffers split over relatively few LRUs then it will take longer to search these queues for a free page.  Increasing the LRU min/max dirty may help as this will reduce the number of dirty pages in the queues.  BTW, check onstat -F to make sure that there are NO foreground writes.  Use Art's buffer turnover ratio (BTR) calculation to make sure that you are making good use of your bufferpools. - if your bufferpool is too small with high turnover then you'll see more things waiting on buffers as more stuff needs to be read in from disk.

Remember though that you shouldn't try to prevent all buffer waits, but first check to see that they are not excessive.

Mike 

Just a couple of addenda to Mike's response:

• When Mike said you could "increase the LRU min/max dirty settings" he mean decrease those values so that dirty buffers are flushed more aggressively. If you do that, you may need to increase the CLEANERS parameter to allow for more parallelism during those flushes, especially if you have many LRU queues or many chunks.
• Note on the BTR: I currently no longer use the original BTR because on any insert-heavy system the BTR value is artificially inflated. I currently use the newer BTR3 calculation which nearly always returns the same (or substantially identical) value as BTR for balanced systems but on insert-heavy systems it return a more accurate, lower, value which can be used to better predict if more buffers are required.
• No need to calculate these and a few other metrics yourself. Just go to my web site (www.askdbmgt.com/my-utilities) and download the ratios.shr_ak package. It contains source for a stored procedure to be installed in your sysmaster database, and a shell script, newratios.ksh, that formats a report of the metric calculations.

Thanks Art - yes I meant to say LOWER the values for the lru min/max.  That way you will get more writes to disk between the checkpoints and hopefully increasing the number of free pages in the bufferpool.  Also, I am glad to see that I am not the only one to type "rations" instead of "ratios"!  :-) 

Mike, Art,

Thank you so much for your answers.

My issue is as follows,

Typical level of sessions waiting for a buffer does not exceed 1 (one). But at that unhappy moment those sessions reached 562. At the same time sessions waiting for a lock grew up to 44. And then in 8 minutes it all dropped back to 0-1.

We have no foreground writes (onstat -F). No record in OS's error log (AIX).
What else to check if the situation get back?

With around 600 concurrent queries running sometimes (as when the number of sessions waiting for buffers exceeds 560 and 44 sessions waiting for locks) it may be that you do not have enough LRU queues. For that level of concurrent sessions I would want each buffer pool to have about 256 LRU queues.

What happens when a session needs to read a new page into a buffer pool is that the session hashes to one of the LRU queues in that pool and checks to see if the queue is latched by another session trying to do the same. If so, it will spin on the lock for a few thousand loops then if it still cannot latch that queue it will rehash to another queue. If there are not enough lru queues this poor session will not be the only one that cannot get the latch so many sessions will chase each other around the buffer pool's lru queues essentially single threading access. More queues means there are more available latches and their queues. Some of them might not be locked reducing bufwaits (which are most often not buffer waits at all but ... lru latch waits). I would note that this very issue what the one that originally got me interested in performance tuning and to develop the metrics that I have used now for 30+ years.

Sometimes, yes, as Mike pointed out, having not enough buffers or not flushing aggressively enough (in the onstat -F report I want to see about 60-70% of all writes being LRU writes and only 30-40% chunk writes, if you flushes are REALLY to infrequent or tame, you will see FGwrites, which you are not seeing). The BTR3 will tell you if you have enough buffers (BTR3 < 10 turns/hour) and the onstat -F report will tell you if you are flushing aggressively enough.

If you cannot figure this one out, then I would suggest engaging one of the consulting groups that specialize in Informix, those being Mike Walker and Tom Beebe's xDB Systems, Paul Watson's Oninit, or myself at ASK Database Management. That's what we do.