psql_lockmon. Расширение для мониторинга блокировки

Версия: 1.0.

В исходном дистрибутиве установлено по умолчанию: да.

Связанные компоненты: отсутствуют.

Схема размещения: ext.

Расширение реализовано в виде двух дополнительных представлений, основанных на pg_locks и pg_stat_activity, которые вносят минимальный вклад в блокировки объектов. Информация, выводимая представлениями, исключает собственные зависимости и блокировки.

Назначение модуля:

• оперативный анализ блокировок сессий;
• выявление слабых мест производительности.

Расширение psql_lockmon используется как инструмент для оперативного мониторинга заблокированных объектов. Подробнее с этой функциональностью можно ознакомиться в документе «Руководство администратора», раздел «Сценарии администрирования», подраздел «Мониторинг блокировок».

Объекты

Решение предоставляет следующие объекты:

locks_plain

locks_plain

Поле	Тип	Описание
locktype	text	Тип блокируемого объекта: relation (отношение); extend (расширение отношения); frozenid (замороженный идентификатор); page (страница); tuple (кортеж); transactionid (идентификатор транзакции); virtualxid (виртуальный идентификатор); object (объект); userlock (пользовательская блокировка); advisory (рекомендательная)
relation	text	Отношение, являющееся целью блокировки. NULL, если целью блокировки не является отношение или его часть. Перед именем отношения указывается признак репликации объекта и его тип. Признаки репликации: Ordinary: обычные объекты; Unlogged: объекты, созданные с признаком Unlogged, или их зависимости; Temporary: объекты, созданные с признаком Temporary, или их зависимости; Типы объектов: Table: таблицы; Index: индексы, включая primary key; Sequence: последовательности; TOAST: TOAST таблицы; View: представления; MatView: материализованные представления; CompositeType: составные типы; ForeignTable: внешние таблицы; PartitionedTable: партиции; PartitionedIndex: индексы партиций
page	int4	Номер страницы в отношении, являющейся целью блокировки, либо NULL, если цель блокировки не страница или кортеж отношения
tuple	int2	Номер кортежа на странице, являющегося целью блокировки, либо NULL, если цель блокировки не кортеж
virtualxid	text	Виртуальный идентификатор транзакции, являющийся целью блокировки, либо NULL, если цель блокировки — другой объект
transactionid	xid	Идентификатор транзакции, являющийся целью блокировки, либо NULL, если цель блокировки — другой объект
classid	oid	OID системного каталога pg_class, содержащего цель блокировки, либо NULL, если цель блокировки не обычный объект базы данных
objid	oid	OID цели блокировки в соответствующем системном каталоге, либо NULL, если цель блокировки не обычный объект базы данных
objsubid	int2	Номер столбца, являющегося целью блокировки (на саму таблицу указывают classid и objid); 0 (ноль), если это некоторый другой обычный объект базы данных; NULL, если целью не является обычный объект
virtualtransaction	text	Виртуальный идентификатор транзакции, удерживающей или ожидающей блокировку
pid	int4	Идентификатор серверного процесса (PID, Process ID), удерживающего или ожидающего эту блокировку, либо NULL, если блокировка удерживается подготовленной транзакцией
blocking_pids	text	Список идентификаторов серверных процессов, удерживающих блокировки, необходимые процессу (через точку с запятой)
safe_snapshot_blocking_pids	text	Список идентификаторов серверных процессов, удерживающих блокировки, необходимые процессу, с установленным уровнем изоляции транзакции SERIALIZABLE или REPEATABLE READ (через точку запятой)
mode	text	Название режима блокировки, которая удерживается или запрашивается процессом
granted	bool	True – блокировка получена; false – блокировка ожидается
fastpath	bool	True – блокировка получена по короткому пути; false – блокировка получена через основную таблицу блокировок
database	name	База данных, к которой относится цель блокировки; 0 (ноль), если разделяемый объект; NULL, если целью является идентификатор транзакции
username	name	Имя пользователя, создавшего подключение. Внимание: переключение ролей через SET ROLE, SET SESSION AUTHORIZATION не меняют отображаемого имени пользователя
client_app_name	text	Название приложения, подключенного к этому серверному процессу
client_addr	text	IP-адрес клиента, подключенного к этому серверному процессу. Значения: null означает, что клиент подключен как внутренний процесс (автоочистка); -1 означает, что клиент подключен через сокет Unix на стороне сервера
ssl	bool	True – подключение произведено с использованием SSL; False – подключение произведено без SSL
sslversion	text	Версия TLS, при наличии
sslcipher	text	Используемый алгоритм шифрования, при наличии
sslbits	int4	Длина ключа SSL, при наличии
sslcompression	bool	Признак использования сжатия SSL соединения, при наличии
sslclientdn	text	Значение поля CN сертификата клиента, при наличии
backend_xid	xid	Идентификатор верхнего уровня транзакции этого серверного процесса или любой другой
backend_xmin	xid	Текущая граница xmin для серверного процесса
backend_type	text	Тип текущего серверного процесса. Возможные варианты: autovacuum launcher; autovacuum worker; logical replication launcher; logical replication worker; parallel worker; background writer; client backend; checkpointer; startup; walreceiver; – walsender; walwriter Фоновые рабочие процессы, регистрируемые расширениями, могут иметь дополнительные типы
backend_age	interval	Время, прошедшее с момента запуска процесса. Для процессов, обслуживающих клиентов, это время подключения клиента к серверу
xact_age	interval	Время, прошедшее с начала текущей транзакции в этом процессе или null при отсутствии активной транзакции. Если текущий запрос был первым в своей транзакции, то значение в этом столбце совпадает со значением столбца query_age
query_age	interval	Время, прошедшее с начала выполнения активного в данный момент запроса, или, если state не active, то время начала выполнения последнего запроса
state_change_age	interval	Время, прошедшее с последнего изменения состояния (поле state)
transaction_last_commit_age	interval	Время, прошедшее с фиксации последней транзакции (при включенном параметре track_commit_timestamp)
wait_event_type	text	Тип события, которого ждет обслуживающий процесс, если это имеет место; в противном случае — NULL
wait_event	text	Имя ожидаемого события, если обслуживающий процесс находится в состоянии ожидания, а в противном случае — NULL
state	text	Общее текущее состояние этого серверного процесса: active: серверный процесс выполняет запрос; idle: серверный процесс ожидает новой команды от клиента; idle in transaction: серверный процесс находится внутри транзакции, но в настоящее время не выполняет никакой запрос; idle in transaction (aborted): состояние подобно idle in transaction, за исключением того, что один из операторов в транзакции вызывал ошибку; fastpath function call: серверный процесс выполняет fast-path функцию; disabled: состояние отображается для серверных процессов, у которых параметр track_activities отключен
query	text	Текст последнего запроса этого серверного процесса

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

locks_tree

Поле	Тип	Описание
main_pid	int4	Идентификатор серверного процесса (PID, Process ID), удерживающего блокировку
pid	int4	Идентификатор серверного процесса (PID, Process ID), ожидающего эту блокировку
lock_queue	text	Список идентификаторов процессов, ожидающих блокировку, созданную процессом main_pid, разделенных знаком >
locktype	text	Тип блокируемого объекта: relation – отношение; extend – расширение отношения; frozenid – замороженный идентификатор; page – страница; tuple – кортеж; transactionid – идентификатор транзакции; virtualxid – виртуальный идентификатор; object – объект; userlock – пользовательская блокировка; advisory – рекомендательная
relation	text	Отношение, являющееся целью блокировки; NULL, если целью блокировки не является отношение или его часть. Перед именем отношения указывается признак репликации объекта и его тип. Признаки репликации: Ordinary: обычные объекты; Unlogged: объекты, созданные с признаком Unlogged, или их зависимости; Temporary: объекты, созданные с признаком Temporary, или их зависимости; Типы объектов: Table: таблицы; Index: индексы, включая primary key; Sequence: последовательности; TOAST: TOAST таблицы; View: представления; MatView: материализованные представления; CompositeType: составные типы; ForeignTable: внешние таблицы; PartitionedTable: партиции; PartitionedIndex: индексы партиций
mode	text	Название режима блокировки, которая удерживается или запрашивается процессом
username	name	Имя пользователя, создавшего подключение. Внимание: переключение ролей через SET ROLE, SET SESSION AUTHORIZATION не меняют отображаемого имени пользователя
database	name	База данных, к которой относится цель блокировки; 0 (ноль), если разделяемый объект; NULL, если целью является идентификатор транзакции
client_app_name	text	Название приложения, подключенного к этому серверному процессу
client_addr	text	IP-адрес клиента, подключенного к этому серверному процессу. Значения: null означает, что клиент подключен как внутренний процесс (автоочистка); -1 означает, что клиент подключен через сокет Unix на стороне сервера
xact_age	interval	Время, прошедшее с начала текущей транзакции в этом процессе или null при отсутствии активной транзакции. Если текущий запрос был первым в своей транзакции, то значение в этом столбце совпадает со значением столбца query_age
query_age	interval	Время, прошедшее с начала выполнения активного в данный момент запроса, или, если state не active, то время начала выполнения последнего запроса
state_change_age	interval	Время, прошедшее с последнего изменения состояния (поле state)
wait_event	text	Имя ожидаемого события, если обслуживающий процесс находится в состоянии ожидания, а в противном случае — NULL
state	text	Общее текущее состояние этого серверного процесса: active: серверный процесс выполняет запрос; idle: серверный процесс ожидает новой команды от клиента; idle in transaction: серверный процесс находится внутри транзакции, но в настоящее время не выполняет никакой запрос; idle in transaction (aborted): состояние подобно idle in transaction, за исключением того, что один из операторов в транзакции вызывал ошибку; fastpath function call: серверный процесс выполняет fast-path функцию; disabled: состояние отображается для серверных процессов, у которых параметр track_activities отключен
query	text	Текст последнего запроса этого серверного процесса

Доработка

Доработка: Набор представлений для работы с pg_locks, pg_stat_activity.

Версия: 5.2.0.

Решение реализовано в виде расширения psql_lockmon, состоящего из набора представлений для работы с pg_locks, pg_stat_activity.

По сравнению с процессом прямого опроса системных представлений в доработке реализована расшифровка:

• OID объектов;
• типов OID объектов;
• дерева взаимоблокировок при их наличии.

Решение позволяет снизить:

• квалификационный порог для эксплуатации;
• затраты на поддержку СУБД экспертами.

Решение не предназначено для автоматизированного частотного мониторинга (от сотен запросов в секунду).

Ограничения

Область видимости ограничена полномочиями пользователя, который выполняет запрос к представлениям и базе данных.

В целях ограничения длительных запросов глубина очереди ограничена в 1000 pid (глубина вложенности pid).

Установка

При наличии прав администратора СУБД включение модуля возможно выполнить вручную:

CREATE EXTENSION psql_lockmon SCHEMA ext;

Настройка

Настройка не требуется.

Использование модуля

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

1. Построение дерева блокировок определенной сессии с целью определить блокирующую и устранить ее:

   SELECT * FROM locks_tree WHERE blocked_pid=<pid>;

   Построение дерева блокировок для сессии с определенным pid. Идентификатор блокирующей сессии будет определен в поле main_pid, список заблокированных сессий - в поле lock_tree. Отсутствие записей означает, что сессия не заблокирована.

2. Построение дерева блокировок всех сессий, используя существующие представления, с целью определить заблокированные сессии и устранить блокировки:

   SELECT * FROM locks_tree;

   Идентификатор блокирующей сессии будет определен в поле main_pid, список заблокированных сессий - в поле lock_tree.

3. Список заблокированных процессов:

   SELECT DISTINCT blocked_pid FROM locks_tree;

   Будет получен список pid процессов, ожидающих блокировки. Если заблокированные процессы отсутствуют, будет получен пустой список.

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

    blocked_pid
   -------------
   
            8053
   (2 rows)

4. Список блокирующих процессов:

   SELECT DISTINCT main_pid FROM locks_tree;

   Будет получен список pid процессов, блокирующих другие процессы. Будет получен пустой список, если блокирующие процессы отсутствуют.

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

    main_pid
   ----------
        7576
   (1 row)

5. Максимальная длительность блокировки:

   SELECT   blocked_pid,
            max (state_change_age)
       FROM    locks_tree
       WHERE   blocked_pid NOTNULL
       GROUP BY 1 ORDER BY 2 DESC,1 LIMIT 1;

   Будут получены pid заблокированного процесса и длительность блокировки.

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

    blocked_pid |       max
   -------------+-----------------
           8053 | 00:25:24.556964
   (1 row)

6. Поиск по логам текста запроса, приведшего к блокировке (требует прав pg_read_all_stats):

   SELECT DISTINCT
       t1.main_pid,
       current_timestamp-t1.xact_age,
       current_timestamp-t2.state_change_age
   FROM locks_tree t1
   JOIN locks_tree t2 USING(main_pid)
   WHERE t1.lock_depth=1 AND t2.lock_depth=2;

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

   • pid блокирующего процесса;
   • время начала транзакции в процессе;
   • время начала ожидания блокировки.

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

    main_pid |           ?column?            |           ?column?
   ----------+-------------------------------+-------------------------------
        7576 | 2023-03-23 10:42:52.409485+03 | 2023-03-23 12:04:39.150078+03
   (1 row)

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

7. Принудительное завершение серверных сессий, приводящих к длительным блокировкам (требует прав pg_signal_backend).

   SELECT DISTINCT
       main_pid,
       pg_terminate_backend (main_pid)
   FROM    locks_tree
   WHERE   lock_depth=2
   AND     state_change_age > interval '30 minutes';

   В примере запрос принудительно завершает процессы, приводящие к блокировкам длительностью более 30 минут.

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

    main_pid | pg_terminate_backend
   ----------+----------------------
       7576 | t
   (1 row)