MySQL 5.5 RC и InnoDB по умолчанию: мигрировали нагруженную базу и получили ноль deadlock-ов

Несколько недель назад Oracle выкатила MySQL 5.5 RC. Главная новость для нас - InnoDB теперь движок по умолчанию вместо MyISAM. Это не просто смена настройки в my.cnf: в 5.5 переписан планировщик потоков внутри InnoDB, изменилась логика управления буферным пулом, переработан механизм блокировок. Мы как раз подбирали момент, чтобы разобраться с одной хронической проблемой у клиента - и сроки совпали.

Клиент и симптомы

Небольшое производство, веб-приложение для внутренней логистики: прием заявок, движение по складу, отчёты диспетчера. База MySQL 5.1, таблицы преимущественно MyISAM. Нагрузка умеренная в обычные часы, но несколько раз в день - пики: одновременно работают несколько операторов, и каждый может обновлять одни и те же строки статусов заявок.

Проблема - deadlock-и. Не катастрофические, но регулярные: несколько раз в день приложение получало ERROR 1213 (40001): Deadlock found when trying to get lock, пользователи видели сообщение об ошибке, нажимали «повторить» и в половине случаев получали то же самое снова. Поддержка разводила руками, разработчик приложения говорил, что это проблема базы, мы как обслуживающая сторона занимались устранением симптомов.

Пробовали поднять innodb_lock_wait_timeout - не помогло. В MyISAM-таблицах с табличными блокировками ситуация была ещё хуже: при конкурентных UPDATE вся таблица блокировалась и запросы выстраивались в очередь.

Почему MyISAM держались так долго

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

Ещё один фактор - FULLTEXT-поиск. В MySQL 5.1 он работал только в MyISAM, и в схеме было несколько таблиц-справочников с полнотекстовыми индексами для поиска по наименованиям. Это создавало реальное препятствие для миграции.

В MySQL 5.5 RC FULLTEXT в InnoDB не появился - его нет. Пришлось переработать этот кусок логики: вынесли полнотекстовый поиск в отдельные MyISAM-таблицы только для чтения, которые обновляются по расписанию, а все рабочие таблицы перевели в InnoDB.

Что делали перед переключением

Первый шаг - аудит схемы. Прошлись по каждой таблице и выяснили:

• Рабочие таблицы со статусами и движением (заявки, позиции, история) - очевидные кандидаты в InnoDB.
• Справочники с FULLTEXT - оставляем в MyISAM, только для чтения из приложения, запись через служебный процесс.
• Архивные таблицы - вынесли в отдельную базу с MyISAM, туда пишут редко и только по ночам.

Второй шаг - тест на копии. Развернули реплику на тестовом сервере, конвертировали таблицы через ALTER TABLE ... ENGINE=InnoDB, прогнали синтетическую нагрузку. Здесь же обнаружили первый сюрприз: несколько таблиц использовали TINYINT в качестве первичного ключа, и в паре мест счётчик уже упирался в 127. В MyISAM это проявлялось как молчаливое переполнение; при конвертации InnoDB стал более строгим к типам на вставке. Исправили схему заодно.

Третий шаг - my.cnf под 5.5. В новой версии innodb_thread_concurrency по умолчанию 0 (без ограничений), но на нашем железе - двухсокетный сервер, восемь ядер суммарно - это давало неожиданную конкуренцию за mutex при высокой нагрузке. Выставили значение равным числу ядер, добавили innodb_buffer_pool_size на 70% доступной RAM. innodb_flush_log_at_trx_commit = 2 - для этой задачи допустимо, приложение не банк.

Переключение и результат

Миграцию делали в воскресенье в ночь - около двух часов с учетом переименования таблиц, ALTER TABLE по одной и проверки контрольных сумм. Приложение поднято, первый рабочий день понедельника прошёл без единого deadlock-а в лог-файле.

Через неделю посмотрели на метрики (у клиента есть простейший мониторинг времени ответа на уровне приложения):

• Deadlock-и - ноль за неделю. При прежнем режиме было несколько штук в день.
• Latency 95-го перцентиля на тяжёлых операциях обновления статусов упала примерно вдвое - примерно так и ожидалось, потому что InnoDB блокирует строки, а не таблицу, и конкурентные запросы перестали выстраиваться в очередь.
• Пиковые INSERT немного замедлились по сравнению с MyISAM - это известная цена транзакционности. Для данной задачи некритично.

Что осталось открытым

RC есть RC. Мы сознательно пошли на это для клиента с умеренной нагрузкой и не самым критичным сервисом - внутренняя логистика, не биллинг. Мониторинг и ежедневные резервные копии оставляем в усиленном режиме.

FULLTEXT в InnoDB нас беспокоит как незакрытый вопрос: костыль с MyISAM-таблицами только для чтения работает, но это именно костыль. Если в финальном релизе или следующем ничего не изменится, будем думать о сторонних решениях.

О том, как вообще устроено сопровождение серверов и баз данных, можно почитать отдельно - там же вопросы мониторинга и реагирования.