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
../ned-int.pz
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.
Server settings:
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
writes checkpoint_segments = 100 # in logfile segments, min
1, 16MB each autovacuum = off # Enable autovacuum
subprocess? 'on'
Server version is CVS HEAD from yesterday some time.
Overall result: baseline: 4h32m parallel: 0h 54m.
Breaking that down:
baseline parallel 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 Command was:
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.
cheers
andrew