Re: Migration study, step 1: bulk write performance - Mailing list pgsql-performance

From Simon Riggs
Subject Re: Migration study, step 1: bulk write performance
Date
Msg-id 1142973890.24487.510.camel@localhost.localdomain
Whole thread Raw
In response to Migration study, step 1: bulk write performance optimization  ("Mikael Carneholm" <Mikael.Carneholm@WirelessCar.com>)
Responses Re: Migration study, step 1: bulk write performance  (Ron <rjpeace@earthlink.net>)
List pgsql-performance
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.

Best Regards, Simon Riggs


pgsql-performance by date:

Previous
From: Simon Riggs
Date:
Subject: Re: WAL logging of SELECT ... INTO command
Next
From: Simon Riggs
Date:
Subject: Re: planner with index scan cost way off actual cost,