Re: Good Buffer Size For Tuning Informix?

> I've added another 128MB of ram to our HP/UX system running > Informix 5.03. I have a total of 384MB and > a buffer size of 3,000 buffers and 10MB total of shared memory > for Informix. > I was going to bump this to 27MB of shared memory, with > buffers going up to 10,000. > > Question: Is this a good thing to do and what are the > tradeoffs? There are 3 references I use when calculating memory requirements. You *definitely* need all three. (Guys, do I get a kickback for all the sales work I'm doing for you? ;) The "Informix OnLine Administrator's Guide" version 5.0, part number 000-7106, dated December 1991. This book has all you need to know about memory, starting on page 2-51. It goes in to exhaustive detail about buffers, LRU queues, paging & swapping, and where your memory goes. It is mind-numbing to read, but well worth the trouble. Take the Informix DBA course for a jump-start. Now that you're really boggled with techno-speak, you are ready for "the real deal". (If you short-cut to here without *really* understanding the Admin Guide you will pay the price in the long run.) Joe Lumbley's book "Informix Database Administrator's Survival Guide", ISBN 0-13-124314-4, available from Informix Press, has a whole section called "Tuning the shared memory buffers." It basically says to increase the number of buffers until your cache hit rate stops increasing. If you run out of system memory or begin swapping then you've gone too far. You must monitor the cache hit rate (tbstat -p and -P) while your application is at normal or peak use, and remember to zero out the tuning statistics after the application gets there (tbstat -z.) Elizabeth Suto's contribution is "Informix-OnLine Performance Tuning", ISBN 0-13-124322-5, and it is also available from Informix Press. She devotes an entire chapter to computing memory requirements, and includes examples, lecture, and case studies for OnLine v 5 and v 6, as well as Unix BSD and SYSV. She very clearly outlines the things about your application that require memory: sorts, stored procedures, and tbcheck runs. How your applications and other users do their thing will determine your memory requirements. While building indexes, for example, your memory requirements may be very high. If, after that, your users do not require large SPL's and only query for single rows then your memory requirements will normally be pretty low. IMHO, two critically important things are locks and logical logs. Your engine comes with only 2000 locks "out of the box". The max is around a quarter-million, and you MUST have enough to handle the largest transaction. If you don't you will hit an OVLOCK (overlock) condition and corrupt the index, maybe the table, and may well have to restore from archive. (Been there, done that.) I know that you should lock the table in exclusive mode when working with a large number of rows, but it doesn't always work like that. Take my advice, pump up this value a lot. The other thing is logical logs. This topic has been well covered, both here and in the literature. It's something to take into account when calculating memory, and the best value is (of course) application-specific. Gee, you got me wound up (again!). The bottom line: yes, more memory is a good thing. The exact amount you need is always "more". How you use it...well that's the hard part. Tradeoffs come when balancing system memory requirements with user (application) memory requirements, and understanding what happens when the system crashes (what will you lose when you lose what's not on disk?) It's not easy. That's why we get paid--surely *somebody* gets paid??--the big bucks. Good luck, __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|