MySQL innodb load data.vs.insert 前因后果
前一段时间接了个业务比较特殊,它的场景是这样的:
存在问题:
1. 主库写入太慢最长的一个表插入时间要329分钟
2. 备库延迟太大(>20w秒),慢查询文件很大16G,存在单点故障
效率图:
优化做法:
把insert换成load
优化成果:
CPU消耗情况:
io情况有所好转,但是iowait仍然严重,因为本来就是io bound型,虽然减少了大部分记录慢日志的io,其他io情况,没有像写入效率那么明显。
备库没有延迟,解决了单点故障
原因:
MySQL手册里已经说得很清楚:
INSERT速度: http://dev.mysql.com/doc/refman/5.5/en/insert-speed.html
如何提高innodb表的load data效率:http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html
1. 关掉自动提交
2. 禁掉唯一索引
3. 禁掉外键
这样能节省大量物理io
原因:
load data跳过sql解析,直接生成数据文件;
load data在导入之前会关掉索引,导入完成后更新索引;
load data开始执行后占用的内存空间不会被purge掉
另外load data的速度和文件每行的大小有关,每行所占的字节数越少,load的速度越快;同样的前提,没有索引的要比有索引的要快;
如何提高插入表的效率,欢迎移步: http://www.informit.com/articles/article.aspx?p=377652&seqNum=4
http://www.innodb.com/doc/innodb_plugin-1.0/innodb-create-index.html
http://www.mysqlperformanceblog.com/2008/04/23/testing-innodb-barracuda-format-with-compression/
http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/