Re: How to get parallel restore in PG 8.4 to work? - Mailing list pgsql-performance

From henk de wit
Subject Re: How to get parallel restore in PG 8.4 to work?
Date
Msg-id COL104-W61EE9F7AF2E58BCC54F122F58B0@phx.gbl
Whole thread Raw
In response to Re: How to get parallel restore in PG 8.4 to work?  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: How to get parallel restore in PG 8.4 to work?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
Hi,

> henk de wit <henk53602@hotmail.com> writes:
>> For performance reasons (obviously ;)) I'm experimenting with parallel restore in PG 8.4. [...] I got this message however:
>> [...]
>> pg_restore: [archiver] WARNING: archive is compressed, but this
>> installation does not support compression -- no data will be available

> As far as one can tell from here, you built *without* zlib support.
> This is unrelated to parallel restore as such.

I see. Thanks for the confirmation. I would have sworn I built with zlib support, but obviously I did something wrong. For some reason that I can't remember now, I did omit support for readline. Could that have anything to do with it, or are those completely unrelated?

To continue testing, I imported a PG 8.3 dump in the plain format into PG 8.4, dumped this again in the custom format and imported that again into PG 8.4 using the parallel restore feature. This proved to be very beneficial. Top shows that all the cores are being used:

./pg_restore -p 5433 -h localhost -d db_test -j 8 -Fc
/ssd/tmp/test_dump.custom

top - 11:33:37 up 1 day, 18:07,  5 users,  load average: 5.63, 2.12, 0.97
Tasks: 187 total,   7 running, 180 sleeping,   0 stopped,   0 zombie
Cpu0  : 91.7%us,  8.0%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.3%si, 0.0%st
Cpu1  : 90.0%us,  9.3%sy,  0.0%ni,  0.7%id,  0.0%wa,  0.0%hi,  0.0%si, 0.0%st
Cpu2  : 81.5%us, 15.9%sy,  0.0%ni,  2.3%id,  0.0%wa,  0.0%hi,  0.3%si, 0.0%st
Cpu3  : 87.0%us, 10.3%sy,  0.0%ni,  2.3%id,  0.0%wa,  0.0%hi,  0.3%si, 0.0%st
Cpu4  : 91.4%us,  8.0%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.3%hi,  0.3%si, 0.0%st
Cpu5  : 66.8%us, 16.3%sy,  0.0%ni,  4.3%id, 11.0%wa,  0.0%hi,  1.7%si, 0.0%st
Cpu6  : 76.0%us, 12.7%sy,  0.0%ni,  0.0%id, 10.7%wa,  0.0%hi,  0.7%si, 0.0%st
Cpu7  : 97.3%us,  2.3%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.3%si, 0.0%st
Mem:  33021204k total, 32861900k used,   159304k free,       40k buffers
Swap:  7811064k total,     2164k used,  7808900k free, 29166332k cached


The performance numbers are quite amazing. The dump is approximately 19GB in size and the filesystem I use is xfs on Debian Lenny. Using the normal restore (with a single process) the time it takes to do a full restore is 45 minutes, when using 8 processes this drops to just 14 minutes and 23 seconds. Using 16 processes it drops further to just 11 minutes and 46 seconds.

I still have some work to do to find out why dumping in the custom format is so much slower. Unfortunately I forgot to time this exactly, but my feeling was that it was 'very slow'. I'll try to get some exact numbers though.

Kind regards,
Henk





What can you do with the new Windows Live? Find out

pgsql-performance by date:

Previous
From: Mahu Vasile
Date:
Subject: PostgreSQL
Next
From: Tom Lane
Date:
Subject: Re: How to get parallel restore in PG 8.4 to work?