MySQL佔用CPU過高,排查原因及解決方案

2022-12-23 14:01:28

一、mysql中的wait_timeout坑

mysql> show variables like '%timeout%';

首先你要明白：

wait_timeout — 指的是mysql在關閉一個非互動的連線之前所要等待的秒數，其取值範圍為1-2147483(Windows)，1-31536000(linux)，預設值28800。
nteractive_time — 指的是mysql在關閉一個互動的連線之前所要等待的秒數(互動連線如mysql gui tool中的連線)，其取值範圍隨wait_timeout變動，預設值28800。

所謂的互動式連線，即在mysql_real_connect()函數中使用了CLIENT_INTERACTIVE選項。說得直白一點，通過mysql使用者端連線資料庫是互動式連線，通過jdbc連線資料庫是非互動式連線。

MySQL 的預設設定下，當一個連線的空閒時間超過8小時後，一到高峰期肯定會造成，會有太多的TCP連線沒關閉，資料庫連線數肯定是不夠。

從而會產生CPU佔用過高，伺服器告警等問題。因EPG的一個存取一次對資料庫操作量不大，查詢完資料就完成ok了，wait_timeout 設定在120s內就很完美了。

1、第一種修改方式需重啟MySQL(6.5為例)

1.1、修改引陣列態檔　　

vi /etc/my.cnf

[mysqld]
wait_timeout    = 90
interactive_timeout    =  90
#增加以上兩列即可，因為官方檔案要求修改此引數必須同時修改interactive_timeout

1.2、重啟資料庫　

service mysqld restart

或者

/etc/init.d/mysql restart

1.3、檢視資料庫引數是否修改成功

連線MySQL 然後檢視

show variables like ‘wait_timeout';

2、第二種修改方式不需重啟MySQL（資料庫重啟就失效）

mysql> show variables like '%timeout';

　　　　+-------------------------+-------+
　　　　| Variable_name           | Value |
　　　　+-------------------------+-------+
　　　　| connect_timeout         | 10    |
　　　　| delayed_insert_timeout  | 300   |
　　　　| interactive_timeout     | 200   |
　　　　| net_read_timeout        | 30    |
　　　　| net_write_timeout       | 60    |
　　　　| slave_net_timeout       | 3600  |
　　　　| table_lock_wait_timeout | 50    |
　　　　| wait_timeout            | 200   |
　　　　+-------------------------+-------+

　　mysql> set global interactive_timeout=100;

　　　　Query OK, 0 rows affected (0.00 sec)

退出後重新登入mysql

如果修改interactive_timeout的話wait_timeout也會跟著變，而只修改wait_timeout是不生效的。

二、排查有沒有地方佔用SQL資源過多

show databases;

3、use 資料庫名; #切換到常用資料庫

4、show full processlist; #顯示哪些執行緒正在執行

或者：

-- select * from information_schema.PROCESSLIST where info is not null;

說明各列的含義和用途

id列: 一個標識，你要kill 一個語句的時候很有用。
user列: 顯示當前使用者，如果不是root，這個命令就只顯示你許可權範圍內的sql語句。
host列: 顯示這個語句是從哪個ip 的哪個埠上發出的。可用來追蹤出問題語句的使用者。
db列: 顯示這個程序目前連線的是哪個資料庫。
command列: 顯示當前連線的執行的命令，一般就是休眠（sleep），查詢（query），連線（connect）。

通常代表資源未釋放，如果是通過連線池，sleep狀態應該恆定在一定數量範圍內

實戰範例：因前端資料輸出時（特別是輸出到使用者終端）未及時關閉資料庫連線，導致因網路連線速度產生大量sleep連線，在網速出現異常時，資料庫too many connections掛死。

簡單解讀，資料查詢和執行通常只需要不到0.01秒，而網路輸出通常需要1秒左右甚至更長，原本資料連線在0.01秒即可釋放，但是因為前端程式未執行close操作，直接輸出結果，那麼在結果未展現在使用者桌面前，該資料庫連線一直維持在sleep狀態！

time列: 此這個狀態持續的時間，單位是秒。
state列: 顯示使用當前連線的sql語句的狀態，很重要的列，後續會有所有的狀態的描述，請注意，state只是語句執行中的某一個狀態，一個sql語句，已查詢為例，可能需要經過copying to tmp table，Sorting result，Sending data等狀態才可以完成。
info列: 顯示這個sql語句，因為長度有限，所以長的sql語句就顯示不全，但是一個判斷問題語句的重要依據。

其中state的狀態十分關鍵

下表列出state主要狀態和描述：

狀態	描述
Checking table	正在檢查資料表（這是自動的）。
Closing tables	正在將表中修改的資料重新整理到磁碟中，同時正在關閉已經用完的表。這是一個很快的操作，如果不是這樣的話，就應該確認磁碟空間是否已經滿了或者磁碟是否正處於重負中。
Connect Out	複製從伺服器正在連線主伺服器。
Copying to tmp table on disk	由於臨時結果集大於tmp_table_size，正在將臨時表從記憶體儲存轉為磁碟儲存以此節省記憶體。
Creating tmp table	正在建立臨時表以存放部分查詢結果。
deleting from main table	伺服器正在執行多表刪除中的第一部分，剛刪除第一個表。
deleting from reference tables	伺服器正在執行多表刪除中的第二部分，正在刪除其他表的記錄。
Flushing tables	正在執行FLUSH TABLES，等待其他執行緒關閉資料表。
Killed	傳送了一個kill請求給某執行緒，那麼這個執行緒將會檢查kill標誌位，同時會放棄下一個kill請求。MySQL會在每次的主迴圈中檢查kill標誌位，不過有些情況下該執行緒可能會過一小段才能死掉。如果該執行緒程被其他執行緒鎖住了，那麼kill請求會在鎖釋放時馬上生效。

Locked	被其他查詢鎖住了。

Sending data	正在處理SELECT查詢的記錄，同時正在把結果傳送給使用者端。
Sorting for group	正在為GROUP BY做排序。
Sorting for order	正在為ORDER BY做排序。
Opening tables	這個過程應該會很快，除非受到其他因素的干擾。例如，在執ALTER TABLE或LOCK TABLE語句行完以前，資料表無法被其他執行緒開啟。正嘗試開啟一個表。
Removing duplicates	正在執行一個SELECT DISTINCT方式的查詢，但是MySQL無法在前一個階段優化掉那些重複的記錄。因此，MySQL需要再次去掉重複的記錄，然後再把結果傳送給使用者端。

Reopen table	獲得了對一個表的鎖，但是必須在表結構修改之後才能獲得這個鎖。已經釋放鎖，關閉資料表，正嘗試重新開啟資料表。
Repair by sorting	修復指令正在排序以建立索引。
Repair with keycache	修復指令正在利用索引快取一個一個地建立新索引。它會比Repair by sorting慢些。
Searching rows for update	正在講符合條件的記錄找出來以備更新。它必須在UPDATE要修改相關的記錄之前就完成了。
Sleeping	正在等待使用者端傳送新請求.
System lock	正在等待取得一個外部的系統鎖。如果當前沒有執行多個mysqld伺服器同時請求同一個表，那麼可以通過增加--skip-external-locking引數來禁止外部系統鎖。
Upgrading lock	INSERT DELAYED正在嘗試取得一個鎖表以插入新記錄。
Updating	正在搜尋匹配的記錄，並且修改它們。
User Lock	正在等待GET_LOCK()。
Waiting for tables	該執行緒得到通知，資料表結構已經被修改了，需要重新開啟資料表以取得新的結構。然後，為了能的重新開啟資料表，必須等到所有其他執行緒關閉這個表。以下幾種情況下會產生這個通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert	INSERT DELAYED已經處理完了所有待處理的插入操作，正在等待新的請求。