使用batch insert解决MySQL的insert吞吐量问题_MySQL
首先我们明确一下,insert吞吐量其实并不是指的IPS(insert per second),而是指的RPS(effect rows per second)。
其次我们再说一下batch insert,其实顾名思义,就是批量插入。这种优化思想是很基本的,MySQL中最出名的应用就是group commit。
简单的来说,就是将SQL A 变成 SQL B
SQL A : insert into table values ($values);SQL B : insert into table values ($values),($values)...($values);
下面,我们来看看这种异常简单的改动会带来什么样子的变化。
测试环境交代:单id的表结构,10w个int values,本地使用socket连接MySQL server,使用shell单进程测试。
首先,我们看下使用SQL A将10w个int values插入到test表中所需的耗时,耗时1777秒。
real 29m37.090suser 9m11.705ssys 5m0.762s
然后,我们看下使用SQL B(每次insert,插入10 values)将10w个int values插入到test表中所需的耗时,耗时53秒
real 0m53.871suser 0m19.455ssys 0m6.285s
这是整整近33倍的时间提升。这部分性能提升的原因在于以下几点:
1、每次和MySQL server建立连接都需要经过各种初始化、权限认证,语法解析等等多个步骤,需要消耗一定的资源。
2、更新一个values和更新n个values耗时基本一致。(下面对比一下insert 单values核insert 10 values的profile耗时)
单values:
+------------------------------+----------+| Status | Duration |+------------------------------+----------+| starting | 0.000056 || checking permissions | 0.000010 || Opening tables | 0.000034 || System lock | 0.000010 || init | 0.000011 || update | 0.000061 || Waiting for query cache lock | 0.000003 || update | 0.000015 || end | 0.000003 || query end | 0.000053 || closing tables | 0.000009 || freeing items | 0.000021 || logging slow query | 0.000002 || cleaning up | 0.000003 |+------------------------------+----------+
10 values:+------------------------------+----------+| Status | Duration |+------------------------------+----------+| starting | 0.000061 || checking permissions | 0.000008 || Opening tables | 0.000027 || System lock | 0.000008 || init | 0.000012 || update | 0.000073 || Waiting for query cache lock | 0.000003 || update | 0.000010 || end | 0.000008 || query end | 0.000053 || closing tables | 0.000010 || freeing items | 0.000021 || logging slow query | 0.000002 || cleaning up | 0.000003 |+------------------------------+----------+
但是,是否values积攒的越多,效率越高吗? 答案自然是否定的,任何优化方案都不会是纯线性的,肯定会在某个条件下出现拐点。
我们按照不同的values number进行测试,分别为1、10、50、100、200、500、1000、5000、10000.
从下图我们可以看出,随着values number的增加,耗时先是急剧下降,从1777s变成53s,然后在增加values number就不会有太大的变化,直到values number超过200,最后的10000个values number耗时达到了2分钟。
从下图我们可以看到随着values numbers的增加,QPS(蓝线)先是猛增,然后下降,最终小于1/s。而RPS(绿线)随着增加猛增到一个高level,然后随着增加逐步下降,超过5000个values number之后开始急剧下降。
另,最关键的是,QPS最高峰和RPS的最高峰并不在同一个values number下,也就是说QPS最高的时候并不代表着insert的吞吐量就最高。
在我这个简单测试场景中,values number最合适的值是50,和单values对比,耗时减少97%,insert吞吐量提升36倍。
而这个值和表结构和字段类型及大小都有关系。需要根据不同的场景进行测试之后才可以得出,但是普遍来说,50-100是比较推荐的考虑值。
至于这个如何实现,只要前端写入的时候加入队列即可,可以按照2个条件进行合并
- 队列中积攒到n个values number后在写入数据库,优点是性能最高,缺点是时间不可控,有可能等到第n个需要n秒,这时候业务已经不可接收了。
- 队列中积攒1s之后,有多少个就写入多少个,优点是时间可控,缺点就是values number数目不可能,高并发的情况,可能1s已经积攒上千个values了。
- 最优的方案其实是2个条件同时起作用,即进行个数效验,也进行时间效验,无论达到那个条件都触发后续写数据库操作。
总结:
1、使用batch insert可以提高insert的吞吐量。
2、叠加的values number需要根据实际情况测试得出。
3、同时使用个数和时间控制阀值。
附简单测试的记录值:
ValuesNum |
Time |
QPS |
Rows |
1 |
1777 |
56 |
56 |
10 |
53 |
188 |
1886 |
50 |
49 |
40 |
2040 |
100 |
50 |
19 |
2000 |
200 |
51 |
10 |
1960 |
500 |
57 |
3 |
1754 |
1000 |
60 |
2 |
1666 |
5000 |
69 |
0.3 |
1449 |
10000 |
133 |
0.07 |
751 |
推荐阅读
-
使用cmd运行mysql数据库的时候,报错:"不是内部命令也不是可有运行的程序"问题的解决办法
-
web.py在SAE中的Session问题解决方法(使用mysql存储)
-
Mysql5.7中使用group concat函数数据被截断的问题完美解决方法
-
使用wordpress的$wpdb类读mysql数据库做ajax时出现的问题该如何解决
-
在mysql中使用模糊查询时,使用中文查询结果不正确问题的解决办法
-
mysql出现无法使用、无法启动服务问题的解决方法
-
对于使用JDBC连接mysql数据时The server time zone value '¤¤°ê¼Ð·Ç®É¶¡'...的异常问题解决。
-
Mysql使用kill命令解决死锁问题(杀死某条正在执行的sql语句)
-
mysql遇到不能使用mysql.sock启动的问题及解决办法
-
记一次MySQL死锁(对同一张表update和insert)的解决