Folks,
I'm hoping that some of you can shed some light on this.
I've been trying to peg the "sweet spot" for shared memory using OSDL's
equipment. With Jan's new ARC patch, I was expecting that the desired
amount of shared_buffers to be greatly increased. This has not turned out to
be the case.
The first test series was using OSDL's DBT2 (OLTP) test, with 150
"warehouses". All tests were run on a 4-way Pentium III 700mhz 3.8GB RAM
system hooked up to a rather high-end storage device (14 spindles). Tests
were on PostgreSQL 8.0b3, Linux 2.6.7.
Here's a top-level summary:
shared_buffers % RAM NOTPM20*
1000 0.2% 1287
23000 5% 1507
46000 10% 1481
69000 15% 1382
92000 20% 1375
115000 25% 1380
138000 30% 1344
* = New Order Transactions Per Minute, last 20 Minutes
Higher is better. The maximum possible is 1800.
As you can see, the "sweet spot" appears to be between 5% and 10% of RAM,
which is if anything *lower* than recommendations for 7.4!
This result is so surprising that I want people to take a look at it and tell
me if there's something wrong with the tests or some bottlenecking factor
that I've not seen.
in order above:
http://khack.osdl.org/stp/297959/
http://khack.osdl.org/stp/297960/
http://khack.osdl.org/stp/297961/
http://khack.osdl.org/stp/297962/
http://khack.osdl.org/stp/297963/
http://khack.osdl.org/stp/297964/
http://khack.osdl.org/stp/297965/
Please note that many of the Graphs in these reports are broken. For one
thing, some aren't recorded (flat lines) and the CPU usage graph has
mislabeled lines.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco