Re: WIP parallel restore patch - Mailing list pgsql-hackers

On Thu, Nov 20, 2008 at 02:26:14PM -0500, Andrew Dunstan wrote:
>
>
> 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
>
Okay, here is the same test run with:

Cheers,
Ken

--------------------------------------------------------------------   fsync = off   --truncate-before-load

-bash-3.00$ time pg_restore -U postgres -p 5435 --truncate-before-load -d rttest/tmp/rtout.pz
...
real    16m25.031s
user    1m3.707s
sys     0m8.776s
improvement - 0%

-bash-3.00$ time pg_restore -U postgres -p 5435 -m 2 --truncate-before-load -d r
ttest /tmp/rtout.pz
...
real    10m26.730s
user    0m48.782s
sys     0m7.214s
improvement - 36%

-bash-3.00$ time pg_restore -U postgres -p 5435 -m 4 --truncate-before-load -d r
ttest /tmp/rtout.pz
...
real    8m5.061s
user    0m48.657s
sys     0m7.602s
improvement - 51%

-bash-3.00$ time pg_restore -U postgres -p 5435 -m 8 --truncate-before-load -d r
ttest /tmp/rtout.pz
...
real    6m18.787s
user    0m45.361s
sys     0m7.811s
improvement - 62%