Re: BUFFERS setting

In article <32BACDF0.3467@informix.com>, Dave Kosenko <davek@informix.com> writes >Bill Ennis wrote: >> >> You need to consider that you will be reading from the buffers as well >> as writing to them. >> >> I would be careful lowering since your read cache percentage is already >> lower than the suggested 95%. > >Neil Truby wrote: >> } I'm running OnLine 7.13 on a Sun Sparcserver 20. It has 160M of physical >> } memory. We have a bought-in application which is very resource hungry. >> } At the suggestion of Tech Support I bumped BUFFERS up to 20K >> } (40MBytes). However, onstat -b consistently shows only a few hundred of >> } the 20,000 BUFFERS modified at any one time. Am I correct in inferring >> } that I therefore have BUFFERS too high, and could begin to reduce it >> } until I notice degradation in my read/write cache percentages (currently >> } 93 and 87 respectively)? > >Bill is absolutely correct. In fact, trying to tune buffers based on >write cacheing is pointless. Write cacheing as reported is really >meaningless, as ALL writes are cached - we NEVER write directly to disk >when updating/inserting/deleting a row, it is ALWAYS done via the >buffer pool. What write cache is telling you is the ratio of database >updates to page flushes. What really controls that is your checkpoint Well really it is page updates to page flushes. >interval and LRU flushing parameters. Decrease checkpoint interval and >increase LRU_MAX_DIRTY, and your write cache rate will go up (NOTE: THIS >DOES NOT CONSTITUTE A RECOMMENDATION, but is simply a point of >discussion. Surely decreasing checkpoint interval increases the amount of page flushes. E.g. The following timeline. Checkpoint interval 5 minutes T+0 checkpoint occurs T+3 minutes page 300 updates T+5 minutes page 300 flushed to disk T+7 minutes page 300 updated T+10 minutes page 300 flushed to disk Total = 2 pages updated, 2 page flushed Checkpoint interval 10 minutes T+0 checkpoint occurs T+3 minutes page 300 updates T+7 minutes page 300 updated T+10 minutes page 300 flushed to disk due to checkpoint Total = 2 pages updated, 1 pages flushed Increasing LRU_MAX_DIRTY MAY improve write caching as more dirty pages can be held in memory, hence more change of the same page being updated twice in between flushes. However only helps if updates occur on the same page. >DO NOT do this tuning to your system except as an exercise in >curiousity). >Therefore, when tuning BUFFERS, tune for read cacheing, as that is what >affects performance. The only time you need to consider tuning buffers >for writes is when you are seeing fg writes in onstat -F (which means >not enough buffers for the update activity occurring), which means you >need more of em. Whilst this is true. Increasing buffers WITHOUT CHANGING LRU_MAX_DIRTY also helps write caching as more dirty buffers can be held in memory and so there is more chance of multiple updates to the same page resulting in only one page flush. > >Furthermore, Neil is looking at things incorrectly. Onstat -b simply >shows you which buffers are LATCHED at the current time, which means the >buffer itself is being modified at that time. It does not indicate i.e having buffer contents changed. This occurs for two reasons:- 1. A free (unmodified buffer) is being used when data is read into a buffer (i.e. we are read caching reads and merely changing which page is being cached). 2. We are modifying a buffer which is already in shared memory. Note the page must generally first be read into memory since there are >1 row per page. And we need the value of the row on the page which is not being modified. >that the buffer is dirty (i.e. contains modified but unflushed data), >and >thus is no indication of the write performance of the system. > Correct. >Generally speaking, the method for tuning BUFFERS is rather obvious: >keep >increasing it until you see no improvement in the read cache value. > Agreed more cache always helps as long as you do not get swapping or paging. i.e. run out of physical memory. -- David Williams