How to determine optimum buffer size?

Hello, how do I (or how do you) determine the optimum buffer size (on IDS 9.40, but I think it's a generic question) ? I have a machine with almost 'unlimited' memory (96 GB RAM). Therefore I could define a large number of buffer pages. But I'm not sure if at some point the administration of the buffer pool will slow down the engine (the engine must locate the requested buffers in memory, it must track the state of all the buffers, there are very large LRU-queues). How do I see, how many buffers are enough? If I had only one query working on 101 MB of data again and again, 101 MB (or a little more) would be sufficient, but 100 MB wouldn't be optimal (although cache rates would be very good, too). And if I had only one query reading a 150 GB table 100 MB might suffice, because there won't be any buffer reads anyway. For sure, one approach is trying different buffer sizes, but that requires a restart of the engine every time - not to easy on a production system. Are there some monitoring parameters available to address that problem (onstat, systables)? Sometimes the 'Buffer wait ratio' (buffwts / (pagreads + bufwrites)) x 100 was named in this context, but I'm not sure what that figure tells me ? And I'm not sure if that is a good measure for IDS 9.40 - I've seen systems with very very high buffer waits (and a large pool and doing almost nothing). Regards, Andreas Kutsche

In a Performance and Tuning class I attended they recommend 2,000 buffers per LRU queue and that's worked out well for me. I once had memory up to 7 GB but Informix spent so much time managing it that performance degraded. That was on 7.31/HP-UX/12 CPU system with 2K page size. I've also had good luck with 1 GB worth of buffers. In general, even though the server has much more memory, I run 2,000 buffers per LRU. Since I run 127 LRU's on my system that's 254,000 BUFFERS. Darrell Murphy Burton Snowboards -----Original Message----- From: Andreas.KUT.... [mailto:andreas.kutsche@spar.at] Sent: Wednesday, April 13, 2005 2:05 PM To: ids@iiug.org Subject: How to determine optimum buffer size? [4706] Hello, how do I (or how do you) determine the optimum buffer size (on IDS 9.40, but I think it's a generic question) ? I have a machine with almost 'unlimited' memory (96 GB RAM). Therefore I could define a large number of buffer pages. But I'm not sure if at some point the administration of the buffer pool will slow down the engine (the engine must locate the requested buffers in memory, it must track the state of all the buffers, there are very large LRU-queues). How do I see, how many buffers are enough? If I had only one query working on 101 MB of data again and again, 101 MB (or a little more) would be sufficient, but 100 MB wouldn't be optimal (although cache rates would be very good, too). And if I had only one query reading a 150 GB table 100 MB might suffice, because there won't be any buffer reads anyway. For sure, one approach is trying different buffer sizes, but that requires a restart of the engine every time - not to easy on a production system. Are there some monitoring parameters available to address that problem (onstat, systables)? Sometimes the 'Buffer wait ratio' (buffwts / (pagreads + bufwrites)) x 100 was named in this context, but I'm not sure what that figure tells me ? And I'm not sure if that is a good measure for IDS 9.40 - I've seen systems with very very high buffer waits (and a large pool and doing almost nothing). Regards, Andreas Kutsche