Синтез как один из методов улучшения производительности PostgreSQL



Философское вступление


Как известно, существует всего два метода для решения задач:
  1. Метод анализа или метод дедукции, или от общего к частному.
  2. Метод синтеза или метод индукции, или от частного к общему.

Для решения проблемы “улучшить производительность базы данных” это может выглядеть следующим образом.

Анализ — разбираем проблему на отдельные части и решая их пытаемся в результате улучшить производительности базы данных в целом.

На практике анализ выглядит примерно так:

  • Возникает проблема (инцидент производительности)
  • Собираем статистическую информацию о состоянии базы данных
  • Ищем узкие места(bottlenecks)
  • Решаем проблемы с узких мест

Узкие места базы данных — инфраструктура (CPU, Memory, Disks, Network, OS), настройки(postgresql.conf), запросы:

Инфраструктура: возможности влияния и изменения для инженера — почти нулевые.

Настройки базы данных: возможности для изменений чуть больше чем в предыдущем случае, но как правило все -таки довольно затруднительны, особенно в облаках.

Запросы к базе данных: единственная область для маневров.

Синтез — улучшаем производительность отдельных частей, ожидая, что в результате производительность базы данных улучшится.

Лирическое вступление или зачем все это надо


Как происходит процесс решения инцидентов производительности, если производительность базы данных не мониторится:

Заказчик -”у нас все плохо, долго, сделайте нам хорошо”
Инженер-” плохо это как?”
Заказчик –”вот как сейчас(час назад, вчера, на прошлой деле было), медленно”
Инженер – “а когда было хорошо?”
Заказчик – “неделю (две недели) назад было неплохо. “(Это повезло)
Заказчик – “а я не помню, когда было хорошо, но сейчас плохо “(Обычный ответ)

В результате получается классическая картина:



Кто виноват и что делать?


На первую часть вопроса ответить легче всего — виноват всегда инженер DBA.

На вторую часть ответить тоже не слишком сложно — нужно внедрять систему мониторинга производительности базы данных.

Возникает первый вопрос — что мониторить?

Путь 1. Будем мониторить ВСЁ



Загрузку CPU, количество операций дискового чтения/записи, размер выделенной памяти, и еще мегатонна разных счетчиков, которые любая более-менее рабочая система мониторинга может предоставить.

В результате получается куча графиков, сводных таблиц, и непрерывные оповещения на почту и 100% занятость инженера решением кучи одинаковых тикетов, впрочем, как правило со стандартной формулировкой — “Temporary issue. No action need”. Зато, все заняты, и всегда есть, что показать заказчику — работа кипит.

Путь 2. Мониторить только то, что нужно, а, что не нужно, не нужно мониторить


Можно мониторить, чуть по-другому- только сущности и события:

  • На которые инженер DBA может влиять
  • Для которых существует алгоритм действий при возникновении события или изменения сущности.

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

Но для того, чтобы улучшить тяжелый запрос, влияющий на общую производительность базы данных, нужно сначала его найти.

Итак, возникает два взаимосвязанных вопроса:

  • какой запрос считается тяжелым
  • как искать тяжелые запросы.

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

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

Какие возможности для мониторинга запросов есть в PostgreSQL?


По сравнению с Oracle, возможностей немного, но все-таки кое-что сделать можно.



PG_STAT_STATEMENTS


Для поиска и мониторинга тяжелых запросов в PostgreSQL предназначено стандартное расширение pg_stat_statements.

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

Целевые столбцы pg_stat_statements для построения системы мониторинга:

  • queryid Внутренний хеш-код, вычисленный по дереву разбора оператора
  • max_time Максимальное время, потраченное на оператор, в миллисекундах

Накопив и используя статистику по этим двум столбцам, можно построить мониторинговую систему.

Как используется pg_stat_statements для мониторинга производительности PostgreSQL




Для мониторинга производительности запросов используется:
На стороне целевой базы данных — представление pg_stat_statements
Со стороны сервера и базы данных мониторинга — набор bash-скриптов и сервисных таблиц.

1 этап — сбор статистических данных


На хосте мониторинга по крону регулярно запускается скрипт который копирует содержание представления pg_stat_statements с целевой базы данных в таблицу pg_stat_history в базе данных мониторинга.

Таким образом, формируется история выполнения отдельных запросов, которую можно использовать для формирования отчетов производительности и настройки метрик.

2 этап — настройка метрик производительности


Основываясь на собранных данных, выбираем запросы, выполнение которых наиболее критично/важно для клиента(приложения). По согласованию с заказчиком, устанавливаем значения метрик производительности используя поля queryid и max_time.

Результат — старт мониторинга производительности


  1. Мониторинговый скрипт при запуске проверяет сконфигурированные метрики производительности, сравнивая значение max_time метрики со значением из представления pg_stat_statements в целевой базе данных.
  2. Если значение в целевой базе данных превышает значение метрики – формируется предупреждение (инцидент в тикетной системе)

Дополнительная возможность 1


История планов выполнения запросов

Для последующего решения инцидентов производительности очень хорошо иметь историю изменения планов выполнения запросов.

Для хранения истории используется сервисная таблица log_query. Таблица заполняется при анализе загруженного лог-файла PostgreSQL. Поскольку в лог-файл в отличии от представления pg_stat_statements попадает полный текст с значениями параметров выполнения, а не нормализованный текст, имеется возможность вести лог не только времени и длительности запросов, но и хранить планы выполнения на текущий момент времени.

Дополнительная возможность 2


Continuous performance improvement process

Мониторинг отдельных запросов в общем случае не предназначен для решения задачи непрерывного улучшения производительности базы данных в целом поскольку контролирует и решает задачи производительности только для отдельных запросов. Однако можно расширить метод и настроить мониторинг запросы для всех базы данных.

Для этого нужно ввести дополнительные метрики производительности:

  • За последние дни
  • За базовый период

Скрипт выбирает запросы из представления pg_stat_statements в целевой базе данных и сравнивает значение max_time со средним значением max_time, в первом случае за последние дни или за выбранный период времени(baseline), во-втором случае.

Таким образом в случае деградации производительности для любого запроса, предупреждение будет сформировано автоматически, без ручного анализа отчетов.

А при чем тут синтез ?


В описанной подходе, как и предполагает метод синтеза — улучшением отдельных частей системы, улучшаем систему в целом.

  • Запрос выполняемый базой данных – тезис
  • Измененный запрос – антитезис
  • Изменение состояние системы — синтез



Развитие системы


  • Расширения собираемой статистики добавлением истории для системного представления pg_stat_activity
  • Расширение собираемой статистики добавлением истории для статистики отдельных таблиц участвующих в запросах
  • Интеграция с системой мониторинга в облаке AWS
  • И еще, что-нибудь можно придумать…
Поделиться публикацией

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

    0
    pg_stat_activity это хорошо, только пользоваться им отдельное исскуство. Можно смотреть тут супер крутые запросы
    github.com/dataegret/pg-utils
      0
      На первую часть вопроса ответить легче всего — виноват всегда инженер DBA.

      Обычно это отсутствие DBA. И тут скорее всего дешевле и проще подключить okmeter

        0
        okmeter это конечно круто, но в данных конкретных условиях к сожалению нереализуемо. По причине нежелания заказчика устанавливать приложения третьих сторон и отсутствия свободного хоста.
          0
          Какой свободный хост? Из статьи видно, что мониторинга у них нет, иначе они бы сказали, что неделю назад ответ api был за N ms, а сейчас ответ делается за M ms. Свой мониторинг штука не дешёвая.
            0
            Мониторинга нет, поэтому все и началось.
            «они бы сказали, что неделю назад ответ api был за N ms, а сейчас ответ делается за M ms» — все почти так, именно так, только временные промежутки несколько иные.
            Началось все с оптимизации запроса который выполнялся 4 часа, в результате удалось убыстрить до 20 секунд. И по ходу дела и возник вопрос — «а может быть нам нужен мониторинг производительности отдельных запросов?».
            Что и удалось реализовать.
            P.S. Насчет недешевости, ну так мне же лучше, я же конечный исполнитель ;-)
        0
        Практика показывает, что очень часто причиной тормозов, особенно после доработок и обновлений, становятся новые запросы или модифицированные, которые пожирают ресурса севера. И правильно дополнительно мониторить топ однотипных: медленных методов (как правило CPU), и прожорливых диск и память. То есть целевые деградируют из-за соседей, а не сами по себе. Особенно часто когда несколько команд разработчиков работает с одной БД
          0
          Кстати, полезно будет для pg_stat_activity чтобы текст запроса не резался, выставить в параметрах track_activity_query_size = 16384. По умолчанию всего 1024 байт
            0
            Вообще, деградация запроса может быть, если статистика не собирается или данные в таблицах фрагментируются из-за постоянного добавления/удаления. Поэтому можно кроном в фоном режиме выполнять команду vacuumdb -z
              0
              Если автовакуум настроен так, как он должен быть настроен описываемой проблемы просто нет.
              А то встречал я умников отключивших автовакуум и потом удивлявшихся — «а почему все тормозит?».
              Зачем запускать кроном то, что итак входит в состав базы?
              Просто настраивать надо нормально.
                0
                Если разово изменяется много записей в таблицах — например массовое перепроведение документов в 1С, autovacuum может не справляться

                Да и в документации пишут, что отключить полностью autovacuum нельзя
                  +1
                  Если оставить дефолтные настройки, автовакуум разумеется не справится.
                    0
                    Wraparound не слышали о таком? Он случается на более менее больших данных, с интенсивным обновлением / вставкой. Когда он случается, то не лечится ни чем кроме vacuum full. А это очень печально, хорошо если его можно себе позволить. Если есть большое желание обновить статистику, выкинуть мертвые записи из таблицы, и заодно перестроить индексы pg-repack, работает в фоновом режиме делает то же что и vacuum full, только таблицу не блокирует (только на момент создания триггера).
                      0
                      не только слышал, но и лечил базу которая вот вот должна была.
                      И обошлись без vacuum full — более агрессивной настройкой autovacuum.
                      Статей и докладов на эту тему много. В общем то ничего особенно не нужно придумывать нового.
                0
                В postgresql есть замечательная штука называется auto explain. Настраиваете порог логирования и будет вам счастье. Можно разобрать все вплоть до узла плана который втупил, посмотреть сколько ресурсов сожрал (buffers память, диск и т.д.).
                  0
                  auto explain, кстати рассматривался. Однако не пошел на продакшн.
                  Основная причина даже не в импактена I/O а в, том, что как и в случае с pgBadger искать и сравнивать планы придется вручную, что совсем неудобно для мониторинга.
                  Проще хранить хеш значения планов и следить за изменением плана.

                    0
                    Мы написали инструмент для этого, он читает лог в реальном времени, парсит и складывает в БД. Потом можно все это добро анализировать, очень удобно. По хешу плана что можно отследить, цифры никогда не совпадают? Т.е. предварительная обработка все таки есть, второй вопрос как вы план получаете, explain analyze руками запускаете? Без auto explain ни как, в логе будут реальные планы по реальным данным, с реальными параметрами.
                      0
                      Я тоже читаю логи в реальном времени и после парсинга план и его хэш значение храниться в базе. Как раз для анализа.
                      А значение хэша не совпадает только если структура плана изменилась.
                      Например если план выбирал 100 записей и 101 запись то это будут считаться одинаковые планы. Дальше стандарнтно — если мониторим производительность запроса, в первую очередь смотрю изменился ли план.
                      Если план не изменился, то дело не в запросе, значить есть импакт на инфраструктуру. Т.е. примерно 50% анализа уже выполнено.
                      Без auto explain обойтись вполне можно. Хотя бы по одной причине — вовсе не обязательно генерить планы для всех запросов, например. Только для запросов которые мониторятся.
                      Сорри, но сложно так объяснить в рамках одного комментария. Я готовлю отдельную статью, с более подробными объяснениями. Надеюсь будет понятнее.

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

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