Введение
Привет. Я ningenMe, веб-разработчик.
Как сказано в названии, моя история — это история о физическом удалении 300 миллионов записей в MySQL.
Я заинтересовался этим, поэтому решил сделать памятку (инструкцию).
Начало — Alert
В пакетном сервере, который я использую и обслуживаю, имеется регулярный процесс, который один раз в день собирает данные за последний месяц из MySQL.
Обычно этот процесс завершается примерно в течение 1 часа, но в этот раз он не завершался 7 или 8 часов, и alert не переставал вылазить…
Поиск причины
Я попробовал перезапустить процесс, посмотреть логи, но ничего страшного не увидел.
Запрос индексировался правильно. Но когда я задумался, что идет не так, я понял, что объем БД довольно большой.
hoge_table | 350'000'000 |350 миллионов записей. Кажется, индексация работала правильно, просто очень медленно.
Требуемый сбор данных за месяц составлял около 12 000 000 записей. Похоже, команда select заняла много времени, и транзакция долгое время не выполнялась.
DB
По сути, это таблица, которая каждый день увеличивается примерно на 400 000 записей. База должна была собирать данные только за последний месяц, следовательно, расчет был на то, что она будет выдерживать именно этот объем данных, но, к сожалению, операция rotate не была включена.
Эта база данных была разработана не мной. Я принял ее от другого разработчика, поэтому осталось ощущение, что это технический долг.
Наступил момент, когда объем ежедневно вставляемых данных стал большим и, наконец, достиг предела. Предполагается, что работая с таким большим объемом данных, надо бы их разделять, но этого, к сожалению, не было сделано.
И тут в дело вступил я.
Исправление
Было рациональнее уменьшить саму БД и сократить время на ее обработку, чем менять саму логику.
Ситуация должна значительно измениться, если стереть 300 миллионов записей, поэтому я решил так и сделать… Эх, я думал, что это точно сработает.
Действие 1
Подготовив надежную резервную копию, я наконец начал отправлять запросы.
「Отправка запроса」
DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';「…」
「…」
“Хм… Ответа нет. Может процесс занимает много времени?” — подумал я, но на всякий случай глянул в grafana и увидел, что загруженность диска очень быстро росла.
«Опасненько» — подумал я еще раз и сразу остановил запрос.
Действие 2
Проанализировав все, я понял, что объем данных был слишком большим, чтобы удалить всё за 1 раз.
Я решил написать скрипт, который сможет удалять около 1 000 000 записей и запустил его.
「реализую скрипт」
“Теперь точно сработает”, — подумал я
Действие 3
Второй метод сработал, но оказался очень трудоемким.
Чтобы сделать все аккуратно, без лишних нервов, потребовалось бы около двух недель. Но все-таки данный сценарий не соответствовал сервисным требованиям, поэтому пришлось от него отойти.
Поэтому, вот что я решил сделать:
Копируем таблицу и переименовываем
Из предыдущего шага я понял, что удаление такого большого объема данных создает такую же большую нагрузку. Поэтому я решил создать новую таблицу с нуля с помощью insert и в неё переместить данные, которые собирался удалить.
| hoge_table | 350'000'000|
| tmp_hoge_table | 50'000'000|Если сделать новую таблицу по размеру такую же, как указано выше, скорость обработки данных также должна стать на 1/7 быстрее.
Создав таблицу и переименовав ее, я начал использовать ее как master (основную) таблицу. Теперь, если я удалю таблицу с 300 миллионами записей, все должно быть в порядке.
Я узнал, что truncate или drop создают меньшую нагрузку, чем delete, и решил использовать этот способ.
Выполнение
「Отправка запроса」
INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';
「…」
「…」
「эм…？」
Действие 4
Думал, предыдущая идея сработает, но после отправки запроса insert появилась множественная ошибка. MySQL не щадит.
Я уже настолько устал, что начал думать, что больше не хочу этим заниматься.
Посидел-подумал и понял, что, может, для одного раза запросов insert было слишком много…
Попробовал отправить запрос insert на объем данных, которые база должна обрабатывать за 1 день. Получилось!
Ну и после этого продолжаем отправлять запросы на тот же объем данных. Так как надо убрать месячный объем данных, повторяем эту операцию примерно 35 раз.
Переименование таблицы
Здесь удача была на моей стороне: всё прошло гладко.
Alert пропали
Скорость пакетной обработки увеличилась.
Ранее этот процесс занимал около часа, теперь уходит примерно 2 минуты.
После того, как я убедился, что все проблемы решены, я дропнул 300 миллионов записей. Я удалил таблицу и почувствовал себя заново родившимся.
Резюмирование
Я понял, что при пакетной обработке был упущен rotate processing, и в этом заключалась основная проблема. Такая ошибка в архитектуре приводит к пустой трате времени.
А вы задумываетесь о нагрузке при репликации данных, удаляя записи из базы? Давайте не будем перегружать MySQL.
Те, кто хорошо разбираются в базах данных, с такой проблемой точно не столкнутся. А остальным, надеюсь, эта статья была полезна.
Спасибо за прочтение!
Мы будем очень рады, если вы расскажете нам, понравилась ли вам данная статья, понятен ли перевод, была ли она вам полезна?
Источник: habr.com