Skip site navigation (1) Skip section navigation (2)

Re: Migration study, step 1: bulk write performance

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Mikael Carneholm <Mikael(dot)Carneholm(at)WirelessCar(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Migration study, step 1: bulk write performance
Date: 2006-03-21 20:44:50
Message-ID: 1142973890.24487.510.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-performance by date

Next:From: Simon RiggsDate: 2006-03-21 20:57:27
Subject: Re: planner with index scan cost way off actual cost,
Previous:From: Simon RiggsDate: 2006-03-21 20:33:50
Subject: Re: WAL logging of SELECT ... INTO command

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group