From: Andy Colson <andy@squeakycode.net>
To: Michael Andreasen <michael@dunlops.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Fastest pq_restore?
Date: Thu, 17 Mar 2011 20:08:05 -0500
Message-ID: <4D82B075.7070202@squeakycode.net> (raw)
In-Reply-To: <AANLkTi=x88_1hTotWNkw8XAGxt2iXs1JmW0WZ4sCtWXm@mail.gmail.com>
References: <AANLkTi=x88_1hTotWNkw8XAGxt2iXs1JmW0WZ4sCtWXm@mail.gmail.com>
On 03/17/2011 09:25 AM, Michael Andreasen wrote:
> Hi,
>
> I've been looking around for information on doing a pg_restore as fast as possible. It is for a backup machine so I am not interested in anything like crash recovery or anything else that would impact speed of load. I just want to go from no database to database there as fast as possible. The server is for postgresql only and this is the only database, sp both system at postgres can be set however is required for the fast load.
>
> Currently I am using a twin processor box with 2GB of memory and raid 5 disk.
>
> I start postgres before my load with these settings, which have been suggested.
>
>
> shared_buffers = 496MB
> maintenance_work_mem = 160MB
> checkpoint_segments = 30
> autovacuum = false
> full_page_writes=false
>
> maintenance_work_mem and checkpoint_segments were advised to be increased, which I have done, but these are just guess values as I couldn't see any advise for values, other than "bigger".
>
>
> I restore like this;
>
> pg_restore -Fc -j 4 -i -O -d my_db my_db_dump.tbz
>
>
> Even as this, it is still slower than I would like.
>
> Can someone suggest some optimal settings (for postgresql 9) that will get this as quick as it can be?
>
> Thanks.
>
>
>
>
>
autovacuum = off
fsync = off
synchronous_commit = off
full_page_writes = off
bgwriter_lru_maxpages = 0
-Andy
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, michael@dunlops.com
Subject: Re: Fastest pq_restore?
In-Reply-To: <4D82B075.7070202@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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