Основные команды мониторинга в PostgreSQL — pg_stat_activity и pg_stat_user_tables

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

Что такое pg_stat_activity?

pg_stat_activity — это системное представление PostgreSQL, которое показывает информацию о всех подключениях к вашей базе данных. Оно отвечает на вопросы: кто подключен к базе данных, какие запросы выполняются прямо сейчас, и какие подключения "зависли" в состоянии бездействия. Это ваш инструмент для анализа текущей активности на сервере.

Давайте разберём основные поля, доступные в pg_stat_activity. Поле datname содержит имя базы данных, к которой подключён клиент, а usename показывает имя пользователя, выполнившего подключение. application_name указывает на имя приложения, использующего подключение, client_addr содержит IP-адрес клиента, подключённого к серверу. backend_start показывает время, когда клиент подключился к серверу, state отражает текущее состояние подключения (active, idle, idle in transaction), а query содержит запрос, который выполняется или выполнялся последним.

Пример 1: просмотр всех активных подключений

Чтобы увидеть активные подключения, выполните следующий запрос:

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

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

Пример 2: Анализ состояния транзакций

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

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

Как это исправить? Если вы обнаружили "зависшую" транзакцию, можно завершить её с помощью команды:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Некоторые разработчики с этим слишком увлекаются. Рекомендуем сначала уточнить у команды, можно ли "убить" процесс. Упс, простите, я имел в виду — завершить подключение.

Мониторинг использования таблиц: представление pg_stat_user_tables

Если pg_stat_activity позволяет следить за подключениями, то pg_stat_user_tables рассказывает про производительность таблиц. С его помощью вы узнаете: как часто данные из таблиц читаются или записываются, какие таблицы используются чаще всего, и где могут быть проблемы с производительностью.

Вот основные поля pg_stat_user_tables, которые помогут вам анализировать таблицы. relname содержит имя таблицы, seq_scan показывает количество последовательных сканирований таблицы, idx_scan — количество сканирований с использованием индекса. n_tup_ins отражает количество строк, вставленных в таблицу, n_tup_upd — количество строк, обновлённых в таблице, а n_tup_del — количество строк, удалённых из таблицы.

Пример 1: сравнение использования индексов и последовательных сканирований

Если индекс используется слишком редко (idx_scan близко к нулю), скорее всего, запросы к этой таблице можно оптимизировать.

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Пример результата:

Если вы видите, что таблица orders имеет очень много последовательных сканирований (seq_scan), подумайте о добавлении индекса. Представьте таблицу orders с 3500 последовательными сканированиями и только 100 сканированиями по индексу, в то время как таблица employees имеет 50 последовательных сканирований и 1000 сканирований по индексу — это явный сигнал для оптимизации.

Пример 2: анализ количества операций с таблицами

Чтобы увидеть, насколько "живые" данные в таблицах, запросите информацию о вставленных, обновленных и удаленных строках:

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

Что вы можете узнать? Таблицы с высоким количеством операций вставки (n_tup_ins) и удалений (n_tup_del) могут быть горячими точками в вашей базе. Это значит, что их производительность заслуживает пристального внимания.

Практическое применение команд для анализа производительности: комбинируем данные pg_stat_activity и pg_stat_user_tables

Когда вы анализируете производительность базы данных, можно объединить показания из двух источников. Сначала определите долгие запросы через pg_stat_activity, затем проверьте, какие таблицы используются этими запросами, через pg_stat_user_tables. Если запросы долго выполняются над таблицами с высоким seq_scan, попробуйте оптимизировать запросы или добавить индекс.

Пример запроса:

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';

2

Задача

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

Недоступна

Анализ операций с таблицами

Анализ операций с таблицами

Открыть