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