Как одно изменение конфигурации PostgreSQL улучшило производительность медленных запросов в 50 раз

Публикация № 1023353

Администрирование - Оптимизация БД (HighLoad)

87
В связи с санкциями и другими событиями сейчас все более и более актуальна тема перевода ПО компаний на отечественное и свободное программное обеспечение. Одной из самых востребанных СУБД на рынке на данный момент является PostgreSQL - надежная, высокопроизводительная и хорошо масштабируемая СУБД, которая является прямым конкуретном таким крупным компаниям с их топовыми продуктами, как Oracle, IBM и Microsoft. Однако каждый, кто переходит на PostgreSQL, сталкивается с трудностями, прежде всего с настройкой и производительностью. Не обошли проблемы с производительностью "слоника" и меня. Предлагаю вашему вниманию перевод статьи "How a single PostgreSQL config change improved slow query performance by 50x" автора Pavan Patibandla, которая мне помогла улучшить производительность PostgreSQL.

В Amplitude наша цель — предоставить простую в использовании интерактивную аналитику продуктов, чтобы каждый мог найти ответы на свои вопросы о продукте. Чтобы обеспечить удобство работы, Amplitude должен быстро предоставить эти ответы. Поэтому, когда один из наших клиентов пожаловался на то, сколько времени потребовалось для загрузки раскрывающегося списка свойств события в пользовательском интерфейсе Amplitude, мы приступили к детальному изучению проблемы.

Отслеживая задержку на разных уровнях, мы поняли, что одному конкретному запросу PostgreSQL потребовалось 20 секунд для завершения. Для нас это стало неожиданностью, так как обе таблицы имеют индексы в соединяемом столбце.

image

Медленный запрос


План выполнения PostgreSQL для этого запроса был для нас неожиданным. Несмотря на то, что в обеих таблицах есть индексы, PostgreSQL решил выполнить Hash Join с последовательным сканированием большой таблицы. Последовательное сканирование большой таблицы занимало большую часть времени запроса.

image

План выполнения медленого запроса


Я изначально подозревал, что это может быть из-за фрагментации. Но после проверки данных я понял, что в эту таблицу данные только добавляются и практически не удаляются оттуда. Так как очистка места с помощью VACUUM здесь не очень поможет, я начал копать дальше. Затем я попробовал этот же запрос на другом клиенте с хорошим временем ответа. К моему удивлению, план выполнения запроса выглядел совершенно иначе!

image

План выполнения того же запроса на другом клиенте


Интересно, что приложение A получило доступ только к 10 раз большему количеству данных, чем приложение B, но время отклика было в 3000 раз больше.

Чтобы увидеть альтернативные планы запросов PostgreSQL, я отключил хеш-соединение и перезапустил запрос.

image

Альтернативный план выполнения для медленного запроса


Ну вот! Тот же запрос завершается в 50 раз быстрее при использовании вложенного цикла вместо хэш-соединения. Итак, почему PostgreSQL выбрал худший план для приложения A?

При более тщательном рассмотрении предполагаемой стоимости и фактического времени выполнения для обоих планов предполагаемые соотношения стоимости и фактического времени выполнения были очень разными. Основным виновником этого несоответствия была оценка стоимости последовательного сканирования. PostgreSQL подсчитал, что последовательное сканирование было бы лучше, чем 4000+ сканирований индекса, но в действительности сканирование индекса было в 50 раз быстрее.

Это привело меня к параметрам конфигурации random_page_cost и seq_page_cost. Значения PostgreSQL по умолчанию 4 и 1 для random_page_cost, seq_page_cost, которые настроены для HDD, где произвольный доступ к диску дороже, чем последовательный доступ. Однако эти затраты были неточными для нашего развертывания с использованием тома gp2 EBS, которые являются твердотельными накопителями. Для нашего развертывания случайный и последовательный доступ практически одинаков.

Я изменил значение random_page_cost на 1 и повторил запрос. На этот раз PostgreSQL использовал Nested Loop, и запрос выполнялся в 50 раз быстрее. После изменения мы также заметили значительное снижение максимального времени отклика от PostgreSQL.

image

Общая производительность медленного запроса значительно улучшилась


Если вы используете SSD и используете PostgreSQL с конфигурацией по умолчанию, я советую вам попробовать настроить random_page_cost и seq_page_cost. Вы можете быть удивлены сильным улучшением производительности.

От себя добавлю, что я выставил минимальные параметры seq_page_cost = random_page_cost = 0.1, чтобы отдать приоритет данным в памяти (кэш) над процессорными операциями, так как у меня выделено большое количество ОЗУ для PostgreSQL (размер ОЗУ превышает размер базы на диске)

Оригинал статьи Источник на хабре

87

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. spectre1978 50 18.03.19 17:03 Сейчас в теме
Респект и звезда! Утащу к себе.
2. user-z99999 18 18.03.19 17:11 Сейчас в теме
Для повышения рейтинга можно копировать статьи Хабр?
))
SerVer1C; Plotks2017; SavR11; genayo; +4 Ответить
4. w.r. 212 18.03.19 18:22 Сейчас в теме
(2) статья не моя, а перевод мой - с копирайтами все впорядке, не переживайте
3. capitan 1187 18.03.19 17:18 Сейчас в теме
Ну то есть Pavan Patibandla получал планы запросов и курил мануалы )
Это не оно случайно ?
Прикрепленные файлы:
igee12; bajiepka; Plotks2017; TODD22; +4 Ответить
5. w.r. 212 18.03.19 18:31 Сейчас в теме
(3) то, что описывают 1С - частные случаи, не всегда приводящие к выигрышу в производительности. Например, рекомендуют отключать Nested Loops. А что получается, когда поиск значений для соединения таблиц идёт с помощью их хэшей (Hash Join) - видно в статье - замедление работы почти в 50 раз по сравнению с поиском с помощью вложенных циклов (Nested Loops). Есть очень хорошая статья на хабре по базам данных https://habr.com/ru/company/mailru/blog/266811/ доходчиво и очень понятно написано
mickey.1cx; +1 Ответить
6. Dream_kz 89 18.03.19 19:51 Сейчас в теме
(5) Ну вообще как бы да, эти настройки описаны в мануалах
https://kb.1c.ru/articleView.jsp?id=91
random_page_cost = 1.5-2.0 для RAID, 1.1-1.3 для SSD
Стоимость чтения рандомной страницы, на которую будет опираться оптимизатор (по-умолчанию 4). Практическое значение параметра должно зависеть от «seek time» дисковой системы: чем он меньше, тем меньше должно быть значение random_page_cost (но не менее 1.0) . Излишне большое значение параметра увеличивает склонность PostgreSQL к выбору планов с сканированием всей таблицы (PostgreSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). Оценка стоимости последовательного чтения делается, в свою очередь, с учетом параметра seq_page_cost, который равен по умолчанию 1.


https://postgrespro.ru/docs/postgrespro/9.5/runtime-config-query
Произвольный доступ к механическому дисковому хранилищу обычно гораздо дороже последовательного доступа, более чем в четыре раза. Однако по умолчанию выбран небольшой коэффициент (4.0), в предположении, что большой объём данных при произвольном доступе, например, при чтении индекса, окажется в кеше. Таким образом, можно считать, что значение по умолчанию моделирует ситуацию, когда произвольный доступ в 40 раз медленнее последовательного, но 90% операций произвольного чтения удовлетворяются из кеша.

Если вы считаете, что для вашей рабочей нагрузки процент попаданий не достигает 90%, вы можете увеличить параметр random_page_cost, чтобы он больше соответствовал реальной стоимости произвольного чтения. И напротив, если ваши данные могут полностью поместиться в кеше, например, когда размер базы меньше общего объёма памяти сервера, может иметь смысл уменьшить random_page_cost. С хранилищем, у которого стоимость произвольного чтения не намного выше последовательного, как например, у твердотельных накопителей, так же лучше выбрать меньшее значение random_page_cost.
mickey.1cx; smit1c; Dach; headMade; +4 Ответить
8. w.r. 212 19.03.19 07:58 Сейчас в теме
(6) это все понятно. Просто вы знали, что именно изменение этих параметров может настолько увеличить производительность PG? Я вот лично не знал
9. Dream_kz 89 19.03.19 08:40 Сейчас в теме
(8) Там изменение любого параметра может влиять на производительность в самых неожиданных местах, причем как ускорять отдельные запросы, так и замедлять другие. Настройка в соответствии с документацией решает половину проблем
10. w.r. 212 19.03.19 09:47 Сейчас в теме
(9)
Там изменение любого параметра может влиять на производительность в самых неожиданных местах

Меня это как-то немного смущает. А вас?
11. Dream_kz 89 19.03.19 10:00 Сейчас в теме
(10) Нюансы тонкой настройки, есть в этом плюсы, что можно настроить как душе угодно, и минусы, что теряется простота и универсальность. Как по мне, оптимизатор запросов в MS SQL "умнее", но из-за этого им сложнее управлять. Но нельзя говорить что в PG он плохой, просто рассчитан на специалиста.
12. w.r. 212 19.03.19 10:05 Сейчас в теме
(11) тут даже сравнивать не стоит. MS, IBM и Oracle - это автоматическая коробка передач, а PG - механика. PG не плохой, просто мне лично из документации не сильно понятна степень влияния каждого параметра на производительность системы в целом. По хорошему за такое должна отвечать автоматика, но трудно требовать такого от свободного ПО, которое пишет некое сообщество
13. capitan 1187 19.03.19 16:59 Сейчас в теме
(12)
MS, IBM и Oracle - это автоматическая коробка передач
Вы просто не представляете сколько есть аналогичных настроек у MS. С аналогичным эффектом
7. ansh15 19.03.19 02:01 Сейчас в теме
Я понимаю, что название публикации также является переводом заголовка оригинала статьи, но некоторая броскость фразы "улучшило производительность медленных запросов в 50 раз" может дать и обратный эффект.
Народ, особенно, недавно приобщающийся к миру PostgreSQL(тоже пафос...), начнет выключать hash join для любых конфигураций - типовых. нетиповых, "в хлам переписанных", оригинальных самописных. Через некоторое время будет получать просадку производительности(зависания) в тех местах, где этого в принципе не должно было бы быть. Особенно может быть весело, если все типы конфигураций присутствуют одновременно. После чего будут раздаваться обиженно-гневные возгласы - ""Ваш этот Постгрес..." и далее все многообразие "непереводимых идиоматических выражений". "Да лучше бы мы MS SQL купили!"
Я это к тому, что результат изысканий автора статьи надо рассматривать с той точки зрения. что в ряде частных случаев изменение тех или иных параметров(или их совокупности), влияющих на оптимизацию работы СУБД может оказаться приемлемым. Для конкретной базы данных и конкретного запроса к ней. Как и в случае с 1С.
wowik; monkbest; fancy; w.r.; +4 Ответить
14. a.doroshkevich 461 19.03.19 17:03 Сейчас в теме
seq_page_cost = random_page_cost = 0.1
Так делать нельзя ни в коем случае.
seq_page_cost = 1 не просто так, а потому что является ориентиром всех остальных коэффициентов.
15. w.r. 212 19.03.19 18:37 Сейчас в теме
(14) 1C сами рекомендуют понизить seq_page_cost до 0.1 и random_page_cost до 0.4

https://its.1c.ru/db/metod8dev/content/4692/hdoc

А вы говорите так делать нельзя ни в коем случае. Каковы основания доверять вашим словам?
17. a.doroshkevich 461 20.03.19 12:57 Сейчас в теме
(15)Прочитайте документацию разработчика БД по этому параметру.

Переменные стоимости, описанные в данном разделе, задаются по произвольной шкале. Значение имеют только их отношения, поэтому умножение или деление всех переменных на один коэффициент никак не повлияет на выбор планировщика. По умолчанию эти переменные определяются относительно стоимости чтения последовательной страницы: то есть, переменную seq_page_cost удобно задать равной 1.0, а все другие переменные стоимости определить относительно неё. Но при желании можно использовать и другую шкалу, например, выразить в миллисекундах фактическое время выполнения запросов на конкретной машине.

Т.е. если вы изменили seq_page_cost, то должны пересмотреть все параметры планировщика опираясь на новый показатель.
Почти наверняка было бы достаточно выставить random_page_cost в 1 или 1,1 и получите то же самое, только не нарушая общую работу планировщика во всех других запросах.

Другое дело, если база полностью кэшируется в ОЗУ, вот тогда оба этих показателя можно снизить, так как стоимость выборки страницы, уже находящейся в ОЗУ, оказывается намного меньше, чем обычно.

Общий посыл такой - нет волшебной пули, есть комплекс настроек которые нужно применять в комплексе
19. w.r. 212 20.03.19 13:44 Сейчас в теме
(17)

Я проводил тесты и в этих тестах получал уменьшение времени выполнения запроса при снижении seq_page_cost с 1.0 до 0.1. Так что давайте оставим этот теоретический спор

И я писал выше, что у меня большие ресурсы выделены на кэш (effective_cache_size=16GB)
16. Gorus 37 20.03.19 10:50 Сейчас в теме
У меня также снижение параметра seq_page_cost = 0.1
привело к существенному повышению общей производительности. Правда
random_page_cost я ставил 0.4, сохраняя отношение одного к другому. Размер базы уже больше ОЗУ, но производительность не падает.
И спасибо за перевод)
18. a.doroshkevich 461 20.03.19 13:00 Сейчас в теме
Если посмотреть рекомендации 1С поновее чем 2012 год, то:

https://kb.1c.ru/articleView.jsp?id=91

Стоимость чтения рандомной страницы, на которую будет опираться оптимизатор (по-умолчанию 4). Практическое значение параметра должно зависеть от «seek time» дисковой системы: чем он меньше, тем меньше должно быть значение random_page_cost (но не менее 1.0) . Излишне большое значение параметра увеличивает склонность PostgreSQL к выбору планов с сканированием всей таблицы (PostgreSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). Оценка стоимости последовательного чтения делается, в свою очередь, с учетом параметра seq_page_cost, который равен по умолчанию 1.
20. inomaratadeath 21.03.19 10:35 Сейчас в теме
нубский вопрос... В тестах от Гилёва смена этих параметров на количество попугаев никак не влияет, чем бы ещё проверить профит?
21. w.r. 212 21.03.19 13:31 Сейчас в теме
(20)

Проверьте наиболее часто используемые запросы в вашей конфигурации в консоле запросов
Оставьте свое сообщение