Buffer Size and Use
A DBA asked whether sizing the buffer pool large enough to hold the entire 18 GB database (on new 64 GB servers) is worthwhile, after being advised that oversized buffer pools cost more than they gain, and whether RESIDENT should be used. Replies (Kagel, Nunes, Leffler) said a big cache is fine if the machine has spare memory and nothing else competes, but only the active working set matters, and with a 99.9% cache hit rate more memory brings little gain. RESIDENT simply pins shared memory so the OS can't swap it; it is unrelated to keeping tables in cache (the old per-table 'resident' feature was dropped after 7.30). Warnings against RESIDENT stem from AIX pinned-memory misconfiguration causing KAIO I/O errors, fixable via OS settings; also allow disk space for larger shared-memory dumps. Kagel also advised against Sun T4 servers for database work.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Folks, We recently had a conversation with a well-respected name in the Informix world, recently, concerning our bufferpool configuration. When exported, our database takes up only about 18 GB. We had a buffer pool of 12 GB, figuring that the more memory resident, the better. Also, we are currently in the midst of configuring some new T4-1 servers (with 64GB of memory) and figured that we could have a buffer pool >= 18 GB, and thus have a totally memory-resident database (and that that would be "a good thing"). We were advised that this is probably not a good idea, and that it is better to reduce the size of the buffer pool, because overhead made incremental cost exceed incremental benefit, after a certain bufferpool size. Casting no aspersion on anyone, just wondering if other Informix gurus have the same opinion: that fully resident database is not necessarily an advantage. Or maybe the point should be phrased in terms of absolute buffer size, as opposed to "fully resident", since even a very tiny database could be "fully resident" with a buffer pool of, say, 100 MB, and no one would argue that a bufferpool of 100MB is "too large", right? Thank you for any comments. Regards, DG
David: First as an important aside, I would strongly recommend against using the Oracle 'T' class servers prior to the T5 series processors. They perform poorly for the kind of large complex processes that are represented by the Informix onint process (same goes for Oracle BTW). Not a good match for databases in general. The older M-class servers running the UltraSparc processors are a better fit. Done. Having the entire working set of your database resident in cache cannot be a bad thing. Is 12GB enough or too much for an 18GB database? That depends on how much data is actually active at any given time. If you can estimate that volume then you know how big your cache needs to be to have the entire working set resident. There is no benefit for static data to be in memory and that includes recently inserted rows that will not be accessed for any reporting purpose until a weekly or monthly report many days later. Can too big a cache cause some performance issues? That will depend on many factors. Is the memory needed for other purposes? Is there enough physical memory to not cause swapping? Is the size going to cause the system's memory subsystem to exceed its own internal caches or cause slower memory to be accessed? For example the old Data General systems had part of their main memory resident on each CPU card. If a process running on a CPU on card #1 needed to access memory on card #2 the access had to go across the slower system bus rather than the faster bus within the local CPU card itself. That meant that on those systems we had to affine CPU VPs to processors on a single card and limit server memory to the memory on a single card. SMP systems do not generally experience these problems, but it is dependent on system architecture. Generally, if you have enough memory on the system so you are not interfering with other memory requirements by having a larger cache, I have not found that big caches negatively affect performance. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Feb 14, 2014 at 2:14 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Folks, > > We recently had a conversation with a well-respected name in the Informix > world, recently, concerning our bufferpool configuration. When exported, > our > database takes up only about 18 GB. We had a buffer pool of 12 GB, figuring > that the more memory resident, the better. Also, we are currently in the > midst > of configuring some new T4-1 servers (with 64GB of memory) and figured > that we > could have a buffer pool >= 18 GB, and thus have a totally memory-resident > database (and that that would be "a good thing"). > > We were advised that this is probably not a good idea, and that it is > better > to reduce the size of the buffer pool, because overhead made incremental > cost > exceed incremental benefit, after a certain bufferpool size. > > Casting no aspersion on anyone, just wondering if other Informix gurus have > the same opinion: that fully resident database is not necessarily an > advantage. Or maybe the point should be phrased in terms of absolute buffer > size, as opposed to "fully resident", since even a very tiny database > could be > "fully resident" with a buffer pool of, say, 100 MB, and no one would argue > that a bufferpool of 100MB is "too large", right? > > Thank you for any comments. > > Regards, > > DG > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c34dca44d78604f263312a
Thank you, Art, for your generous and rapid comments. Our management wanted to replace our existing V480s, so they bought us some T4-1's. We are grateful for whatever "meat" they occasionally toss in our cages. Our Informix servers have only one purpose: to be database servers. No other programs or users. So, unless we allocate it to Informix, the memory is just sitting there. Might as well hole even static data, I would say. So, we could configure a 32 GB buffer pool, have almost twice as much as we need for everything, and still have plenty (32 GB) for O/S, Informix virtual mem, etc. So, in light of your comments, my "take away" is why not throw everything at the database, make it all RESIDENT, and "let 'er rip". Someone had suggested to us that using "RESIDENT" may not have the desired effect of increasing performance, and recommended avoiding it. There was a comment to the effect that IBM was no longer recommending its use. I really value this person's opinions on other Informix issues, and don't want to offend in any way, but I was curious about how widespread this thinking was. I did an experiment, reducing the buffer cache to 8 GB, and noticed no change in performance. Still have about 99.9% hit rate. But, the memory is there, and not otherwise being used for anything. Might as well use it. Thank you, again. Regards, DG
RESIDENT has nothing to do with table residency in the cache btw. It controls whether shared memory is swappable or marked as nonswappable so always resident. In your case with lots of memory and no other processes it's unlikely to make a difference either way. No competition for physical memory after all. I always set RESIDENT unless memory is very tight. Art Art S. Kagel Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG or any other organization with which I sm associated either explicitely or implicitely not on individuals affiliated with those organizations. On Feb 14, 2014 3:40 PM, "DAVID GROVE" <david.grove@alaska.gov> wrote: > Thank you, Art, for your generous and rapid comments. > > Our management wanted to replace our existing V480s, so they bought us some > T4-1's. We are grateful for whatever "meat" they occasionally toss in our > cages. > > Our Informix servers have only one purpose: to be database servers. No > other > programs or users. So, unless we allocate it to Informix, the memory is > just > sitting there. Might as well hole even static data, I would say. > > So, we could configure a 32 GB buffer pool, have almost twice as much as we > need for everything, and still have plenty (32 GB) for O/S, Informix > virtual > mem, etc. > > So, in light of your comments, my "take away" is why not throw everything > at > the database, make it all RESIDENT, and "let 'er rip". > > Someone had suggested to us that using "RESIDENT" may not have the desired > effect of increasing performance, and recommended avoiding it. There was a > comment to the effect that IBM was no longer recommending its use. I really > value this person's opinions on other Informix issues, and don't want to > offend in any way, but I was curious about how widespread this thinking > was. > > I did an experiment, reducing the buffer cache to 8 GB, and noticed no > change > in performance. Still have about 99.9% hit rate. But, the memory is there, > and > not otherwise being used for anything. Might as well use it. > > Thank you, again. > > Regards, > > DG > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11344e5a80332804f263efa2
Thank you for educating me, Art. I will have to read up on the RESIDENT parm. So, if buffer cache exceeds the size of all tables combined, wouldn't using RESIDENT guarantee that all tables, once having been read in, would stay (because the resident memory stays)? DG
No... RESIDENT does not mean that. What you're referring was the SET RESIDENT... statement which lost it's effect around 9.40 with a change in the buffer management policy. (although a look at the latest sysmaster.sql can make us wonder...) RESIDENT as Art explained it to ask the OS to make sure the Informix segment memory can nver be swapped, or in some OS nomenclature, it's "pinned". The "rumour" about IBM advicing against it's use is related to (this happens so many times) some easy misconfiguration that customer could do on AIX (maybe on other platforms, but AIX became usual). This OS has some other configuration about how much memory the system can be configured "pinned". The defaults are not ok when very large buffer pools were used with RESIDENT set. The consequence was that during heavy I/O, the system could lack enough memory for KAIO buffers (whihc MUST belong to pinned memory)... and you would start to have nasty I/O errors with even more nasty consequences. This should be solved with proper OS configuration, but I grant it was a bit tricky/confusing... I believe we have a technote somewhere explaining this... yes.. thank you Google, here it is: http://www-01.ibm.com/support/docview.wss?uid=swg21608334 Regarding your initial questions... I can't exactly thinking about issues with big memory buffer pools... Certainly some things can take a bit longer... but I can't think of anything that would cause problems unless you do mess up your $ONCONFIG (imagine having 8 LRUs... or something like that. However, be carefull with your expectations... If you have a 99.999 (or around this) hit cache ratio, increasing the memory would probably not help too much... Your system currently is not (I assume) memory bound... So if you suffer from performance issues you have to look elsewhere. Other considerations.... If you happen to see an Assert Failure (not that we get them often!) the dump will take longer, and will require more space... I always remind of a customer (hopefully not reading this...) that told me to use ~90GB... and when I asked for "2 or 3 times" the shared memory dump in disk space for dump collection he told me "forget it!" (forget the dumps, as he decided to keep the shm size)... And this brings me to another point... you should make sure you have enough virt size... not only buffers.... Regards On Fri, Feb 14, 2014 at 8:55 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Thank you for educating me, Art. I will have to read up on the RESIDENT > parm. > > So, if buffer cache exceeds the size of all tables combined, wouldn't using > RESIDENT guarantee that all tables, once having been read in, would stay > (because the resident memory stays)? > > DG > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1133c9aa7ef89d04f2655cd4
Tables once read into memory, if not replaced by any other data, will remain in the cache regardless of what RESIDENT is set to. One thing has nothing to do with the other. Informix version 7.30 did have the ability to mark a table as "resident" in the cache which raised its priority in the cache so that its data was less likely to be replaced by data from other tables. This was such a performance nightmare that the feature was ripped out in v7.31/9.21 and has never been proposed again. In your case, with more memory than you can use, it's not an issue. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Feb 14, 2014 at 3:55 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Thank you for educating me, Art. I will have to read up on the RESIDENT > parm. > > So, if buffer cache exceeds the size of all tables combined, wouldn't using > RESIDENT guarantee that all tables, once having been read in, would stay > (because the resident memory stays)? > > DG > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0115ff34b6e2c004f27ac4fb
Thank you for explanations. Could I summarize it like this? RESIDENT speaks to the O/S memory management, and does not affect Informix's management of the buffer cache. That is, the two are orthogonal processes. DG
Yes, that seems to me to be a fair summary. On Sun, Feb 16, 2014 at 12:46 AM, DAVID GROVE <david.grove@alaska.gov>wrote: > Thank you for explanations. > > Could I summarize it like this? RESIDENT speaks to the O/S memory > management, > and does not affect Informix's management of the buffer cache. That is, the > two are orthogonal processes. > > DG > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --001a11c237ae7d22d104f28a7661