Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Improve the performance of pg_dump pg_restore

PG_DUMP 9.6 Fc Format VS Fd Format
  1. Increase maintenance_work_mem so you can build indexes in larger chunks
  2. Turn off fsync during the restore. If your machine crashes, you'll start from scratch again anyway.
  3. Disable index when Restoring 
  4. autovacuum = off
Fc
-bash-4.1$ time pg_dump -Fc fsdcm_external > /tmp/new_dump96_fsdcm_external.sql
Fd
-bash-4.1$ time pg_dump -j 8 -Fd -f /tmp/newout.dir fsdcm_external
Time
Fc
Fd
real
44m32.553s
10m12.214s
user
43m28.341s
63m19.678s
sys
0m46.313s
0m39.869s
PG_RESTORE 9.6 Fc With no tunning deafult setting postgres.conf || With no -j VS yes -j
With no -j
-bash-4.1$ time pg_restore --format=c -C -d postgres /tmp/fsdcm_external.s
With yes -j
bash-4.1$ time pg_restore -j 8 --format=c -C -d postgres /tmp/fsdcm_external.sql
Time
No j
yes J
real
58m53.599s
35m21.029s
user
11m0.415s
10m11.956s
sys
0m38.405s
0m42.460s
PG_RESTORE 9.6 With tunning setting postgres.conf || Fc Format VS Fd Format
postgres.conf
work_mem = 32MB
shared_buffers = 4GB
maintenance_work_mem = 2GB
full_page_writes = off
autovacuum = off
wal_buffers = -1
Fc
-bash-4.1$ time pg_restore -j 8 --format=c -C -d postgres /tmp/fsdcm_external.sql
Fd
-bash-4.1$ time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/
Time
Fc Format
Fd Format
real
25m21.241s
26m18.635s
user
10m18.189s
11m25.439s
sys
0m43.944s
0m41.772s
the bottom line is
PG_DUMP | Always Used Format Direcroty With J option
-bash-4.1$ time pg_dump -j 8 -Fd -f /tmp/newout.dir fsdcm_external
PG_RESTORE | Always Used tunning for postgres.conf with Format Directory With J option
work_mem = 32MB
shared_buffers = 4GB
maintenance_work_mem = 2GB
full_page_writes = off
autovacuum = off
wal_buffers = -1

-bash-4.1$ time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL