Отчёт нагрузки PostgreSQL: как читать и ставить диагноз

← назад к разделу

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

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

Отвечает на такой вопрос отчёт нагрузки за период. В мире Oracle эту роль играет AWR, и многие знают её именно оттуда. В PostgreSQL то же самое делают расширения pg_profile и pgpro_pwr — второе идёт в сборках Postgres Pro.

Откуда берётся отчёт за период

Все нужные числа PostgreSQL считает сам и без всяких расширений: pg_stat_statements копит статистику по запросам, семейство pg_stat_* — по таблицам, индексам и базам.

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

Отсюда и устройство инструмента. Он периодически вызывает take_sample() и сохраняет состояние счётчиков в свою схему — это снимок. Отчёт за интервал строится функцией get_report(start_id, end_id) и представляет собой разницу между двумя снимками: сколько накрутилось между ними. Есть и get_diffreport() — она сравнивает сразу два периода, чем удобно сличать вчерашний пик с обычным днём неделей раньше.

Чтобы отчёт был полным, включают несколько вещей:

• pg_stat_statements — обязательно, без него не будет разделов про запросы;
• pg_wait_sampling — формально необязательно, но именно он даёт разделы про ожидания. Без него будет видно, сколько времени ушло, но не на чём система стояла;
• pg_stat_kcache — по желанию, добавляет процессорное время и обращения к файловой системе;
• в конфигурации track_io_timing = on, иначе все времена ввода-вывода останутся нулями.

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

Это дополнение к обычному мониторингу, а не замена ему. Мониторинг отвечает на вопрос «плохо ли прямо сейчас», отчёт — «куда ушло время за те два часа».

В каком порядке читать

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

Сначала масштаб. Раздел со статистикой по базе: сколько было транзакций, сколько прочитано и изменено строк, сколько ушло во временные файлы и в журнал. Здесь вы отвечаете на первый вопрос — период вообще был необычным по нагрузке или нагрузка была штатной, а плохо стало по другой причине. Разница принципиальная: «пришло вдвое больше запросов» и «запросов столько же, но каждый втрое медленнее» — совершенно разные расследования.

Потом ожидания. Разделы про типы ожиданий и топ событий показывают, во что упирается система:

Затем виновники. Топы SQL: по суммарному времени, по числу выполнений, по чтению с диска, по временным файлам, по объёму журнала. Порядок здесь важен — сначала суммарное время, всё остальное потом.

Дальше объекты. Топ таблиц по последовательному чтению, по изменениям, по числу мёртвых строк, статистика вакуума.

И в конце настройки. Отчёт показывает параметры сервера, а сравнительный отчёт — что между периодами изменилось. Иногда всё расследование заканчивается строчкой «кто-то тронул work_mem».

Главная ловушка: среднее вместо суммарного

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

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

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

Из этого следует простое разделение диагнозов.

Если суммарное время большое и среднее большое — перед вами тяжёлый запрос. Дальше смотрят план через EXPLAIN, ищут недостающий индекс, переписывают запрос.

Если суммарное время большое при крошечном среднем и огромном числе вызовов — проблема не в базе, а в приложении. Скорее всего, запрос выполняется в цикле: достаём заказы списком, а потом для каждого отдельно идём за покупателем. Никакой индекс это не вылечит, запрос и так быстрый. Лечится пакетной выборкой на стороне сервиса.

Второй случай встречается чаще первого и почти всегда приезжает из кода.

От чисел к диагнозу

Дальше — типовые картины, которые складываются из этих разделов.

Много чтения с диска, ожидания в IO, средний запрос вырос. Данные читаются с диска, а не из кэша. Причин обычно три: рабочий набор перерос память; пропал индекс, и база пошла читать таблицу целиком — это видно по топу таблиц; либо таблица распухла, и строк столько же, а страниц втрое больше.

Большой объём временных файлов у нескольких запросов. Сортировки и соединения не поместились в память и ушли на диск. Лечится увеличением work_mem — осторожно, он выделяется на операцию, а не на соединение, — уменьшением выборки или индексом, который сразу отдаёт нужный порядок.

Ожидания Lock и всплеск времени на простых обновлениях. Идёт конкуренция за одни и те же строки либо кто-то держит длинную транзакцию. Смотреть надо не на сам медленный запрос, а на того, кто его держит: обычно это сессия, которая давно ничего не делает, но не закрыла транзакцию. Разбор — в статье про блокировки.

Растёт число мёртвых строк, автовакуум отстаёт. Видно по разделу вакуума: доля мёртвых строк увеличивается, дата последней уборки старая. Дальше по цепочке — распухание, чтение с диска, деградация планов. Что с этим делать, разобрано в статье про vacuum.

Один запрос с огромным объёмом журнала. Обычно это массовое обновление: UPDATE по всей таблице, миграция данных, перестроение. Такое разбивают на порции — и заодно проверяют, не отстала ли реплика, пока это ехало.

Транзакций стало вдвое больше, а время каждой не изменилось. База ни при чём: выросла нагрузка. Дальше вопрос про ёмкость, пул соединений и кэширование, а не про индексы.

Чего отчёт не покажет

У метода есть границы, и знать их важнее, чем помнить названия разделов.

Плана конкретного запроса. Отчёт называет виновника, но не объясняет, почему тот медленный. Следующий шаг всегда один — EXPLAIN (ANALYZE, BUFFERS).

Разных планов одного запроса. Запросы в статистике нормализуются: параметры заменяются плейсхолдерами, и все выполнения слипаются в одну строку. Запрос, который для одного клиента идёт по индексу за миллисекунду, а для другого читает половину таблицы, будет одной строкой со средним по больнице.

Того, что не поместилось. У pg_stat_statements есть предел на число хранимых запросов, и когда их становится больше, редкие вытесняются. Если отчёт предупреждает об этом — часть картины потеряна, и предел стоит поднять.

Событий короче интервала. Тридцатисекундная блокировка внутри часового окна растворится в средних без следа. Для такого нужны либо частые выборки состояния сессий, либо обычный мониторинг.

Как этим пользоваться

Отчёт приносит пользу не в момент аварии, а как привычка.

Держите эталон. Снимите отчёт за нормальный рабочий день и сохраните. Без него любое число не с чем сравнить: 40% времени базы на одном запросе — это авария или так было всегда?

Сравнивайте периоды, а не читайте один отчёт. Сравнительный отчёт между «сейчас» и «неделю назад» отвечает на вопрос быстрее, чем чтение обоих по отдельности.

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

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

Если хочется понять, из каких именно запросов складывается каждый раздел, — это разобрано в статье «Отчёт нагрузки своими руками»: там по каждому разделу дан запрос, который его порождает.

Коротко

• Отчёт нагрузки — это разница между двумя снимками накопительной статистики; в Oracle ту же роль играет AWR, в PostgreSQL — pg_profile и pgpro_pwr.
• Нужны pg_stat_statements и track_io_timing; ожидания появляются только с pg_wait_sampling, процессорное время — с pg_stat_kcache.
• Порядок чтения: масштаб нагрузки → ожидания → топы SQL → объекты схемы → настройки и их изменения.
• Сортировать надо по суммарному времени, а не по среднему: тысяча мелких запросов дороже одного тяжёлого отчёта.
• Большое среднее время лечится планом и индексами; огромное число вызовов — изменениями в приложении.
• Отчёт называет виновника, но не показывает план: следующий шаг всегда EXPLAIN (ANALYZE, BUFFERS).
• Одиночный отчёт почти бесполезен без эталонного периода — держите базовый снимок нормального дня.

Что почитать дальше

• Отчёт нагрузки своими руками — какими запросами получается каждый раздел отчёта.
• Мониторинг PostgreSQL — что смотреть в реальном времени и какие метрики держать на дашборде.
• EXPLAIN и планы запросов — следующий шаг после того, как виновник найден.
• VACUUM и распухание — почему таблица растёт, а строк не прибавляется.