Re: Backup causing poor performance - suggestions

On Mon, May 5, 2008 at 11:19 AM, Campbell, Lance <lance(at)uiuc(dot)edu> wrote:
> Scott,
> When I do a df -h I see that the database takes up a total of 8Gig of
> disk space. This is not the size of the backup file of the database.

Ok. Just wanted to make sure. Looking at vmstat, with little or no
blocks read in, it would appear your database fits in memory.

> I have attached the vmstats as a text file. Are there any properties I
> could adjust that would help with writing data during a backup? Are
> there any properties that might help with improving pg_dump performance?

With as much writing as you have going on, it might help to crank up
your setting for checkpoint segments to something like 100 or more.
With as much disk space as you've got you can afford it. Other than
that, you might wanna look at a faster / better RAID controller in the
future. One with lots of battery backed cache set to write back.

> Ideally I want to keep the backups as simple a process as possible
> before we have to go to the next level of backup. My next strategy is
> to put some of the tables in tablespaces on a couple different disks. I
> will also backup to a dedicated set of disks. If there are a few
> performance tweaks I can make I will. I was hoping to wait a few years
> before we have to go to a more involved backup process.

It may well be cheaper to go the route of a faster RAID controller
with battery backed cache, since it's a one time investment, not an
ongoing maintenance issue like running PITR or slony can become. Plus
it helps overall performance quite a bit.