mysql 占用cpu高_mysqlCPU占用过高解决方案

最近压测一个接口,发现吞吐率一直上不去,平均响应时间达到13秒多。

压测线程组设置如下:

200个线程,60秒内执行完成,每个线程循环60次。可以归纳为每秒启动200个线程。

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

压测结果如下:

平均响应时间在13秒左右;吞吐量只有14.2。

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

下面说一说自己的解决思路:

1、先去服务器上面看了一下,CPU和内存的占用情况。

top #查看CPU情况

mysqld占用CPU资源持续在370%以上。

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

然后我用nmon监测了一下压测时CPU的占用情况,具体如下:

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

free -m #查看内存占用情况

可利用内存一直都是6G左右。

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

综合以上说明主要问题出在高并发的时候mysql占用CPU比例过高。

2、mysql的问题,肯定是因为接口在进行数据库操作的时候,某些sql语句出了问题。在压测并发的时候,登录mysql后,用命令

show processlist

看到有一条sql多次出现,网上看了的解释如下:

sending data 代表sql正从表中查询数据,如果查询条件没有适当索引,会导致sql执行时间过长

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

单独复制这条sql执行以下,发现时间在0.27秒左右,似乎不是很慢。

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

首先我去代码中定位到这条sql,

List listBill = billService.findByStoreIdAndOrderNumber(storeId, orderNumber); //根据商户号、订单号查询是否已存在订单,返回List数组

if(!listBill.isEmpty()){

//已存在订单,并且是正常消费订单,提示订单已存在

if(transMain.getTransType() == 1){

return “订单数据重复”;

}

if(transMain.getTransType() == 6){

//反结账订单, 先删除原先订单

}

}

这个listBill只是为了判断上报的订单是否已存在,如果已存在,提示已存在,不需要新增。此处先优化这条sql。判断是否存在,不需要将全部的字段都返回,只需要count一下即可。

改写如下:

将返回的List改写为返回数量。

Long billCount = billService.countByStoreIdAndOrderNumber(storeId, orderNumber); //根据商户号、订单号查询是否已存在订单,返回count数量

if(billCount > 0){

//已存在订单,并且是正常消费订单,提示订单已存在

if(transMain.getTransType() == 1){

return “订单数据重复”;

}

if(transMain.getTransType() == 6){

//反结账订单, 先删除原先订单

}

}

3、改完后,再次同样策略压测。发现结果如下

mysql占用CPU占用率变化不大,只是下降了10%左右。但是压测结果有了很大的提升。如下:

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

平均响应时间提升了5秒左右,吞吐率也上升到了30。

但是,这个效果还是不太理想!!执行

show processlist

后发现:

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

改写后的这条sql也频繁的出现。说明还是这条sql的问题。

3、加索引

改sql后虽然有些许改善,但是还是不够理想,所以最后只能加索引,增加查询速度。

在where查询条件上加索引,会很有效果,所以此处我的所以加在storeId, orderNumber这两个字段上,类型为聚合索引。

ALTER TABLE `bill`

ADD INDEX `storeId_orderNumber_index` (`storeId` ASC, `orderNumber` ASC);

加完索引后,再次压测。结果如下:

压测报告:

平均响应时间:0.13秒,吞吐率:135。 有了非常大的提升。

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

CPU占用情况:

可以看到mysql的CPU占用率降到了40%以下。

《mysql 占用cpu高_mysqlCPU占用过高解决方案》

image.png

总结:

1、压测发现接口响应时间不理想;

2、可以肯定是代码问题,想办法定位到具体哪一行代码。

3、登录服务器,查看CPU、内存、I/O等情况。

4、发现是mysql占用CPU过大。

5、使用show processlist查看压测时sql运行情况。

6、定位到具体代码。

7、优化代码逻辑,再次压测验证结果。

8、代码优化有一定效果,但效果不大。增加数据库索引。解决问题。

思考:

1、select * 与 select count(*) 的性能区别。

以往一直只是听说,避免使用select * ,但是没有遇到select *与select 具体字段的性能区别,在此处就遇到了。不需要的字段会增加数据传输的时间,即使mysql服务器和客户端是在同一台机器上,使用的协议还是tcp,通信也是需要额外的时间。

2、索引对于查询的性能提升原理。

点赞