MySQL блокування та persistent connection, Oracle mechanics

Продуктивність СУБД та суміжні питання

persistent

MySQL: блокування та persistent connection

Оскільки в процесі роботи стикаюся, крім Oracle, з більш «свіжими» варіантами реалізації ідеї СУБД, надалі описуватиму чимось чудові і недостатньо (imho) описані особливості реалізації серверів MySQL і MS SQL в частині продуктивності.

Отже, MySQL 5.0.67, Innodb, навантажений LAMP-проект із високою OLTP-активністю: веб-сайт активно відвідується, контент активно доповнюється та редагується. Періодично почали з'являтися такі помилки:

як наслідок - блокування роботи всього сайту аж до перезапуску сервера MySQL фахівцями хостера.

Щоразу у момент виникнення проблем статус INNODB показував схожу картину:

mysql> SHOW ENGINE INNODB STATUS\G;

… LATEST DETECTED DEADLOCK *** (1) TRANSACTION: TRANSACTION 0 14541775, ACTIVE 65 sec, process no 6728,OS thread id 1195632976 inserting mysql tables use 3, locked 3 LOCK WAIT 19 lock struct(s), heap size 3024, undo log entries 25MySQL thread id 5071, query id 10282109 Sending data INSERT INTOnon_stat_table … *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 461340 n bits 608 index `UX2` of table `non_stat_table ` trx id 0 14541775 lock_mode Xinsert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION 0 14548939, ACTIVE 31 sec, process no 6728, OS thread id 1187227984 введення, підписання в InnoDB 500 mysql tables in use 3>18 lock struct(s), heap size 3024, undo logзаписи 27 Ідентифікатор потоку MySQL 5346, ідентифікатор запиту 10282118 Надсилання даних INSERT INTOnon_stat_table … *** (2) УТРИМУЄ ЗАМОК(И): ЗАПИС БЛОКУЄ пробіл id 0 сторінка № 461340 n бітів 608 індекс `UX2` таблиці `non_stat_table ` trx id 0 14548939 lock_mode X Блокування запису, купа № 1 ФІЗИЧНИЙ ЗАПИС: n_fields 1; компактний формат; інформаційні біти 0 0: len 8; шістнадцятковий 73757072656d756d; asc supremum;; *** (2) ЧЕКАЄМО НАДАННЯ ЦЬОГО БЛОКУВАННЯ: ЗАПИС БЛОКУВАННЯ Ідентифікатор простору 0 № сторінки 461340 n бітів 608 індекс `UX2` таблиці `non_stat_table` ідентифікатор trx 0 14548939 lock_mode Xвставити очікування наміру Блокування запису, купа № 1 ФІЗИЧНИЙ ЗАПИС: n_fields 1; компактний формат; інформаційні біти 0 0: len 8; шістнадцятковий 73757072656d756d; asc supremum;; *** МИ ВІДКОТИМО ТРАНЗАКЦІЮ (1) ТРАНЗАКЦІЇ … —ТРАНЗАКЦІЯ 0 14585003, АКТИВНА 1271 с, процес № 6728,Ідентифікатор потоку ОС 1195632976 1 структура блокування, розмір купи 368Ідентифікатор потоку MySQL 5071, ідентифікатор запиту 10311470 БЛОКУВАННЯ ТАБЛИЦІ таблиця `some_stat_table` ідентифікатор trx 0 14585003 режим блокування IX …

Спочатку викликав подозрение досить забавного виду взаємоблокування з очікуваннями типу: lock_mode X вставити намір очікування (своєподібний штатний функціонал MySQL, більше схожий на імхо, подробиці — в описі помилки MySQL #43210 і в свіжій документації MySQL5.1). Але ця тупикова блокування вирішила сервером задовго до виникнення проблеми, яка відносилася до цілком іншої таблиці!

Служба підтримки MySQL не сильно допомогла нам в аналізі причин виникнення — наш пакет все ще в статусі OPEN :(

Судя по діагностиці блокувань Innodb (которая виводиться в розділі TRANSACTION команди SHOW ENGINE INNODB STATUS), будь-який раз при виникненні проблеми, яка блокує, виконується довгограюча (час очікування 1271 сек!) транзакція з темами ж ID потоку, що ітранзакція, яку раніше MySQL вибирав як «жертву» при розборі DEADLOCK'а і мав відкотити (WE ROLL BACK TRANSACTION (1)). На підтвердження цього припущення, проблема з блокуваннями (і, відповідно, «зависанням» всього веб-сайту) легко вирішувалася силовим видаленням «винного» thread'а замість перезапуску всього сервера MySQL:

MYSQL> kill thread_id;

За порадою Максима Смирнова, звернули увагу на постійне з'єднання PHP з MySQL, яке могло бути причиною подібної поведінки: при дозволі deadlock сервер MySQL не повністю очищає блокування конкретного thread'а (у нашому випадку, залишилися блокування на статистичних таблицях, які не брали участі в deadlock ) і подальше існування цього «блокуючого» thread'а (persistent connection) породжує ланцюжок блокувань - див. також обговорення в блозі Peter Zaitsev Are PHP

Для вирішення проблеми та враховуючи, що у випадку з MySQL нові з'єднання створюються дуже швидко і час з'єднання незначно впливає на загальну продуктивність сайту, ми відключили постійне з'єднання з БД:

define(«DBPersistent», false); у файлі dbconn.php

Блокування більше не виявлялося.

І хоча початковою причиною виникнення проблеми (deadlock) була затримка в роботі сервісів, що не стосуються MySQL на веб сервері (див. докладний опис на сайті Бітрікс), приклад добре ілюструє потенційні проблеми використання persistent connection в OLTP системах на основі MySQL.