mysql异常占用资源排查

通过执行日志与连接信息排查

1. 查看是否开启日志记录

   mysql> show global variables like '%general%';
   +------------------+---------------+
   | Variable_name    | Value         |
   +------------------+---------------+
   | general_log      | OFF           |
   | general_log_file | localhost.log |
   +------------------+---------------+
   2 rows in set (0.03 sec)
   

2. 开启sql记录

   mysql> set global general_log=on;
   Query OK, 0 rows affected (0.00 sec)
   

3. 查看日志位置观察异常sql

   mysql> show global variables like '%general%';
   +------------------+---------------+
   | Variable_name    | Value         |
   +------------------+---------------+
   | general_log      | ON            |
   | general_log_file | localhost.log |
   +------------------+---------------+
   

4. 查看当前连接数

   mysql> SHOW STATUS LIKE 'Threads_connected';
   +-------------------+-------+
   | Variable_name     | Value |
   +-------------------+-------+
   | Threads_connected | 2     |
   +-------------------+-------+
   1 row in set (0.04 sec)
   

5. 查看哪些客户端连接到了mysql

   mysql> SHOW PROCESSLIST;
   +-------+-------------+----------------------+------------------+---------+------+--------------------------+------------------+----------+
   | Id    | User        | Host                 | db               | Command | Time | State                    | Info             | Progress |
   +-------+-------------+----------------------+------------------+---------+------+--------------------------+------------------+----------+
   |     1 | system user |                      | NULL             | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
   |     2 | system user |                      | NULL             | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
   |     3 | system user |                      | NULL             | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
   |     4 | system user |                      | NULL             | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
   |     5 | system user |                      | NULL             | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
   | 98821 | root        | 192.168.2.42:53790   | test_cloud       | Sleep   |    2 |                          | NULL             |    0.000 |
   | 98824 | root        | 192.168.2.14:64112   | NULL             | Query   |    0 | Init                     | SHOW PROCESSLIST |    0.000 |
   +-------+-------------+----------------------+------------------+---------+------+--------------------------+------------------+----------+
   7 rows in set (0.05 sec)
   

通过慢sql信息排查

1. 查看是否开启慢sql记录

   mysql> show global variables like '%slow_query_log%';
   +---------------------+--------------------+
   | Variable_name       | Value              |
   +---------------------+--------------------+
   | slow_query_log      | OFF                |
   | slow_query_log_file | localhost-slow.log |
   +---------------------+--------------------+
   2 rows in set (0.05 sec)
   

2. 开启慢sql记录

   mysql> set global slow_query_log=1;
   Query OK, 0 rows affected (0.00 sec)mysql> show global variables like '%slow_query_log%';
   +---------------------+--------------------+
   | Variable_name       | Value              |
   +---------------------+--------------------+
   | slow_query_log      | ON                 |
   | slow_query_log_file | localhost-slow.log |
   +---------------------+--------------------+
   2 rows in set (0.05 sec)
   

3. 关闭慢sql记录

   mysql> set global slow_query_log=0;
   Query OK, 0 rows affected (0.00 sec)mysql> show global variables like '%slow_query_log%';
   +---------------------+--------------------+
   | Variable_name       | Value              |
   +---------------------+--------------------+
   | slow_query_log      | OFF                 |
   | slow_query_log_file | localhost-slow.log |
   +---------------------+--------------------+
   2 rows in set (0.05 sec)
   

修改slow_launch_time与 long_query_time

• slow_launch_time：定义一个客户端连接启动所花费的时间阈值。如果一个客户端连接的启动时间超过了这个阈值，MySQL 将认为这是一个慢连接启动，并将其记录到慢查询日志中。

• long_query_time：定义一个 SQL 查询执行所花费的时间阈值。如果一个 SQL 查询的执行时间超过了这个阈值，MySQL 将认为这是一个慢查询，并将其记录到慢查询日志中。

这两个参数都可以在 MySQL 的配置文件中进行设置，通常在 my.cnf 或 my.ini 文件中定义。例如：

slow_launch_time = 2
long_query_time = 1

也可以通过sql进行查看和修改：

1. 查看慢连接判定时间：

   mysql> show variables like 'slow_launch_time%';
   +------------------+-------+
   | Variable_name    | Value |
   +------------------+-------+
   | slow_launch_time | 2     |
   +------------------+-------+
   1 row in set (0.04 sec)
   

2. 修改慢连接判定时间：

   mysql> set global slow_launch_time=3;
   Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'slow_launch_time%';
   +------------------+-------+
   | Variable_name    | Value |
   +------------------+-------+
   | slow_launch_time | 3     |
   +------------------+-------+
   1 row in set (0.04 sec)
   

3. 查看慢sql判定时间

   mysql> show variables like 'long_query_time%';
   +-----------------+-----------+
   | Variable_name   | Value     |
   +-----------------+-----------+
   | long_query_time | 10.000000 |
   +-----------------+-----------+
   1 row in set (0.05 sec)
   

4. 修改慢sql判定时间

   mysql> set long_query_time=3;
   Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'long_query_time%';
   +-----------------+----------+
   | Variable_name   | Value    |
   +-----------------+----------+
   | long_query_time | 3.000000 |
   +-----------------+----------+
   1 row in set (0.04 sec)