Доброе время суток! Администрирование и сопровождение реляционных баз данных чаще всего является нетривиальной задачей. Иногда запросы, работавшие быстро, внезапно начинают "тормозить" по непонятным причинам, размер таблиц растет и в целом производительность базы данных снижается.
Часто причиной такого поведения являются возникающие в базе блокировки различных ресурсов, и соответственно - вырастающее время ожидания этих ресурсов. Например, сложности начинаются в ситуациях, когда два или более запроса в разных сеансах пытаются одновременно изменить одни и те же данные в таблицах или саму структуру таблицы.
Чтобы разобраться в сложившейся ситуации, администратору БД необходимо понять, какой процесс блокирует и какой процесс является блокируемым, а также иметь возможность отменить или "убить" блокирующий процесс и в конце проверить результат.
В этой статье я хочу коснуться темы блокировок в 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-запросом больше никого не блокирует. Быстро и удобно.
Еще из мелких и приятных фич приложения можно отметить:
- Сохранение истории блокировок в файл и загрузка обратно в приложение. Этакий snapshot всех блокировок на момент сохранения, который позволяет в любой удобный момент просмотреть и проанализировать, какие были блокировки в БД;
- Иконка в трее меняется, если хотя бы в одной из подключенных БД появилась блокировка;
- Нотификации в трее при появлении блокировок;
- Настраиваемое автообновление списка процессов.
Как установить pgSqlBlocks и чем он удобен по сравнению с описанными выше вариантами?
Установка и настройка
В системе должна быть предустановлена JRE 8.
Заходим по адресу pgcodekeeper.ru/pgsqlblocks и выбираем последнюю актуальную версию программы. В папке будут лежать 4 jar-файла. Выбираем тот, который подходит под ОС и разрядность Вашей системы. Скачиваем, запускаем и вуаля!
Это всё, что нужно для запуска приложения. Всё работает "из коробки".
Для начала работы с приложением стоит заполнить список с базами данных. Для добавления новой БД нажмите иконку БД со значком "+" над списком БД и заполните необходимые данные в появившемся диалоге. Пароль лучше хранить в pgpass файле.
Протестировано на версиях 9.2-9.6 PostgreSQL.
Дополнительно можно настроить частоту обновления информации из БД, необходимость показывать idle процессы, список отображаемых колонок.
Заключение
Проблема появления блокирующих запросов в БД может быть очень серьезной и приводить к заметному замедлению работы БД и исчерпанию дискового пространства. Поэтому важно иметь удобный и быстрый инструмент для детектирования блокировок и принятия (иногда) оперативных действий.
Таким инструментом для нас является pgSqlBlocks - это приложение, которое позволяет легко ориентироваться среди процессов и получать информацию о блокирующих и ожидающих запросах.
К преимуществам его можно отнести наглядность предоставленной информации, а также удобство выполнения типичных задач - просмотра информации о процессах, поиска проблем среди списка процессов, отмены или терминирования процесса и оценки результата. Кроме того, приятной возможностью является сохранение истории блокировок в файл для дальнейшего разбора сложившейся ситуации. Всё это делает вашу работу с блокировками в БД PostgreSQL быстрой и удобной.
P.S.: вдохновением для создания этого приложения стала утилита MSSQL Blocks. Но она предназначена именно для работы с БД MSSQL. Для PostgreSQL его аналогов не оказалось.