首页 > 代码库 > mysql 占CPU100%处理一则

mysql 占CPU100%处理一则

2024-08-21 05:24:16   220人阅读

  今天下班前一位同事负责的一套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

sql语句的执行效率立马提升。CPU的使用率也降下来了。


这也还有一个疑问，oracle在创建索引时为了避免锁表引入了online创建索引。不知道mysql中如何在线创建索引？

参考文档:http://blog.csdn.net/wlzjsj/article/details/51537736

参考文档:http://www.jb51.net/article/75217.htm


本文出自 “DBA Fighting!” 博客，请务必保留此出处http://hbxztc.blog.51cto.com/1587495/1873265

mysql 占CPU100%处理一则


联系
我们