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@gregsmith.com http://www.gregsmith.com Baltimore, MD