InnoDB занимает более часа, чтобы импортировать 600 Мбайт файлов, MyISAM через несколько минут

В настоящее время я работаю над созданием среды для проверки производительности приложения; Я тестирую MySQL и InnoDB, чтобы узнать, какие из них могут служить нам лучше всего. В этой среде мы автоматически подготовим базу данных (загрузим существующие свалки) и применим наши тестовые инструменты.

Я готов тестировать один и тот же дамп данных с MySQL и InnoDB, но я уже не могу принести первоначальный импорт в удобную для использования часть InnoDB. Начальная свалка заняла больше времени, но это меня еще не беспокоило:

$ for i in testdb_myisam testdb_innodb; do time mysqldump --extended-insert $i > $i.sql; done

real    0m38.152s
user    0m8.381s
sys     0m2.612s

real    1m16.665s
user    0m6.600s
sys     0m2.552s

Однако время импорта было совсем другим:

$ for i in  testdb_myisam testdb_innodb; do time mysql $i < $i.sql; done

real    2m52.821s
user    0m10.505s
sys     0m1.252s

real    87m36.586s
user    0m10.637s
sys     0m1.208s

После исследования я пришел Изменение таблиц из MyISAM в InnoDB делает систему медленной, а затем используется set global innodb_flush_log_at_trx_commit=2:

$ time mysql testdb_innodb < testdb_innodb.sql

real    64m8.348s
user    0m10.533s
sys     0m1.152s

ИМХО все еще ужасно медленно. Я также отключил log_bin для этих тестов и вот список всех переменных mysql.

Должен ли я принимать эти длинные времена InnoDB или их можно улучшить? Я полностью контролирую этот сервер MySQL, поскольку он исключительно для этой тестовой среды.

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

Update:

Учитывая обратную связь, я отключил автосообщение и различные проверки:

$ time ( echo "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;" \
; cat testdb_innodb.sql ; echo "COMMIT;" ) | mysql testdb_innodb;date

real    47m59.019s
user    0m10.665s
sys     0m2.896s

Скорость улучшилась, но не так. Является ли мой тест испорченным?

Обновление 2:

Мне удалось получить доступ к другой машине, импорт был занят всего около 8 минут. Я сравнил конфигурации и применил следующие настройки к моей установке MySQL:

innodb_additional_mem_pool_size = 20971520
innodb_buffer_pool_size = 536870912
innodb_file_per_table
innodb_log_buffer_size = 8388608
join_buffer_size = 67104768
max_allowed_packet = 5241856
max_binlog_size = 1073741824
max_heap_table_size = 41943040
query_cache_limit = 10485760
query_cache_size = 157286400
read_buffer_size = 20967424
sort_buffer_size = 67108856
table_cache = 256
thread_cache_size = 128
thread_stack = 327680
tmp_table_size = 41943040

С этими настройками я дошел примерно до 25 минут. Еще далеко от нескольких минут, которые принимает MyISAM, но это становится более удобным для меня.

Ответы

Ответ 1

Вы пробовали Насыщенные советы по загрузке данных из советов по настройке производительности InnoDB (особенно первый):

При импорте данных в InnoDB убедитесь, что MySQL не имеет режим автосохранения включен, поскольку требует записи журнала на диск для каждого вставить. Отключение автообмена во время ваш импорт операции, окружают его с SET autocommit и COMMITзаявления:
SET autocommit=0;
... SQL import statements ...
COMMIT;
Если вы используете опцию mysqldump --opt, вы получаете файлы дампа, которые быстро импортировать в таблицу InnoDB, даже не обертывая их SET autocommit и COMMITзаявления.
Если у вас есть ограничения UNIQUE для вторичных ключей, вы можете ускорить таблицу импорта путем временного отключения проверки уникальности во время импорта сессия:
SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;
Для больших таблиц это экономит много дисковых операций ввода-вывода, поскольку InnoDB может использовать его буфер вставки для записи вторичного индексные записи в партии. Быть уверенным что данные не содержат дубликатов ключи.
Если у вас есть ограничения FOREIGN KEY в ваших таблицах, вы можете ускорить импорт таблиц путем внешний ключ проверяет продолжительность сеанса импорта:
SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;
Для больших таблиц это может сэкономить много дискового ввода-вывода.

IMO, вся глава заслуживает чтения.

Ответ 2

Вы пытались начать транзакцию с самого начала и совершить ее в конце? Из связанного с нами вопроса : "Измените шаг" Вставить данные ", чтобы начать транзакцию в начале и зафиксировать ее в конце. Вы получите улучшение, я гарантирую."

Помните, что InnoDB является транзакционным, MyISAM - нет. Транзакционные механизмы обрабатывают каждый оператор как отдельную транзакцию, если вы явно не контролируете транзакцию. Это может быть дорогостоящим.

Ответ 3

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

Удаление всех индексов (кроме первичного ключа), загрузка данных, а затем повторное добавление индексов
Проверка вашего innodb_log_file_size * innodb_log_files_in_group достаточна, чтобы избежать записи на диск с частотой менее секунды

В отношении №2 значения по умолчанию для 5M * 2 не будут достаточными для современной системы. Подробнее см. innodb_log_file_size и innodb_log_files_in_group

Ответ 4

Я обнаружил, что жесткий диск является узким местом - старомодные диски безнадежны, SSD в порядке, но все еще далек от совершенства. Импортирование в tmpfs и копирование данных происходит быстрее, подробности: https://dba.stackexchange.com/a/89367/56667