BackendlyLab
PostgreSQL • Backend • DevOps

Как найти медленные запросы в PostgreSQL

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

Таблица pg_stat_activity

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

Использование pg_stat_activity:

SELECT pid,
    now() - query_start AS duration,
    query,
    wait_event_type, -- (Lock - ожидание блокировки, IO - диск, Client - клиент долго читает данные)
    wait_event,
    state
FROM pg_stat_activity
WHERE (now() - query_start) > interval '00:00:02'
    AND state = 'active'
ORDER BY duration DESC; -- покажет все активные запросы, которые выполняются дольше 2 секунд

Представление pg_stat_statements

Дальше стоит воспользоваться pg_stat_statements. Она собирает статистику по запросам, с момента старта сервера БД или последнего сброса статистики, например сколько раз выполнялся запрос, сколько времени занял, сколько страниц прочитал и т.д.

Это расширение PostgreSQL, которое по умолчанию не включено. Надо проверить postgresql.conf и добавить если ещё нет строчку: shared_preload_libraries = 'pg_stat_statements'. После добавления строки надо обязательно перезапустить сервер и создайте расширение запросом:

Включение расширения pg_stat_statements:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Использование pg_stat_statements:

SELECT query,
       calls,
       mean_exec_time, -- среднее время выполнения в миллисекундах (если показатель большой стоит исследовать с EXPLAIN)
       total_exec_time,
       rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

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

Логирование медленных запросов

В PostgreSQL можно включить логирование запросов, которые работают дольше определённого времени. В postgresql.conf нужно найти настройку и указать время в миллисекундах log_min_duration_statement = 1000 — будут писаться в лог все запросы выполнявшиеся больше 1 секунды.

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

Что ещё можно проверить

Есть не очевидный способ проверить есть ли проблема в запросах. Когда запросу требуется больше оперативной памяти чем выделено, PostgreSQL будет использовать временные файлы на диске, а это существенно замедлит работу. Это значит что проводя исследование запросов также полезно смотреть и статистику CPU, памяти и дисковой активности. В этом помогут iostat, vmstat, htop и т.п.

Похожие статьи