Обсуждение: Tuning postgres for fast restore?
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
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
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.