Subject: Re: Migration study, step 1: bulk write performance
From: Simon Riggs <simon@2ndquadrant.com>
To: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <7F10D26ECFA1FB458B89C5B4B0D72C2B088286@sesrv12.wirelesscar.com>
References: <7F10D26ECFA1FB458B89C5B4B0D72C2B088286@sesrv12.wirelesscar.com>
Content-Type: text/plain
Date: Tue, 21 Mar 2006 20:44:50 +0000
Message-Id: <1142973890.24487.510.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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