//
union和union all,你使用哪一个?
//
这是去年在线上遇到了一个系统负载的问题,问题的内容如下:某个从库上的系统负载从5天前开始,一直处于比较高的状态,磁盘IO也比较高,这里我先截取一部分监控的曲线图:
从监控上不难发现,该环境的系统负载成阶梯状线性提升,从5天前开始,逐渐增高,今天负载已经到达了10以上。磁盘的使用率也是从5天前开始,一直处于100%的状态。使用dstat的方法查看当前磁盘的状态,如下:
dstat -cdlmrtn --disk-util --top-io --top-latency 1 ----total-cpu-usage---- -dsk/total- ---load-avg--- ------memory-usage----- --io/total- ----system---- -net/total- sda--sdb-> usr sys idl wai hiq siq| read writ| 1m 5m 15m | used buff cach free| read writ| date/time | recv send|util:util> 0 1 49 50 0 0| 84M 872k|10.1 10.2 10.2|7365M 4320k 8389M 178M|2178 217 |01-06 14:51:10| 941B 1481B|5.70: 100> 1 1 46 52 0 0| 84M 0 |10.1 10.2 10.2|7365M 4312k 8390M 177M|2110 0 |01-06 14:51:11|1062B 2202B|23.1: 100> 1 1 48 50 0 0| 81M 1124k|10.1 10.2 10.2|7365M 4232k 8388M 179M|2145 280 |01-06 14:51:12|1042B 1336B| 0: 100> 1 1 48 50 0 0| 78M 0 |10.1 10.2 10.2|7365M 4232k 8387M 180M|2087 0 |01-06 14:51:13|4731B 3958B|4.50: 100> 1 1 47 51 0 0| 84M 980k|10.1 10.2 10.2|7365M 4248k 8384M 183M|2061 220 |01-06 14:51:14|4653B 17k|4.20: 100> 1 1 55 43 0 0| 82M 952k|10.1 10.2 10.2|7365M 4336k 8385M 182M|2204 186 |01-06 14:51:15|2638B 2844B|1.50: 100> 1 1 60 38 0 0| 74M 84k|10.1 10.2 10.2|7366M 4260k 8383M 183M|1936 7.00 |01-06 14:51:16|1102B 1356B|6.10: 100>
可以看到,磁盘(dsk/total)的read值非常高,达到了84MB/s,当前磁盘IO资源比较吃紧。
针对这个问题,我把我的分析思路写下来,希望会对大家有所帮助:
01
查看连接情况
登录到该机器上,使用show processlist的命令查看这个mysql实例的连接情况,可以看到如下的结果:
mysql--dba_admin@127.0.0.1:sys 11:22:16>>show processlist; +---------+-----------------+--------------------+--------+---------+---------+-------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +---------+-----------------+--------------------+--------+---------+---------+-------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+ | 2031512 | srv_datasync_ro | 10.xx.xxx.51:4619 | mygame | Query | 504168 | removing tmp table| select uid,s,g,t2,m from (select uid,s,g,t2,m from mygame_list_0 union select uid,s,g,t2,m from | 144 | 144 | | 2093843 | srv_datasync_ro | 10.xx.xxx.81:51287 | mygame | Query | 471115 | removing tmp table| select uid,s,g,t2,m from (select uid,s,g,t2,m from mygame_list_0 union select uid,s,g,t2,m from | 144 | 144 | | 2259357 | srv_datasync_ro | 10.xx.xxx.31:7982 | mygame | Query | 384715 | executing | select uid,s,g,t2,m from (select uid,s,g,t2,m from mygame_list_0 union select uid,s,g,t2,m from | 0 | 0 | | 2294662 | srv_datasync_ro | 10.xx.xxx.63:52149 | mygame | Query | 366218 | executing | select uid,s,g,t2,m from (select uid,s,g,t2,m from mygame_list_0 union select uid,s,g,t2,m from | 0 | 0 | | 2298410 | srv_datasync_ro | 10.xx.xxx.75:31859 | mygame | Query | 364181 | executing | select uid,s,g,t2,m from (select uid,s,g,t2,m from mygame_list_0 union select uid,s,g,t2,m from | 0 | 0 | | 2421697 | srv_datasync_ro | 10.xx.xxx.78:64434 | mygame | Query | 298299 | executing | select uid,s,g,t2,m from (select uid,s,g,t2,m from mygame_list_0 union select uid,s,g,t2,m from | 0 | 0 | | 2584289 | srv_datasync_ro | 10.xx.xxx.60:39386 | mygame | Query | 211911 | executing | select uid,s,g,t2,m from (select uid,s,g,t2,m from mygame_list_0 union select uid,s,g,t2,m from | 0 | 0 | | 2746619 | srv_datasync_ro | 10.xx.xxx.51:28107 | mygame | Query | 125515 | executing | select uid,s,g,t2,m from (select uid,s,g,t2,m from mygame_list_0 union select uid,s,g,t2,m from | 0 | 0 | | 2906024 | srv_datasync_ro | 10.xx.xxx.54:8190 | mygame | Query | 39114 | executing | select uid,s,g,t2,m from (select uid,s,g,t2,m from mygame_list_0 union select uid,s,g,t2,m from | 0 | 0 | | 2975370 | srv_datasync_ro | 10.xx.xxx.37:60255 | mygame | Query | 1643 | executing | select uid,s,g,t2,m from (select uid,s,g,t2,m from mygame_list_0 union select uid,s,g,t2,m from | 0 | 0 | | 2975827 | srv_datasync_ro | 10.xx.xxx.61:36801 | NULL | Sleep | 1088 | | NULL | 0 | 0 | +---------+-----------------+--------------------+--------+---------+---------+-------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+ | 0 | 0 |
从上面的show processlist结果,可以看到以下几条信息:
1、
1、state列的状态有2种,一种是executing,另外一种是removing tmp table,从这里的状态不难看出,该查询使用了内存临时表
2、time字段的最大值为504168,这个值说明有些select查询已经hang在这里了,time的值代表已经执行了这么多秒(依旧没有拿到结果),我们以id为2031512这一行为例,它的Time值是504168,进行简单的时间单位换算:
mysql> select 504168/3600/24; +----------------+ | 504168/3600/24 | +----------------+ | 5.83527778 | +----------------+ 1 row in set (0.00 sec)
可以看到,这个连接,其实是5天前的连接了,也就是说,这个SQL已经执行了5天,一直卡在这个removing tmp table的界面没有返回。这样算起来,似乎和发现故障的时间比较吻合,以这个信息为切入点,我问业务方要了下执行的SQL语句。
3、并发的SQL语句看起来都是一样的,只有time字段在递减,这表示之前的一个SQL执行的时间太长了,导致后续的SQL都卡在这里了,由于后续的SQL也进入了executing状态,也占用了一部分MySQL的资源,又反向影响之前的SQL,导致之前的SQL迟迟拿不到返回结果。
02
确认业务方的SQL语句
经过和业务方沟通,拿到了业务方执行的SQL语句,具体的表名字和数据库名字不写了,这里简单说下这个SQL的情况,它是对20个表的一个union查询,类似:
select * from t1 union
select * from t2 union
...
select * from t20;
其中,单表的数据量有200w。所有表加起来在磁盘上的文件大小总共是5G。
使用explain查看执行计划,发现对20个表做的都是全表扫描,最后还有个using temporary table 的字样,也就是使用了临时表。
从这个负载上升的阶梯状图形,大概能猜到,这个任务是每天执行一次,将所有的表数据通过union的方式查到,然后推送给前端。但是很明显,这样的操作使用了内存临时表,导致执行时间过长,是有问题的。
看到这里,系统负载这张图就比较容易看懂了:
每一天的任务还没有执行完成,第二天的任务就来了,这样一天一天累计,系统的负载也就慢慢上来了。
03
尝试修改MySQL部分参数
看到执行的命令迟迟得不到返回,而且可以确定,整个union的过程使用了临时表,于是我习惯性的修改了MySQL的几个参数:
1、调大buffer pool size的值;
2、调整innodb_thread_concurrency值为一个更大的值,让它兼容更多的并发查询数
3、调整tmp_table_size的值,让临时表容量变得更大点儿
等待了数十分钟之后,发现问题依旧没有得到解决。
04
尝试kill这几个查询线程
因为业务方对数据的读取采用的是快照读,所以不牵扯大事务回滚的情况,我使用kill queryid的方法对其中的几条select进行了kill操作,发现一个现象。
| 2975370 | srv_datasync_ro | 10.xx.xxx.37:60255 | mygame | Query | 1643 | executing kill query 2975370; | 2975370 | srv_datasync_ro | 10.xx.xxx.37:60255 | Killed | Query | 1683 | removing tmp table
kill操作之后,状态从query变为killed,连接的状态从executing变为removing tmp table,但是并没有释放连接。
关于kill + queryid这个命令到底做了什么操作,大家可以查看一周前的文档,里面有比较详细的说明。目前我们需要了解该命令的本质是:
0、kill + queryid命令等于kill + connection +queryid
1、它将那个session的状态改为kill_connection,此时MySQL会进行判断,如果一个连接线程的状态为kill_connection,那么MySQL会将其Command列改为killed
2、关掉该查询线程的网络连接,等待innodb识别到该线程的状态为kill_connection,进行资源回收。
05
重启MySQL服务
因为是在从库上进行的SQL操作,而且目前负载过大,磁盘IO打满,整个库几乎处于不可用状态,为了快速解决问题,我直接进行了重启MySQL服务的操作。
注意,如果是主库,请不要直接执行停库动作,除非的你的环境已经有了HA的保障。
重启服务的时候,为了让整个重启的过程更加平滑,可以提前调整参数:innodb_max_dirty_pages_pct.
我们可以使用set global variables的方法临时设置这个参数的值为0,那么就意味着动态的慢慢主动将buffer pool中的脏页刷回磁盘,而不是通过关闭MySQL被动刷新,这个参数的默认值是75,也就是说,最大的脏页最多可以占用buffer_pool中75%空间。我们可以通过查看show engine innodb status命令中的modified db pages,等到这个值很小的时候,我们就可以关闭数据库了,这个时候关闭数据库的速度就会很快。
整个重启过程还算顺利,关闭MySQL和开启MySQL服务分别用了30s左右,整个过程耗时1min左右。重启服务之后,效果还是很明显的,监控如下:
06
对union这个SQL的优化
经过跟业务方进行沟通,发现了这个业务的几个特点:
1、所有的20个表都是状态表,每个表平均200w数据,每天这些数据都会更新和新增,也就是update和insert
2、这个任务每天运行一次,之前每次运行的时长是数个小时,最近数据量增加了,运行时间越来越长。
3、数据是用uid维度进行插入的,理论上不存在重复的数据,注意,这条很关键。
既然不存在重复,那么应用union这个连接方法,似乎就有点不妥。
我们知道,union对两个表进行联合查询的时候,会进行一个去重的操作,而union all进行联合查询的时候,会将所有的数据都给罗列出来。现在看起来,似乎是所有表的数据在提取的时候,有个去重的操作,导致这个SQL的执行时间变长了。为了验证这个过程,我进行了一组测试:
mysql yeyztest>>create table test_union (id int); Query OK, 0 rows affected (0.04 sec) mysql yeyztest>>insert into test_union values (1),(2),(3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql yeyztest>>select 4 as f union select id from test_union; +------+ | f | +------+ | 4 | | 1 | | 2 | | 3 | +------+ 4 rows in set (0.00 sec) mysql yeyztest>>explain select 4 as f union select id from test_union; +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | UNION | test_union | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec) mysql yeyztest>>explain select 4 as f union all select id from test_union; +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | UNION | test_union | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+ 2 rows in set, 1 warning (0.00 sec)
经过这个测试,可以看到,使用union all的方法进行联合查询的时候,执行计划结果只有2行,是没有using temporary的字样的。也就是说,不会出现内存临时表。而使用union查询的时候,执行计划有3行,而且第三行里面有明显的using temporary table字样,这一点,可能是这个SQL的一个重要优化点。
其实,在MySQL中,还可以使用union distinct来显示的指定union查询去重,union distinct语法和单独union的语法执行结果是一样的,只不是加了distinct之后,更加容易理解。如下:
mysql> select 1 union select 1 union select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> select 1 union distinct select 1 union distinct select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> select 1 union all select 1 union all select 1; +---+ | 1 | +---+ | 1 | | 1 | | 1 | +---+ 3 rows in set (0.00 sec)
07
将业务SQL改写为union all的方法重试
经过了上面的测试,跟业务方协商,将SQL改为了union all的方法手工执行了一两次,也就是从:
select * from t1 union
select * from t2 union
...
select * from t20
改为:
select * from t1 union all
select * from t2 union all
...
select * from t20 ;
重新测试这个数据联合查询的SQL,发现执行时间从之前的数个小时变为了7分钟。性能整整提高了好几百倍。
监控图像也变为了:
从这个图像上不难看出,每次执行SQL期间,负载有些许上升,但是整体可控,查询的整个过程呈现周期性。
这个案例给了我几点启发:
业务侧:
1、大表连接查询的时候,尽量不要使用union 的操作,因为union的操作要进行去重,所以会进行重复值的判断,这个判断过程消耗CPU和磁盘IO比较严重
2、可以使用union all的方法代替union的方法,当然,如果表特别大,不建议使用union的方式进行查询,还是建议拆分成单个表进行查询,然后再汇总结果
3、如果表中的字段有时间字段,定时任务取每天的增量数据可能比全量数据更加容易一些。
DB侧:
1、可以使用pt-kill来限制最长查询时间,一旦某个查询超过这个时间阈值,就直接kill掉查询,防止拖垮整个数据库。
2、对于服务器的监控还是需要完善,负载长时间处于较高位置,或者IO util值持续10分钟达到100%,就应该报警,而不是故障驱动,被动发现。