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;

Настройка#

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

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

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

Построение дерева блокировок определенной сессии с целью определить блокирующую и устранить ее:
```
SELECT * FROM locks_tree WHERE blocked_pid=<pid>;
```
Построение дерева блокировок для сессии с определенным pid. Идентификатор блокирующей сессии будет определен в поле main_pid, список заблокированных сессий - в поле lock_tree. Отсутствие записей означает, что сессия не заблокирована.
Построение дерева блокировок всех сессий, используя существующие представления, с целью определить заблокированные сессии и устранить блокировки:
```
SELECT * FROM locks_tree;
```
Идентификатор блокирующей сессии будет определен в поле main_pid, список заблокированных сессий - в поле lock_tree.
Список заблокированных процессов:
```
SELECT DISTINCT blocked_pid FROM locks_tree;
```
Будет получен список pid процессов, ожидающих блокировки. Если заблокированные процессы отсутствуют, будет получен пустой список.

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

         8053
(2 rows)
```
Список блокирующих процессов:
```
SELECT DISTINCT main_pid FROM locks_tree;
```
Будет получен список pid процессов, блокирующих другие процессы. Будет получен пустой список, если блокирующие процессы отсутствуют.

Пример вывода результата выполнения запроса:
```
 main_pid
----------
     7576
(1 row)
```

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

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)

Поиск по логам текста запроса, приведшего к блокировке (требует прав 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 блокирующего процесса и диапазоны времени для поиска по логам.
Принудительное завершение серверных сессий, приводящих к длительным блокировкам (требует прав 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)
```