一场mysql查询引起的“卡顿” 作者: 灯小笼 时间: 2018-09-03 分类: 架构 一般我们说优化mysql,总是会将着眼点放在mysql的慢查询的优化上,或者字段的数据类型,mysql的分库/分表,读写分离,异或事务这些看上去高大上的东东上。但是,对于一些更加基础或者更加常识的地方,往往会加以忽视。就比如,本文将要提到的:mysql的查询本身的优化。 这次接到的优化任务出现的背景是: 我们有一个内部电话呼叫的系统,在下午使用客服相对较多的时候,在很随机的情况下,会出现突然的卡顿,这种现象维持的时间很短,检查的时候,往往发现cpu、内存、负载、网络,都很正常,查询错误日志也看不到异常。 系统用的是阿里云,2C4G,看负载的曲线也维持得很小很平稳,内存消耗也没有达到瓶颈。中间一度怀疑过对缓存的使用有问题,因为有很多临时数据都是通过cache在传输和保存的,调用频率也很高。于是,负责此项目的同学也做过很多方面的优化,想过很多招数。比如: 1、临时数据的保存从全量改为增量,大大降低了数据传输的量。 2、将数据库改成长连接,减少连接使用量。 原来我们使用的数据库是由阿里云提供的,后来降配了,长连接经常出现连接数不够的时候,又改为普通连接了。最后,我们又把mysql改成了自建,连接数2000,还是够用的。不过,即使使用长连接的时候,依然会出现卡顿,索性,就一直维持着使用普通连接了。 ## 问题浮出水面 最近,这个优化的任务转到我手头了,于是开始系统地对这个系统做了一些检查。各种看参数,也都正常,没有明显的问题。直到用`iftop`检查的时候,问题终于浮出水面来了。 ```bash iftop -n ``` ![25167-smtou3bgxaj.png](https://c.dengxiaolong.com/blog/typecho/25167-smtou3bgxaj.png-typecho) 右侧第1列表示2s的平均值,同一个ip对应两行,第1行表示发送,第2行表示接收。 标红的地方,分别对应db(172.30.0.66)和cache(172.30.0.65)。 可以发现,db接收到的流量(21.0M)远大于cache的流量(298K)。 往客户端发送流量大致为:总发送量 3.21M - (db发送量1.59M+cache发送量189k) = 1.44M,而接收的数据量则高达22M,1.44/22=6.5%,也就是仅有6.5%的数据被利用起来了,这个利用率还真是低的可怕。 那么,什么原因导致这样的结果呢?来看一个查询量较大的页面的压测数据: ```bash ab -c 7 -n 1000 -H https://xxxx.com/call/user/lists?leadin_id=1533534509 ... Concurrency Level: 7 Time taken for tests: 61.964 seconds Complete requests: 1000 Failed requests: 0 Total transferred: 251424000 bytes HTML transferred: 251108000 bytes Requests per second: 16.14 [#/sec] (mean) Time per request: 433.745 [ms] (mean) Time per request: 61.964 [ms] (mean, across all concurrent requests) Transfer rate: 3962.51 [Kbytes/sec] received Connection Times (ms) min mean[+/-sd] median max Connect: 23 40 13.3 36 144 Processing: 236 392 61.0 394 560 Waiting: 163 295 54.8 299 447 Total: 276 432 64.4 434 627 ... ``` 这是我们能做到的最大的并发数——7,而此时的`iftop`界面已经让人抓狂不已。 ![69153-f7uwwl4wze8.png](https://c.dengxiaolong.com/blog/typecho/69153-f7uwwl4wze8.png-typecho) 是的,没有看错,mysql数据发送量已经高达145M,1000M带宽已经完全被打满了,想要从mysql接收更多的数据已经不太可能。试想想,如果遇到这样的情况,怎么能不卡顿呢? ## 找出深层原因 问题找到了,接下来就要找原因了,最简单粗暴的办法就是抓包,看看sql执行的具体情况了。 ```bash tcpflow -i eth0 -cp port 3306 > sql.log ``` 我们抓取了较长时间的sql,用sublime打开,发现大部分都是这样的场景: ![36165-pnklra36yq.png](https://c.dengxiaolong.com/blog/typecho/36165-pnklra36yq.png-typecho) 咱们来看看划红线的三块: - 典型的`select *`问题,没有对真正要查询的数据字段进行过滤。 - 查询条件过于复杂,当然,这和业务的复杂有关系。 - 数据查询量过大,这当然和第1点有直接的关系。 结合到业务的场景,我们最后形成了如下的结论: 1、没有对返回的表字段加以控制,直接select *,导致单次sql查询返回数据量过大。 2、业务逻辑复杂,同一个页面sql查询次数较多,导致通页面总的查询次数以及返回的数据量过大。 3、没有对一些不常变化的数据加上缓存,导致频繁查用同样的数据。 ## 对症下药 找对了问题,找准了原因,接下来就是对症下药了。这个反而是水到渠成的事情,比前面的环节要简单地多了。我们做了这些针对性地处理: - 优化`select *`语句,只返回必要的字段。 - 缓存部分查询结果,因为中间的过程sql省掉了,因此能减少大量的mysql带宽占用。 - 有的页面没有使用查询条件时,不显示结果,必须加上过滤条件,才查询数据,减少数据集的大小。 ## 开花结果 经过几天的努力,我们的优化成果很快显现出来了。 先来看一张mysql服务器的tcp连接图: ![28862-4skqwoeprkn.png](https://c.dengxiaolong.com/blog/typecho/28862-4skqwoeprkn.png-typecho) 再来看一张对应的mysql服务器的流量图: ![73903-hnfgjrxsb3.png](https://c.dengxiaolong.com/blog/typecho/73903-hnfgjrxsb3.png-typecho) 我们的优化上线的时间大概在8月9日下午4点左右,上线后,mysql的流量一泻千里,径直从原来的20多M降低到平均5M左右。 下面来做大致的推算。以流量图的流入速率作为基准,选取连接图的对应时刻点的已经established的连接数作为除数,计算单个established的连接数对应的网卡流入速率。 * 未优化前,以8月8日18:00左右计算:19.83M/620 = 32.75k * 再以优化后的8月9日19:30左右计算:5.72M/550=10.65k * 后面继续优化了,以8月10日12:00左右计算:1.91M/305=6.41k | 时间 | 流入速度(M/s) | 绑定连接数(#/s) | 流入流量(k/#) | 降低 | |------------|--------------|----------------|-------------|--------| | 8月8日18:00 | 19.83 | 620 | 32.75 | - | | 8月9日19:30 | 5.72 | 550 | 10.65 | 67.5% | | 8月10日12:00 | 1.91 | 305 | 6.41 | 80.4% | 这个80.4%的优化还是来得相当给力的!!!而从实际的效果来看,后面也没有人再因为网站卡顿的问题而抱怨过了。 ## 结论 优化首先讲究的是对症下药,找准了方向,结合业务场景分析,剖析对了原因,如何解决问题就是水到渠成的事情了。因此,我们也需要掌握一些更加系统更加基础的分析问题的方法和手段,能帮我们更好地驾驭各种问题,提升解决问题的能力。 标签: mysql, iftop