我目前正在创建一个环境来测试应用程序的性能;我正在使用 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
经过研究我过来了Changing tables from MyISAM to InnoDB make the system slow然后用 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
,这是 all mysql variables 的列表.
我必须接受这么长的 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 所需的几分钟还很远,但它对我来说越来越有用了。
最佳答案
您是否尝试过 InnoDB Performance Tuning Tips 中的批量数据加载技巧 (尤其是第一个):
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 withSET autocommit
andCOMMIT
statements:SET autocommit=0; ... SQL import statements ... COMMIT;
If you use the mysqldump option
--opt
, you get dump files that are fast to import into anInnoDB
table, even without wrapping them with theSET autocommit
andCOMMIT
statements.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;
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.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;
For big tables, this can save a lot of disk I/O.
IMO,整章都值得一读。
https://stackoverflow.com/questions/2167522/