Эй, запрос! Ты живой? Как легко обработать блокировки в PostgreSQL

KaldaiDemon

/ инженер-программист

18 января, 2017

Доброе время суток! Администрирование и сопровождение реляционных баз данных чаще всего является нетривиальной задачей. Иногда запросы, работавшие быстро, внезапно начинают «тормозить» по непонятным причинам, размер таблиц растет и в целом производительность базы данных снижается.

Часто причиной такого поведения являются возникающие в базе блокировки различных ресурсов, и соответственно — вырастающее время ожидания этих ресурсов. Например, сложности начинаются в ситуациях, когда два или более запроса в разных сеансах пытаются одновременно изменить одни и те же данные в таблицах или саму структуру таблицы.

Чтобы разобраться в сложившейся ситуации, администратору БД необходимо понять, какой процесс блокирует и какой процесс является блокируемым, а также иметь возможность отменить или «убить» блокирующий процесс и в конце проверить результат.

В этой статье я хочу коснуться темы блокировок в PostgreSQL и рассказать об инструментах для работы с ними. Но сначала попробуем разобраться в самой теме.

Немного теории: ликбез о блокировках

Что же такое блокировки в БД? Википедия предлагает следующее определение: “Блокировка (англ. lock) в СУБД — отметка о захвате объекта транзакцией в ограниченный или исключительный доступ с целью предотвращения коллизий и поддержания целостности данных.”

PostgeSQL поддерживает целостность данных, реализуя модель MVCC. MVCC (MultiVersion Concurrency Control) — один из механизмов обеспечения параллельного доступа к БД, заключающийся в предоставлении каждому пользователю так называемого «снимка» БД. Особое «свойство» такого снимка в том, что вносимые пользователем изменения в БД невидимы для других пользователей до момента фиксации транзакции.

PostgreSQL гарантирует целостность даже для самого строгого уровня изоляции транзакций, используя инновационный уровень изоляции SSI (Serializable Snapshot Isolation, Сериализуемая изоляция снимков).

Для большего понимания темы можно почитать статью на Хабре на Хабре и статью в блоге Александра Журавлёва о блокировках, их работе и конкурентном доступе вообще.

Непредвиденные ситуации

К сожалению, возникают ситуации, когда реализованные механизмы для обеспечения целостности данных всё равно не могут справиться с поступающими запросами без возникновения блокировок. Бывает это редко, но если уж возникнет ситуация, что какой-нибудь запрос заблокировал целую таблицу на продолжительное время, то это может привести к неприятностям.

Например, если запустить долго обрабатываемый запрос к таблице c 1000 записей, к которой в секунду происходит 100 UPDATE запросов, то за 5-6 часов размер таблицы увеличится до 1.8 миллионов записей, соответственно, физический размер таблицы тоже увеличивается (так как БД хранит все версии строк, пока длинная транзакция не завершит свою работу.

Рассмотрим такую ситуацию подробнее.

Пример с возникающей блокировкой

Пусть в некоторой БД у нас есть таблица pgsqlblocks_testing и у неё есть правило rule_pgsqlblocks_testing. Эмулируем к нему “долгий” запрос на 10 минут, к примеру, с помощью SQL редактора pgAdmin:

SELECT * FROM public.pgsqlblocks_testing LIMIT 1000; SELECT pg_sleep(600);

Pid процесса 16728

DROP RULE rule_pgsqlblocks_testing ON public.pgsqlblocks_testing;

Pid процесса 16726

И вот DROP RULE блокируется SELECT запросом. MVCC в данном случае не смог обойтись без явной блокировки таблицы pgsqlblocks_testing

Инструменты для работы с блокировками

Как же нам просмотреть имеющиеся блокировки? Можно самому писать запрос для таблицы блокировок pg_locks и представления pg_stat_activity или использовать встроенный в pgAdmin инструмент.

Состояние сервера в pgAdmin

pgAdmin представляет собой достаточно удобное и простое ПО для работы с БД PostgreSQL. На данный момент актуальными версиями являются pgAdmin III и вышедший только в конце сентября pgAdmin IV.

pgAdmin III

Отображение информации о блокировках и активных процессах в pgAdmin III требует наличия расширения adminpack в базе данных. После установки этого расширения нужное нам окно открывается через меню Инструменты — Состояние сервера

В этом окне мы видим таблицу с процессами и таблицу с имеющимися блокировками в БД. Чтобы не растеряться среди большого количества процессов, мы можем настроить цвета процессов в зависимости от их статуса: активный, заблокированный, бездействующий или «медленный».

В таблице каждый блокирующий и блокируемый процесс представлены отдельными строками, и нет возможности быстро определить, кто кого блокирует. Для решения этой задачи нам придется сопоставлять разные строки между собой в попытке найти строки, объединенные общим значением колонки relation и отличными значениями колонки granted.

Для отмены или терминирования выбранного процесса в окне имеются две кнопки. После терминирования какого-либо из процессов нужно обновить окно и снова сопоставить строки, чтобы оценить результат.

Итак, pgAdmin III может быть использован как инструмент для работы с блокировками, но обладает парой минусов: требует предварительной настройки БД и показывает блокировки в плоском виде (без древовидного отображения блокирующих-блокируемых процессов), что осложняет поиск проблемных процессов и оценку их терминирования. Это делает его не самым удобным инструментом для наших задач

pgAdmin IV

После установки и запуска pgAdmin IV мы сможем посмотреть существующие блокировки в том же виде, как это было в pgAdmin III.

Но… это все, что мы сможем сделать здесь. В pgAdmin IV пропала панель инструментов для действий над процессами, и мы уже не можем отменить или терминировать процессы из этого вида, что делает pgAdmin IV неудобным инструментом работы с блокировками.

Запросы в БД

В сети есть много разных реализаций запросов для просмотра заблокированных и блокирующих запросов в БД

Первый же результат в поисковике по запросу “pg_locks monitoring” выдает ссылку с вариантом запроса:

Запрос 1:

SELECT blocked_locks.pid     AS blocked_pid,
     blocked_activity.usename  AS blocked_user,
     blocking_locks.pid     AS blocking_pid,
     blocking_activity.usename AS blocking_user,
     blocked_activity.query    AS blocked_statement,
     blocking_activity.query   AS current_statement_in_blocking_process
FROM  pg_catalog.pg_locks         blocked_locks
  JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = 
blocked_locks.pid
  JOIN pg_catalog.pg_locks         blocking_locks 
      ON blocking_locks.locktype = blocked_locks.locktype
      AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
      AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
      AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
      AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
      AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
      AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
      AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
      AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
      AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
      AND blocking_locks.pid != blocked_locks.pid

    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
  WHERE NOT blocked_locks.GRANTED;
      

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

Выглядит достаточно сложно, но результат приятен для глаз. Вообще, сообщество PostgreSQL создало и поддерживает достаточно много ресурсов, которые помогают и облегчают поиск информации рядовым администраторам БД. Например, та же вики wiki.postgresql.org

Итак, видим кто и кого блокирует. Есть ещё варианты подобных запросов, где можно вывести информацию и о том, как долго уже процесс ждет своей очереди, и тд.

Вторая ссылка (из официальной, между прочим, документации) предлагает совсем уж простой запрос:

Запрос 2:

  SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
      ON pl.pid = psa.pid;
      

Смысл всех этих вариаций по сути одного и того же запроса: вывод информации о блокировках. Нужную информацию мы получили, но ответ не лежит прямо на поверхности. Особенно если к БД осуществляется много запросов. Сиди и разбирайся сам, кто кого и почему блокирует! Построить граф заблокированных ресурсов у себя в голове может далеко не каждый!

К тому же, нам надо уничтожить или остановить блокирующий процесс. И да, это придется вручную, через другой запрос с указанием pid процесса —

select pg_backend_pid(16728);

или

select pg_terminate_backend(16728);

Чтобы проверить результат, снова запускаем Запрос 1 или

SELECT * FROM pg_catalog.pg_stat_activity WHERE pid=16728;.

Всё просто и удобно с pgSqlBlocks!

Хочу показать вам ещё один инструмент и поделиться, чем он так удобен, — pgSqlBlocks. Инструмент pgSqlBlocks написан нами для себя, и создан именно для того, чтобы облегчить решение проблем с блокировками в PostgreSQL, которым мы пользуемся уже больше года.

Вот так выглядит окно pgSqlBlocks в случае нашего примера с двумя процессами (здесь они имеют pid 29981 (SELECT) и 28710 (DROP RULE)).

В левой части окна имеется список баз данных, в котором отображается информация о состоянии подключения к БД (соединен, отключен, обновление информации, ошибка соединения, имеются блокировки в БД).

Основную часть приложения занимает дерево процессов, которые на данный момент есть в выбранной БД. Блокированные процессы имеют иконку закрытого серого замка и являются потомками блокирующих процессов, чья иконка — красный замок. Иконка обычных процессов — зеленая точка

Такое представление процессов позволяет нам легко ориентироваться в них, получать информацию о блокирующих и ожидающих процессах, а также об их отношении друг к другу. Можно для большей наглядности скрыть обычные (не заблокированные и не блокирующие) процессы.

Наглядно видим, что процесс с pid 29981 с долгим SELECT-запросом блокирует процесс с pid 28710.

При необходимости можно послать сигнал отмены или уничтожении любого процесса. Например, если уничтожить блокируемый процесс 28710, то информация в дереве процессов тут же обновится и мы увидим результат — процесс 29981 с долгим SELECT-запросом больше никого не блокирует. Быстро и удобно.

Еще из мелких и приятных фич приложения можно отметить:

Установка и настройка

В системе должна быть предустановлена JRE 8.

Заходим по адресу github.com/technology16/pgsqlblocks/releases/latest и выбираем последнюю актуальную версию программы. В папке будут лежать 4 jar-файла. Выбираем тот, который подходит под ОС и разрядность Вашей системы. Скачиваем, запускаем и вуаля!

Это всё, что нужно для запуска приложения. Всё работает “из коробки”.

Для начала работы с приложением стоит заполнить список с базами данных. Для добавления новой БД нажмите иконку БД со значком "+" над списком БД и заполните необходимые данные в появившемся диалоге. Пароль лучше хранить в pgpass файле.

Протестировано на версиях 9.2-9.6 PostgreSQL.

Дополнительно можно настроить частоту обновления информации из БД, необходимость показывать idle процессы, список отображаемых колонок.

Заключение

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

Таким инструментом для нас является pgSqlBlocks — это приложение, которое позволяет легко ориентироваться среди процессов и получать информацию о блокирующих и ожидающих запросах.

К преимуществам его можно отнести наглядность предоставленной информации, а также удобство выполнения типичных задач — просмотра информации о процессах, поиска проблем среди списка процессов, отмены или терминирования процесса и оценки результата. Кроме того, приятной возможностью является сохранение истории блокировок в файл для дальнейшего разбора сложившейся ситуации. Всё это делает вашу работу с блокировками в БД PostgreSQL быстрой и удобной.

P.S.: вдохновением для создания этого приложения стала утилита MSSQL Blocks. Но она предназначена именно для работы с БД MSSQL. Для PostgreSQL его аналогов не оказалось.

→ Скачать последнюю версию для своей ОС можно тут.