>>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.
>Please read you're logfile, which should have relevant WARNING messages.
It does ("LOG: checkpoints are occurring too frequently (2 seconds apart)") However, I tried increasing
checkpoint_segmentsto 32 (512Mb) making it checkpoint every 15 second or so, but that gave a more uneven insert rate
thanwith checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a better value? If I set checkpoint_segments to 64,
whatwould a reasonable bgwriter setup be? I still need to improve my understanding of the relations between
checkpoint_segments<-> shared_buffers <-> bgwriter... :/
- Mikael