Пятница, вечер. Один из эндпоинтов начал отвечать секунд по восемь вместо привычных двухсот миллисекунд. Графики в Grafana — зелёные. CPU спокойный, память на месте, диск не забит. По всем дашбордам база здорова. А она не здорова.
Знакомая ситуация: мониторинг показывает, что сервер жив, но не показывает, что внутри базы что-то медленно гниёт. Раздулась таблица. Появился индекс, которым никто не пользуется, но который тормозит каждый INSERT. Висит забытая транзакция и держит блокировку. Ничего из этого не «падает» — оно просто потихоньку делает базу хуже, пока в пятницу вечером не станет совсем плохо.
У меня для таких случаев годами жил файл queries.sql — свалка запросов, которые я копипастил в psql, когда что-то уже горело. Потом мы с коллегой собрали эти запросы в одно место. Ниже — те из них, которыми я реально пользуюсь. Все работают на голом PostgreSQL, без агентов и платных мониторингов, расширение нужно ровно в одном месте — и там оно нужно честно. Открываете psql и проверяете свою базу прямо сейчас.
Запросы рабочие на современных версиях PostgreSQL (проверялись на 13 и новее). Единственное версионное различие отмечено по ходу.
1. Самый дешёвый сигнал — мёртвые строки
PostgreSQL не удаляет строки физически. UPDATE и DELETE оставляют мёртвые версии строк (dead tuples), а убирает их потом autovacuum. Пока он не пришёл — или не успевает — мёртвые строки лежат в таблице, занимают место и заставляют каждый запрос пролистывать лишнее.
Самый быстрый способ увидеть масштаб — pg_stat_user_tables. Никаких расширений, доступно всегда:
SELECT
schemaname,
relname AS table,
n_live_tup,
n_dead_tup,
round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_ratio,
last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;
Примерный вывод:
schemaname | table | n_live_tup | n_dead_tup | dead_ratio | last_autovacuum
------------+----------+------------+------------+------------+--------------------------
public | orders | 12041233 | 3502118 | 22.5 | 2026-05-12 03:14:09+00
public | sessions | 840112 | 791044 | 48.5 |
На что смотреть. dead_ratio выше 20% на большой таблице — повод разобраться. Строка sessions в примере особенно показательна: почти половина таблицы — это мусор, а last_autovacuum пустой, то есть autovacuum по ней не отрабатывал ни разу. Почему так бывает — вернёмся к этому в секции 5, там вся история сходится в одну точку.
Точное число даст расширение pgstattuple, но оно читает таблицу целиком — на сорокагигабайтной таблице это недёшево. Для регулярной проверки оценки из статистики достаточно.
Что делать. Если autovacuum просто не поспевает — настраивается порог срабатывания: autovacuum_vacuum_scale_factor для конкретной горячей таблицы можно опустить с дефолтных 0.2 до, скажем, 0.05. А вот уже накопившийся bloat обычным VACUUM не убрать — он помечает место как свободное, но не возвращает его операционной системе. Возвращает VACUUM FULL, но он берёт ACCESS EXCLUSIVE lock и переписывает таблицу целиком: на проде это значит, что таблица недоступна всё время операции. На живой системе вместо него — pg_repack: делает почти то же самое без долгой блокировки, ценой места на диске под копию и наличия первичного ключа.
2. Кто на самом деле съедает время сервера
Здесь понадобится расширение — pg_stat_statements. Скорее всего, оно у вас уже есть: его включают по умолчанию во многих сборках и почти во всех облаках. Если нет — shared_preload_libraries = 'pg_stat_statements' в postgresql.conf, рестарт, CREATE EXTENSION pg_stat_statements;. Оно того стоит: это самый полезный диагностический инструмент, который вообще есть в PostgreSQL из коробки.
Расширение копит статистику по каждому уникальному запросу: сколько раз вызван, сколько суммарно времени съел, сколько в среднем.
SELECT
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;
Ключевой момент — сортировка по total_exec_time, а не по среднему. Это контринтуитивно. Запрос, который отрабатывает за 4 мс, выглядит безобидно. Но если он вызывается два миллиона раз в сутки, он съедает сервера больше, чем трёхсекундный отчёт, который гоняют два раза в день. total_exec_time это ловит, mean_exec_time — нет. Колонка pct сразу показывает, какой процент всего времени БД уходит на конкретный запрос; обычно первые три-четыре строки — это и есть весь ваш perf-backlog.
Нюанс версий: в PostgreSQL 12 и старше колонки назывались total_time и mean_time, без _exec. С 13-й версии появилось разделение на planning и execution — отсюда total_exec_time и total_plan_time.
Запросы в выводе нормализованы: конкретные значения заменены на $1, $2, поэтому одинаковые по форме запросы с разными параметрами схлопываются в одну строку — это и нужно. Когда что-то поправили и хотите померить заново — SELECT pg_stat_statements_reset(); обнуляет накопленную статистику.
Дальше по найденному запросу — EXPLAIN (ANALYZE, BUFFERS), и вот там уже видно, где именно он буксует. Чтение плана — отдельная большая тема (и, скажу честно, разбирать глазами текстовый план на двести строк — то ещё удовольствие).
3. Таблицы, которые постоянно читают целиком
Sequential scan — это не зло. На маленькой таблице планировщик осознанно выбирает seq scan, потому что прочитать её целиком быстрее, чем лезть в индекс. Проблема начинается, когда целиком читают большую таблицу, и делают это часто.
SELECT
schemaname,
relname AS table,
seq_scan,
idx_scan,
seq_tup_read,
CASE WHEN seq_scan > 0
THEN seq_tup_read / seq_scan
ELSE 0
END AS avg_rows_per_scan,
pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;
seq_tup_read — сколько всего строк прочитано последовательными сканами. Если у большой таблицы это число огромное, а idx_scan рядом скромный — её регулярно перебирают целиком. avg_rows_per_scan показывает, насколько тяжёлый каждый отдельный скан.
Оговорка: этот запрос даёт кандидатов, а не диагноз. Он не скажет, какую колонку индексировать, — он только показывает, куда смотреть. Дальше всё равно EXPLAIN на конкретном запросе из секции 2: связка «вот запрос, который ест время» плюс «вот таблица, которую перебирают целиком» обычно сходится в одной точке, и там уже виден нужный индекс. И не бросайтесь индексировать таблицу на тысячу строк — для неё seq scan правильный, оставьте как есть.
4. Индексы, которые только мешают
Обратная сторона. Лишний индекс — это не «просто занимает место». Он замедляет каждый INSERT, UPDATE и DELETE по таблице (его тоже надо обновлять), занимает диск и вытесняет из кэша то, что реально нужно. А появляются такие индексы легко: добавили под фичу, фичу выпилили, индекс остался жить.
SELECT
s.schemaname,
s.relname AS table,
s.indexrelname AS index,
s.idx_scan,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
AND NOT i.indisunique
AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;
idx_scan = 0 — индексом ни разу не воспользовались. Из выборки я сразу выкидываю уникальные индексы и первичные ключи: они обеспечивают ограничения целостности, и неважно, что по ним не ходят SELECT-ы, — удалять их нельзя.
Две оговорки, без которых можно выстрелить себе в ногу. Первая: статистика считается с момента последнего сброса или старта сервера. Индекс под квартальный отчёт сегодня честно показывает ноль — а первого числа он внезапно нужен. Смотрите на статистику, накопленную хотя бы за пару недель, а лучше за месяц. Вторая: статистика использования индексов локальна для каждого узла. Индекс, который простаивает на мастере, может вовсю работать на реплике, куда вы отправили читающую аналитику. Проверяйте на всех нодах.
Если всё сошлось — DROP INDEX CONCURRENTLY, чтобы не блокировать таблицу на время удаления.
5. Транзакция, которая висит и тихо ломает всё вокруг
Самый недооценённый пункт. Открытая, но ничего не делающая транзакция — состояние idle in transaction — выглядит безобидно. На деле она держит блокировки и, что хуже, держит назад xmin horizon: autovacuum не может убрать мёртвые строки новее этой транзакции. То есть одна забытая транзакция (приложение взяло коннект, начало транзакцию и ушло думать) добавляет bloat по всей базе. Круг замкнулся — вот вам и пустой last_autovacuum из секции 1.
SELECT
pid,
state,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
wait_event_type,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 20;
xact_age — возраст транзакции. Если там idle in transaction и возраст в десятки минут — это ваш клиент, который забыл закоммитить. Лечится на уровне сервера параметром idle_in_transaction_session_timeout: поставьте минуту-другую, и PostgreSQL сам прибьёт такие сессии.
Второй запрос — кто кого блокирует. Когда что-то «зависло» прямо сейчас:
SELECT
blocked.pid AS blocked_pid,
left(blocked.query, 60) AS blocked_query,
blocking.pid AS blocking_pid,
left(blocking.query, 60) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';
Слева — кто ждёт, справа — из-за кого. На загруженной базе это бывает целая цепочка: один держит, второй ждёт первого, третий ждёт второго. Функция pg_blocking_pids() доступна начиная с PostgreSQL 9.6.
Бонус: одна цифра за тридцать секунд
Если совсем некогда — хотя бы это:
SELECT
round(blks_hit * 100.0 / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();
Доля чтений, которые попали в кэш, а не пошли на диск. Для OLTP-базы здоровое значение — 99% и выше. Просело до 90% — либо данным стало тесно в shared_buffers, либо какой-то запрос регулярно вычитывает пол-таблицы с диска (привет, секция 3).
Что с этим делать в понедельник
Соберите эти запросы в один файл и прогоните по основной базе — без повода, просто чтобы зафиксировать исходную точку. Дальше — раз в пару недель. Половина инцидентов уровня «в пятницу всё стало медленно» — это то, что неделю назад уже было видно в pg_stat_user_tables, просто никто туда не смотрел.
На самом деле гонять эти запросы руками скучно, и именно поэтому это не делается. Файл queries.sql есть у всех, открывают его раз в квартал, когда уже горит. У меня было точно так же.
В какой-то момент мы с коллегой устали копипастить и собрали из этих запросов отдельный экран в нашем инструменте — ide99, десктопной IDE для PostgreSQL. Health Screen внутри — это ровно то, что выше: bloat, топ запросов из pg_stat_statements, недоиспользуемые индексы, висящие транзакции. Посчитано по расписанию, показано одним списком, с понятными порогами и кнопкой «починить» там, где починка безопасна. Рядом — нормальный визуализатор EXPLAIN, потому что разбирать текстовый план на двести строк глазами невозможно. IDE бесплатная, с открытым кодом, ставится за полминуты: ide99.ru.
Дисклеймер: ide99 — наш проект, так что абзац выше читайте с поправкой на это. Но запросы из статьи самодостаточны и не зависят ни от какого инструмента — забирайте их себе в queries.sql и пользуйтесь, даже если ничего ставить не собираетесь. Для того и написано.
А что лежит в вашем диагностическом файле? Скиньте в комментариях свои запросы — особенно интересны нестандартные, под конкретные грабли.
Автор: exzvor
