On Thu, 14 Jan 2010, fkater(at)googlemail(dot)com wrote:
> This takes about 50s, so, 800MB/50s = 16MB/s.
> However the harddisk (sata) could write 43 MB/s in the worst
> case! Why is write performance limited to 16 MB/s?
The data needs to be written first to the WAL, in order to provide
crash-safety. So you're actually writing 1600MB, not 800.
Postgres needs to update a few other things on disc (indexes on the large
object table maybe?), and needs to call fsync a couple of times. That'll
add a bit of time.
Your discs can't write 43MB/s in the *worst case* - the worst case is lots
of little writes scattered over the disc, where it would be lucky to
manage 1MB/s. Not all of the writes Postgres makes are sequential. A handy
way of knowing how sequential the writes are is to listen to the disc as
it writes - the clicking sounds are where it has to waste time moving the
disc head from one part of the disc to another.
No trees were killed in the sending of this message. However a large
number of electrons were terribly inconvenienced.
In response to
pgsql-performance by date
|Next:||From: Aidan Van Dyk||Date: 2010-01-14 15:07:35|
|Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?|
|Previous:||From: tom||Date: 2010-01-14 14:58:44|
|Subject: Slow "Select count(*) ..." query on table with 60 Mio. rows|