Перейти к основному содержимому

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

Версия: 1.0.

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

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

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

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

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

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

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

Объекты​

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

  1. locks_plain
ПолеТипОписание
locktypetextТип блокируемого объекта:
- relation (отношение);
- extend (расширение отношения);
- frozenid (замороженный идентификатор);
- page (страница);
- tuple (кортеж);
- transactionid (идентификатор транзакции);
- virtualxid (виртуальный идентификатор);
- object (объект);
- userlock (пользовательская блокировка);
- advisory (рекомендательная)
relationtextОтношение, являющееся целью блокировки.
NULL, если целью блокировки не является отношение или его часть.
Перед именем отношения указывается признак репликации объекта и его тип.
Признаки репликации:
- Ordinary: обычные объекты;
- Unlogged: объекты, созданные с признаком Unlogged, или их зависимости;
- Temporary: объекты, созданные с признаком Temporary, или их зависимости;
Типы объектов:
- Table: таблицы;
- Index: индексы, включая primary key;
- Sequence: последовательности;
- TOAST: TOAST таблицы;
- View: представления;
- MatView: материализованные представления;
- CompositeType: составные типы;
- ForeignTable: внешние таблицы;
- PartitionedTable: партиции;
- PartitionedIndex: индексы партиций
pageint4Номер страницы в отношении, являющейся целью блокировки, либо NULL, если цель блокировки не страница или кортеж отношения
tupleint2Номер кортежа на странице, являющегося целью блокировки, либо NULL, если цель блокировки не кортеж
virtualxidtextВиртуальный идентификатор транзакции, являющийся целью блокировки, либо NULL,` если цель блокировки — другой объект
transactionidxidИдентификатор транзакции, являющийся целью блокировки, либо NULL, если цель блокировки — другой объект
classidoidOID системного каталога pg_class, содержащего цель блокировки, либо NULL, если цель блокировки не обычный объект базы данных
objidoidOID цели блокировки в соответствующем системном каталоге, либо NULL, если цель блокировки не обычный объект базы данных
objsubidint2Номер столбца, являющегося целью блокировки (на саму таблицу указывают classid и objid);
- 0 (ноль), если это некоторый другой обычный объект базы данных;
- NULL, если целью не является обычный объект
virtualtransactiontextВиртуальный идентификатор транзакции, удерживающей или ожидающей блокировку
pidint4Идентификатор серверного процесса (PID, Process ID), удерживающего или ожидающего эту блокировку, либо NULL, если блокировка удерживается подготовленной транзакцией
blocking_pidstextСписок идентификаторов серверных процессов, удерживающих блокировки, необходимые процессу (через точку с запятой)
safe_snapshot_blocking_pidstextСписок идентификаторов серверных процессов, удерживающих блокировки, необходимые процессу, с установленным уровнем изоляции транзакции SERIALIZABLE или REPEATABLE READ (через точку запятой)
modetext Название режима блокировки, которая удерживается или запрашивается процессом
grantedboolTrue – блокировка получена;
false – блокировка ожидается
fastpathboolTrue – блокировка получена по короткому пути;
false – блокировка получена через основную таблицу блокировок
databasenameБаза данных, к которой относится цель блокировки;
- 0 (ноль), если разделяемый объект;
- NULL, если целью является идентификатор транзакции
usernamenameИмя пользователя, создавшего подключение.
ВНИМАНИЕ: переключение ролей через SET ROLE, SET SESSION AUTHORIZATION не меняют отображаемого имени пользователя
client_app_nametextНазвание приложения, подключенного к этому серверному процессу
client_addrtextIP-адрес клиента, подключенного к этому серверному процессу.
Значения:
- null означает, что клиент подключен как внутренний процесс (автоочистка);
- -1 означает, что клиент подключен через сокет Unix на стороне сервера
sslboolTrue – подключение произведено с использованием SSL;
False – подключение произведено без SSL
sslversiontextВерсия TLS, при наличии
sslciphertextИспользуемый алгоритм шифрования, при наличии
sslbitsint4Длина ключа SSL, при наличии
sslcompressionboolПризнак использования сжатия SSL соединения, при наличии
sslclientdn`` text`Значение поля CN сертификата клиента, при наличии
backend_xidxidИдентификатор верхнего уровня транзакции этого серверного процесса или любой другой
backend_xminxidТекущая граница xmin для серверного процесса
backend_typetextТип текущего серверного процесса.
Возможные варианты:
- autovacuum launcher;
- autovacuum worker;
- logical replication launcher;
- logical replication worker;
- parallel worker;
- background writer;
- client backend;
- checkpointer;
- startup;
- walreceiver;
- walsender;
- walwriter
Фоновые рабочие процессы, регистрируемые расширениями, могут иметь дополнительные типы
backend_ageintervalВремя, прошедшее с момента запуска процесса. Для процессов, обслуживающих клиентов, это время подключения клиента к серверу
xact_ageintervalВремя, прошедшее с начала текущей транзакции в этом процессе или null при отсутствии активной транзакции. Если текущий запрос был первым в своей транзакции, то значение в этом столбце совпадает со значением столбца query_age
query_ageintervalВремя, прошедшее с начала выполнения активного в данный момент запроса, или, если state не active, то время начала выполнения последнего запроса
state_change_ageintervalВремя, прошедшее с последнего изменения состояния (поле state)
transaction_last_commit_ageintervalВремя, прошедшее с фиксации последней транзакции (при включенном параметре [track_commit_timestamp](https://www.postgresql.org/docs/current
wait_event_typetextТип события, которого ждет обслуживающий процесс, если это имеет место; в противном случае — NULL
wait_eventtextИмя ожидаемого события, если обслуживающий процесс находится в состоянии ожидания, а в противном случае — NULL
statetextОбщее текущее состояние этого серверного процесса:
active: серверный процесс выполняет запрос;
idle: серверный процесс ожидает новой команды от клиента;
idle in transaction: серверный процесс находится внутри транзакции, но в настоящее время не выполняет никакой запрос;
idle in transaction (aborted): состояние подобно idle in transaction, за исключением того, что один из операторов в транзакции вызывал ошибку;
fastpath function call: серверный процесс выполняет fast-path функцию;
disabled: состояние отображается для серверных процессов, у которых параметр track_activities отключен
querytextТекст последнего запроса этого серверного процесса
  1. Представление locks_tree
ПолеТипОписание
main_pidint4Идентификатор серверного процесса (PID, Process ID), удерживающего блокировку
pidint4Идентификатор серверного процесса (PID, Process ID), ожидающего эту блокировку
lock_queuetextСписок идентификаторов процессов, ожидающих блокировку, созданную процессом main_pid, разделенных знаком >
locktypetextТип блокируемого объекта:
- relation – отношение;
- extend – расширение отношения;
- frozenid – замороженный идентификатор;
- page – страница;
- tuple – кортеж;
- transactionid – идентификатор транзакции;
- virtualxid – виртуальный идентификатор;
- object – объект;
- userlock – пользовательская блокировка;
- advisory – рекомендательная
relationtextОтношение, являющееся целью блокировки;
- NULL, если целью блокировки не является отношение или его часть.
Перед именем отношения указывается признак репликации объекта и его тип.
Признаки репликации:
- Ordinary: обычные объекты;
- Unlogged: объекты, созданные с признаком Unlogged, или их зависимости;
- Temporary: объекты, созданные с признаком Temporary, или их зависимости;
Типы объектов:
- Table: таблицы;
- Index: индексы, включая primary key;
- Sequence: последовательности;
- TOAST: TOAST таблицы;
- View: представления;
- MatView: материализованные представления;
- CompositeType: составные типы;
- ForeignTable: внешние таблицы;
- PartitionedTable: партиции;
- PartitionedIndex: индексы партиций
modetextНазвание режима блокировки, которая удерживается или запрашивается процессом
usernamenameИмя пользователя, создавшего подключение.
ВНИМАНИЕ: переключение ролей через SET ROLE, SET SESSION AUTHORIZATION не меняют отображаемого имени пользователя
databasenameБаза данных, к которой относится цель блокировки;
- 0 (ноль), если разделяемый объект;
- NULL, если целью является идентификатор транзакции
client_app_nametextНазвание приложения, подключенного к этому серверному процессу
client_addrtextIP-адрес клиента, подключенного к этому серверному процессу.
Значения:
- null означает, что клиент подключен как внутренний процесс (автоочистка);
- -1 означает, что клиент подключен через сокет Unix на стороне сервера
xact_ageintervalВремя, прошедшее с начала текущей транзакции в этом процессе или null при отсутствии активной транзакции. Если текущий запрос был первым в своей транзакции, то значение в этом столбце совпадает со значением столбца query_age
query_ageintervalВремя, прошедшее с начала выполнения активного в данный момент запроса, или, если state не active, то время начала выполнения последнего запроса
state_change_ageintervalВремя, прошедшее с последнего изменения состояния (поле state)
wait_eventtextИмя ожидаемого события, если обслуживающий процесс находится в состоянии ожидания, а в противном случае — NULL
statetextОбщее текущее состояние этого серверного процесса:
- active: серверный процесс выполняет запрос;
- idle: серверный процесс ожидает новой команды от клиента;
- idle in transaction: серверный процесс находится внутри транзакции, но в настоящее время не выполняет никакой запрос;
- idle in transaction (aborted): состояние подобно idle in transaction, за исключением того, что один из операторов в транзакции вызывал ошибку;
- fastpath function call: серверный процесс выполняет fast-path функцию;
- disabled: состояние отображается для серверных процессов, у которых параметр track_activities отключен
querytextТекст последнего запроса этого серверного процесса

Доработка​

Доработка: Набор представлений для работы с 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)