Oracle mechanics

21.06.2009

MySQL: блокировки и persistent connection

Filed under: Блокировки,MySQL,persistent connection — Игорь Усольцев @ 01:56
Tags: ,

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

Итак, MySQL 5.0.67, Innodb, нагруженный LAMP-проект с высокой OLTP-активностью: веб-сайт активно посещается, контент активно дополняется и редактируется. Периодически начали появляться ошибки типа:

MySQL Query Error: UPDATE some_stat_table SET ...
[Lock wait timeout exceeded; try restarting transaction]

как следствие — блокировка работы всего сайта вплоть до перезапуска 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 in use 3, locked 3
LOCK WAIT 19 lock struct(s), heap size 3024, undo log entries 25
MySQL thread id 5071, query id 10282109 Sending data
INSERT INTO non_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 X insert 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 inserting, thread declared inside InnoDB 500
mysql tables in use 3, locked 3
18 lock struct(s), heap size 3024, undo log entries 27
MySQL thread id 5346, query id 10282118 Sending data
INSERT INTO non_stat_table
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 461340 n bits 608 index `UX2` of table `non_stat_table` trx id 0 14548939 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) 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 14548939 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (1)
TRANSACTIONS

—TRANSACTION 0 14585003, ACTIVE 1271 sec, process no 6728, OS thread id 1195632976
1 lock struct(s), heap size 368
MySQL thread id 5071, query id 10311470
TABLE LOCK table `some_stat_table` trx id 0 14585003 lock mode IX

Первоначально вызвал подозрение довольно забавного вида deadlock с ожиданиями типа: lock_mode X  insert intention waiting (своеобразный штатный функционал MySQL, больше похожий imho на баг, подробности — в описании MySQL Bug #43210 и в свежей документации MySQL5.1). Но этот deadlock разрешался сервером задолго до возникновения проблем относился к совершенно другой таблице!

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

Судя по диагностике блокировок Innodb (которая выводится в секции TRANSACTION команды SHOW ENGINE INNODB STATUS), всякий раз при возникновении проблем, блокирующей оказывалась долгоиграющая (время ожидания 1271 sec!) транзакция с теми же thread 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 persistent connections evil ?: «…If you recycle connection which has uncommitted transactions you run into trouble».

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

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

Блокировки больше не проявлялись.

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

2 комментария »

  1. Тоже столкнулся с проблемой лока. Решил убиранием mysql_pconnect

    комментарий от Алексей Гуменюк — 14.10.2009 @ 12:09 | Ответить

  2. Не прошло и полгода, как 4 сентября 2009 поддержка MySQL англоязычным голосом Sveta Smirnova ответила на нашу кляузу (bug#43591, см. http://bugs.mysql.com/bug.php?id=43591) и признала:
    1. Описанные нами случай действительно выглядит очень похоже на MySQL bug #43210, см. http://bugs.mysql.com/bug.php?id=43210
    2. Наше первоначальное предложение по решению проблемы методом разнесения операций INSERT по времени вполне разумно, для MySQL 5.0
    Судя по документации, в версии MySQL 5.1 подобных deadlock возникать не должно

    комментарий от iusoltsev — 18.10.2009 @ 22:48 | Ответить


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: