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

Здравствуйте, хабровчане! Предлагаю вашему вниманию перевод статьи «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 (размер ОЗУ превышает размер базы на диске). Не очень понятно, почему сообщество postgres до сих пор использует настройки по умолчанию, актуальные для сервера с небольшим объемом ОЗУ и дисками HDD, а не для современных серверов. Надеюсь в скором времени это исправят.
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 36

    –14
    Спасибо но уже где-то было ...https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-by-50x-85593b8991b0
    • НЛО прилетело и опубликовало эту надпись здесь
      0
      Интересно, а есть рекомендации по оптимизации Microsoft SQL Server под твердотельные накопители?
        0
        M$ где-то хвалился что они сами детектируют SSD/HDD (точнее говоря измеряют метрики).
          0
          Ну здесь они душой вроде бы не кривили. Сколько я не копал, сильного изменения производительности относительно настроек по дефолту на твердотельных не заметил.
          Всё равно самым узким местом была 1С, даже с учётом того, что базы размерами всего гигов по 50-100 были. Те, несколько процентов, что удалось отвоевать на конфигах mssql, всё равно терялись на уровне производительности логики вышестоящего приложения (того самого 1С).
        +17
        размер ОЗУ превышает размер базы на диске). Не очень понятно, почему сообщество postgres до сих пор использует настройки по-умолчанию, актуальные для сервера с небольшим объемом ОЗУ и дисками HDD
        У вас не ОЗУ большое, у вас просто база маленькая)
        • НЛО прилетело и опубликовало эту надпись здесь
            0
            Мое решение такие СУБД не поддерживает + у меня несколько баз на инстансе и планируется увеличение количества баз
          0
          Эта тема целиком раскрыта тут www.youtube.com/watch?v=aaecM4wKdhY
            +7
            Не очень понятно, почему сообщество postgres до сих пор использует настройки по-умолчанию, актуальные для сервера с небольшим объемом ОЗУ и дисками HDD, а не для современных серверов. Надеюсь в скором времени это исправят.

            Не исправят, потому что это не ошибка. Параметры постгреса по умолчанию установлены таким образом, чтобы база запускалась на чём угодно, включая холодильники и кофеварки. Для оптимизации производительности его нужно настраивать (сюрприз!). Поздравляю автора статьи с этим открытием.
              +2
              Исправляют что-то не только из-за ошибок, но и из-за добавления удобства.

              В mysql, например, раньше были примеры конфигов — my-small, my-large, my-medium, my-huge, в базе годились как и для холодильника (меньше 64мб), так и для вполне приличного вдс (1-2гб). И даже был отдельный пример для 4гб с подключением иннодб my-innodb-heavy-4G

              Да, безусловно, в идеальном мире, базу данных должен настраивать администратор под конкретные задачи, блаблабла. Но в подавляющем большинстве случаев этих конфигов хватало для того, что бы на свежеподнятом вдс не напарываться на дурацкие проблемы прямо из коробки и не надо было звать дорогостоящего администратора или разбираться с этими нюансами самостоятельно.
                0
                Безусловно, в идеальном мире можно настраивать базу только исходя из доступных ресурсов железа. Но на самом деле не менее важен профиль нагрузки, характер хранимых данных и другие вещи. Типовой конфигурации постгреса хватает для работы. Если вас перестала устраивать его производительность — значит, пора начать думать (хотя наверное правильнее было бы делать это сразу) или нанимать специалиста.
                +7
                Подход не очень правильный. Автоконфигуратор от Постгреса для настройки под типовые конфигурации был бы очень полезен и приятен.

                Юзкейс:
                Надо поставить Постгрес вот на эту машину. Конфигурация машины известна, примерный профиль нагрузки тоже известен. Хочется в красивой менюшке потыкать кнопочки и готово. Более-менее оптимальные настройки выставлены.
                Добавить возможность сохранить конфиг и применять его на другие такие же машины и вообще хорошо будет.
                  +6
                  так ведь есть
                  pgconfigurator.cybertec.at
                    +4

                    И вот ещё один, попроще: https://pgtune.leopard.in.ua/
                    Вбиваете количество ОЗУ, тип дисков и вам дают минимальные изменения, которые нужно внести в конфиг под эту конфигурацию.

                      +1
                      Тут уж больно минимальные. Оценить конкретные стоимости seq/random/cpu/memory можно только на своей железке. И какой-нить pg_tune --analyze был бы очень полезен.
                      А сейчас вся эта оценка перекладывается на админа, а разработчики постгре умывают лапки.
                        0
                        спасибо за ссылку, pgtune меняет random_page_cost при смене типа дисков с HDD на SSD, а выше упомянутый pgconfigurator — нет.
                      +2
                      Почему в коммерческих базах данных даже на версиях типа Express есть автонастройка параметров, тем более параметров планировщика запросов, а в СУБД, которая позиционирует себя как конкурент промышленным коммерческим СУБД — нет. Это для меня действительно сюрприз со знаком -
                      +3
                      От себя добавлю, что я выставил минимальные параметры seq_page_cost = random_page_cost = 0.1, чтобы отдать приоритет данным в памяти над дисковыми

                      По-моему автор чего-то сильно нагородил здесь. Открываем документацию:
                      By default, these cost variables are based on the cost of sequential page fetches; that is, seq_page_cost is conventionally set to 1.0

                      Если я правильно понял, то выставив в 0.1 он просто ухудшил стоимость остальных операций в 10 раз (остальные это сканы на CPU), т.е. предпочёл использовать обращение к диску вместо использования процессора. Т.е. всё наоборот сделал, относительно заявленного.

                        0
                        Спасибо, исправил. Только смысл в том, что данные из ОЗУ выбираются быстрее, чем выполняются вычисления процессора. Поэтому стоимость процессорных операций понижена относительно данных в памяти
                        0
                        А как это всё на AWS? Там наверное из коробки настроено?
                          +1

                          Если имеете в виду RDS, то нет. По дефолту 4. Приходится все настраивать руками.

                          0
                          я может придераюсь и не знаю сколько и каких данных в таблицах, но где limit в запросе? если всё же аналитику считать, то скорее всего данный запрос будет всё хуже и хуже работать с увеличением данных.
                            +2
                            По поводу тюнинга PostgreSQL есть такой проект github.com/jfcoz/postgresqltuner
                              +1

                              Грустно, когда люди работают с дефолтными настройкам

                                +3
                                Грустно, что в 21 веке, когда активно развиваются и внедряются нейросети и ИИ, мы в самой технологичной отрасли вынуждены экспериментальным способом находить оптимальные настройки. Я бы понял это в 70х, 80х или даже в 90х, но не в 2020 году
                                  0
                                  Эксперимент тут ни при чём. Один раз вдумчиво прочитать 10-15 страниц официальной документации ради получения знаний, достаточных для 90% случаев настройки — довольно невысокая цена, как мне кажется… то, что люди, как обычно, начинают читать мануалы только когда всё встало колом — отдельная проблема, к постгресу отношения не имеющая.
                                    +3
                                    Вы, видимо, особо не ознакамливались с документацией по Postgres, иначе бы знали, что она содержит более 3 тыс страниц, из них настройка сервера занимает 90 repo.postgrespro.ru/doc/pgpro/11.1.1/ru/postgres-A4.pdf так что не нужно про 10-15 страниц и 90% случаев. Это балабольство

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

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

                                      Я утверждаю, что для настройки постгреса, подходящей в подавляющем количестве случаев достаточно ознакомиться и осознать не все 3000 страниц и даже не 90. Знания о параметрах, связанных с оперативной памятью, I/O, bgwriter`ом и вакуумом расположены в документации достаточно компактно. Отправной точкой для, как вы выражаетесь, «адресного поиска» может служить любой онлайн-калькулятор вроде pgtune.leopard.in.ua.
                                        +1
                                        А еще неплохо бы выучить тогда архитектуру конкретной СУБД, чтобы понимать какой параметр ОЗУ или I/O или CPU будет оказывать большее или меньшее влияние на производительность. Ставить бездумно параметры с сайтов типа pgtune.leopard.in.ua и смотреть, что получится, не понимая их сути — не самая здравая идея
                                          0
                                          Вы опять приписываете мне идеи, которых я не высказывал.

                                          Какой параметр в каждом конкретном случае окажет большее влияние — зависит от многих критериев, в том числе не связанных с железом и никакая автонастройка все их не в состоянии объять. Разумеется, бездумно ничего ставить не нужно, именно для этого я и рекомендую обратиться к документации, а точнее тем её немногим страницам, которые как раз описывают влияние на СУБД. Pgtune я привёл в качестве примера отправной точки, раз уж вы отказываете людям в способности загуглить, что в первую очередь стоит настраивать после установки постгреса.
                                            0
                                            Однако почему-то в коммерческих СУБД, например в DB2, есть автонастройка параметров (AUTOMATIC) www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.config.doc/doc/r0005181.html + реализованы профили настроек для различных приложений через переменную DB2_WORKLOAD, например 1C, SAP и тд
                                              0
                                              Хорошо, когда у вас база, на которой крутится нечто, с уже хорошо хорошо известными параметрами и имеющее профиль настроек. Но это скорее исключение в инновационной сфере, а поддержка всяких энтерпрайзных продуктов — это тема отдельная и не очень-то относящаяся к постгресу по понятным причинам. Он скорее не для мамонтятины, а для чего-то нового.

                                              Ну и я, если бы был на свою голову, DBA DB2 или там Оракла какого-нибудь, не стал бы безоговорочно доверять подобным автонастройкам и профилям, хотя бы до проведения натурных испытания со сравнением с собственной конфигурацией.
                                                0
                                                Как правило реляционные СУБД используются для широко распространённых продуктов — различных бухгалтерских, ERP систем и других систем различного финансового учёта, да и вообще, для хранения больших объемом структурированной информации. Там мало нужны инновации и свои велосипеды. Системные требования давно прописаны и конфигурации известны. И именно для таких продуктов старается сейчас позиционировать себя PGSQL, в частности компания Postgres Pro активно пиарится для 1С. Потому что инновации — это конечно хорошо, но основная маржа будет с рядового потребителя с массовым продуктом
                                                  0
                                                  Ну так и все распространённые рецепты для постгреса тоже давно известны, благо, комьюнити у него обширное и активное. Никакой неподъёмной задачи конфигурация базы из себя не представляет и отсутствие автонастройки и профилей не является значимым недостатком (мы же всё ещё про IT-бизнес, у них обычно с экспертизой проблем нет).

                                                  Возвращаясь к теме статьи — то, что её автор узнал для себя про веса операций (при условии, что остальные настройки у него в порядке) так поздно и это стало для него открытием — досадное недоразумение, только и всего.
                                                    0
                                                    Как я понимаю, каждый останется при своём мнении, но вам лично не стоит злоупореблять ошибкой хайндсайта

                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                              Самое читаемое