Запровадження
Вітання. Я 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';«…»
«…»
“Хм… Відповіді немає. Може, процес займає багато часу?” — подумав я, але про всяк випадок глянув у графану і побачив, що завантаженість диска дуже швидко зростала.
"Небезпечно" - подумав я ще раз і відразу зупинив запит.
Дія 2
Проаналізувавши все, я зрозумів, що обсяг даних був занадто великим, щоб видалити за 1 раз.
Я вирішив написати скрипт, який зможе видаляти близько 1 записів і запустив його.
「реалізую скрипт」
"Тепер точно спрацює", - подумав я
Дія 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 було надто багато.
Спробував надіслати запит наобсяг даних, які база повинна обробляти за 1 день. Вийшло!
Ну і після цього продовжуємо надсилати запити на той самий обсяг даних. Так як треба забрати місячний обсяг даних, повторюємо цю операцію приблизно 35 разів.
Перейменування таблиці
Тут удача була на моїй стороні: все пройшло гладко.
Alert зникли
Швидкість пакетної обробки збільшилась.
Раніше цей процес займав близько години, тепер триває приблизно 2 хвилини.
Після того, як я переконався, що всі проблеми вирішені, я тремтів 300 мільйонів записів. Я видалив таблицю і відчув себе новонародженим.
Резюмування
Я зрозумів, що при пакетній обробці було втрачено rotate processing, і в цьому полягала основна проблема. Така помилка в архітектурі призводить до марнування часу.
А ви думаєте про навантаження при реплікації даних, видаляючи записи з бази? Давайте не перевантажуватимемо MySQL.
Ті, хто добре розуміються на базах даних, з такою проблемою точно не зіткнуться. А решті, сподіваюся, ця стаття була корисною.
Спасибі за прочитання!
Ми будемо дуже раді, якщо ви розповісте нам, чи сподобалася вам ця стаття, чи зрозумілий переклад, чи була вона вам корисною?
Джерело: habr.com