Thread: Fastest pq_restore?

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
amnot interested in anything like crash recovery or anything else that would impact speed of load. I just want to go
fromno database to database there as fast as possible. The server is for postgresql only and this is the only database,
spboth 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
guessvalues 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

Andy Colson <andy@squeakycode.net> wrote:
> On 03/17/2011 09:25 AM, Michael Andreasen wrote:

>> I've been looking around for information on doing a pg_restore as
>> fast as possible.

>> I am using a twin processor box with 2GB of memory

>> shared_buffers = 496MB

Probably about right.

>> maintenance_work_mem = 160MB

You might get a benefit from a bit more there; hard to say what's
best with so little RAM.

>> checkpoint_segments = 30

This one is hard to call without testing.  Oddly, some machines do
better with the default of 3.  Nobody knows why.

>> autovacuum = false
>> full_page_writes=false

Good.

> fsync = off
> synchronous_commit = off

Absolutely.

> bgwriter_lru_maxpages = 0

I hadn't thought much about that last one -- do you have benchmarks
to confirm that it helped with a bulk load?

You might want to set max_connections to something lower to free up
more RAM for caching, especially considering that you have so little
RAM.

-Kevin

On 3/18/2011 9:38 AM, Kevin Grittner wrote:
> Andy Colson<andy@squeakycode.net>  wrote:
>> On 03/17/2011 09:25 AM, Michael Andreasen wrote:
>
>>> I've been looking around for information on doing a pg_restore as
>>> fast as possible.
>
>> bgwriter_lru_maxpages = 0
>
> I hadn't thought much about that last one -- do you have benchmarks
> to confirm that it helped with a bulk load?
>

Nope, I got it from the "running with scissors" thread (I think), (maybe
from Greg Smith)


or here:

http://rhaas.blogspot.com/2010/06/postgresql-as-in-memory-only-database_24.html

I dont recall exactly.  I saw it, add added a comment to my .conf just
incase I ever needed it.

-Andy

On Thu, Mar 17, 2011 at 7:25 AM, Michael Andreasen <michael@dunlops.com> wrote:
> 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.
>
> I restore like this;
> pg_restore -Fc -j 4 -i -O -d my_db my_db_dump.tbz
>

Just throwing this out there, but you have 4 parallel jobs running the
restore (-j 4), with two processors? They are multi-core? You might be
seeing some contention there if they aren't.