【207】使用数据库时cpu持续飙升排查

内容目录（原文见公众号python宝）

一、top 看命令查看是否是数据库导致的二、分析语句，查看连接数三、explain 分析sql语句www.xmmup.com

一、top看命令查看是否是数据库导致的

1、通过top命令可以看到哪个进程的cpu是高的。

当 cpu持续飙升时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

show processlist;   # 命令可以查看数据库当前正在执行的线程


命令返回的具体参数比较多，这里主要注意state字段中如果包含大量的
Sending data、Waiting for tabls、或各种lock(锁)，
大概率就是因为这些SQL造成的数据库拥堵。


解决办法自然是优化对应的SQL，如希望更进一步，则需要开启查看慢查询日志。

2、然后通过top -Hp 进程id，可以查看这个进程对应的线程情况。

3、准备好jstack 进程id > /opt/test.log 命令。最好是新开窗口。再执行命令的那一刻，截图下top -Hp的线程情况，可以看到哪些线程当时是飙高的。

1. 执行“printf "%x\n 10"命令 ：后续查看线程堆栈信息展示的都是十六进制，为了找到咱们的线程堆栈信息，咱们需要把线程号转成16进制。
例如,printf "%x\n 10-》打印：a，那么在jstack中线程号就是0xa.


2. 执行 “jstack 进程号 | grep 线程ID” 查找某进程下-》线程ID（jstack堆栈信息中的nid）=0xa的线程堆栈信息。


3. 执行“jstat -gcutil 进程号 统计间隔毫秒 统计次数（缺省代表一致统计）”，
   查看某进程GC持续变化情况，如果发现返回中FGC很大且一直增大-》确认Full GC! 
   也可以使用“jmap -heap 进程ID”查看一下进程的堆内从是不是要溢出了，
   特别是老年代内从使用情况一般是达到阈值(具体看垃圾回收器和启动时配置的阈值)就会进程Full GC。


4 .执行“jmap -dump:format=b,file=filename 进程ID”，
   导出某进程下内存heap输出到文件中。可以通过eclipse的mat工具查看内存中有哪些对象比较多。

二、分析语句，查看连接数

文件句柄限制

在linux下编写网络服务器程序的朋友肯定都知道每一个tcp连接都要占一个文件描述符，一旦这个文件描述符使用完了，新的连接到来返回给我们的错误是“Socket/File:Can't open so many files”。这时你需要明白操作系统对可以打开的最大文件数的限制。

进程限制

执行 ulimit -n 输出 1024，说明对于一个进程而言最多只能打开1024个文件，所以你要采用此默认配置最多也就可以并发上千个TCP连接。临时修改：ulimit -n 1000000，但是这种临时修改只对当前登录用户目前的使用环境有效，系统重启或用户退出后就会失效。

重启后失效的修改（某些系统可能重启后并不会失效），编辑 /etc/security/limits.conf 文件，修改后内容为：

soft nofile 1000000

hard nofile 1000000

永久修改：编辑/etc/rc.local，在其后添加如下内容：

ulimit -SHn 1000000

全局限制

执行cat /proc/sys/fs/file-nr 输出 9344 0 592026，分别为：

1. 已经分配的文件句柄数，

2. 已经分配但没有使用的文件句柄数，

3. 最大文件句柄数。

但在kernel 2.6版本中第二项的值总为0，这并不是一个错误，它实际上意味着已经分配的文件描述符无一浪费的都已经被使用了。

我们可以把这个数值改大些，用 root 权限修改 /etc/sysctl.conf 文件:

fs.file-max = 1000000


net.ipv4.ip_conntrack_max = 1000000


net.ipv4.netfilter.ip_conntrack_max = 1000000

参考：https://baijiahao.baidu.com/s?id=1684478055634156491&wfr=spider&for=pc

三、explain分析sql语句

explain的列分析
id: 代表select 语句的编号, 如果是连接查询,表之间是平等关系, select 编号都是1,从1开始. 如果某select中有子查询,则编号递增.

select_type: 查询类型
 
table: 查询针对的表
有可能是
实际的表名  如select * from t1;
表的别名    如 select * from t2 as tmp;
derived      如from型子查询时
null         直接计算得结果,不用走表
possible_key: 可能用到的索引
注意: 系统估计可能用的几个索引,但最终,只能用1个.
key : 最终用的索引.
key_len: 使用的索引的最大长度
ref: 指连接查询时, 表之间的字段引用关系.
rows: 是指估计要扫描多少行.
extra: 利用到了哪些索引,可能的值有
（1）index: 是指用到了索引覆盖,效率非常高
（2）using where 是指光靠索引定位不了,还得where判断一下
（3）using temporary 是指用上了临时表, group by 与order by 不同列时,或group by ,order by 别的表的列.
(4) using filesort : 文件排序(文件可能在磁盘,也可能在内存)
type列: 是指查询的方式, 非常重要,是分析”查数据过程”的重要依据
可能的值
(1) all:  意味着从表的第1行,往后,逐行做全表扫描.,运气不好扫描到最后一行.
(2) index: 比all性能稍好一点，通俗的说: all 扫描所有的数据行,相当于data_all  index 扫描所有的索引节点,相当于index_all
(3) range: 意思是查询时,能根据索引做范围的扫描  如sql：
  explain select goods_id,goods_name,shop_price from  goods where goods_id >25 \G
(4) ref  意思是指 通过索引列,可以直接引用到某些数据行 如sql:
    
explain select goods_id,goods_name from  goods where cat_id=4 \G
 
(5) eq_ref 是指,通过索引列,直接引用某1行数据 常见于连接查询中
  explain select goods_id,shop_price from  goods innert join ecs_categoy using(cat_id) where goods_id> 25 \G
(6) const, system, null  这3个分别指查询优化到常量级别, 甚至不需要查找时间.
  一般按照主键来查询时,易出现const,system 或者直接查询某个表达式,不经过表时, 出现NULL
  explain select goods_id,goods_name,click_count from  goods wher_id=4 \G

可参考： https://www.cnblogs.com/lamp01/p/9818228.html