Re: WIP parallel restore patch

Kenneth Marshall wrote:
> Okay, I have had a chance to run some timing benchmarks.
> Here are my results for the parallel pg_restore patch:
>
> Ken
> --------------------------------------------------
> Server settings:
>
> max_connections = 100 # (change requires restart)
> shared_buffers = 256MB # min 128kB
> work_mem = 128MB # min 64kB
> maintenance_work_mem = 256MB # min 1MB
>
> fsync = on # turns forced synchronization on or off
>
> synchronous_commit = off # immediate fsync at commit
>
> full_page_writes = on # recover from partial page writes
> checkpoint_segments = 10 # in logfile segments, min 1, 16MB each
> autovacuum = on # Enable autovacuum subprocess? 'on'
>
> The total final database size is 6.5GB. Here are the timings for
> the different run parallelism from 1 to 8 on a 4-core AMD box:
>
> -bash-3.00$ time pg_restore -U postgres -p 5435 -d rttest /tmp/rtout.pz
> ...
>
> real 19m3.175s
> user 1m2.968s
> sys 0m8.202s
>
> improvement - 0%
>
> -bash-3.00$ time pg_restore -U postgres -p 5435 -m 2 -d rttest /tmp/rtout.pz
> ...
> real 12m55.680s
> user 1m12.440s
> sys 0m8.343s
>
> improvement - 32%
>
> -bash-3.00$ time pg_restore -U postgres -p 5435 -m 4 -d rttest /tmp/rtout.pz
> ...
> real 9m45.056s
> user 1m1.892s
> sys 0m8.980s
>
> improvement - 49%
>
> The system only has 4 cores, but here are the results with "-m 8":
>
> -bash-3.00$ time pg_restore -U postgres -p 5435 -m 8 -d rttest /tmp/rtout.pz
> ...
> real 8m15.320s
> user 0m55.206s
> sys 0m8.678s
>
> improvement - 53%
>
>
>

Interesting.

Can you try with two changes? Turn fsync off, and use the
--truncate-before-load switch.

In general, though, this is fairly much in line with other experience,
i.e. we can get up to about n/2 times speedup with n cores.

thanks

andrew