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

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.