MySQL 之 show processlist 神器

dapideng發表於2019-11-06
MySql

今天在同步測試資料時，網突然斷了，等到重連之後，發現表打不開了。

可以看到表的資料長度已有112192kb，可惜打不開了。

打不開，就準備刪掉重來。

事情往往沒這麼簡單，果然刪不掉，truncate也不行，然後navicat卡死，遂登上資料庫，執行dorp操作，還是不行。

估計是網路錯誤，導致了一些奇怪的事情發生。

那麼就一起看看，到底發生了什麼吧。

神器登場。

show full processlist;

show full processlist 返回的結果是實時變化的，是對mysql連結執行的現場快照，所以用來處理突發事件非常有用。

這個sql，一般就是充當救火隊員的角色，解決一些突發性的問題。

它可以檢視當前mysql的一些執行情況，是否有壓力，都在執行什麼sql，語句耗時幾何，有沒有慢sql在執行等等。

當發現一些執行時間很長的sql時，就需要多注意一下了，必要時kill掉，先解決問題。

命令有三種執行方式：

1、這種是直接在命令列查詢，末尾帶\G是表示將查詢結果進行按列列印，可以使每個欄位列印到單獨的行。

mysql> show full processlist;
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
| Id     | User | Host                 | db    | Command | Time | State    | Info                  |
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
| 449000 | root | 127.123.213.11:59828 | stark | Sleep   | 1270 |          | NULL                  |
| 449001 | root | 127.123.213.11:59900 | stark | Sleep   | 1241 |          | NULL                  |
| 449002 | root | 127.123.213.11:59958 | stark | Sleep   | 1216 |          | NULL                  |
| 449003 | root | 127.123.213.11:60088 | stark | Sleep   | 1159 |          | NULL                  |
| 449004 | root | 127.123.213.11:60108 | stark | Sleep   | 1151 |          | NULL                  |
| 449005 | root | 127.123.213.11:60280 | stark | Sleep   | 1076 |          | NULL                  |
| 449006 | root | 127.123.213.11:60286 | stark | Sleep   | 1074 |          | NULL                  |
| 449007 | root | 127.123.213.11:60344 | stark | Sleep   | 1052 |          | NULL                  |
| 449008 | root | 127.123.213.11:60450 | stark | Sleep   | 1005 |          | NULL                  |
| 449009 | root | 127.123.213.11:60498 | stark | Sleep   |  986 |          | NULL                  |
| 449013 | root | localhost            | NULL  | Query   |    0 | starting | show full processlist |
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
11 rows in set (0.01 sec)

mysql> show full processlist\G;
*************************** 1. row ***************************
     Id: 449000
   User: root
   Host: 127.123.213.11:59828
     db: stark
Command: Sleep
   Time: 1283
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 449001
   User: root
   Host: 127.123.213.11:59900
     db: stark
Command: Sleep
   Time: 1254
  State: 
   Info: NULL

2、通過查詢連結執行緒相關的表來檢視快照

SELECT id, db, USER, HOST, command, time, state, info FROM information_schema. PROCESSLIST WHERE command != 'Sleep' ORDER BY time DESC;

3、通過navicat中的【工具】=> 【伺服器監控】進行檢視。

這種方式比較方便，還可以排序。

簡單介紹一下，每列的含義：

  • Id：連結mysql 伺服器執行緒的唯一標識，可以通過kill來終止此執行緒的連結。

  • User：當前執行緒連結資料庫的使用者

  • Host：顯示這個語句是從哪個ip 的哪個埠上發出的。可用來追蹤出問題語句的使用者

  • db: 執行緒連結的資料庫，如果沒有則為null

  • Command: 顯示當前連線的執行的命令，一般就是休眠或空閒（sleep），查詢（query），連線（connect）

  • Time: 執行緒處在當前狀態的時間，單位是秒

  • State：顯示使用當前連線的sql語句的狀態，很重要的列，後續會有所有的狀態的描述，請注意，state只是語句執行中的某一個狀態，一個 sql語句，已查詢為例，可能需要經過copying to tmp table，Sorting result，Sending data等狀態才可以完成

  • Info: 執行緒執行的sql語句，如果沒有語句執行則為null。這個語句可以使客戶端發來的執行語句也可以是內部執行的語句

發現問題之後怎樣解決它呢？

1、可以單獨kill掉上面有問題的行

kill 449000

2、也可以批量結束時間超過3分鐘的執行緒

-- 查詢執行時間超過3分鐘的執行緒，然後拼接成 kill 語句
select concat('kill ', id, ';')
from information_schema.processlist
where command != 'Sleep'
and time > 3*60
order by time desc;

當然問題到這，一般都能解決了，但是本次在show processlist過程中，只是看到了前面的truncate和drop操作，把這兩個執行緒kill了，也沒啥用。。。。

當然上面這些不是廢話昂，這就是類似方法論的東西，就像【中國機長】裡面，遇到飛行事故時，首先按照手冊，檢查一遍，排查原因，解決問題。

繼續

緊接著，又用navicat執行了修復表操作，結果返回了Waiting for table metadata lock

當MySQL在進行一些alter table等DDL操作時，如果該表上有未提交的事務則會出現 Waiting for table metadata lock，而一旦出現metadata lock，該表上的後續操作都會被阻塞。

解決辦法：

1、從 information_schema.innodb_trx 表中檢視當前未提交的事務

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx\G

欄位意義：

  • trx_state: 事務狀態，一般為RUNNING
  • trx_started: 事務執行的起始時間，若時間較長，則要分析該事務是否合理
  • trx_mysql_thread_id: MySQL的執行緒ID，用於kill
  • trx_query: 事務中的sql

一般只要kill掉這些執行緒，DDL操作就不會Waiting for table metadata lock。

2、調整鎖超時閾值

lock_wait_timeout 表示獲取metadata lock的超時（單位為秒），允許的值範圍為1到31536000（1年）。 預設值為31536000。

詳見 https://dev.mysql.com/doc/refman/5.6/en/se...

預設值為一年。。。。

將其調整為30分鐘

  • set session lock_wait_timeout = 1800;
  • set global lock_wait_timeout = 1800;

好讓出現該問題時快速失敗（failfast）。

All done

本作品採用《CC 協議》，轉載必須註明作者和本文連結

本文由telami 創作，採用CC BY 3.0 CN協議 進行許可，可自由轉載、引用，但需署名作者且註明。

相關文章