Skip site navigation (1) Skip section navigation (2)

Re: Slow dump with pg_dump/pg_restore ? How to improve ?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: soeren(at)all-about-shift(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Slow dump with pg_dump/pg_restore ? How to improve ?
Date: 2004-06-30 14:52:06
Message-ID: 8811.1088607126@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Soeren Gerlach <soeren(at)all-about-shift(dot)com> writes:
> So...the dump in the above format needs some 14 minutes, the restore 10 
> minutes. This seems to be very slow as it means something like 100K/sec for 
> dumping and restoring. The drive is cappable of 40 Meg/seconds, so thats 
> not the bottleneck ,-) Anyhow postmaster and pg_dump seem to max out the 
> CPU cycles as it's running at nearly 100% while dumping and restoring.

What datatypes have you got in the large tables?  Also, what character
set encoding are you using?

The only reason I can think of for dump to be that slow is if conversion
of the data to text is a big time sink.  This would involve the
datatype's own output routine plus possibly a character set conversion.
You should at least make sure that no character set conversion needs to
happen (offhand I think this would only be an issue if pg_dump is
invoked with PGCLIENTENCODING set in its environment).

Also I trust you are using dump with the default COPY-style output,
not dump-as-INSERTs?

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Alvaro HerreraDate: 2004-06-30 15:13:25
Subject: Re: substring syntax with regexp
Previous:From: Andy BDate: 2004-06-30 14:44:18
Subject: Enough RAM for entire Database.. cost aside, is this going to be fastest?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group