Виправлення уповільнення мого розробницького середовища: експерименти з SQL та базами даних великого обсягу

Виконання масових видалень у базах даних з великим обсягом даних, особливо коли потрібно зберегти невеликий підмножину рядків, не завжди є простим. Можуть виникати проблеми, такі як тайм-аути очікування блокувань, конфлікти тимчасових таблиць і проблеми з обмеженнями зовнішніх ключів. У цій статті я поясню свій підхід до видалення рядків при збереженні вибраних даних за допомогою методу обміну таблицями, щоб уникнути проблем з продуктивністю.

Моя мета полягала у видаленні сотень тисяч рядків, автоматично згенерованих в моєму середовищі розробки нашою внутрішньою інфраструктурою. Хоча такий обсяг даних може бути корисним для багатьох сценаріїв тестування, він спричиняє проблеми з продуктивністю додатка в моєму середовищі розробки, яке працює на повільних віртуальних машинах. Це робить тестування повільним і розчаровуючим, адже рендеринг сторінки з даними інколи займає кілька хвилин.

Як інженер з тестування, імплементація додатка була новою і більше схожою на чорну скриньку для мене, тому я потребував швидкого рішення. Розробники могли б знайти кращий або ефективніший підхід, але на той момент занурення в таблиці зв’язків, розуміння того, як працює інфраструктура, або дослідження інших складних рішень не виглядали як найшвидший варіант. Цей підхід добре працював для того, що мені було потрібно в той момент.

1. Початкове видалення за допомогою NOT IN з обмеженнями партій

Перший підхід полягав у серії команд видалення по частинах, щоб уникнути проблем з блокуваннями і покращити продуктивність. Ось що я зробив:

Вставив рядки для збереження в тимчасову таблицю для спрощення умови видалення.

CREATE TEMPORARY TABLE retain_rows AS  
SELECT * FROM original_table  
ORDER BY primary_key_column DESC  
LIMIT 150; -- Залишити перші 150 рядків за певною умовою

1. Використовуючи процес видалення по частинах з умовою LIMIT, видалити рядки, яких немає в retain_rows.

DELETE FROM original_table  
WHERE primary_key_column NOT IN (SELECT primary_key_column FROM retain_rows)  
LIMIT 500;

1. Для автоматизації процесу видалення по частинах була створена збережена процедура:

DELIMITER //  

CREATE PROCEDURE DeleteInBatches()  
BEGIN  
 DECLARE done INT DEFAULT 0;  
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET done = 1;  

 WHILE done = 0 DO  
 DELETE FROM original_table  
 WHERE primary_key_column NOT IN (  
 SELECT primary_key_column   
 FROM retain_rows  
 )  
 LIMIT 500;  

 SET done = (ROW_COUNT() = 0); -- Вихід, якщо не було змінено рядків  
 COMMIT; -- Збереження після кожної партії  
 END WHILE;  
END //  

DELIMITER ;

1. Виконати процедуру:

CALL DeleteInBatches();

1. Проблеми, з якими я зіткнувся:

• Тайм-аут очікування блокувань: MySQL видавав тайм-аути блокувань (Помилка запиту (1205): Перевищено тайм-аут очікування блокування; спробуйте перезапустити транзакцію) під час видалення через блокування на рівні рядків.
• Час виконання: навіть при обробці партіями видалення сотень тисяч рядків виявилося повільним.
• Конфлікти з тимчасовими таблицями: виникали конфлікти з тимчасовими таблицями (наприклад, Помилка запиту (1064): Синтаксична помилка поблизу ‘END WHILE’) через повторне використання тимчасових таблиць у паралельних сесіях.

2. Альтернативний підхід: дублювання таблиць та обмін

Для вирішення цих проблем я вирішив використовувати метод обміну таблицями, дублюючи оригінальну таблицю (порожню), вставляючи тільки потрібні рядки і міняючи імена таблиць. Цей метод уникнув повільного видалення по частинах та зменшив контенцію блокувань.

Створивши таблицю за допомогою CREATE TABLE … LIKE, була створена нова таблиця з такою ж структурою, як у оригіналу.

CREATE TABLE new_table LIKE original_table;

1. Вставив тільки збережені рядки в нову таблицю new_table.

INSERT INTO new_table  
SELECT * FROM original_table  
ORDER BY primary_key_column DESC  
LIMIT 150;

1. Оригінальну таблицю перейменували в резервну, а нову таблицю перейменували, щоб замінити оригінальну.

RENAME TABLE original_table TO backup_table, new_table TO original_table;

3.
Цей обмін зробив так, що originaltable містила лише збережені рядки, не впливаючи на оригінальні дані (які тепер знаходяться в backuptable).

Опція скасування — якщо це буде необхідно, таблиці можна обміняти назад, виконавши операцію RENAME у зворотному порядку:

RENAME TABLE original_table TO new_table, backup_table TO original_table;

Проблеми з обмеженнями зовнішніх ключів

Після обміну додавання зовнішніх ключів до нової original_table викликало наступну помилку:

ALTER TABLE original_table  
ADD CONSTRAINT fk_constraint FOREIGN KEY (foreign_key_column)   
REFERENCES other_table (primary_key_column);  

-- Помилка: Неможливо записати; дублікати ключів у таблиці '#sql-1_xxx'

Мої спроби:

• Я виявив і видалив конфліктні індекси, але проблема залишилася.
• Помилка була спричинена обробкою MySQL тимчасових таблиць, ймовірно, через неповні додавання обмежень у попередніх операціях.
• Перезапуск (LOL) інколи може очистити такі конфлікти, але це зазвичай призводить до збоїв.

Висновок

У цій статті я поділився тим, як я вирішував проблеми з видаленням партіями у таблицях з великим обсягом даних, використовуючи дублювання таблиць та їх обмін. Це не обов'язково має бути універсальний метод, але це приклад нестандартного підходу. Це був рідкісний, відносно простий випадок, де я застосував свої ідеї, знання та ШІ для пошуку ефективних рішень. Я взяв на себе ініціативу, хоча це не було моєю прямою відповідальністю, і уникнув глобальних змін чи використання часу інших інженерів для виправлення проблеми з продуктивністю.

Перекладено з: Fixing my dev env’s slowdown: fun with SQL and high-volume databases