Re: What exactly is postgres doing during INSERT/UPDATE ?

On Fri, 28 Aug 2009, Joseph S wrote:

> If I run " dd if=/dev/zero bs=1024k of=file count=1000 " iostat shows me:
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 671.50 88.00 113496.00 176 226992

That's the sequential transfer rate of your drive. It's easier to present
these numbers if you use "vmstat 1" instead; that shows the I/O in more
useful units, and with the CPU stats on the same line.

> However postgres 8.3.7 doing a bulk data write (a slony slave, doing inserts
> and updates) doesn't go nearly as fast:

In PostgreSQL, an update is:

1) A read of the old data
2) Writing out the updated data
3) Marking the original data as dead
4) Updating any indexes involved
5) Later cleaning up after the now dead row

On top of that Slony may need to do its own metadata updates.

This sort of workload involves random I/O rather than sequential. On
regular hard drives this normally happens at a tiny fraction of the speed
because of how the disk has to seek around. Typically a single drive
capable of 50-100MB/s on sequential I/O will only do 1-2MB/s on a
completely random workload. You look like you're getting somewhere in the
middle there, on the low side which doesn't surprise me.

The main two things you can do to improve this on the database side:

-Increase checkpoint_segments, which reduces how often updated data has to
be flushed to disk

-Increase shared_buffers in order to hold more of the working set of data
in RAM, so that more reads are satisfied by the database cache and less
data gets evicted to disk.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD