mysql異常占用資源排查

更新時間：2025年07月23日 10:39:59 作者：L-960

本文詳細介紹了通過MySQL的全局變量檢查日志狀態(tài),監(jiān)控連接數(shù)及啟用和調(diào)整慢查詢記錄,以幫助排查數(shù)據(jù)庫性能問題,感興趣的可以了解一下

通過執(zhí)行日志與連接信息排查

查看是否開啟日志記錄

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

開啟sql記錄

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

查看日志位置觀察異常sql

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

查看當(dāng)前連接數(shù)

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

查看哪些客戶端連接到了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信息排查

查看是否開啟慢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)

開啟慢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)

關(guān)閉慢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 將認為這是一個慢連接啟動，并將其記錄到慢查詢?nèi)罩局小?/p>
long_query_time：定義一個 SQL 查詢執(zhí)行所花費的時間閾值。如果一個 SQL 查詢的執(zhí)行時間超過了這個閾值，MySQL 將認為這是一個慢查詢，并將其記錄到慢查詢?nèi)罩局小?/p>

這兩個參數(shù)都可以在 MySQL 的配置文件中進行設(shè)置，通常在 my.cnf 或 my.ini 文件中定義。例如：

slow_launch_time = 2
long_query_time = 1

也可以通過sql進行查看和修改：

查看慢連接判定時間：

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

修改慢連接判定時間：

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)

查看慢sql判定時間

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

修改慢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)

到此這篇關(guān)于mysql異常占用資源排查的文章就介紹到這了,更多相關(guān)mysql異常占用資源排查內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: