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

Re: Inserting 8MB bytea: just 25% of disk perf used?

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: "fkater(at)googlemail(dot)com" <fkater(at)googlemail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Date: 2010-01-14 15:04:02
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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?

Several reasons:

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 DykDate: 2010-01-14 15:07:35
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Previous:From: tomDate: 2010-01-14 14:58:44
Subject: Slow "Select count(*) ..." query on table with 60 Mio. rows

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