I have just completed a test of the patch I posted a few days ago.
The test is a 2Gb dump file that restores to a 22Gb database. The
database is very complex, with some 28,000 objects.
The baseline test was run in a single transaction:
pg_restore --use-list tlist -1 -d mdata ../ned-int.pz
The parallel test was run with 8 concurrent threads, truncating the data
members before load:
pg_restore --use-list tlist -m 8 --truncate-before-load -d mdatap
The server is an 8-way machine running 2.66 Ghz Xeons running OpenSuse
11.0. with Linux kernel 2.6.25. with 16gB of physical RAM.
max_connections = 100 # (change requires restart)
shared_buffers = 1GB # min 128kB
work_mem = 100MB # min 64kB
maintenance_work_mem = 200MB # min 1MB
fsync = off # turns forced
synchronization on or off
synchronous_commit = off # immediate fsync at commit
full_page_writes = off # recover from partial page
checkpoint_segments = 100 # in logfile segments, min
1, 16MB each
autovacuum = off # Enable autovacuum
Server version is CVS HEAD from yesterday some time.
Overall result: baseline: 4h32m parallel: 0h 54m.
Breaking that down:
pre-data 35m 16m
data load 38m 20m*
post-data 3h29m 30m*
* these two steps overlap.
I am not sure why the pre-data load took so much longer in the baseline.
I can only assume that there is a small penalty per object from doing it
all in a single transaction.
There was a single error on the parallel run, for which I am currently
unable to account:
ERROR: relation "foo" does not exist
ALTER TABLE ONLY foo
ADD CONSTRAINT foo_role_fk FOREIGN KEY (foo_key, role_key) ....
But relation "foo" does indeed exist, and in fact three out of four of
its FK constraints got created successfully.
pgsql-hackers by date
|Next:||From: Pavel Stehule||Date: 2008-10-03 05:29:34|
|Subject: using pgfarm for other projects|
|Previous:||From: Andrew Dunstan||Date: 2008-10-02 23:07:55|
|Subject: Re: [PATCHES] Infrastructure changes for recovery|