>>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_segments to 32 (512Mb) making it
checkpoint every 15 second or so, but that gave a more uneven insert rate than
with checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a better value?
If I set checkpoint_segments to 64, what would a reasonable bgwriter setup be?
I still need to improve my understanding of the relations between
checkpoint_segments <-> shared_buffers <-> bgwriter... :/
- Mikael
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
