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

Поиск
Список
Период
Сортировка
От henk de wit
Тема Re: How to get parallel restore in PG 8.4 to work?
Дата
Msg-id COL104-W61EE9F7AF2E58BCC54F122F58B0@phx.gbl
обсуждение исходный текст
Ответ на Re: How to get parallel restore in PG 8.4 to work?  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: How to get parallel restore in PG 8.4 to work?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Mahu Vasile
Дата:
Сообщение: PostgreSQL
Следующее
От: Tom Lane
Дата:
Сообщение: Re: How to get parallel restore in PG 8.4 to work?