Імпорт великої бази даних MySQL: оптимізація та прискорення

Проблеми під час імпорту великих дампів

Імпорт дампів розміром 1 ГБ+ через стандартний mysql < dump.sql часто зависає або працює дуже повільно. Причини: малий max_allowed_packet, повільний запис InnoDB, величезна кількість транзакцій, тайм-аути.

Оптимізація MySQL перед імпортом

mysql -u root -p -e "
SET GLOBAL max_allowed_packet = 1073741824;  -- 1GB
SET GLOBAL net_buffer_length = 1048576;       -- 1MB
SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB (якщо є RAM)
SET GLOBAL innodb_log_buffer_size = 67108864; -- 64MB
SET GLOBAL innodb_flush_log_at_trx_commit = 2; -- швидше, менш надійно"

Імпорт зі швидкісними оптимізаціями

# Стандартний імпорт
mysql -u root -p mydb < dump.sql

# Стиснений дамп
gunzip < dump.sql.gz | mysql -u root -p mydb

# З прогрес-баром (pv)
apt install pv
pv dump.sql.gz | gunzip | mysql -u root -p mydb

Прискорення через вимкнення перевірок

Додайте на початок дампа (або скористайтеся прапорцями):

mysql -u root -p mydb << 'EOF'
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
SOURCE /path/to/dump.sql;
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;
EOF

mydumper / myloader — паралельний бекап та імпорт

# Встановлення
sudo apt install mydumper

# Створення паралельного дампа (4 потоки)
mydumper -u root -p password -B mydb -t 4 -c -o /backup/mydb_dump/

# Паралельний імпорт
myloader -u root -p password -B mydb -t 4 -d /backup/mydb_dump/

Імпорт частинами (для дуже великих файлів)

# Розбити дамп на частини по 100MB
csplit dump.sql "/^-- Table structure/" {*}

# BigDump — вебінструмент для імпорту через браузер
# Завантажити: https://www.ozerov.de/bigdump/

Моніторинг прогресу імпорту

# В іншому терміналі - дивитися поточну операцію
mysql -u root -p -e "SHOW PROCESSLIST\G" | grep -i import

# Розмір таблиць під час імпорту
mysql -u root -p -e "
SELECT table_name,
    ROUND(data_length/1024/1024, 2) AS 'Data MB',
    ROUND(index_length/1024/1024, 2) AS 'Index MB'
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY data_length DESC LIMIT 10;"

Типові помилки під час імпорту великих дампів

Чому паралельний імпорт не завжди швидший

mydumper/myloader пришвидшують імпорт за рахунок паралельних потоків, але виграш помітний лише якщо диск і процесор самі не є вузьким місцем. На сервері з одним повільним HDD паралельні потоки конкурують за ту саму дискову чергу, і сумарна швидкість запису не зростає, а іноді падає через випадковий доступ замість послідовного. На NVMe-дисках паралелізація дає кратний приріст, тому що там немає штрафу за випадковий доступ.

Коли оптимізації імпорту не допоможуть

Якщо дамп містить одну величезну таблицю з кількома індексами, самі індекси можуть створюватися довше, ніж триває завантаження даних — MySQL перебудовує вторинні індекси після вставки всіх рядків для InnoDB, і на таблиці в десятки мільйонів рядків це може зайняти години незалежно від налаштувань буферів. У цьому разі тимчасове видалення вторинних індексів перед імпортом і їх перестворення після — єдиний спосіб пришвидшити процес, а не тюнінг innodb_buffer_pool_size.

Перевірка результату імпорту

# Звірити кількість рядків з очікуваною (наприклад, зі звіту mydumper)
mysql -u root -p mydb -e "SELECT COUNT(*) FROM orders;"

# Перевірити, що всі індекси створені і таблиця не лишилася у стані Crashed
mysql -u root -p -e "SHOW TABLE STATUS FROM mydb;" | grep -i orders

# Перевірити план типового запиту — індекси мають працювати як раніше
EXPLAIN SELECT * FROM orders WHERE status = 'pending' LIMIT 10;

# Перевірити стан InnoDB — не повинно бути незавершених транзакцій
SHOW ENGINE INNODB STATUS\G

Стовпець Comment у SHOW TABLE STATUS покаже помилку, якщо таблиця пошкоджена після перерваного імпорту — це варто перевірити в першу чергу, якщо імпорт завершився з помилкою посередині файлу. Детальніше про створення бекапів, які потім легко і швидко імпортувати, — у статті про mysqldump і відновлення, про гарячий бекап великих баз без блокувань — у статті про Percona XtraBackup.