Восстановление MYSQL Backup очень медленное

Question

Я восстанавливаю файл дампа mysql 26 ГБ в ОЗУ MySQL 16 ГБ, работающем на MacOS.

Сначала я попытался восстановить резервную копию MySQL, как это

mysql -ufoo -pbar foo < foo.dump

Это привело к сбою mySQL, потому что foo.dump содержит много очень забавных международных символов, а приведенная выше команда не заботилась о кодировках.

Так я попробовал

mysql -uroot -p --default-character-set=utf8 foo
mysql> SET names 'utf8'
mysql> SOURCE foo.dump

Это сработало, и процесс восстановления не потерпел крах, потому что я думаю, что забавные международные персонажи были обработаны правильно.

Но сейчас процесс восстановления идет очень медленно. Для файла размером 26 ГБ он работает всю ночь (виновником является одна таблица с 40 миллионами строк). Я вижу, что он восстанавливает примерно 3000 строк каждые 15 секунд. Но при таких темпах процесс восстановления будет длиться вечно.

Так есть ли способ быстро восстановить файл дампа и не испортить кодировки?

Answer 1

Ваш процесс медленный из-за высокой активности дискового I/O для операций SQL. Вам нужно оптимизировать innodb для интенсивных операций. Измените свой конфигурационный файл mysql, чтобы иметь следующие строки:

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16

innodb_buffer_pool_size: область памяти, в которой InnoDB кэширует данные таблицы и индекса. Когда данные таблицы кэшируются в пуле буферов InnoDB, к ним можно обращаться с помощью запросов, не требуя дискового ввода-вывода. Изменения данных кэшируются, а не сразу записываются на диск.

Больший буферный пул требует меньше дискового ввода-вывода для доступа к одним и тем же данным таблицы более одного раза. На выделенном сервере базы данных вы можете установить размер пула буферов равным 80% физической памяти компьютера, иначе использовать от 50 to 75 процентов системной памяти. Размер пула буферов по умолчанию составляет 128 МБ.

innodb_log_buffer_size: размер в байтах буфера, который InnoDB использует для записи в файлы журналов на диске.

Большой буфер журнала позволяет запускать большие транзакции без необходимости записи журнала на диск до фиксации транзакций. Таким образом, если у вас есть транзакции, которые обновляют, вставляют или удаляют много строк, увеличение размера буфера журнала экономит дисковый ввод-вывод.

innodb_log_file_size: размер в байтах каждого файла журнала в группе журнала. Большой размер гарантирует, что сервер может сгладить пики и впадины в активности рабочей нагрузки, что часто означает, что имеется достаточно места для журнала повторов для обработки более часа записи. Чем больше значение, тем меньше действий по очистке контрольных точек требуется в пуле буферов, сохраняя дисковый ввод-вывод

Innodb_write_io_threads: количество потоков ввода-вывода для операций записи в InnoDB. Значением по умолчанию является 4. InnoDB использует фоновые потоки для обслуживания различных типов запросов ввода / вывода. , Вы можете настроить количество фоновых потоков, которые обслуживают операции чтения / записи на страницах данных, используя параметры конфигурации innodb_read_io_threads и innodb_write_io_threads.

Эти параметры обозначают количество фоновых потоков, используемых для запросов на чтение и запись соответственно. Каждый фоновый поток может обрабатывать до 256 ожидающих запросов ввода-вывода.