MySQL 5.5 GA и переезд на InnoDB: что меняется при одновременных записях

MySQL 5.5 вышла в GA ещё в конце 2010-го, но несколько месяцев часть команды выжидала - слишком свежо, пусть дистрибутивы переварят. К январю 2012-го ситуация сложилась: Ubuntu 10.04 LTS подтянул 5.5 в репозитории, CentOS 6 поставляет её из коробки, и молчать дальше не получается. Плюс Oracle официально объявил InnoDB движком по умолчанию - то есть каждая новая таблица без явного ENGINE= теперь идёт в InnoDB, а не MyISAM. Для нас это сигнал разобраться на живых проектах - лучше заранее, чем когда это случится само собой в самый неподходящий момент.

Что за базы переводим

Три проекта, которые оказались под рукой:

• Биллинговый журнал - таблица событий, несколько сот тысяч строк, активная запись с нескольких агентов одновременно, чтение - периодические отчёты.
• Справочник клиентов - небольшая по объёму, но с частыми UPDATE по нескольким полям из разных процессов.
• Лог импорта данных - исторически MyISAM, запись пакетами, чтение почти никогда.

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

Как переводили

Никаких сюрпризов: ALTER TABLE tbl ENGINE=InnoDB. На небольших таблицах - секунды, на журнале событий - несколько минут с блокировкой. Для продакшена это важно учитывать, мы делали в окно минимальной активности.

Перед переездом сделали mysqldump с явными --single-transaction для тех таблиц, где MyISAM уже обновили на тестовом - дамп MyISAM так не работает без блокировки, это старая история. InnoDB тут сразу удобнее: снапшот без таблличного локка.

Отдельный момент - AUTO_INCREMENT. MyISAM хранит максимальное значение счётчика в заголовке файла, InnoDB восстанавливает его из данных при старте. На практике это значит: если вы когда-то удаляли строки с максимальным id, после рестарта сервера счётчик может прыгнуть назад. Проверили, подправили AUTO_INCREMENT вручную там, где было нужно.

Что изменилось при конкурентных записях

Вот здесь стало интересно.

Биллинговый журнал. При MyISAM любая INSERT или UPDATE накладывала table-level lock - то есть пока один агент пишет, остальные стоят в очереди. При умеренной нагрузке это незаметно, но при пиках мы периодически видели очереди запросов в SHOW PROCESSLIST. После перевода на InnoDB с row-level locking агенты пишут в разные строки без взаимной блокировки. Очереди исчезли. Это наблюдение качественное - мы не ставили нагрузочный стенд с секундомером, но разница в поведении при пиках ощутима.

Справочник клиентов. Тут получили неожиданный бонус: несколько процессов обновляют разные поля одной строки - раньше тоже table lock, теперь row lock. Конфликты между ними сошли на нет.

Транзакции. Это отдельная история. MyISAM не поддерживает транзакции, и несколько мест в коде просто не озадачивались атомарностью - мол, «и так работает». После перехода на InnoDB мы нашли два места, где логика подразумевала «сделай A, потом B» без какой-либо защиты от сбоя между ними. Теперь это можно обернуть в BEGIN / COMMIT. Обернуть - следующий шаг, но сам факт, что теперь есть инструмент, меняет подход к написанию новых вещей.

Лог импорта. Вот здесь - обратная сторона. Пакетная запись без транзакций на InnoDB работает медленнее, чем на MyISAM: каждый INSERT идёт с журналированием. Обернули импорт в одну транзакцию на пакет - скорость вернулась и даже чуть выросла. Но это надо помнить: бездумный переезд на InnoDB для bulk-insert без транзакции может удивить.

Что с памятью

InnoDB активно использует innodb_buffer_pool - буфер, который кэширует данные и индексы. По умолчанию он 8 МБ, что для продакшена смешно. Подняли до примерно 70% от доступной оперативки на выделенных серверах БД - это стандартная рекомендация, и она работает. На серверах, где MySQL соседствует с другими процессами, надо считать аккуратнее.

MyISAM, для сравнения, полагался на кэш файловой системы ОС - более непредсказуемо, но менее требовательно к настройке при старте.

Итог на сегодня

Переезд прошёл без инцидентов. Три базы работают на InnoDB, поведение при одновременных записях заметно улучшилось на двух из трёх проектов. Пакетный импорт потребовал минимальной правки кода - обёртки в транзакцию.

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

Один нюанс, который не стоит упускать: FULLTEXT-индексы в InnoDB на 5.5 не поддерживаются - только в MyISAM. Если у вас есть полнотекстовый поиск прямо в MySQL, переезд без замены механизма невозможен. У нас таких баз не оказалось, но это стоит проверить заранее.