记一条慢SQL导致mysql进程耗尽服务器CPU资源(400%),导致服务器负载(40左右)高居不下,最后导致业务无法交易。
常见的原因:
原因1、应用负载(QPS)高导致mysql消耗非常高的CPU资源。(此情况下MySQL的连接数会非常高)
原因2、查询执行成本高(查询访问表数据行数多)导致mysql消耗非常高的CPU资源。(此情况下MySQL的连接数不一定会很高,但是每一个mysql进程占CPU高时都有重SQL)
原因1:
如果应用连接MySQL的连接数远远大于MySQL的最大连接数,就会给MySQL带来很大的压力。造成原因1所指问题。
特征:实例的 QPS(每秒执行的查询次数)高,查询比较简单,没有出现慢查询(或者慢查询不是主要原因)。MySQL的连接数很多。
解决方法:SQL优化收益不大。可以从应用程序、架构,服务器的硬件配置出发解决问题。
1、升级服务器的配置。例如:增加CPU资源。
2、业务方面。增加从库,将对数据一致性不敏感的查询业务转移到从库上,分担主库的压力
3、借助一些缓存数据库(像:Redis)尽量从缓存中查询需要的数据。
4、对于查询数据比较静态、查询重复度高、查询结果集小于 1 MB 的应用,考虑开启查询缓存(Query Cache)。
5、尽量优化查询,减少查询的执行成本(逻辑 IO,执行需要访问的表数据行数),提高应用可扩展性。
原因2:
当查询成本比较高的SQL(慢sql,重sql),频繁使用时,就会给mysql带来很大的压力,造成原因2所指问题。
特征:MySQL的连接数不一定很大;如果MySQL消耗CPU资源的比例不稳定,每次MySQL进程消耗CPu高时,都会出现一些慢SQL;SQL查询执行效率低、执行时需要扫描大量表中数据、没有走索引。
解决方法:
追踪MySQL进程在消耗很高CPU的情况下,执行的SQL语句。可以通过执行如下简单的脚本来记录,每次负载高是执行的sql语句。(每次mysql负载高时,执行./trace.sh,可以把正在运行的SQL记录到/data/traceProcess/processlist_${time}.log中)
#!/bin/sh
time=$(date +"%Y%m%d-%H%M%S")
echo "MySQL当前连接数:" >> /data/traceProcess/processlist_${time}.log
mysql -u root -ppassworld -e "SELECT COUNT(*) FROM sys.processlist;" >> /data/traceProcess/processlist_${time}.log
echo "MySQL当前执行的SQL" >> /data/traceProcess/processlist_${time}.log
mysql -u root -ppassworld -e "SHOW FULL PROCESSLIST" | grep -vi "sleep" >> /data/traceProcess/processlist_${time}.log
定位到慢SQL(如果MySQL开启慢SQL过滤的或,慢查询日志中也会记录相关的慢查询语句),使用explain看sql的执行计划、是否走索引、查询时间、记录数等。分析慢的原因,然后进行优化(创建合适的索引,改变慢sql中的筛选条件顺序)。
像这种情况,解决了慢查询,mysql的负载就会降下来了。