MySQL 5.1 GA: партиционирование по дате сократило запросы к истории с минут до секунд

MySQL 5.1 наконец получил статус GA. Sun Microsystems объявил об этом в ноябре прошлого года, но пока не было нескольких месяцев живой обкатки в сообществе - мы с продакшеном не спешили. К августу картина стала достаточно ясной, чтобы перевести один из клиентских проектов в направлении DWH/BI.

Спойлер: партиционирование работает, event scheduler работает, откатываться не пришлось.

Зачем вообще двигаться с 5.0

У клиента - оптовая компания, несколько десятков пользователей - MySQL 5.0 работал несколько лет без особых жалоб. Транзакционная часть на InnoDB, аналитические таблицы на MyISAM. Проблема нарастала медленно: таблица фактов продаж разбухла примерно до 15 миллионов строк, и запросы за период «квартал прошлого года» или «весь 2008» начали уходить в минуты. Не десятки минут, но стабильно больше двух-трёх - достаточно, чтобы менеджеры жаловались.

Индексы были, EXPLAIN показывал их использование, но таблица к тому времени стала просто большой - и никакой индекс не спасает, когда надо прочитать треть строк за исторический диапазон.

Партиционирование по дате в MySQL 5.1 казалось прямым ответом на эту задачу. Но в 5.0 его не было, а тащить патч Percona в продакшен клиенту не хотелось.

Что такое GA и почему это важно

Разница между Release Candidate и General Availability в MySQL - не формальность. RC означает «мы считаем это стабильным, но поищите баги». GA означает «рекомендуем для продакшена и берём на себя обязательства по совместимости». Для 5.1 путь от первого RC до GA занял больше двух лет - именно потому что партиционирование и ряд других фич в промежуточных версиях вели себя непредсказуемо.

К текущему GA-релизу (5.1.36 на момент нашей миграции) сообщество успело собрать заметный объём отчётов о реальной работе. Критических багов в партиционировании за последние месяцы не всплывало - это и стало сигналом двигаться.

Партиционирование: что сделали

Перед переездом сняли резервную копию, развернули тест на отдельном сервере. Схема изменений была прямолинейной:

Пересоздали таблицу фактов с партиционированием по RANGE на основе числового представления даты (YEAR(date_col) * 100 + MONTH(date_col)). Одна партиция - один месяц. Исторические данные - с 2006-го по сегодня - разложились по партициям при загрузке.

Проверили EXPLAIN PARTITIONS - MySQL честно показывает, какие партиции будут задействованы в запросе. Запрос за конкретный квартал шёл строго в 3 партиции, игнорируя остальные. Это и называется partition pruning.

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

Результат оказался очевидным при первом же прогоне: запросы за исторический квартал упали с нескольких минут до единиц секунд. Не «стали быстрее», а именно - порядок поменялся. Это не оптимизация, это другой план выполнения.

Есть оговорки. Запросы, которые не фильтруют по дате (например, «все продажи менеджера Иванова за всё время»), ускорения не получили - MySQL обходит все партиции. Так и должно быть, это не баг.

Event scheduler: убрали четыре cron-задачи

Вторая фича 5.1, которую попробовали сразу - встроенный планировщик задач. В 5.0 всё, что нужно было делать по расписанию на уровне базы, жило в cron на сервере: ночная агрегация, пересчёт накопленных итогов, удаление устаревших записей в служебных таблицах.

Проблема с cron в таком контексте - хрупкость. Задача запускает mysql -u... -p..., и если база недоступна - cron не знает об этом ничего, просто возвращает ненулевой код, который никто не смотрит. Мониторинг на это отдельно не настроен, и обнаруживается пропуск обычно не сразу.

Event scheduler живёт внутри MySQL. Включается одной строчкой в конфиге:

event_scheduler = ON

Создание события - стандартный SQL:

CREATE EVENT nightly_aggregation
ON SCHEDULE EVERY 1 DAY
STARTS '2009-08-01 02:00:00'
DO
  CALL proc_aggregate_daily();

Четыре задачи переехали в события. SHOW EVENTS показывает статус и время последнего запуска. Ошибки видны в error log MySQL, а не теряются в stdout cron-задачи. Это не радикальное изменение архитектуры, но заметно опрятнее.

Как шла миграция

Сам переезд занял одно воскресное утро. Стандартный порядок:

• Остановили приложение, сняли mysqldump полной базы.
• Обновили MySQL 5.0 до 5.1 на месте (пакет из официального репозитория).
• Запустили mysql_upgrade - он проверяет совместимость таблиц.
• Пересоздали таблицу фактов с партиционированием, загрузили данные из дампа.
• Включили event scheduler, перенесли задачи.
• Запустили приложение, прогнали стандартный набор запросов.

Единственный нюанс - mysql_upgrade нашёл несколько таблиц с предупреждениями о совместимости (несущественные изменения в обработке NULL в индексах). Исправляется REPAIR TABLE, занимает минуты.

Что сейчас

Продакшн работает на 5.1 вторую неделю. Проблем не было. Запросы в аналитическом разделе - быстрые, event scheduler крутит задачи по расписанию, mysqldump в бэкапе отрабатывает без изменений.

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