Как импортировать большую базу данных в mysql innobd — 87Gb?

Question

[AnGel]

Имеется база данных в 87Gb. Импортирую конечно же через терминал.
В базе данных 4 таблицы и последняя на 450 млн. строк. Она же и дает такой вес. Мой конфиг mysql:

[server]
key_buffer_size = 10G
max_allowed_packet = 10G
sort_buffer_size = 10G
read_buffer_size = 10G
read_rnd_buffer_size = 10G
myisam_sort_buffer_size = 10G
thread_cache_size = 10G
#table_open_cache =
query_cache_size = 10G
thread_concurrency = 2048

#innodb_use_native_aio = 0
##innodb_file_per_table

#
innodb_buffer_pool_size = 60G
innodb_additional_mem_pool_size = 128M
#innodb_log_file_size = 2048M
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
#


max_connections=2000
max_user_connections=500
wait_timeout=15
interactive_timeout = 60
#interactive_timeout=2500
#long_query_time=1000
# this is only for the mysqld standalone daemon

Сервер:
Intel Xeon E5-1620v2
64GB DDR3 ECC 1333 MHz
SoftRaid 2x2TB SATA

Что с дефолтным конфигом (никаким), что с этим - импорт долгий. Сначала наблюдаю в phpmyadmin добавление по 100к+, а затем снижение скорости. В итоге затягивается до 4+ дней. Самое минимальное было не так давно. Добавлялось по 1000 строк, это отвратительно.

К слову, это вторая попытка импорта. Предыдущая завершилась сегодня успешно, НО потом бд куда-то пропала!

Импорт запускаю конечно же с применением nohup, терминал не могу поддерживать открытым.

P.S. мыслю над отключением буфера лога полностью, подозрение, что тут проблема еще имеется.

Comments

[AnGel]

Добавлю, что скорость постоянно снижается, вот вам гифка обновления через phpmyadmin.

Answer 1

[Philipp]

Налицо проблема перестроения индекса.

Следующие операции делаем внутри одной сессии с mysql.
Mysql-клиент можно запустить внутри screen сессии, чтобы не бояться отвала терминала.
Сначала делаем так:

SET autocommit=0; 
SET unique_checks=0; 
SET foreign_key_checks=0;

Затем вставляем, используя вставку со множество значений
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
Теперь необходимо закоммитить транзакцию
COMMIT;
И вернуть настройки назад.

SET unique_checks=1; 
SET foreign_key_checks=1;

Еще рекомендуется установить (необходимо полностью остановить сервер и запустить заново после изменения my.cnf)

[mysqld]
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000
innodb_autoinc_lock_mode =2
innodb_open_files=32768

Comments

[AnGel]

Поясните пожалуйста по поводу:
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
Я тут затрудняюсь с пониманием, что мы добавляем? Там таблица из трех колонок в 450 млн строк

[Philipp]

Максим, да, но можно эту таблицу экпортировать в виде, когда каждая строка записана отдельной инструкцией вставки

INSERT INTO yourtable VALUES (1,2,3);
 INSERT INTO yourtable VALUES (4,5,6);
... много строк ...

А можно и в формате с группировкой вставок каждой строки внутри одной инструкции

INSERT INTO yourtable VALUES (1,2,3), (4,5,6), ...и так примерно 500 вставок;
... много строк ...

Answer 2

[tema_sun]

Есть вариант с отключением DoubleWrite буффера
innodb_doublewrite = 0

Только на продкашне так не оставляйте.

Answer 3

[zapimir]

Может очень ускорить восстановление
innodb_flush_log_at_trx_commit = 0 (потом желательно вернуть обратно)
также отключение бинарных логов на время восстановления, и включение каждой таблицы в отдельном файле опция innodb_file_per_table. Ну и более сложно удалить все индексы кроме PRIMARY, а потом добавить их.

Как именно делали бекап, какие настройки mysqldump?

Посмотрите занятую память в процессе восстановления, возможно mysql вылазит в swap.

Comments

[AnGel]

Совсем ничего не выходит в swap, цп совсем не загружен при импорте. Озу максимум забита кешем при импорте.

Стандартные, я просто сдампил. Сейчас думаю как бинарные файлы подсунуть в мускул, чтобы просто не импортипровать

Answer 4

[Andrey Shatokhin]

Вообще не рекомендуется делать логические дампы таких больших баз. Именно из-за перестроения индекса. Даже если его отключить - восстанавливаться все равно будет часами. Используйте для бекапа Percona XtraBackup. Или снапшоты.