SEO

Hi,
I'm currently restoring a fairly large DB from a pg_dump and it's taking
about 12 hours to finish. The main part of this time is spent creating
indexes. Is there anyway I can speed up the restore process, or do i just
have to wait? I'm using postgres 8.1.3 on freebsd and the pg_dump is gzip'd.
I'm restoring with the following command : "zcat <gzip'd db> | psql -U
postgres <destination db>"

Regards,
Ruairi Carroll


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Ruairi" <rcarroll@bluemetrix.com> writes:
> I'm currently restoring a fairly large DB from a pg_dump and it's taking
> about 12 hours to finish. The main part of this time is spent creating
> indexes. Is there anyway I can speed up the restore process, or do i just
> have to wait?

There's not much you can do to improve the performance of a restore
already in progress. Next time you might want to think about kicking up
maintenance_work_mem before you start it; that's about the only thing
you can really do to speed up index builds.

(Actually, if there are lots of indexes yet to be built, increasing
the setting in postgresql.conf and SIGHUP'ing the postmaster could
be worth doing, as that should affect the builds yet to be done.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Todd A. Cook" <tcook@blackducksoftware.com> writes:
> I have found that increasing maintenance_work_mem can decrease index
> build speed on large tables:

You should probably re-measure when 8.2 comes out; we've fixed a number
of performance issues in the sorting code that might cause that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane wrote:
> "Todd A. Cook" <tcook@blackducksoftware.com> writes:
>> I have found that increasing maintenance_work_mem can decrease index
>> build speed on large tables:
>
> You should probably re-measure when 8.2 comes out; we've fixed a number
> of performance issues in the sorting code that might cause that.

Thanks. I'll do that. If I can, I'll try it sooner on a build
from CVS.

-- todd

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane wrote:
>
> There's not much you can do to improve the performance of a restore
> already in progress. Next time you might want to think about kicking up
> maintenance_work_mem before you start it; that's about the only thing
> you can really do to speed up index builds.

I have found that increasing maintenance_work_mem can decrease index
build speed on large tables:

maintenance_work_mem 32768 262144 1048576 2097151
---------------------------------------------------------------
index 400M rows (min) 55 61 68 70
index 45M rows (min) 4.03 4.22 4.88 2.55

These timings were done on a machine with 8GB ram and postgresql 8.1.3
(from the RPMs on postgresql.org). Each time is an average of three
runs; before each run, postgres was shut down and the filesystem with
/var/lib/pgsql was remounted to clear the filesystem cache.

I'm assuming that the times are increasing because there is less
memory available for the filesystem cache. Similarly, I assume that
the runs on the smaller table with maintenance_work_mem = 2097151
are faster because everything fits in that space.

-- todd

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On May 24, 2006, at 2:56 PM, Todd A. Cook wrote:

> Tom Lane wrote:
>> "Todd A. Cook" <tcook@blackducksoftware.com> writes:
>>> I have found that increasing maintenance_work_mem can decrease index
>>> build speed on large tables:
>> You should probably re-measure when 8.2 comes out; we've fixed a
>> number
>> of performance issues in the sorting code that might cause that.
>
> Thanks. I'll do that. If I can, I'll try it sooner on a build
> from CVS.

If you'll be messing around with CVS, you might also want to try this
patch: http://jim.nasby.net/misc/pgsqlcompr...ess-sort.patch
(which was written by someone else). It hacks compression into the on-
disk sort code, which has shown a 50% speed improvement on my
machine. It should be fine to use for loading a database, but you
wouldn't want to leave it in for serious use (IIRC there's some cases
it flat-out doesn't handle right now).

You could probably apply that to 8.1.4 as well if you wanted to; it
should be fine for just loading the database.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly