记录一次线上Mysql Cpu飙升问题

在最近的工作中，运维紧急告知我司的数据库CPU飙升到97%！ioutil的指数也在90%以上，这台服务器的配置是16核32g，2T的硬盘空间，而这个16核还是从上周的8核升上来的，按理来说不至于资源这么快用完了。于是开始漫长的分析之路。

-- 列出当前数据库总数据量
select sum(table_rows) from  tables;
-- 列出数据库各表数据量
select table_name,table_rows from tables order by table_rows desc;

 先看了下当前数据库的数据量，好家伙总量有四个多亿了

排查思路：

因为没有搭建数据库监视工具，所以按照老办法来解决了

因为这个库主要做数据的处理，不涉及到业务，定时任务较多，笔者在这个时候观测到有几个任务处理excel文件解析时，数据库从20%飙升到97%！，那么问题不多说，一定出在这里。

-- 列出当前数据库执行情况
show processlist;
-- 查看sql查询详情信息，各阶段查询耗时
set profiling=on;
show profiles;
show profile for query query_id;

-- 查看sql执行情况
select * from information_schema.PROCESSLIST where info is not null;
-- 查看sql执行情况
select * from information_schema.PROCESSLIST where info is not null;

通过上述语句帮助我们找出了在cpu飙升的情况下，出现的频率最多的sql​

这条语句经过分析查看显然走的是create_time的索引，而级别也是index，显然并没有走我们查询条件的索引。而同样的sql 这里将report_type的条件换一个类型也许结果并不相同，如下图可以看到type走的是索引合并，key字段也是选择了多个索引，key_len也比之前长的多。仅仅因为类型相同，索引的选择却不相同，究竟是什么原因导致的呢。

经过细查，在这张表总数据量大概600w，而未走索引合并的类型数据大概150w，而走了索引合并的类型数据量大概为80w，难道是因为数据量过大数据区分度不高导致未走索引？这里研究了一下未得出具体结论。

本着解决问题的原则，笔者先根据查询条件建了一个组合索引，下图可以看到，type变为了range（虽然比index好了一点），但是是走了具体的条件索引的，所以查询效率应该是大大增强了。 

于是笔者重新执行了一遍任务，grafana里可以看到cpu已经稳定在了30%左右！而IOUtil的指标一直居高，这个就后面再看吧。

后面挂上explain 字段的解读： 

顺便记录一下一些基本的mysql语句

-- 展示当前执行进程和语句
show PROCESSLIST;

-- 查看指定用户每小时查询次数
select user, max_questions from ${userName};

-- 查询执行过程
show master status;
set profiling=on;
show profiles;
show profile for query query_id;

-- 清除最近七天binlog日志
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);
kill 1190;
select DATE_SUB( NOW( ), INTERVAL 7 DAY);
show open tables where in_use > 0 ;

-- 查看当前数据库引擎死锁
SELECT * FROM information_schema.INNODB_TRX; 　
SELECT * FROM information_schema.INNODB_LOCKs; 　
SELECT * FROM information_schema.INNODB_LOCK_waits;


-- 设置用户权限，查看用户权限
SELECT * FROM mysql.user WHERE user='$P{userName}';
show grants for '${userName}'@'%'; 
GRANT ALL PRIVILEGES ON `xxxxxx`.* TO '${dbName}'@'%' WITH GRANT OPTION
show profiles;

-- 生成杀掉执行线程命令
SELECT concat('KILL ',id,';') 
FROM information_schema.processlist p 
INNER JOIN  information_schema.INNODB_TRX x 
ON p.id=x.trx_mysql_thread_id 
WHERE db='db_name';

-- 列出当前数据库总数据量
select sum(table_rows) from  tables;
-- 列出数据库各表数据量
select table_name,table_rows from tables order by table_rows desc;

-- 解析生产环境binlog
python /home/db-pro-02/binlog2sql/binlog2sql/binlog2sql.py -hip -P3306 -uroot -p'${password}' -dtest -t f --start-file='${binlogName}' --start-pos=4 --end-pos=1000000 -B

-- 查询master节点log日志记录
show master logs;