0. w.r. 468 18.03.19 16:50 Сейчас в теме

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

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

Перейти к публикации

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

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

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

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

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

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

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

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

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

Проверьте наиболее часто используемые запросы в вашей конфигурации в консоле запросов
a.doroshkevich; +1 Ответить
Оставьте свое сообщение
Новые вопросы с вознаграждением
Автор темы объявил вознаграждение за найденный ответ, его получит тот, кто первый поможет автору.

Вакансии

Бизнес-архитектор 1С, ведущий консультант
Санкт-Петербург
Полный день

Руководитель проектов 1С
Санкт-Петербург
Полный день

Программист 1С
Краснодар
зарплата от 80 000 руб. до 160 000 руб.
Полный день

Консультант 1 С
Краснодар
зарплата от 50 000 руб. до 150 000 руб.
Полный день

Консультант-методолог 1С
Краснодар
зарплата от 110 000 руб.
Полный день