On Friday 12 September 2008 14:32:07 Greg Smith wrote: > On Fri, 12 Sep 2008, William Garrison wrote: > > Is there a definitive list of things to do? > > That section of the documention is pretty good: > http://www.postgresql.org/docs/current/static/populate.html > > The main thing it's missing is a discussion of how to cut down on disk > commit overhead by either usinc async commit or turning fsync off. If > you've got a good caching controller that may not be needed though. > > The other large chunk of information it doesn't really go into is what > server tuning you could do to improve general performance, which obviously > would then help with loading as well. > http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server goes over > much of that. > > > * Turn off full_page_writes > > Don’t write the WAL archives in a safe way. But we don’t need WAL > > archives during a restore. Future versions of postgres will let you turn > > off WAL archives entirely > > Ideally you'd be using COPY such that the table was just created or > truncated before loading, which (if archive_mode is off) keeps them from > being WAL logged, as described in 14.4.7. If you do that and vastly > increase checkpoint_segments, full_page_writes has minimal impact. > > > * Increase the checkpoint_segments parameter (the default is 3 – so... > > maybe 10?) > > 64-256 is the usual range you'll see people using for bulk loading. > Don't forget to bump up checkpoint_timeout along with that... actually, I blogged a couple of times on this topic: http://people.planetpostgresql.org/xzilla/index.php?/archives/133-Getting-faster-database-restores-on-postgresql-8.1.html http://people.planetpostgresql.org/xzilla/index.php?/archives/223-Measuring-database-restore-times.html A little old, but might be helpful. -- Robert Treat http://www.omniti.com Database: Scalability: Consulting:
