MySQL神器之show full processlist
- Guanhui轉載
- 2020-05-09 11:29:134648瀏覽
今天在同步測試資料時，網子突然斷了，等到重連之後，發現表打不開了。
可以看到表格的資料長度已有 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: NULL2、透過查詢連結執行緒相關的表格來查看快照
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_schesche.
#欄位意義：trx_state: 交易狀態，一般為RUNNINGtrx_started: 交易執行的起始時間，若時間較長，則要分析該交易是否合理trx_mysql_thread_id: MySQL 的線程ID，用於killtrx_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）。以上是MySQL神器之show full processlist的詳細內容。更多資訊請關注PHP中文網其他相關文章！