Skip site navigation (1) Skip section navigation (2)

Re: Tuning postgres for fast restore?

From: Guillaume Lelarge <guillaume(at)lelarge(dot)info>
To: Tino Schwarze <postgresql(at)tisc(dot)de>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Tuning postgres for fast restore?
Date: 2009-02-21 07:14:47
Message-ID: 499FA9E7.1040109@lelarge.info (view raw or flat)
Thread:
Lists: pgsql-admin
Tino Schwarze a écrit :
> [...]
> I'm going to pg_restore a database dump of about 220 GiB (uncompressed,
> but most data is BLOBs). The machine has 8 GiB of memory and 8 cores.
> Is there any advice to speed up restoring, postgresql.conf-wise?
> 
> I already have a script which does the data loading and index creation
> in parallel. I'm looking for advice regarding shared_mem, work_mem and
> maintenance_mem - shall I raise them?
> 

You should definitely raise shared_buffers and maintenance_work_mem.

> I currently have:
> shared_buffers = 240MB
> work_mem = 4096               # no units here, d'oh!
> maintenance_work_mem = 150000

When there's no unit, work_mem and maintenance_work_mem are in KB. You
didn't tell us your PostgreSQL release, but shared_buffers can be set at
least to 1GB and maintenance_work_mem to 512MB. You can also raise
checkpoint_segments.

Regards.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

In response to

Responses

pgsql-admin by date

Next:From: Michael MonnerieDate: 2009-02-21 08:23:32
Subject: Re: 8.3.5 broken after power fail
Previous:From: Uwe C. SchroederDate: 2009-02-21 05:45:49
Subject: Re: very, very slow performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group