Мониторинг активных запросов: Использование pg_stat_activity для настройки производительности

Когда ваша база данных внезапно замедляется, вам нужно знать, что PostgreSQL делает прямо сейчас. pg_stat_activity показывает активные запросы, бездействующие сеансы, ожидания блокировок и открытые транзакции, чтобы вы могли отличить медленный запрос от заблокированного.

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

Понимание представления pg_stat_activity

pg_stat_activity — это динамическое системное представление, содержащее одну строку для каждого серверного процесса, подключенного к кластеру базы данных. Сюда входят клиентские бэкенды, фоновые рабочие процессы и сеансы, которые бездействуют, но все еще подключены.

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

Ключевые столбцы для анализа производительности

Хотя pg_stat_activity содержит десятки столбцов, следующие являются важными при диагностике проблем производительности:

Имя столбца	Описание	Значение для настройки
pid	Идентификатор процесса бэкенда.	Требуется для отмены или завершения сеансов.
datname	Имя базы данных, к которой подключен этот бэкенд.	Помогает ограничить мониторинг в средах с несколькими базами данных.
usename	Пользователь, инициировавший подключение.	Идентифицирует конкретное приложение или активность пользователя.
application_name	Имя подключающегося приложения (если задано клиентом).	Отлично подходит для идентификации подключений от конкретных микросервисов.
state	Текущий статус активности (например, active, idle, idle in transaction).	Основной индикатор того, что делает бэкенд.
query	Текущий запрос или последний запрос для бездействующих сеансов. Видимость может быть ограничена привилегиями и настройками.	Идентифицирует задействованный оператор SQL.
query_start	Временная метка начала выполнения текущего запроса.	Используется для расчета продолжительности запроса.
wait_event_type и wait_event	Детали того, чего ожидает процесс (например, получения блокировки, ввода-вывода).	Критически важно для диагностики конкуренции и блокировок.

Практические сценарии использования мониторинга

Реальная сила pg_stat_activity заключается в фильтрации данных для ответа на конкретные вопросы производительности.

Просмотр всех активных запросов

Чтобы увидеть только процессы, выполняющие в данный момент оператор (не бездействующие), отфильтруйте представление по столбцу state.

-- Просмотр всех выполняющихся в данный момент запросов
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Выявление долго выполняющихся запросов

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

Чтобы выявить запросы, выполняющиеся дольше определенного порога (например, 5 секунд), используйте вычитание интервалов с помощью now() и query_start.

-- Поиск запросов, выполняющихся дольше 5 секунд
SELECT
    pid,
    usename,
    datname,
    state,
    (now() - query_start) AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '5 seconds'
ORDER BY duration DESC;

Используйте порог, соответствующий вашей рабочей нагрузке. Пятисекундный запрос оформления заказа может быть серьезным в OLTP-приложении, в то время как пятиминутный отчетный запрос может быть нормальным, если он выполняется в нерабочее время.

Диагностика сеансов в состоянии "Idle in Transaction"

Подключение в состоянии idle in transaction начало транзакцию, но не зафиксировало и не откатило ее. Оно ожидает от клиента следующей команды. Такие сеансы могут удерживать блокировки и сохранять видимость старых версий строк, что может задерживать очистку автовакуумом и способствовать раздуванию таблиц.

-- Поиск сеансов, которые бездействуют, но удерживают открытую транзакцию
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - xact_start AS txn_duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY txn_duration DESC;

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

Анализ конкуренции за блокировки и блокировок

Когда запрос зависает, он часто ожидает блокировки, удерживаемой другим процессом. Представление pg_stat_activity в сочетании с pg_locks имеет решающее значение для диагностики конкуренции.

Чтобы найти сеансы, которые в данный момент ожидают ресурс (блокировку, ввод-вывод и т.д.), посмотрите на столбец wait_event. Если сеанс заблокирован, его wait_event_type часто будет Lock.

-- Определение процессов, заблокированных в данный момент блокировкой
SELECT
    a.pid,
    a.usename,
    a.query_start,
    a.query,
    a.wait_event,
    a.wait_event_type
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND a.wait_event IS NOT NULL
ORDER BY a.query_start;

Для быстрого просмотра "кто кого блокирует" PostgreSQL также предоставляет pg_blocking_pids().

-- Показать заблокированные сеансы и сеансы, которые их блокируют
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query 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';

Если это возвращает заблокированный веб-запрос, ожидающий длительного обновления администратора, отмена административного запроса может быть безопаснее, чем завершение сеанса приложения.

Управление проблемными сеансами

После того как проблемный запрос или сеанс идентифицирован с помощью его идентификатора процесса (pid), PostgreSQL предоставляет две функции для управления им:

Отмена запроса с помощью pg_cancel_backend

Эта функция пытается корректно остановить выполнение конкретного запроса. Сам сеанс остается подключенным и доступным для будущих запросов.

-- Пример: Отмена запроса, выполняющегося на PID 12345
SELECT pg_cancel_backend(12345);

Завершение сеанса с помощью pg_terminate_backend

Эта функция принудительно отключает бэкенд-процесс от сервера. Если сеанс находился в середине транзакции, PostgreSQL автоматически откатит транзакцию.

-- Пример: Принудительное завершение сеанса с PID 54321
SELECT pg_terminate_backend(54321);

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

Лучшие практики мониторинга

Агрессивно фильтруйте

Избегайте SELECT * FROM pg_stat_activity в качестве вашей привычной производственной практики. Вывод зашумлен, а текст query может раскрывать конфиденциальные значения, если ваши приложения отправляют литералы вместо параметров привязки. Выбирайте нужные столбцы и фильтруйте по state, datname, application_name или продолжительности.

Используйте инструменты для автоматического мониторинга

Ручные проверки полезны во время инцидента, но тенденции должны отслеживаться в мониторинге. Отслеживайте активные сеансы, ожидающие сеансы, длительные транзакции и количество idle in transaction на панели мониторинга PostgreSQL.

Настройте ведение журнала операторов

Сочетайте мониторинг в реальном времени с историческими данными. Настройте такие параметры, как log_min_duration_statement, для регистрации запросов, превышающих определенный порог, предоставляя данные для анализа даже после завершения выполнения запроса.

Вывод

Держите три сохраненных проверки: активные запросы, упорядоченные по продолжительности, бездействующие транзакции, упорядоченные по возрасту транзакции, и заблокированные сеансы с их блокировщиками. Когда PostgreSQL кажется медленным, эти представления подскажут вам, нужно ли настраивать SQL, исправлять обработку транзакций или очищать блокирующий сеанс.