InnoDB занимает более часа, чтобы импортировать файл 600MB, 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, поскольку это чисто для этой тестовой среды.

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

обновление:

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

$ 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 занимает, но он становится более полезным для меня.

4   51   2010-01-30 13:23:07

4 ответа:

вы пробовали Советы По Массовой Загрузке Данных С 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;
    

    для больших таблиц это может сэкономить много дискового ввода-вывода.

ИМО, вся глава стоит прочитать.

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

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

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

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

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

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