Re: Best Use of Free Memory

On 24 Oct, 21:01, "Kevin Cherkauer" <invalid_addr...@nowhere.com> wrote: > BUFFERS is generally a good place place to use extra memory, subject to a > couple caveats: > > 1. If the working set already fits in the current buffer pool, adding still > more buffers will not produce any noticeable performance increase. Check > "onstat -g buf" output for your %cached. Reads should be in the high 90's > (e.g. 97-98%), and writes about 90%. If you are already at these numbers, > the potential upside from more buffers is likely small. > > 2. Time taken to do a checkpoint against a very large buffer pool containing > a high percentage of dirty buffers is an issue in pre-11.10 versions. > Starting in 11.10 the checkpoint algorithm was rewritten to do pretty much > all of its work in the background (the "non-blocking checkpoints" feature), > only locking writers out for a very short moment to establish a consistent > point. The actual buffer flushing is all done without blocking any queries > anymore, and that is 99.99% of the time used by a checkpoint. So this is one > really only an issue before the 11.10 release. If you are pre-11.10, you can > still minimize checkpoint times with large buffer pools by setting the > LRU_MIN_DIRTY and LRU_MAX_DIRTY onconfigs to low values to keep most of the > buffers clean. > > -- > Kevin Cherkauer > Software Engineer > IBM Informix Dynamic Server -- Database Kernel > > <nate...@gmail.com> wrote in message > > news:262b4e36-4b88-4121-a4dc-23bb396e2c30@79g2000hsk.googlegroups.com... > > > > > In general, I'm just wondering about how to use additional free memory > > for Informix. We have a lot of boxes that might have 4,6, even 8+ GB's > > of memory that is never used. > > > The only places I know to use it are SHMVIRTSIZE and BUFFERS. I was > > under the impression that SHMVIRTSIZE only needs to be sized so that > > no additional segments show up in onstat -g seg. > > And for BUFFERS, there are of course Checkpoint implications of making > > that number too large. > > > Any general advice? Links are fine if anyone has any good ones. > > > Nate- Hide quoted text - > > - Show quoted text - As the number of buffers go up the lru chains get longer and so the time to find a buffer can increase (if the buffer is further down an LRU queue at the time). As buffers are normally distributed unevenly across lru queues the max length for an lru queue does not even go up neccessarily proportionally to the number of buffers, it all depends on the workload. http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.admin.doc/ids_admin_0400.htm "When a user thread needs to acquire a buffer, the database server randomly selects one of the FLRU queues and uses the oldest or least- recently used entry in the list. If the least-recently used page can be latched, that page is removed from the queue. If the FLRU queue is locked, and the end page cannot be latched, the database server randomly selects another FLRU queue. " NOTE: The last bit, if latch is per lru queue, changing the number of buffers changing the access pattern and hence amount of latch contention. If everything is already in buffers then changing the distribution of buffers will change the way the latches are used and so the amount of latch contention (either postively or negatively depending upon the application and usage pattern at the time). The docs do not mention what happens if the lru latch is held when the session thread attempts to put the buffer back onto either the original FLRU queue or a MLRU queue and that latch is already held (the Informix FAQ mentioned LRUPOLICY) that implies it can either decide to use a different LRU queue or wait. I would query the number of used buffers: http://groups.google.co.uk/group/comp.databases.informix/browse_thread/thread/9d9ec5a62b3184ec/9667a056077097d9?hl=en&lnk=st&q=sysbufhdr+select#9667a056077097d9 -- IDS 9.40+ select count(*) into usedbuffs from sysbufhdr where offset >= 0 and chunk > 0; -- IDS pre- 9.40 select count(*) into usedbuffs from sysbufhdr where pagenum > 0; and see how much you are currently using.

Longer chains do mean more latch contention, however there is a very simple solution -- increase the number of chains. These are very cheap -- the overhead is roughly the amount of memory it takes to hold a couple extra latches and pointers, which is only in the tens of bytes per chain. If you use the new-style BUFFERPOOL onconfig parameter, the number of chains is controlled by the value of the "lrus" field. If you are using the old-style BUFFERS onconfig parameter, the number of chains is controlled by the separate (old-style) LRUS parameter. Contention for chain latches is a function of several things -- at least the following: -- number of buffers per chain -- number of concurrently executing threads -- number of CPUs / cores / hardware threads It should be no problem to increase the number of chains from the default value of 8 up to much larger numbers. I have used over 100 chains on some large systems. As with most things, the law of diminishing returns applies, but if you have a hugely parallel system with lots of buffers, concurrent threads, and CPUs, you can see incremental performance gains even with a hundred or more chains. If the chain latch is held when needed, my recollection is it does not wait and instead tries the next chain, going through the chains in circular fashion until it is able to latch one. The buffers in fact should be pretty evenly distributed across chains -- actually across pairs of chains. Each chain is really two chains: one for clean and another for dirty pages. The clean and dirty chains may be radically different sizes (for example, if you keep your buffer pool 20% dirty, we would expect to see four times as many buffers on the clean chains as on the dirty chains), but the sum of (clean + dirty) for each pair should be roughly constant across pairs. -- Kevin Cherkauer Software Engineer IBM Informix Dynamic Server -- Database Kernel >> <nate...@gmail.com> wrote: > As the number of buffers go up the lru chains get longer and so the > time to find a buffer can increase (if the buffer is further down an > LRU queue > at the time). > As buffers are normally distributed unevenly across lru queues the max > length for an lru queue does not even go up neccessarily > proportionally to the number of buffers, it all depends on the > workload. > The docs do not mention what happens if the lru latch is held when the > session thread attempts to put the buffer back onto either the > original FLRU queue or a MLRU queue and that latch is already held > (the Informix FAQ mentioned LRUPOLICY) that implies it can either > decide to use a different LRU queue or wait.

I apologize for saving the wrong attribution on the portion I quoted in my reply below. It should be <david@smooth1.co.uk>, not <nate...@gmail.com>. Fixed below. -- Kevin Cherkauer Software Engineer IBM Informix Dynamic Server -- Database Kernel "Kevin Cherkauer" <invalid_address@nowhere.com> wrote in message news:ge89to$g0m$1@aioe.org... > Longer chains do mean more latch contention, however there is a very > simple solution -- increase the number of chains. These are very cheap -- > the overhead is roughly the amount of memory it takes to hold a couple > extra latches and pointers, which is only in the tens of bytes per chain. > If you use the new-style BUFFERPOOL onconfig parameter, the number of > chains is controlled by the value of the "lrus" field. If you are using > the old-style BUFFERS onconfig parameter, the number of chains is > controlled by the separate (old-style) LRUS parameter. > > Contention for chain latches is a function of several things -- at least > the following: > -- number of buffers per chain > -- number of concurrently executing threads > -- number of CPUs / cores / hardware threads > > It should be no problem to increase the number of chains from the default > value of 8 up to much larger numbers. I have used over 100 chains on some > large systems. As with most things, the law of diminishing returns > applies, but if you have a hugely parallel system with lots of buffers, > concurrent threads, and CPUs, you can see incremental performance gains > even with a hundred or more chains. > > If the chain latch is held when needed, my recollection is it does not > wait and instead tries the next chain, going through the chains in > circular fashion until it is able to latch one. > > The buffers in fact should be pretty evenly distributed across chains -- > actually across pairs of chains. Each chain is really two chains: one for > clean and another for dirty pages. The clean and dirty chains may be > radically different sizes (for example, if you keep your buffer pool 20% > dirty, we would expect to see four times as many buffers on the clean > chains as on the dirty chains), but the sum of (clean + dirty) for each > pair should be roughly constant across pairs. > > -- > Kevin Cherkauer > Software Engineer > IBM Informix Dynamic Server -- Database Kernel > > <david@smooth1.co.uk> wrote: >> As the number of buffers go up the lru chains get longer and so the >> time to find a buffer can increase (if the buffer is further down an >> LRU queue >> at the time). > >> As buffers are normally distributed unevenly across lru queues the max >> length for an lru queue does not even go up neccessarily >> proportionally to the number of buffers, it all depends on the >> workload. > >> The docs do not mention what happens if the lru latch is held when the >> session thread attempts to put the buffer back onto either the >> original FLRU queue or a MLRU queue and that latch is already held >> (the Informix FAQ mentioned LRUPOLICY) that implies it can either >> decide to use a different LRU queue or wait.

On Tue, Oct 28, 2008 at 7:14 PM, david@smooth1.co.uk <david@smooth1.co.uk>wrote: <SNIP> > > > > As the number of buffers go up the lru chains get longer and so the > time to find a buffer can increase (if the buffer is further down an > LRU queue > at the time). > David, If the engine needs to access a specific page that is already in the buffer cache, it is not searched for in the LRU queues, there is a hash table that is used to locate the buffer page that contains the specific disk page so the number of buffers does NOT affect the time needed to locate data in memory. The LRU queues are primarily used to decide which existing page in the cache to overwrite when another page needs to be read in from disk or a new page created and to quickly locate dirty pages that require cleaning. Your points about LRU latching are certainly valid, as is Kevin's reply that you can sometimes increase (or simply change) the number of LRU queues to reduce contention for the LRU latches. However, Kevin misses the point that the number of LRU queues is limited to 128 in 32bit releases and 512 in 64bit releases which limits ones ability to make that adjustment in busier environments. Art -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Hello Nate, Back to your original question about what to do with your extra memory. RAMDRIVE is free and can be used for many things: 1. Unix swap space. 2. Applications. 3. Informix tempdbs (if it is "cooked"). -L.S.