Greg Smith <greg@2ndquadrant.com> wrote:
> What I do to quantify that is...well, the attached shows it better
> than I can describe; only works on 9.0 or later as it depends on a
> feature I added for this purpose there. It measures exactly how
> much buffer cache churn happened during a test, in this case
> creating a pgbench database.
I'm not entirely sure I understand what I'm supposed to get from
that. On a 3GB workstation, a compile from a recent HEAD checkout,
with a default postgresql.conf file, I get this:
-[ RECORD 1 ]------+------------------------------
now | 2010-08-04 14:25:46.683766-05
checkpoints_timed | 0
checkpoints_req | 0
buffers_checkpoint | 0
buffers_clean | 0
maxwritten_clean | 0
buffers_backend | 0
buffers_alloc | 73
Initializing pgbench
-[ RECORD 1 ]------+------------------------------
now | 2010-08-04 14:27:49.062551-05
checkpoints_timed | 0
checkpoints_req | 0
buffers_checkpoint | 0
buffers_clean | 0
maxwritten_clean | 0
buffers_backend | 633866
buffers_alloc | 832
I boost shared_buffers from 32MB to 320MB, restart, and get this:
-[ RECORD 1 ]------+------------------------------
now | 2010-08-04 14:30:42.816719-05
checkpoints_timed | 0
checkpoints_req | 0
buffers_checkpoint | 0
buffers_clean | 0
maxwritten_clean | 0
buffers_backend | 0
buffers_alloc | 0
Initializing pgbench
-[ RECORD 1 ]------+------------------------------
now | 2010-08-04 14:32:40.750098-05
checkpoints_timed | 0
checkpoints_req | 0
buffers_checkpoint | 0
buffers_clean | 0
maxwritten_clean | 0
buffers_backend | 630794
buffers_alloc | 2523
So run time dropped from 123 seconds to 118 seconds, buffers_backend
dropped by less than 0.5%, and buffers_alloc went up. Assuming this
is real, and not just "in the noise" -- what conclusions would you
draw from this? Dedicating an additional 10% of my free memory got
me a 4% speed improvement? Was I supposed to try with other scales?
Which ones?
-Kevin