Пятница, вечер. Один из эндпоинтов начал отвечать секунд по восемь вместо привычных двухсот миллисекунд. Графики в 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_autovacuumFROM pg_stat_user_tablesWHERE n_dead_tup > 0ORDER BY n_dead_tup DESCLIMIT 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, queryFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 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 sizeFROM pg_stat_user_tablesWHERE seq_scan > 0ORDER BY seq_tup_read DESCLIMIT 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_sizeFROM pg_stat_user_indexes sJOIN pg_index i ON i.indexrelid = s.indexrelidWHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimaryORDER BY pg_relation_size(s.indexrelid) DESCLIMIT 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 queryFROM pg_stat_activityWHERE state <> 'idle' AND xact_start IS NOT NULLORDER BY xact_startLIMIT 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_queryFROM pg_stat_activity blockedJOIN 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_ratioFROM pg_stat_databaseWHERE 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 и пользуйтесь, даже если ничего ставить не собираетесь. Для того и написано.
А что лежит в вашем диагностическом файле? Скиньте в комментариях свои запросы — особенно интересны нестандартные, под конкретные грабли.
ссылка на оригинал статьи https://habr.com/ru/articles/1041480/