pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
tweak config file to increase pg_dump speed on pg 8.02
2+ messages / 2 participants
[nested] [flat]

* tweak config file to increase pg_dump speed on pg 8.02
@ 2005-08-05 19:28  D Kavan <bitsandbytes88@hotmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: D Kavan @ 2005-08-05 19:28 UTC (permalink / raw)
  To: pgsql-admin

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.

~DjK





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: tweak config file to increase pg_dump speed on pg 8.02
@ 2005-08-05 19:49  Scott Marlowe <smarlowe@g2switchworks.com>
  parent: D Kavan <bitsandbytes88@hotmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Scott Marlowe @ 2005-08-05 19:49 UTC (permalink / raw)
  To: D Kavan <bitsandbytes88@hotmail.com>; +Cc: pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-08-05 19:49 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-05 19:28 tweak config file to increase pg_dump speed on pg 8.02 D Kavan <bitsandbytes88@hotmail.com>
2005-08-05 19:49 ` Scott Marlowe <smarlowe@g2switchworks.com>

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