InnoDB需要一个多小时才能在几分钟内导入600MB文件MyISAM [英] InnoDB takes over an hour to import 600MB file, MyISAM in a few minutes

问题描述

我目前正在创建一个环境来测试应用程序的性能；我正在用MySQL和InnoDB进行测试，以找出最能为我们服务的.在这种环境下，我们将自动准备数据库(加载现有的转储文件)并测试我们的测试工具.

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测试相同的数据转储，但是我已经无法使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:

$ 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

经过研究，我发现从MyISAM更改表到InnoDB会使系统变慢，然后使用set global innodb_flush_log_at_trx_commit=2:

After research I came over Changing tables from MyISAM to InnoDB make the system slow and then used 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变量的列表.

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

我是否必须接受如此长时间的InnoDB或可以改善它们?我完全可以控制该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.

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

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

更新:

鉴于反馈，我已禁用了自动提交功能和各种检查功能:

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?

更新2:

我能够访问另一台机器，而进口仅花费了大约8分钟的时间.我比较了配置并将以下设置应用于我的MySQL安装:

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:

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几分钟的路程还很远，但是对我来说，它变得越来越有用.

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.

推荐答案

您是否尝试过批量数据加载提示 dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html"rel =" noreferrer> InnoDB性能调整提示(尤其是第一个):

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

• 将数据导入到InnoDB时，请确保MySQL没有 启用自动提交模式，因为 要求每个磁盘都刷新日志到磁盘 插入.要在期间禁用自动提交 您的导入操作，将其包围 使用SET autocommit和COMMIT 声明:

• 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 autocommit and COMMIT statements:

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

如果使用mysqldump选项--opt，则会得到以下转储文件: 快速导入到InnoDB表中， 即使没有用 SET autocommit和COMMIT 声明.

If you use the mysqldump option --opt, you get dump files that are fast to import into an InnoDB table, even without wrapping them with the SET autocommit and COMMIT statements.

如果辅助键上具有UNIQUE约束，则可以加快表速度 通过暂时关闭 导入期间的唯一性检查 会话:

If you have UNIQUE constraints 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;

对于大表，这可以节省很多磁盘I/O，因为InnoDB可以使用 其插入缓冲区以写入辅助 批量索引记录.肯定 数据不包含重复项 键.

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

如果表中具有FOREIGN KEY约束，则可以 通过打开 外键检查 导入会话的持续时间:

If you have FOREIGN KEY constraints 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;

对于大表，这可以节省很多磁盘I/O.

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

IMO，整章值得一读.

IMO, the whole chapter is worth the read.

这篇关于InnoDB需要一个多小时才能在几分钟内导入600MB文件MyISAM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！