Здравствуйте, хабровчане! Предлагаю вашему вниманию перевод статьи «Как одно изменение конфигурации PostgreSQL улучшило производительность медленных запросов в 50 раз» Паван Патибандла.
Это очень помогло мне улучшить производительность PostgreSQL. Наша цель в Amplitude — предоставить простую в использовании интерактивную аналитику продуктов, чтобы каждый мог найти ответы на свои вопросы о продуктах.
Чтобы обеспечить бесперебойную работу, Amplitude должна предоставлять эти ответы быстро.
Поэтому, когда один из наших клиентов пожаловался на то, что загрузка раскрывающегося списка свойств событий в пользовательском интерфейсе Amplitude занимает много времени, мы решили более внимательно изучить проблему.
Отслеживая задержку на разных уровнях, мы поняли, что выполнение одного конкретного запроса PostgreSQL занимало 20 секунд. Для нас это стало неожиданностью, поскольку в обеих таблицах есть индексы по соединяемому столбцу.
Медленный запрос 
План выполнения PostgreSQL этого запроса нас удивил.
Несмотря на то, что обе таблицы имеют индексы, PostgreSQL решил выполнить хэш-соединение с последовательным сканированием большой таблицы.
Последовательное сканирование большой таблицы занимало большую часть времени запроса.
План выполнения медленного запроса 
Сначала я подозревал, что это может быть связано с фрагментацией.
Но проверив данные, я понял, что данные только добавляются в эту таблицу и практически не удаляются оттуда.
Поскольку очистка территории ВАКУУМОМ здесь особо не поможет, я начал копать дальше.
Затем я попробовал тот же запрос на другом клиенте с хорошим временем отклика.
К моему удивлению, план выполнения запроса выглядел совершенно иначе! Запланируйте выполнение того же запроса на другом клиенте.
Интересно, что приложение А получило доступ всего в 10 раз к большему количеству данных, чем приложение Б, но время ответа было в 3000 раз быстрее.
Чтобы просмотреть альтернативные планы запросов PostgreSQL, я отключил хеш-соединение и повторно выполнил запрос.
Альтернативный план выполнения медленного запроса 
Ну вот! Тот же запрос выполняется в 50 раз быстрее при использовании вложенного цикла вместо хэш-соединения.
Так почему же PostgreSQL выбрал худший план для приложения А? При более внимательном рассмотрении расчетной стоимости и фактического времени завершения для обоих планов соотношение расчетной стоимости и фактического времени завершения сильно различалось.
Главной причиной этого несоответствия была смета затрат на последовательное сканирование.
PostgreSQL подсчитал, что последовательное сканирование будет лучше, чем сканирование более 4000 индексов, но на самом деле сканирование индекса было в 50 раз быстрее.
Это привело меня к параметрам конфигурации случайная_страница_стоимость И seq_page_cost .
Параметры PostgreSQL по умолчанию 4 и 1 Для случайная_страница_стоимость , seq_page_cost , которые настроены для HDD, где произвольный доступ к диску обходится дороже, чем последовательный доступ.
Однако эти затраты не были точными для нашего развертывания с использованием тома gp2 ЭБС , которые представляют собой твердотельные накопители.
Для нашего развертывания произвольный и последовательный доступ практически одинаковы.
я изменил значение случайная_страница_стоимость на 1 и повторил просьбу.
На этот раз PostgreSQL использовал вложенный цикл, и запрос выполнялся в 50 раз быстрее.
После изменения мы также заметили значительное уменьшение максимального времени ответа PostgreSQL. Общая производительность медленных запросов значительно улучшилась.
Если вы используете SSD и используете PostgreSQL с конфигурацией по умолчанию, я предлагаю вам попробовать установить случайная_страница_стоимость И seq_page_cost .
Вы можете быть удивлены резким улучшением производительности.
От себя добавлю, что ставлю минимальные параметры seq_page_cost = случайная_стоимость_страницы = 0,1 чтобы отдать приоритет данным в памяти (кэшу) над операциями ЦП, поскольку у меня большой объем оперативной памяти, выделенный для PostgreSQL (размер оперативной памяти больше, чем размер базы данных на диске).
Не очень понятно, почему сообщество postgres до сих пор использует настройки по умолчанию, актуальные для сервера с небольшим объемом оперативной памяти и HDD, а не для современных серверов.
Я надеюсь, что это скоро будет исправлено.
Теги: #postgresql #производительность postgresql
-
Реверс Бренда
19 Oct, 24 -
Sun Tech Days 2010 - Как Это Было
19 Oct, 24 -
Перевод? Медведь!
19 Oct, 24
