今天,解决一个在大量数据环境下,查找Mysql数据库恢复的功能丢数据问题。在调试过程中发现一个现象,半个小时大约才插入了2.5万条记录。12小时才能插入60多万条记录。小量数据恢复是没有问题。只有在大量数据条件下恢复采用问题,200多万条记录的数据库恢复,我得恢复到猴年马月啊。正好今天时间宽松。优化它一下。
分阶段计时之后,发现是插入数据库的时间最长。之前的核心设计是采用sql模板方式(insert into table1 (c1,c2) values(@c1,@c2))实现的。每插入一条记录,重新填充一次模板即可。好处是不用关注特殊字符和数据类型,但平均时间在0.1秒/条。也是十分头疼的。
我决定利用一条SQL语句插入多条记录的方式(insert into table1(c1,c2)values(1,2),(3,4))来优化它。结果由于部分语句sql长度太长了。导致mysql缓存不足(Packets larger than max_allowed_packet are not allowed.)。于是百度了一段
查看目前配置
show VARIABLES like '%max_allowed_packet%';
显示的结果为:
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
以上说明目前的配置是:1M
修改方法
1) 方法1
可以编辑my.cnf来修改(windows下my.ini),在[mysqld]段或者mysql的server配置段进行修改。
max_allowed_packet = 500M
如果找不到my.cnf可以通过
mysql --help | grep my.cnf
去寻找my.cnf文件。
2) 方法2
进入mysql server
在mysql 命令行中运行
set global max_allowed_packet = 500*1024*1024
然后关闭掉这此mysql server链接,再进入。
show VARIABLES like '%max_allowed_packet%';
查看下max_allowed_packet是否编辑成功
show VARIABLES like '%max_allowed_packet%';
显示的结果为:
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
以上说明目前的配置是:1M
修改方法
1) 方法1
可以编辑my.cnf来修改(windows下my.ini),在[mysqld]段或者mysql的server配置段进行修改。
max_allowed_packet = 500M
如果找不到my.cnf可以通过
mysql --help | grep my.cnf
去寻找my.cnf文件。
2) 方法2
进入mysql server
在mysql 命令行中运行
set global max_allowed_packet = 500*1024*1024
然后关闭掉这此mysql server链接,再进入。
show VARIABLES like '%max_allowed_packet%';
查看下max_allowed_packet是否编辑成功
然后又也出现了特殊字符问题。什么单引号、什么斜杠问题。
全部通过之后,发现速度飞快。提升了不止10倍效率。
不过,这里还有一个隐患。在模块化设计的框架下,如果单条语句的通道占用时间过长,会导致其他语句的排队进而超时。所以我们不能只关注整体效率,还要关注局部效率。
如何同时提高整体和局部效率,是我们要思考的重点。