在最近的工作中,运维紧急告知我司的数据库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;