Гей, запит! Ти живий? Як легко обробити блокування в PostgreSQL

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

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

Щоб розібратися в ситуації, адміністратора БД необхідно зрозуміти, який блокує процес і який процес є блокованим, а також мати можливість скасувати або «вбити» блокуючий процес і в кінці перевірити результат.

У цій статті я хочу торкнутися теми блокувань в 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. Але вона призначена саме для роботи з БД ms sql. Для PostgreSQL його аналогів не виявилося.

→ Завантажити останню версію своєї ОС можна тут.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.