On Mon, 5 May 2008, Campbell, Lance wrote:
We currently backup all of our database tables per schema using pg_dump every half hour. We have been noticing that the database performance has been very poor during the backup process. How can I improve the performance?
Uh, don't do that? pg_dump works OK doing periodic backups during relatively calm periods, it's not really appropriate to run all the time like that.
If you need a backup to stay that current, you might instead consider a replication solution that does a periodic full snapshot and then just moves incrementals around from there. WAL shipping is the most obvious candidate as it doesn't necessarily require an additional server and the main components are integrated into the core now. You could just save the files necessary to recover the database just about anywhere. Most other replication solutions would require having another server just to run that which is probably not what you want.
I have tried doing backups to a second set of disks but the performance only improved somewhat.
Then the real problem you're having is probably contention against the database you're dumping from rather than having enough write capacity on the output side. This isn't a surprise with pgdump as it's not exactly gentle on the server you're dumping from. To improve things here, you'd need to add the second set of disks as storage for some of the main database.
Would adding two quad core processors improve performance?
Doubt it. pg_dump is basically taking up a processor and some percentage of disk resources when you're running it. If your server has all of the other 3 processors pegged at the same time, maybe adding more processors would help, but that seems pretty unlikely.
A message from Scott alluded to you showing some vmstat output, but I didn't see that show up on the list. That would give more insight here. Also, if you still have checkpoint_segments at its default (3, you didn't mention adjusting it) that could be contributing to this general problem; that should be much higher on your hardware.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
