From: Scott Marlowe <smarlowe@g2switchworks.com>
To: pgsql-performance@lusis.org
Cc: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Performance of pg_dump on PGSQL 8.0
Date: Wed, 14 Jun 2006 11:44:10 -0500
Message-ID: <1150303450.26538.9.camel@state.g2switchworks.com> (raw)
In-Reply-To: <44902165.7020502@lusis.org>
References: <44902165.7020502@lusis.org>
On Wed, 2006-06-14 at 09:47, John E. Vincent wrote:
> -- this is the third time I've tried sending this and I never saw it get
> through to the list. Sorry if multiple copies show up.
>
> Hi all,
BUNCHES SNIPPED
> work_mem = 1048576 ( I know this is high but you should see some of our
> sorts and aggregates)
Ummm. That's REALLY high. You might want to consider lowering the
global value here, and then crank it up on a case by case basis, like
during nighttime report generation. Just one or two queries could
theoretically run your machine out of memory right now. Just put a "set
work_mem=1000000" in your script before the big query runs.
> We're inserting around 3mil rows a night if you count staging, info, dim
> and fact tables. The vacuum issue is a whole other problem but right now
> I'm concerned about just the backup on the current hardware.
>
> I've got some space to burn so I could go to an uncompressed backup and
> compress it later during the day.
That's exactly what we do. We just do a normal backup, and have a
script that gzips anything in the backup directory that doesn't end in
.gz... If you've got space to burn, as you say, then use it at least a
few days to see how it affects backup speeds.
Seeing as how you're CPU bound, most likely the problem is just the
compressed backup.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: smarlowe@g2switchworks.com, pgsql-performance@lusis.org
Subject: Re: Performance of pg_dump on PGSQL 8.0
In-Reply-To: <1150303450.26538.9.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox