Re: Migration study, step 1: bulk write performanceoptimization

["Mikael Carneholm" <Mikael(dot)Carneholm(at)WirelessCar(dot)com>]

>>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