MySQL占用CPU100%解决一例

376次阅读

共计 4326 个字符，预计需要花费 11 分钟才能阅读完成。

一位同事负责的一套 MySQL 数据库的 CPU 使用率达到 100%，登上服务器 top 查看

Cpu(s): 95.9%us, 4.0%sy, 0.0%ni, 0.0%id, 20.0%wa, 0.0%hi, 0.0%si, 0.0%st

由于一直是在做 Oracle 的维护，对 MySQL 不熟悉，先在网上查了几篇文章，但好像都是一个人写的，不过从这些文章中也有了一个大体的处理思路。(由于不是我本人操作，没有记录详细的日志，这里只介绍下处理过程和使用到的命令，正文中的代码只是后期为说明加的。)

先使用 root 用户登上 mysql，使用 show processlist 命令查看当前哪些线程正在运行。查看下来一共有 160 多个

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  1 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

先简单说一下各列的含义和用途：

id 一个标识，你要 kill 一个语句的时候很有用。

user 显示当前用户，如果不是 root，这个命令就只显示你权限范围内的 sql 句。

host 显示这个语句是从哪个 ip 哪个端口上发出的。可以用来追踪出问题语句的用户。

db 显示这个进程目前连接的是哪个数据库。

command 显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。

time 此这个状态持续的时间，单位是秒。

state 显示使用当前连接的 sql 语句的状态，很重要的列。state 只是语句执行中的某一个状态，以查询 sql 为例，可能需要经过 copying to tmp table，Sorting result，Sending data 等状态才可以完成。

info 显示这个 sql 语句，因为长度有限，所以长的 sql 语句就显示不全，但是一个判断问题语句的重要依据。

state 列各种状态参考文档：http://blog.csdn.net/e421083458/article/details/38342051

从 show processlist 命令输出的结果看到有一条 sql 语句重复出现，但是 info 列显示的不全只有 select a.col1,a.col2,a.col3 from table1 a 这样的信息。那就先从这个表入手查，select count(*) from table1; 查出这张表有 60W+ 的数据。select count(*) 使用了 6 秒。但是现在不确定这个语句执行的时候有没有 where 条件。

继续查资料，查到 show processlist 命令出的结果出处 information_schema 库下的 processlist 表。

mysql> use information_schema
Database changed
mysql> desc processlist;
+---------+---------------------+------+-----+---------+-------+
| Field   | Type                | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| ID      | bigint(21) unsigned | NO   |     | 0       |       |
| USER    | varchar(16)         | NO   |     |         |       |
| HOST    | varchar(64)         | NO   |     |         |       |
| DB      | varchar(64)         | YES  |     | NULL    |       |
| COMMAND | varchar(16)         | NO   |     |         |       |
| TIME    | int(7)              | NO   |     | 0       |       |
| STATE   | varchar(64)         | YES  |     | NULL    |       |
| INFO    | longtext            | YES  |     | NULL    |       |
+---------+---------------------+------+-----+---------+-------+
8 rows in set (0.04 sec)

可看到表 porcesslist 表的列跟 show processlist 输出的列是一致的。

再查询 processlist 表时发现 info 信息是完整的，在这里找到上边怀疑的 sql 的完整版为 select a.col1,a.col2,a.col3 from table1 a where a.col4=’123′ and a.col5=’abc’;

查看这个语句的执行计划 (类似下面这种)

mysql> explain select ename,hiredate,sal from emp where sal=1000 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3072
        Extra: Using where
1 row in set (0.00 sec)
 
ERROR: 
No query specified

可以看出语句没有使用索引而是使用全表扫描。分别对 col4=’123′ 和 col5=’abc’ 做了统计，发现 col4=’123’ 的记录只有一条，而 col5=’abc’ 的记录有 5W+ 条，很明显在 col4 上创建索引执行效率会高很多。查看表上是否有 col4 列上的索引 (类似下面这种)。

mysql> show index from emp \G;
*************************** 1. row ***************************
        Table: emp
   Non_unique: 1
     Key_name: idx_emp_2
 Seq_in_index: 1
  Column_name: deptno
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
 
ERROR: 
No query specified

发现 col4 列上没有索引，表的存储引擎为 InnoDB，于是在 col4 列上创建索引

mysql> show table status from test1 like 'emp'\G;
*************************** 1. row ***************************
           Name: emp
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3072
 Avg_row_length: 53
    Data_length: 163840
Max_data_length: 0
   Index_length: 65536
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-11-15 21:54:49
    Update_time: NULL
     Check_time: NULL
      Collation: gbk_chinese_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
 
ERROR: 
No query specified
mysql> create index idx_sal on emp(sal);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次查看执行计划，发现语句使用索引扫描。

mysql> explain select ename,hiredate,sal from emp where sal=1000 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
         type: ref
possible_keys: idx_sal
          key: idx_sal
      key_len: 6
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)
 
ERROR: 
No query specified