Re: Speeding up pg_upgrade

Поиск
Список
Период
Сортировка
От Alexander Kukushkin
Тема Re: Speeding up pg_upgrade
Дата
Msg-id CAFh8B=mG-JUVBmZJdnUNYXLukvUzCbwHumyGGianbGbN0Vj0Hw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Speeding up pg_upgrade  (Bruce Momjian <bruce@momjian.us>)
Ответы Re: Speeding up pg_upgrade  (Stephen Frost <sfrost@snowman.net>)
Список pgsql-hackers
Hi,


Yes, dump/reload of analyze statistics seems like a better use of time.
I have avoided it since it locks us into supporting the text
respresentation of data type, but at this point it might be worth it.


Couple of months ago we at Zalando upgraded a few databases of different sizes to 9.6.
During preparations to the I've found 2.5 pain-points:

1. We are using schema-based api deployment. Basically ~every week we create a new schema in the database and hundreds of stored procedures in it.
    Off course we remove old API schemas and trying not to keep more than last 10. Before the upgrade we basically dropped all API schemas except the one used in production.
    And even in this case dump-restore phase was taking much more time than relinking of datafiles.
    Unfortunately I don't have any numbers right now, but usually run of pg_upgrade was taking about 30-35 seconds, and about 2/3 of the time was spend in dump-restore.

2 ANALYZE phase is a pain. I think everybody agrees with it. 

2.5 Usually ANALYZE stage 1 completes quite fast and performance becomes reasonable, except one case: some of the columns might have non default statistics target.
    It breaks `vacuumdb --analyze-in-stages`, because those specific columns it will not use value of default_statistics_target provided by vacuumdb.
    What I did - reset those non default values right before running pg_upgrade and restored them only when analyze was completed. Off course after that I've re-analyze those columns.



Regards,
Alexander Kukushkin

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Rushabh Lathia
Дата:
Сообщение: Re: [HACKERS] Parallel tuplesort (for parallel B-Tree index creation)
Следующее
От: Anthony Bykov
Дата:
Сообщение: Re: Transform for pl/perl