Уронил прод на 40 минут одним ALTER TABLE на 280 млн строк. Разбор косяка, не повторяйте

[solidityops]

В пятницу, да, я в курсе, что в пятницу, катал миграцию на основную базу. MySQL 8.0.36 на выделенке в Селектеле, таблица orders, 280 млн строк, около 190 гигов. Задача плёвая: добавить колонку promo_source и индекс по ней. Доку читал, добавление колонки с 8.0 идёт как INSTANT, миллисекунды. Думал, безопасно.

Запустил в 14:20 по Москве. ALTER повис. И вот тут началось. Он встал в очередь за metadata lock, а все остальные запросы к таблице, включая обычные селекты, выстроились в очередь уже за ним. Пул в 500 коннектов кончился за полторы минуты, php-fpm воркеры забились, прод лёг целиком. Причиной оказался аналитический запрос, который кто-то запустил прямо в прод с открытой транзакцией, и он крутился уже четвёртый час. Пока поняли, пока нашли его в processlist, пока убили, прошло 40 минут.

Выводы себе записал: перед любым DDL смотреть information_schema.innodb_trx на долгие транзакции, ставить SET SESSION lock_wait_timeout=5, чтобы альтер сдыхал сам, а не вешал всех, аналитику выгнать на реплику. Что ещё упустил? И как вы катаете DDL на таких объёмах, gh-ost, pt-osc или руками с молитвой?

[markrob1]

@solidityops, Главное, чего ты не написал: lock_wait_timeout в мускуле по дефолту 31536000 секунд. Это ГОД, если что. То есть альтер готов висеть в очереди за MDL год, и весь прод вместе с ним. Мы себе в my.cnf прибили 60 на сервер и 5 в сессии миграций, плюс max_execution_time 30000 глобально для read-only юзеров.

По инструментам: gh-ost поверх 8.0 работает нормально, он без триггеров, читает binlog, умеет тормозить по лагу реплики и вставать на паузу. pt-osc тоже живее всех живых, но триггеры на горячей таблице это плюс 10-15 процентов к нагрузке на запись, на твоих 280 млн я бы подумал. Наша таблица в 400 млн через gh-ost едет часов девять, зато прод вообще не замечает.

[RedisNinja]

@markrob1, проблема не в альтере. у вас аналитика четыре часа держит открытую транзакцию в проде, и узнают об этом только когда всё легло. вот это чините, остальное симптомы

[roman2026]

DDL в пятницу в 14:20 это мощно, уважаю. У нас после похожего случая сделали тупо и сердито: права на DDL в проде есть только у деплой-пайплайна, руками туда не ходит никто. И заведи pt-kill на крайний случай, он сам прибивает запросы дольше N минут. Стоит ноль рублей, спасает регулярно.

[Bill2001]

а зачем что-то выдумывать, с 8.0 же есть ALGORITHM=INSTANT, колонка добавляется мгновенно. у меня на 50 млн строк отрабатывало за секунду

[go_whale]

@RedisNinja, @maxdba_71 он и был INSTANT. Мгновенный альтер всё равно сначала берёт metadata lock, и если перед ним висит длинная транзакция, вся очередь строится за ним. Перечитай ОП, там ровно это и случилось.

[Pmannn]

у нас был идентичный фейл, тоже аналитик, тоже забытая транзакция, только даунтайм вышел два часа. правила теперь такие: вся аналитика только на реплику (на селектеле она стоит около 15к в месяц и окупилась в первый же инцидент), все DDL через gh-ost из пайплайна, окно с 3 до 6 утра, автостоп по лагу. индекс на полмиллиарда строк едет 14 часов и всем плевать, прод живой. скучно, зато за два года ноль инцидентов