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

Версия: 1.0.

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

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

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

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

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

  • оперативный анализ блокировок сессий;

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

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

Объекты#

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

  1. 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

    Текст последнего запроса этого серверного процесса

  2. Представление 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)