Re: Backup causing poor performance - suggestions

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD