Лабораторная работа: Мониторинг активности с помощью `pg_stat_activity`

Теоретические сведения

1. Представление `pg_stat_activity`

Это системное представление в PostgreSQL, которое отображает текущие сессии и их активность.
Ключевые колонки: - pid — идентификатор процесса сессии. - usename — имя пользователя. - application_name — название приложения, подключившегося к БД. - client_addr — IP-адрес клиента. - state — состояние сессии (active, idle, idle in transaction). - query — выполняемый SQL-запрос. - query_start — время начала выполнения запроса. - wait_event — событие ожидания (если сессия заблокирована).

Пример использования: - Поиск активных запросов: SELECT * FROM pg_stat_activity WHERE state = 'active'.

2. Анализ блокировок

Блокировки возникают, когда одна транзакция удерживает ресурс, необходимый другой.
Как найти блокировки:

SELECT 
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked ON blocked_locks.pid = blocked.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

Пример ситуации:
Две транзакции пытаются изменить одни и те же данные. Первая транзакция блокирует вторую.

3. Выявление "долгих" запросов

Запросы, выполняющиеся дольше заданного времени, могут замедлять работу БД.
Пример запроса:

SELECT 
    pid,
    usename,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
    AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

4. Автоматическое логирование проблемных операций

Параметр log_min_duration_statement (в postgresql.conf):
Логирует все запросы, выполняющиеся дольше указанного времени (в миллисекундах).
Пример: log_min_duration_statement = 5000 — логировать запросы > 5 секунд.
Расширение pg_stat_statements:
Собирает статистику по всем выполненным запросам.
Установка: sql CREATE EXTENSION pg_stat_statements; SELECT pg_stat_statements_reset(); Анализ: sql SELECT query, total_time, calls FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

5. Другие инструменты мониторинга

pgBadger: Анализатор логов PostgreSQL для визуализации нагрузки.
pgAdmin: Графический интерфейс с мониторингом активности.
Prometheus + PostgreSQL Exporter: Система сбора метрик в реальном времени.
ОС-утилиты: top, htop, vmstat для мониторинга нагрузки на сервер.

Практические задания

1. Анализ активных сессий

-- Посмотреть все активные сессии
SELECT pid, usename, application_name, state, query 
FROM pg_stat_activity 
WHERE state = 'active';

-- Найти сессии в состоянии "idle in transaction"
SELECT pid, query_start, query 
FROM pg_stat_activity 
WHERE state = 'idle in transaction';

2. Поиск блокировок

-- Запустите в первой сессии:
BEGIN;
UPDATE users SET name = 'Blocked' WHERE id = 1;

-- Во второй сессии (будет заблокирована):
UPDATE users SET name = 'Blocker' WHERE id = 1;

-- В третьей сессии выполните запрос из раздела 2 теории.
-- Найдите PID блокирующего процесса и завершите его:
SELECT pg_terminate_backend(blocking_pid);

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

-- Настройте временное логирование
ALTER SYSTEM SET log_min_duration_statement = 5000; -- 5 секунд
SELECT pg_reload_conf();

-- Сымитируйте долгий запрос
SELECT pg_sleep(10); -- Запрос "уснет" на 10 секунд

-- Проверьте логи (путь к логам можно узнать через `SHOW log_directory;`)
cat /var/log/postgresql/postgresql-15-main.log

4. Анализ статистики запросов

-- Установите расширение (если не установлено)
CREATE EXTENSION pg_stat_statements;

-- Найдите ТОП-10 самых медленных запросов
SELECT query, total_time, calls 
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

Пример вывода заданий

Вывод для анализа блокировок:

 blocked_pid |         blocked_query         | blocking_pid |       blocking_query
-------------+--------------------------------+--------------+---------------------------
    12345    | UPDATE users SET name = '...' |    67890     | UPDATE users SET name =...

Вывод для долгих запросов:

 pid  | usename  |   duration   |         query
------+----------+--------------+------------------------
 4567 | admin    | 00:10:32.15  | SELECT * FROM large_table

Лабораторная работа: Мониторинг активности с помощью pg_stat_activity