Re: Ways to speed up dump&reload

Hi!

Thanks very much, this was exactly the kind of advice I was hoping for! I'll give the WAL to ramdisk thing a try; fsync is off by default anyways - the data in this cluster is not so valuable as not to risk one day of rollback for the performance gain of having fsync turned off and I'm doing nightly dumps anyway AND the machine as both a dedicated UPS and redundant PSUs, so the likelyhood of things turning snafu is considerably small. But having WAL in RAM might actually speed up the whole lot quite considerably.

Thank you!

Kind regards

Markus

-----Original Message-----
From: Magnus Hagander [mailto:mha(at)sollentuna(dot)net]
Sent: Mon 2/21/2005 13:04
To: Markus Wollny; pgsql-general(at)postgresql(dot)org
Cc:
Subject: RE: [GENERAL] Ways to speed up dump&reload
> Hello!
>
> Finally the time has come for us to upgrade our PostgreSQL
> 7.4.5-servers to 8.0.1 - and though I'm very much looking
> forward to some of the new features, the dump&reload process
> is worrying me a bit this time. I've got one cluster in
> particular which is roughly 9GB in size and features some
> dreadfully large indices of the GiST-type for Tsearch2.
>
> I have already scheduled a nightshift for this upgrade, but
> I'm not all too certain that I'll be up and running again in
> the morning, so I wondered if there might be some
> preparations that would allow for some speed-up during the
> reload process? What kind of tuning could be done in
> postgresql.conf in respect to just this particular workload
> (COPY and finally the CREATE INDEX stuff) with no other
> concurrent access going on?

If you'll be around to babysit the system during the reload, turn off
fsync during the load.
If you have the RAM, consider temporarily moving the WAL logs to a
ramdrive.
Increase sort_mem and maintenance_sort_mem, since you will only have a
single connection.
If you're on hyperthreading CPUs, disable hyperthreading. Since you load
in a single connectino, only one CPU will be used.

Be very careful to remember to turn this back on after the load!

Not sure if there is something specific you can do for tsearch, but this
should help with the general stuff

//Magnus