[Из песочницы] Синтез как один из методов улучшения производительности PostgreSQL
Философское вступление
Как известно, существует всего два метода для решения задач:
- Метод анализа или метод дедукции, или от общего к частному.
- Метод синтеза или метод индукции, или от частного к общему.
Для решения проблемы «улучшить производительность базы данных» это может выглядеть следующим образом.
Анализ — разбираем проблему на отдельные части и решая их пытаемся в результате улучшить производительности базы данных в целом.
На практике анализ выглядит примерно так:
- Возникает проблема (инцидент производительности)
- Собираем статистическую информацию о состоянии базы данных
- Ищем узкие места (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.
Результат — старт мониторинга производительности
- Мониторинговый скрипт при запуске проверяет сконфигурированные метрики производительности, сравнивая значение max_time метрики со значением из представления pg_stat_statements в целевой базе данных.
- Если значение в целевой базе данных превышает значение метрики — формируется предупреждение (инцидент в тикетной системе)
Дополнительная возможность 1
История планов выполнения запросов
Для последующего решения инцидентов производительности очень хорошо иметь историю изменения планов выполнения запросов.
Для хранения истории используется сервисная таблица log_query. Таблица заполняется при анализе загруженного лог-файла PostgreSQL. Поскольку в лог-файл в отличии от представления pg_stat_statements попадает полный текст с значениями параметров выполнения, а не нормализованный текст, имеется возможность вести лог не только времени и длительности запросов, но и хранить планы выполнения на текущий момент времени.
Дополнительная возможность 2
Continuous performance improvement process
Мониторинг отдельных запросов в общем случае не предназначен для решения задачи непрерывного улучшения производительности базы данных в целом поскольку контролирует и решает задачи производительности только для отдельных запросов. Однако можно расширить метод и настроить мониторинг запросы для всех базы данных.
Для этого нужно ввести дополнительные метрики производительности:
- За последние дни
- За базовый период
Скрипт выбирает запросы из представления pg_stat_statements в целевой базе данных и сравнивает значение max_time со средним значением max_time, в первом случае за последние дни или за выбранный период времени (baseline), во-втором случае.
Таким образом в случае деградации производительности для любого запроса, предупреждение будет сформировано автоматически, без ручного анализа отчетов.
А при чем тут синтез ?
В описанной подходе, как и предполагает метод синтеза — улучшением отдельных частей системы, улучшаем систему в целом.
- Запрос выполняемый базой данных — тезис
- Измененный запрос — антитезис
- Изменение состояние системы — синтез
Развитие системы
- Расширения собираемой статистики добавлением истории для системного представления pg_stat_activity
- Расширение собираемой статистики добавлением истории для статистики отдельных таблиц участвующих в запросах
- Интеграция с системой мониторинга в облаке AWS
- И еще, что-нибудь можно придумать…
