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

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

Обновление:

Учитывая отзывы, я отключил автоматическую фиксацию и различные проверки:

$ 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 все еще далек от нескольких минут, но для меня он становится все более удобным.


mysql performance innodb
person mark    schedule 30.01.2010    source источник
comment
Знаешь что? Нам действительно нужно было переместить это в serverfault. Проголосовал за это. Удачи.   -  person T.J. Crowder    schedule 31.01.2010
comment
@ T.J. Звучит разумно. Могу ли я что-то делать с моей стороны?   -  person mark    schedule 01.02.2010

Ответы (4)


arrow_upward
146
arrow_downward

Вы пробовали Советы по массовой загрузке данных из Советы по настройке производительности 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;

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

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

person Pascal Thivent    schedule 30.01.2010
comment
Спасибо за указатель! Я просмотрел главу, вы все равно процитировали наиболее важные части, применили, и они снова провели тест. Быстрее, но все же намного медленнее. Не могу поверить в это. Кажется, я здесь совершаю настоящую ошибку новичка. - person mark; 31.01.2010
comment
@mfn Пожалуйста. Но действительно, без автоматической фиксации, без проверок уникальности, без проверок внешнего ключа вы должны получить аналогичную скорость. У вас есть какой-нибудь указатель на эту таблицу? Возможно, отбросьте их и создайте заново после массового импорта. - person Pascal Thivent; 31.01.2010
comment
Да, в таблицах есть индексы, особенно самые большие. Я посмотрю, как я могу это сделать. - person mark; 01.02.2010
comment
Я сбросил с вариантом --opt. Размер дампа 700Мб. Сейчас импорт занимает много времени. Прошло 20 минут. Все еще его импорт !!! - person Shiplu Mokaddim; 06.01.2013
comment
По-видимому, --opt по умолчанию, по крайней мере, в 10.13 (из mysql 5.5.31). - person amoe; 06.11.2013
comment
Пробовал все это, но импорт файла размером 4 МБ все равно занял 10 минут. Выключенный. - person Roman Starkov; 29.03.2014
comment
Есть ли способ добавить строки оболочки к файлам дампа sql размером ~ 1 ГБ? У меня много таких огромных файлов. - person Amir Uval; 31.05.2014
comment
@uval Я создал файлы before и after и обернул их: cat before <(pv my_large_dump.gz | gunzip) after | mysql -uroot -p dbname Надеюсь, это кому-то поможет - person snapfractalpop; 26.08.2015
comment
Имейте в виду, что операторы DDL завершают транзакцию. То есть использование autocommit=0 может быть бесполезным или даже контрпродуктивным. - person Rick James; 28.07.2019

arrow_upward
6
arrow_downward

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

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

person T.J. Crowder    schedule 30.01.2010
comment
Теперь я обновил вопрос, явно отключив автоматическую фиксацию. Он работал быстрее, но все равно занимает слишком много времени (ИМХО). - person mark; 31.01.2010

arrow_upward
2
arrow_downward

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

person egmont    schedule 15.01.2015

arrow_upward
1
arrow_downward

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

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

Что касается №2, то в современной системе значений по умолчанию 5M * 2 будет недостаточно. Подробнее см. innodb_log_file_size и _ 4_

person KCD    schedule 31.05.2012