Good Buffer Size For Tuning Informix?
Michael Mstowski
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?
Thanks,
Michael Mstowski
m...@spaceworks.com
Terry Boyland
Tuning is a site issue but I believe that 30% of RAM is the
recommended max. After tuning everything else to get your engine
performance as good as possible try adjusting the number of
buffers to bring your shared memory usage up to this 30%
figure.
Really at the end of the day only careful monitoring of the
performance after each change will show the best configuration
for your system
Watch out for the shared memory parameters particularly
relating to semaphores and make sure your physical log
and logical log buffers are the correct size
Good luck !!
================================================================
Terry Boyland :^{})
Proverb: Once a king always a king But once a knight is enough !
Chris Jenkins
The only really important factor in determining the number of buffers is
the read and write cache hit ratios reported by tbstat -p. Keep
increasing the number of buffers until these cache hit rates do not
increase when the system is running a 'normal' workload. This number
of buffers is optimum for the workload. Having significantly more
buffers than this optimum simply wastes valuable memory.
Michael Mstowski wrote:
> 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?
--
Chris Jenkins (chr...@informix.com)
>>> All statements and opinions are mine and should in no way <<<
>>> be construed as representing those of Informix Software <<<
David Kosenko
: The only really important factor in determining the number of buffers is
: increasing the number of buffers until these cache hit rates do not
: increase when the system is running a 'normal' workload. This number
: of buffers is optimum for the workload. Having significantly more
: buffers than this optimum simply wastes valuable memory.
Forget write cache ratios - they are completely meaningless. ALL writes
in online are cached; a page that is modified is always going to be
resident in the buffer pool, even if it means bringing the page in for
the write.
What the write cache is giving you is the relationship between how often
you modified pages and how often you flush dirty pages in the buffer pool.
For an OLTP system with high throughput, you want to keep your LRU max and
min DIRTY values low, which means you will be writing pages fairly often
(but your checkpoints will be quicker as a result). That results in a lower
write cache, but who cares? Since writes are completely asynchronous to
database activity, they have (generally) little appreciable impact.
Be careful when monitoring read cache hits as well; you want to zero out
your stats when doing this (tbstat -z). Modest improvements get lost in
the noise when the stats are for long time periods.
Finally, the value of increased buffer pool sizes depends a lot on the nature
of the app. For example, in a DSS environment, using OnLine 7.10 or better,
the buffer pool may get very little use, if light scans are being used to
read data. In that case, you'd be better off increasing the DS memory
tuneable than dedicating the memory to the buffer pool. For OLTP, though,
the buffer pool is a good bet.
Dave Kosenko
Disclaimer: All opinions expressed in this message are well-reasoned and
insightful; needless to say, they are not those of Informix Software, its
partners or lackeys. Anyone who says otherwise is itching for a fight.
****************************************************************************
"I look back with some satisfaction on what an idiot I was when I was 25,
but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney