From: Scott Marlowe <smarlowe@g2switchworks.com>
To: D Kavan <bitsandbytes88@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: tweak config file to increase pg_dump speed on pg 8.02
Date: Fri, 05 Aug 2005 14:49:22 -0500
Message-ID: <1123271362.15481.329.camel@state.g2switchworks.com> (raw)
In-Reply-To: <BAY102-F9FDE95849B132BF9E9D5FD1C70@phx.gbl>
References: <BAY102-F9FDE95849B132BF9E9D5FD1C70@phx.gbl>
On Fri, 2005-08-05 at 14:28, D Kavan wrote:
> Hi,
>
> It's taken 4 hours so far to dump a 3.5 GB database on a local disk.
> How can I increase performance?
>
> We have the work memory & effective cach size are set to 16 MB. Our
> maintenance_work_memory is set to 256 MB. Thanks for any help.
> For the record we are running with dual 64-bit chips with 8 GB of memory. I
> have noticed that the only 6.5 % of the memory is being used for this and
> the load is only at just over 1.
Sounds like your machine is I/O starved. What's your CPU utilization?
What's your shared_buffers set to? (not that it'll usually help a lot in
a backup usually, but if there's a lot of other access going on it
might)
How much memory is the KERNEL using as cache? PostgreSQL doesn't really
use a lot of memory by the way, relying on the kernel to cache for it.
You might wanna crank up effective_cache_size. That setting doesn't
allocate anything, it tells the planner about how much memory your OS is
using to cache the postgresql dataset. on a dedicated pgsql server with
8 gig ram, I'd expect it to be equal to about 7.5gig/8192.
Are you dumping to the same drive your database is on? Often dumping to
another drive helps a lot too.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: smarlowe@g2switchworks.com, bitsandbytes88@hotmail.com
Subject: Re: tweak config file to increase pg_dump speed on pg 8.02
In-Reply-To: <1123271362.15481.329.camel@state.g2switchworks.com>
References: <BAY102-F9FDE95849B132BF9E9D5FD1C70@phx.gbl> <1123271362.15481.329.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