Отслеживание активных транзакций в реальном времени с pg_stat_activity

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

Пример базового запроса к pg_stat_activity:

SELECT * 
FROM pg_stat_activity;

Этот запрос выведет все активные подключения и текущие запросы. Отлично! Но данных будет слишком много, и мы можем потратить вечность, просматривая их. Поэтому полезно отфильтровать наиболее важную информацию.

Основные поля в pg_stat_activity

Давайте взглянем на ключевые поля, которые вам пригодятся в дополнение к уже известным. query_start показывает время начала выполнения запроса, что критически важно для определения долгих операций. pid содержит идентификатор процесса соединения — это нужно для управления (например, завершения) соединением. state_change показывает время, когда было установлено текущее состояние подключения, что особенно полезно для анализа долгоживущих проблемных состояний.

Пример выборки активных процессов:

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'active';

Как отследить долгие запросы?

Представьте, что вы администратор базы данных, и вдруг нагрузки на сервер взлетели до небес. Что делать? Сначала нужно понять, какой запрос съедает все ресурсы. Используем pg_stat_activity, чтобы найти такие «жадные» запросы.

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '10 seconds';

Этот запрос покажет все запросы, которые выполняются больше 10 секунд. Настройте значение интервала по своим требованиям.

Завершение проблемных запросов

Давайте разберем, как избавиться от запросов, которые уже слишком долго выполняются и мешают базе данных работать. Используйте функцию pg_terminate_backend() для принудительного завершения процесса.

Пример завершения процесса с определенным PID:

SELECT pg_terminate_backend(12345);

Где 12345 — это идентификатор процесса (поле pid) из pg_stat_activity.

Важно: Завершение процесса может вызвать rollback для некорректно завершенной транзакции, поэтому будьте осторожны.

Теперь, если вам нужно автоматически завершить все «зависшие» процессы, например idle-транзакции, можете выполнить следующий PL/pgSQL-блок. Поскольку вы уже изучали программирование, понятие цикла (англ. loop) вам знакомо — это конструкция, которая повторяет выполнение определённых инструкций до тех пор, пока выполняется заданное условие или пока не закончится обработка набора данных:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

Это динамическое решение позволяет выполнять очистку системы от проблемных транзакций. Цикл FOR проходит по каждой записи из результата запроса и выполняет операцию завершения процесса для каждого найденного PID.

Скоро мы приступим к изучению PL/pgSQL, подождите, немного осталось :P

Фильтрация по состоянию транзакций

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

Пример запроса для обнаружения транзакций, находящихся в idle in transaction:

SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

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

Практическое применение

Мониторинг долгих запросов на продакшене: вы можете настроить регулярный мониторинг запросов, превышающих определенный порог времени, чтобы оповещать об этом через Slack, Telegram или любой другой инструмент уведомлений. Это позволит быстро реагировать на проблемы производительности.

Анализ запросов во время инцидентов: если сервер начинает тормозить, первым делом смотрите в pg_stat_activity, чтобы найти причину. Это должно быть вашим стандартным протоколом реагирования на проблемы производительности.

Обслуживание базы данных: регулярный анализ pg_stat_activity поможет вам отслеживать неэффективные запросы и оптимизировать их (например, добавляя индексы или переписывая запросы).

Когда дело доходит до мониторинга, ошибки могут возникнуть из-за неправильной фильтрации или интерпретации данных. Например, если вы фильтруете по состоянию active, вы можете пропустить запросы, которые находятся в состоянии idle in transaction, а они также могут привести к блокировке ресурсов. Еще одна ошибка — слишком агрессивное завершение процессов, что может вызвать нежелательные откаты транзакций и потерю данных. Всегда анализируйте контекст перед принятием радикальных мер.

Дополнительные техники мониторинга

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

Также полезно настроить автоматическое логирование долгих запросов в файлы журналов PostgreSQL, используя параметры конфигурации log_min_duration_statement и log_statement. Это поможет вам анализировать проблемы производительности постфактум и выявлять закономерности в поведении приложений.

2

Задача

SQL SELF, 45 уровень, 2 лекция

Недоступна

Извлечение активных запросов

Извлечение активных запросов

Открыть