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

18.03.19

База данных - HighLoad оптимизация

В связи с санкциями и другими событиями сейчас все более и более актуальна тема перевода ПО компаний на отечественное и свободное программное обеспечение. Одной из самых востребанных СУБД на рынке на данный момент является 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 (размер ОЗУ превышает размер базы на диске)

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

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2993    spyke    27    

42

Быстродействие типовой 1С

HighLoad оптимизация Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    5117    vasilev2015    19    

37

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7649    158    ZAOSTG    68    

96

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5986    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8874    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    5106    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16192    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. spectre1978 60 18.03.19 17:03 Сейчас в теме
Респект и звезда! Утащу к себе.
2. user-z99999 67 18.03.19 17:11 Сейчас в теме
Для повышения рейтинга можно копировать статьи Хабр?
))
SerVer1C; Plotks2017; SavR11; genayo; +4 Ответить
4. w.r. 643 18.03.19 18:22 Сейчас в теме
(2) статья не моя, а перевод мой - с копирайтами все впорядке, не переживайте
26. stopa85 33 17.03.24 09:39 Сейчас в теме
(2) Не говорите. Про эти два параметра на каждом заборе написано. Включая ИТС, ПГпро и официальную документацию.
3. capitan 2466 18.03.19 17:18 Сейчас в теме
Ну то есть Pavan Patibandla получал планы запросов и курил мануалы )
Это не оно случайно ?
Прикрепленные файлы:
igee12; bajiepka; Plotks2017; TODD22; +4 1 Ответить
5. w.r. 643 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 129 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. 643 19.03.19 07:58 Сейчас в теме
(6) это все понятно. Просто вы знали, что именно изменение этих параметров может настолько увеличить производительность PG? Я вот лично не знал
9. Dream_kz 129 19.03.19 08:40 Сейчас в теме
(8) Там изменение любого параметра может влиять на производительность в самых неожиданных местах, причем как ускорять отдельные запросы, так и замедлять другие. Настройка в соответствии с документацией решает половину проблем
10. w.r. 643 19.03.19 09:47 Сейчас в теме
(9)
Там изменение любого параметра может влиять на производительность в самых неожиданных местах

Меня это как-то немного смущает. А вас?
11. Dream_kz 129 19.03.19 10:00 Сейчас в теме
(10) Нюансы тонкой настройки, есть в этом плюсы, что можно настроить как душе угодно, и минусы, что теряется простота и универсальность. Как по мне, оптимизатор запросов в MS SQL "умнее", но из-за этого им сложнее управлять. Но нельзя говорить что в PG он плохой, просто рассчитан на специалиста.
12. w.r. 643 19.03.19 10:05 Сейчас в теме
(11) тут даже сравнивать не стоит. MS, IBM и Oracle - это автоматическая коробка передач, а PG - механика. PG не плохой, просто мне лично из документации не сильно понятна степень влияния каждого параметра на производительность системы в целом. По хорошему за такое должна отвечать автоматика, но трудно требовать такого от свободного ПО, которое пишет некое сообщество
13. capitan 2466 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 Ответить
22. Crazy_Max 82 28.06.19 13:41 Сейчас в теме
(7) Те, кто поступит именно так, как вы описали, пусть на самом деле покупают MS SQL. Всё абсолютно верно!
23. w.r. 643 28.06.19 18:47 Сейчас в теме
(22)

Купил, поставил и не паришься ) Если денег много - почему нет? )
Crazy_Max; +1 Ответить
14. a.doroshkevich 1407 19.03.19 17:03 Сейчас в теме
seq_page_cost = random_page_cost = 0.1
Так делать нельзя ни в коем случае.
seq_page_cost = 1 не просто так, а потому что является ориентиром всех остальных коэффициентов.
15. w.r. 643 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

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

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

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

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

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

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

И я писал выше, что у меня большие ресурсы выделены на кэш (effective_cache_size=16GB)
24. D_astana 110 22.11.20 19:31 Сейчас в теме
(19)Если я не ошибаюсь, этот параметр не дает память pg под кеш, а показывает планировщику сколько винде отведено оперативки под кэш файлов. А вот shared buffer бронируе оперативку под нужды pg
16. Gorus 48 20.03.19 10:50 Сейчас в теме
У меня также снижение параметра seq_page_cost = 0.1
привело к существенному повышению общей производительности. Правда
random_page_cost я ставил 0.4, сохраняя отношение одного к другому. Размер базы уже больше ОЗУ, но производительность не падает.
И спасибо за перевод)
18. a.doroshkevich 1407 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. 643 21.03.19 13:31 Сейчас в теме
(20)

Проверьте наиболее часто используемые запросы в вашей конфигурации в консоле запросов
a.doroshkevich; +1 Ответить
25. gubanoff 63 15.01.21 12:08 Сейчас в теме
(0) Поставил postgres 9.6.7 "из коробки" на ssd. Запустил тест Гилева на 8.3.12 на серверной базе сервер x64 - получил результаты 32.05 и 105 пользователей.
Затем настроил postgres по рекомендациям со статей инфостарта, 1с (в частности seq_page_cost = 0.1 + random_page_cost = 0.4) получил в тесте Гилева 26.18 и 120 пользователей.
Вот теперь думаю, что с этими результатами делать :)
Оставьте свое сообщение