MySQL InnoDB 需要一个多小时才能在几分钟内导入 600MB 的文件 MyISAM

InnoDB takes over an hour to import 600MB file, MyISAM in a few minutes

提问by mark

I'm currently working on creating an environment to test performance of an app; I'm testing with MySQL and InnoDB to find out which can serve us best. Within this environment, we'll automatically prepare the database (load existing dumps) and instrument our test tools.

我目前正在创建一个环境来测试应用程序的性能；我正在使用 MySQL 和 InnoDB 进行测试，以找出哪个可以最好地为我们服务。在此环境中，我们将自动准备数据库（加载现有转储）并检测我们的测试工具。

I'm preparing to test the same data dump with MySQL and InnoDB, but I'm already failing to bring the initial import to an usable speed for the InnoDB part. The initial dump took longer, but that didn't concerned me yet:

我正准备用 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

However, the import times were quite different:

但是，导入时间却大不相同：

$ 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

After research I came over Changing tables from MyISAM to InnoDB make the system slowand then used set global innodb_flush_log_at_trx_commit=2:

经过研究，我发现将表从 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

IMHO still shockingly slow. I've also disabled log_binfor these tests and here's a list of all mysql variables.

恕我直言，仍然慢得惊人。我也禁用log_bin了这些测试，这里是所有 mysql 变量的列表。

Do I've to accept this long InnoDB times or can they be improved? I've full control over this MySQL server as it's purely for this test environment.

我必须接受这么长的 InnoDB 时间还是可以改进？我完全控制了这个 MySQL 服务器，因为它纯粹是为了这个测试环境。

I can apply special configurations only for initial import and change them back for applications tests so they better match production environments.

我可以只为初始导入应用特殊配置，然后将它们更改回应用程序测试，以便它们更好地匹配生产环境。

Update:

更新：

Given the feedback, I've disabled autocommit and the various checks:

鉴于反馈，我已禁用自动提交和各种检查：

$ 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

The speed improved, but not that much. Is my test flawed?

速度提高了，但没那么快。我的测试有问题吗？

Update 2:

更新 2：

I was able to gain access to a different machine were imports only took about 8 minutes. I compared the configurations and applied the following settings to my MySQL installation:

我能够访问不同的机器，导入只需要大约 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

With these settings I'm now down to about 25 minutes. Still far away from the few minutes MyISAM takes, but it's getting more usable for me.

通过这些设置，我现在可以缩短到大约 25 分钟。离 MyISAM 花费的几分钟时间还很远，但它对我来说越来越有用了。

回答by Pascal Thivent

Did you try the Bulk Data Loading Tipsfrom the InnoDB Performance Tuning Tips(especially the first one):

你尝试的 大容量数据加载提示从InnoDB的性能优化提示（特别是第一个）：

• When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommitand COMMITstatements:

  SET autocommit=0;
  ... SQL import statements ...
  COMMIT;
  

  If you use the mysqldump option --opt, you get dump files that are fast to import into an InnoDBtable, even without wrapping them with the SET autocommitand COMMITstatements.

• If you have UNIQUEconstraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

  SET unique_checks=0;
  ... SQL import statements ...
  SET unique_checks=1;
  

  For big tables, this saves a lot of disk I/O because InnoDBcan use its insert buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.

• If you have FOREIGN KEYconstraints in your tables, you can speed up table imports by turning the foreign key checks off for the duration of the import session:

  SET foreign_key_checks=0;
  ... SQL import statements ...
  SET foreign_key_checks=1;
  

  For big tables, this can save a lot of disk I/O.

• 将数据导入 时InnoDB，请确保 MySQL 没有启用自动提交模式，因为每次插入都需要将日志刷新到磁盘。要在导入操作期间禁用自动提交，请使用SET autocommit和COMMIT语句将其括起来：

  SET autocommit=0;
  ... SQL import statements ...
  COMMIT;
  

  如果您使用 mysqldump 选项--opt，您可以获得可以快速导入到InnoDB表中的转储文件，即使没有用SET autocommitandCOMMIT语句包装它们 。

• 如果您UNIQUE对辅助键有限制，您可以通过在导入会话期间暂时关闭唯一性检查来加速表导入：

  SET unique_checks=0;
  ... SQL import statements ...
  SET unique_checks=1;
  

  对于大表，这可以节省大量磁盘I/O，因为InnoDB可以使用其插入缓冲区批量写入二级索引记录。确保数据不包含重复的键。

• 如果您FOREIGN KEY的表中有约束，您可以通过在导入会话期间关闭外键检查来加速表导入：

  SET foreign_key_checks=0;
  ... SQL import statements ...
  SET foreign_key_checks=1;
  

  对于大表，这可以节省大量磁盘 I/O。

IMO, the whole chapter is worth the read.

IMO，整章值得一读。

回答by T.J. Crowder

Have you tried starting a transaction at the outset and committing it at the end? From the question you linked: "Modify the Insert Data step to start a transaction at the start and to commit it at the end. You will get an improvement, I guarantee it."

您是否尝试过从一开始就开始一个事务并在最后提交它？从您链接的问题：“修改插入数据步骤以在开始时启动事务并在最后提交。您会得到改进，我保证。”

Remember that InnoDB is transactional, MyISAM is not. Transactional engines treat every statement as an individual transaction if you don't explicitly control the transaction. This can be costly.

请记住，InnoDB 是事务性的，而 MyISAM 不是。如果您没有明确控制事务，事务引擎会将每个语句视为一个单独的事务。这可能是昂贵的。

回答by egmont

I found the hard drive to be the bottleneck - old-fashioned disks are hopeless, SSD is okay-ish but still far from perfect. Importing to tmpfs and copying out the data is way faster, details: https://dba.stackexchange.com/a/89367/56667

我发现硬盘驱动器是瓶颈——老式磁盘是没有希望的，SSD 还可以，但仍然远非完美。导入到 tmpfs 并复制出数据的速度更快，详情：https: //dba.stackexchange.com/a/89367/56667

回答by KCD

I had issues doing a lot of bulk importing and recommend the accepted answer. I found you can also speed things up significantly by:

我在进行大量批量导入时遇到问题并推荐接受的答案。我发现您还可以通过以下方式显着加快速度：

1. Dropping all indexes (other than primary key), loading the data then re-adding indexes
2. Checking your innodb_log_file_size* innodb_log_files_in_groupis sufficient to avoid writing to disk in sub-second frequency

1. 删除所有索引（主键除外），加载数据然后重新添加索引
2. 检查您的innodb_log_file_size*innodb_log_files_in_group足以避免以亚秒频率写入磁盘

Regarding #2 the defaults of 5M * 2 will not be enough on a modern system. For details see innodb_log_file_sizeand innodb_log_files_in_group

关于#2，5M * 2 的默认值在现代系统上是不够的。有关详细信息，请参阅innodb_log_file_size和innodb_log_files_in_group