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: alpine.DEB.2.00.1001141458590.6195@aragorn.flymine.org (view raw or flat)
Thread:
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.

Matthew

-- 
 No trees were killed in the sending of this message.  However a large
 number of electrons were terribly inconvenienced.

In response to

Responses

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-2014 The PostgreSQL Global Development Group