Комментарии 6
В 10-ке ждем «человеческое»: партиционирование
Возможно, но судя по этому
CREATE TABLE book_history_2016_09
PARTITION OF book_history
FOR VALUES FROM ('2016-09-01 00:00:00') TO ('2016-10-01 00:00:00');
и по этому Текущие ограничения партиционирования в 10 это пока красивая надстройка над наследованием плюс механизм раскидывания вставляемых записей. Глобальных индексов, ключей и уж тем более правильного планирования и отсечения партиций в запросах там ещё нет. Это всё надо будет пилить и пилить. То есть счастье похоже будет, но не скоро.
CREATE TABLE book_history_2016_09
PARTITION OF book_history
FOR VALUES FROM ('2016-09-01 00:00:00') TO ('2016-10-01 00:00:00');
и по этому Текущие ограничения партиционирования в 10 это пока красивая надстройка над наследованием плюс механизм раскидывания вставляемых записей. Глобальных индексов, ключей и уж тем более правильного планирования и отсечения партиций в запросах там ещё нет. Это всё надо будет пилить и пилить. То есть счастье похоже будет, но не скоро.
pg_pathman прекрасен и развивают они его очень быстрыми темпами. Уже более полугода используется в продакшене и с тех пор все счастливы (разбили распухшую за год до 200 ГБ табличку по месяцам).
Спасибо большое за вашу статью, сами мы страшно ленивые и пишем редко. А вы и реальные кейсы описали, и вердикт хороший вынесли :)
Как один из разработчиков pg_pathman, хочу обозначить несколько важных моментов:
1) Методика тестирования все-таки не идеальна. Во-первых, виртуализация и дисковый кеш Windows оказывают свое влияние, поэтому времени исполнения запросов доверять мы не можем, а это очень важный критерий. Конечно, страничные чтения тоже важны, но их нельзя напрямую использовать для оценки стоимости выполнения запросов. Необходимо также учитывать, что горячие партиции практически не будут вымываться из shared_buffers, при вычислении join'ов будут тратиться ресурсы CPU на проекцию и сборку кортежей, не учитывается стоимость вычисления функций и многие другие вещи.
2) Можно было бы не сбрасывать файловый кеш, а наоборот, рассмотреть ситуацию (в вакууме), когда все данные попали в shared_buffers. Это позволило бы дать представление о преимуществах pg_pathman при отсечении лишних секций.
3) Раз уж зашла речь о количестве секций: с самого начала наш проект стремился оптимизировать кейс с большим количеством секций. Многие оптимизации опираются на наш кеш, по которому осуществляется бинарный поиск подходящих партиций (или поиск по хешу ключа партицирования). Эти преимущества не получится показать на маленьком числе партиций, потому что в таком случае даже брутфорс по check constraints (который применяет ванильный постгрес) окажется вполне приемлемым решением.
4) Время исполнения первого запроса не очень показательно, т.к. инициализируется syscache постгреса и наш собственный кеш (как и мы, он тоже ленивый :) Можно было бы показать результат прогона запросов при помощи pgbench, чтобы сгладить выбросы. Кроме того, можно применить prepared statements для исключения времени планирования. В таком случае запросы, которые pg_pathman дольше планировал, заиграют новыми красками.
Как один из разработчиков pg_pathman, хочу обозначить несколько важных моментов:
1) Методика тестирования все-таки не идеальна. Во-первых, виртуализация и дисковый кеш Windows оказывают свое влияние, поэтому времени исполнения запросов доверять мы не можем, а это очень важный критерий. Конечно, страничные чтения тоже важны, но их нельзя напрямую использовать для оценки стоимости выполнения запросов. Необходимо также учитывать, что горячие партиции практически не будут вымываться из shared_buffers, при вычислении join'ов будут тратиться ресурсы CPU на проекцию и сборку кортежей, не учитывается стоимость вычисления функций и многие другие вещи.
2) Можно было бы не сбрасывать файловый кеш, а наоборот, рассмотреть ситуацию (в вакууме), когда все данные попали в shared_buffers. Это позволило бы дать представление о преимуществах pg_pathman при отсечении лишних секций.
3) Раз уж зашла речь о количестве секций: с самого начала наш проект стремился оптимизировать кейс с большим количеством секций. Многие оптимизации опираются на наш кеш, по которому осуществляется бинарный поиск подходящих партиций (или поиск по хешу ключа партицирования). Эти преимущества не получится показать на маленьком числе партиций, потому что в таком случае даже брутфорс по check constraints (который применяет ванильный постгрес) окажется вполне приемлемым решением.
4) Время исполнения первого запроса не очень показательно, т.к. инициализируется syscache постгреса и наш собственный кеш (как и мы, он тоже ленивый :) Можно было бы показать результат прогона запросов при помощи pgbench, чтобы сгладить выбросы. Кроме того, можно применить prepared statements для исключения времени планирования. В таком случае запросы, которые pg_pathman дольше планировал, заиграют новыми красками.
1) Методика тестирования все-таки не идеальна.
Тут согласен, к сожалению, у меня нет обычной линукс машины, чтобы протестировать более чисто. Так что да, на время выполнения запросов лучше не смотреть.
С остальным не согласен, я хотел сравнить логику работы обычного партиционирования и pg_pathman на большой бд, то есть на той бд для которой нужно партиционирование. Для этого нужно было смоделировать большую бд. У неё есть собственно сама большая бд (у меня партиционированные таблицы заняли почти 6 гигов) и мальнький кэш (shared_buffers = 512M) по сравнению с самой бд. В таких бд обычно есть дефицит кэша бд, и их узкое место — работа с диском.
Процессорное время там может быть узким местом, только если бд неправильно спроектирована, ну или в каких-то сильно специфических случаях.
Хотя, можно было бы, конечно, оперативу у виртуалки убавить и размер shared_buffers уменьшить. И индексы, возможно, зря создал, но без них схема на нормальную бд была бы вообще не похожа.
Конечно, страничные чтения тоже важны, но их нельзя напрямую использовать для оценки стоимости выполнения запросов.
Первым и главным критерием для запросов в статье является сработало ли отсечение партиций или нет. А в качестве второго критерия использовать чтения страниц, я считаю, даже нужно. При выполнении запроса по партиционным таблицам, некоторая часть данных конечно будет в кэше, но какая это будет часть зависит от количества просматриваемых страниц. То есть чем меньше страниц смотрим — тем меньше читаем с диска.
Тут ещё надо учесть, что разница в два раза количества страниц здесь, она на большой бд с 300-ми партициями легко может превратиться в 200 и более раз.
Можно было бы не сбрасывать файловый кеш, а наоборот, рассмотреть ситуацию (в вакууме), когда все данные попали в shared_buffers.
Ну эта ситуация не типична для большой бд. И если бы вся бд уместилась в буфере, то непартиционированная схема была бы быстрее обоих рассмотренных вариантов. :) Но так как бд была больше кэша, то всю её туда уместить не получилось бы. Сделать же одинаковое начальное содержимое кэша для всех запросов было сложно, поэтому я просто перезагружал постгрес, чтобы в буфере ничего не было.
Раз уж зашла речь о количестве секций: с самого начала наш проект стремился оптимизировать кейс с большим количеством секций. Многие оптимизации опираются на наш кеш, по которому осуществляется бинарный поиск подходящих партиций (или поиск по хешу ключа партицирования).
Я очень рад, что такая оптимизация в pg_pathman есть, но я про неё не знал и поэтому не протестировал.
На большом количестве партиций она может сэкономить процессорное время, это хорошо.
Кстати, я ж протестировал две константы на 30 партициях и план таки не изменился, вот что обидно.
Но на это надо будет багу постгресовцам написать — у них с планированием limit проблемка похоже есть. Будет свободное время — напишу.
Время исполнения первого запроса не очень показательно, т.к. инициализируется syscache постгреса и наш собственный кеш (как и мы, он тоже ленивый :) Можно было бы показать результат прогона запросов при помощи pgbench, чтобы сгладить выбросы. Кроме того, можно применить prepared statements для исключения времени планирования. В таком случае запросы, которые pg_pathman дольше планировал, заиграют новыми красками.
Вот поэтому на время я особо и не смотрел, а смотрел на страницы. Что касается pgbench, то опять же, это не типичный случай когда всё уже в кэше.
Главное это срабатывание отсечения партиций — оно даст основной эффект, все остальные преимущества оптимизаций pg_pathman будут не таким большим, но приятным бонусом.
Спасибо за комментарий.
Раньше наша жизнь была серой и однообразной, мы создавали новые секции, триггеры и т.д. вручную. Страшно было даже подумать о том, чтобы разбить существующую распухшую таблицу на секции на боевой базе. А потом появился pg_pathman. funbringer спасибо большое вам и вашим коллегам за это, ваш вклад трудно переоценить!
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Партиционирование в postgres 9.x. Использование pg_pathman для оптимизации вставки и отсечения (pruning) партиций