At 03:44 PM 3/21/2006, Simon Riggs wrote:
On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote:
> This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off),
compared to the raw dd result (~75.5Mb/s).
>
> I assume this difference is due to:
> - simultaneous WAL write activity (assumed: for each byte written
to the table, at least one byte is also written to WAL, in effect:
10Gb data inserted in the table equals 20Gb written to disk)
> - lousy test method (it is done using a function => the
transaction size is 10Gb, and 10Gb will *not* fit in wal_buffers :) )
> - poor config
> checkpoint_segments = 3
With those settings, you'll be checkpointing every 48 Mb, which will be
every about once per second. Since the checkpoint will take a reasonable
amount of time, even with fsync off, you'll be spending most of your
time checkpointing. bgwriter will just be slowing you down too because
you'll always have more clean buffers than you can use, since you have
132MB of shared_buffers, yet flushing all of them every checkpoint.
IIRC, Josh Berkus did some benches that suggests in pg 8.x a value of
64 - 256 is best for checkpoint_segments as long as you have the RAM available.
I'd suggest trying values of 64, 128, and 256 and setting
checkpoint_segments to the best of those.
Ron
