Re: How to get parallel restore in PG 8.4 to work?

Hi,
> henk de wit <henk53602(at)hotmail(dot)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
http://www.microsoft.com/windows/windowslive/default.aspx