pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTuning postgres for fast restore?
3+ messages / 3 participants
[nested] [flat]
* Tuning postgres for fast restore?
@ 2009-02-21 00:42 Tino Schwarze <postgresql@tisc.de>
2009-02-21 07:14 ` Re: Tuning postgres for fast restore? Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 3+ messages in thread
From: Tino Schwarze @ 2009-02-21 00:42 UTC (permalink / raw)
To: pgsql-admin
Hi there,
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?
I currently have:
shared_buffers = 240MB
work_mem = 4096 # no units here, d'oh!
maintenance_work_mem = 150000
Thanks!
Tino.
--
"What we nourish flourishes." - "Was wir nähren erblüht."
www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tuning postgres for fast restore?
2009-02-21 00:42 Tuning postgres for fast restore? Tino Schwarze <postgresql@tisc.de>
@ 2009-02-21 07:14 ` Guillaume Lelarge <guillaume@lelarge.info>
2009-02-23 01:57 ` Re: Tuning postgres for fast restore? Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Guillaume Lelarge @ 2009-02-21 07:14 UTC (permalink / raw)
To: Tino Schwarze <postgresql@tisc.de>; +Cc: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tuning postgres for fast restore?
2009-02-21 00:42 Tuning postgres for fast restore? Tino Schwarze <postgresql@tisc.de>
2009-02-21 07:14 ` Re: Tuning postgres for fast restore? Guillaume Lelarge <guillaume@lelarge.info>
@ 2009-02-23 01:57 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Scott Marlowe @ 2009-02-23 01:57 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Tino Schwarze <postgresql@tisc.de>; pgsql-admin
On Sat, Feb 21, 2009 at 12:14 AM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
> 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.
Also, you can disable fsync during a restore if the machine's not
handling any other databases.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-02-23 01:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-02-21 00:42 Tuning postgres for fast restore? Tino Schwarze <postgresql@tisc.de>
2009-02-21 07:14 ` Guillaume Lelarge <guillaume@lelarge.info>
2009-02-23 01:57 ` Scott Marlowe <scott.marlowe@gmail.com>
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