Re: PG writes a lot to the disk

On Wed, 19 Mar 2008, Laurent Raufaste wrote:

> When I try to plug it to a client (sending 20 transactions/s) it works
> fine for like 10 minutes, then start to write a lot in the pgdata/base
> directory (where the database files are, not the index). It writes so
> much (3MB/s randomly) that it can't serve the queries anymore, the load
> is huge.

You didn't mention adjusting any of the checkpoint parameters and you also
didn't say what version of PostgreSQL you're running. If you've got
frozen sections approximately every 5 minutes you should figure out of
they line up with the checkpoints on your system. How you do that varies
depending on version, I've covered most of what you need to get started
at:

http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm

It's also worth noting that RAID5 is known to be awful on write
performance with some disk controllers. You didn't mention what
controller you had. You should measure your disks to be sure they're
performing well at all, it's possible you might be getting performance
that's barely better than a single disk.
http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm has
some ideas on how to do that.

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