Mysql载入大量数据(load data infile)的优化过程

公司开发的某个产品需要从文本格式的文件导入大量数据, 其中某一个表特别大, 数据文件有接近30G, 大概有6千万行数据. MySQL提供了很强大的工具, load data infile, 具体格式不去介绍了, 它的主要优势在于批量插入数据.

服务器是20核 cpu.

MySQL 部分配置大概如下:

innodb_buffer_pool_size = 20000M

innodb_file_per_table = 1
innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 0

直接开始load数据,  结果悲剧开始了, 命令执行了1个小时,没结果,  2个小时, 还是没结果.

开另外一个终端连到数据库, 查询当前表的行数, 结果是0,  说明 load data infile是一个transaction, 在当前的隔离级别下, 没load完是看不到数据的.

用TOP看了一下资源使用情况, MySQL 只使用了接近100%的CPU(最高2000%).

漫长的等待,  只好做点别的事情...

快下班了, 终于load结束, 看时间统计,执行了5个半小时!

晚上睡觉还一直在思考这个问题, 号称最快的load data infile为什么这样慢?是innodb_buffer_pool_size 太小? 很有可能, 因为文件已近有30G, 比buffer小了10G! 一阵狂喜, 找到答案啦!

一早赶到公司,  把buffer改成40G, 重新执行load data infile, 漫长的等待...

结果很失望,  只加快了十几分钟, 没什么根本的改善! 想了很久,  发现有个地方有点问题,  MySQL号称多线程,  为什么加载数据的时候只使用了5%的总CPU时间呢?  其他19个CPU在看戏啊!

想到这个, 立刻有了思路, 多线程解决之! 先将这个大文件切割成,小文件,然后多线程执行load data infile!

linux提供了split命令, 非常方便, 将文件切成150万行的40个小文件.

改代码, 启动40个线程, 分别执行load data infile!

很快遇到了错误:  Lock wait timeout exceeded!

Google之,  发现innodb_lock_wait_timeout使用了默认值, 50秒, 改成1000秒.

重新执行,  没有看到lock的错误.  察看CPU使用率,接近900%, 说明MySQL的多线程起作用了!等待了接近50分钟,  加载完成, 登陆到数据库,

查行数,  和待导入文件的行数一致!

补充其他几个需要配置的参数:

innodb_doublewrite = 0

innodb_log_file_size = 2048M

innodb_log_buffer_size=2048M

总结:

1, 多观察程序执行时候的资源使用情况.

2, 数据库是很笨的, 不要想当然的以为它很聪明, 这也是

为什么数据库要简单,简洁的原因!