MySQL中SHOW PROCESSLIST協助故障診斷全過程的示例分析

小編給大家分享一下MySQL中SHOW PROCESSLIST協助故障診斷全過程的示例分析，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

一、SHOW PROCESSLIST命令

SHOW PROCESSLIST顯示正在運行（用戶運行線程）的線程（或SHOW FULL PROCESSLIST顯示更多信息）。您還可以從INFORMATION_SCHEMA PROCESSLIST表或mysqladmin processlist命令獲取此信息。如果你有這個PROCESS特權，你可以看到所有的線程。否則，您只能看到自己的線程（即與您正在使用的MySQL帳戶相關聯的線程）。如果不使用該FULL關鍵字，則每個語句的前100個字符都將顯示在該Info字段中。

進程信息也可從performance_schema.threads表中獲得。但是，訪問threads不需要互斥鎖，對服務器性能影響最小。INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST由于需要互斥鎖而具有負面的性能后果。performance_schema.threads還顯示有關后臺線程，哪些信息在INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST中沒有，這意味著performance_schema.threads可以用來監視活動的其他線程信息源。

SHOW PROCESSLIST如果您收到“ 太多連接 ” 錯誤消息，并且想要了解發生了什么， 該聲明非常有用。MySQL保留一個額外的連接以供有權限的帳戶使用SUPER，以確保管理員始終能夠連接和檢查系統（假設您沒有向所有用戶授予此權限）。

線程可以用KILL語句殺死。

以下是SHOW PROCESSLIST輸出示例：

mysql> show full processlist\G
*************************** 1. row ***************************
  Id: 123
 User: root
 Host: localhost
  db: performance_schema
Command: Query
 Time: 0
 State: starting
 Info: show full processlist
1 row in set (0.00 sec)

所產生的列SHOW PROCESSLIST具有以下含義：

Id

連接標識符，這是同一類型的在所顯示的值ID的列INFORMATION_SCHEMA.PROCESSLIST表，該PROCESSLIST_ID性能視圖的列threads 表，并且通過返回的 CONNECTION_ID()功能。

User

發出聲明的MySQL用戶，如果是system user，它是指由服務器產生的非客戶線程，以在內部處理任務。這可能是復制從站或延遲行處理程序使用的I / O或SQL線程。unauthenticated user指的是已經與客戶端連接關聯但是還沒有完成客戶機用戶的認證的線程。 event_scheduler指的是監視預定事件的線程。如果是system user那么在Host列中不會指定主機 。

Host

發出該語句的客戶端的主機名（system user沒有主機除外），以便更容易地確定哪個客戶端正在做什么，顯示方式：host_name:client_port。

db

當前執行語句對應的默認數據庫，如果選擇了；否則為NULL。

Command

顯示這個線程此刻正在執行的命令，一般對應DDL或DML語句。

Time

表示線程處于當前狀態的時間長短，線程當前時間的概念在某些情況下可能會發生改變：線程可以改變時間。對于正在從主機處理事件的從站上運行的線程，線程時間設置為事件中發現的時間，因此反映了主站而不是從站的當前時間。SET TIMESTAMP = value。

State

對應Command指令，大多數狀態對應于非常快速的操作。如果線程在給定狀態下保持多秒，則可能存在需要調查的問題。

Info

包含由線程執行的語句的文本或者NULL，如果它不是執行的話。默認情況下，此值僅包含語句的前100個字符。要查看完整的語句，請使用SHOW FULL PROCESSLIST。

二、線程命令（Command）值

線程可以具有以下任何 Command值：

Binlog Dump：這是主服務器上的線程，用于將二進制日志內容發送到從服務器。

Table Dump：線程將表內容發送到從服務器。

Change user：線程正在執行改變用戶操作。

Close stmt：線程正在關閉準備好的語句。

Connect：復制中，從服務器連接到其主服務器。

Connect Out：復制中，從服務器正在連接到其主服務器。

Create DB：線程正在執行create-database操作。

Daemon：此線程在服務器內部，而不是服務客戶端連接的線程。

Debug：線程正在生成調試信息。

Delayed insert：線程是一個延遲插入處理程序。

Drop DB：線程正在執行drop-database操作。

Execute：線程正在執行一個準備好的語句（prepare statement類型就是預編譯的語句，JDBC支持次類型執行SQL）。

Fetch：線程正在執行一個準備語句的結果。

Field List：線程正在檢索表列的信息。

Init DB：線程正在選擇默認數據庫。

Kill：線程正在殺死另一個線程。

Long Data：該線程在執行一個準備語句的結果中檢索長數據。

Ping：線程正在處理服務器ping請求。

Prepare：線程正在為語句生成執行計劃。

Processlist：線程正在生成有關服務器線程的信息。

Query：該線程正在執行一個語句。

Quit：線程正在終止。

Refresh：線程是刷新表，日志或緩存，或重置狀態變量或復制服務器信息。

Register Slave：線程正在注冊從服務器。

Reset stmt：線程正在重置一個準備好的語句。

Set option：線程正在設置或重置客戶端語句執行選項。

Shutdown：線程正在關閉服務器。

Sleep：線程正在等待客戶端向其發送新的語句。

Statistics：線程正在生成服務器狀態信息。

Time：沒用過。

三、線程狀態（State）值

• 一般線程狀態（State）值

以下列表描述State 了與常規查詢處理關聯的線程值，而不是更復雜的活動，例如復制。其中許多僅用于在服務器中查找錯誤。

After create：當線程創建表（包括內部臨時表）時，會在創建表的函數的末尾創建。即使由于某些錯誤而無法創建表，也會使用此狀態。

Analyzing：線程正在計算MyISAM表密鑰分布（例如:for ANALYZE TABLE）。

checking permissions：線程正在檢查服務器是否具有執行語句所需的權限。

Checking table：線程正在執行表檢查操作。

cleaning up：線程已經處理了一個命令，正在準備釋放內存并重置某些狀態變量。

closing tables：線程將更改的表數據刷新到磁盤并關閉已用表。這應該是一個快速的操作。如果沒有，請驗證您是否沒有完整的磁盤，并且磁盤沒有被非常大的使用。

copy to tmp table：線程正在處理ALTER TABLE語句。此狀態發生在已創建新結構的表之后，但是將行復制到該表之前。對于此狀態的線程，可以使用性能模式來獲取有關復制操作的進度。

Copying to group table：如果語句具有不同ORDER BY和GROUP BY標準，各行按組排列和復制到一個臨時表。

Creating index：線程正在處理ALTER TABLE … ENABLE KEYS一個MyISAM表。

Creating sort index：線程正在處理一個SELECT使用內部臨時表解析的線程 。

creating table：線程正在創建一個表，這包括創建臨時表。

committing alter table to storage engine：服務器已經完成就位ALTER TABLE并提交結果。

deleting from main table：服務器正在執行多表刪除的第一部分，它僅從第一個表中刪除，并從其他（引用）表中保存要用于刪除的列和偏移量。

deleting from reference tables：服務器正在執行多表刪除的第二部分，并從其他表中刪除匹配的行。

discard_or_import_tablespace：線程正在處理ALTER TABLE … DISCARD TABLESPACE或ALTER TABLE … IMPORT TABLESPACE聲明。

end：這發生在結束，但的清理之前ALTER TABLE， CREATE VIEW， DELETE， INSERT， SELECT，或UPDATE語句。

executing：該線程已經開始執行一個語句。

Execution of init_command：線程正在init_command系統變量的值中執行語句 。

freeing items：線程已經執行了一個命令，在這種狀態下完成的項目的一些釋放涉及查詢緩存，這個狀態通常在后面cleaning up。

FULLTEXT initialization：服務器正在準備執行自然語言全文搜索。

init：此操作在初始化ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE之前發生，服務器在該狀態中采取的操作包括刷新二進制日志、Innodb日志和一些查詢緩存清理操作。對于最終狀態, 可能會發生以下操作：更改表中的數據后刪除查詢緩存項、將事件寫入二進制日志、釋放內存緩沖區, 包括blob。

Killed：執行KILL語句，向線程發送了一個聲明，下次檢查kill標志時應該中斷。在MySQL的每個主循環中檢查該標志，但在某些情況下，線程可能需要很短時間才能死掉。如果線程被某個其他線程鎖定，則一旦其他線程釋放鎖定，該kill就會生效。

Locking system tables：線程正在嘗試鎖定系統表（例如，時區或日志表）。

login：連接線程的初始狀態，直到客戶端成功認證為止。

manage keys：服務器啟用或禁用表索引。

NULL：該狀態用于SHOW PROCESSLIST狀態。

Opening system tables：線程嘗試打開系統表（例如，時區或日志表）。

Opening tables：線程正在嘗試打開一個表，這應該是非常快的程序，除非有事情阻止打開。例如，一個ALTER TABLE或一個LOCK TABLE語句可以阻止打開一個表，直到語句完成。還可能需要關注table_open_cache參數的值是否足夠大。對于系統表，使用Opening system tables狀態。

optimizing：服務器正在執行查詢的初始優化。

preparing：此狀態發生在查詢優化期間。

Purging old relay logs：線程正在刪除不需要的中繼日志文件。

query end：處理查詢之后，freeing items狀態之前會發生這種狀態。

Removing duplicates：該查詢的使用SELECT DISTINCT方式使得MySQL不能在早期階段優化不同的操作。因此，MySQL需要一個額外的階段來刪除所有重復的行，然后將結果發送給客戶端。

removing tmp table：處理語句后，該線程正在刪除一個內部臨時表SELECT 。如果沒有創建臨時表，則不使用該狀態。

rename：線程正在重命名一個表。

rename result table：線程正在處理一個ALTER TABLE語句，已經創建了新表，并重新命名它來替換原始表。

Reopen tables：線程獲得了表的鎖，但在獲得基礎表結構更改的鎖之后注意到。它釋放了鎖，關閉了table，并試圖重新打開它。

Repair by sorting：修復代碼正在使用排序來創建索引。

preparing for alter table：服務器正在準備就地執行ALTER TABLE。

Repair done：線程已經完成了一個MyISAM表的多線程修復 。

Repair with keycache：修復代碼通過密鑰緩存逐個使用創建密鑰，這比慢得多Repair by sorting。

Rolling back：線程正在回滾事務。

Saving state：對于MyISAM表操作（如修復或分析），線程將新的表狀態保存到.MYI文件頭。狀態包括行數， AUTO_INCREMENT計數器和鍵分布等信息。

Searching rows for update：線程正在進行第一階段，以便在更新之前查找所有匹配的行。如果UPDATE要更改用于查找涉及的行的索引，則必須執行此操作 。

setup：線程正在開始一個ALTER TABLE操作。

Sorting for group：線程正在做一個滿足一個GROUP BY。

Sorting for order：線程正在做一個滿足一個ORDER BY。

Sorting index：線程是排序索引頁，以便在MyISAM表優化操作期間更有效地訪問。

Sorting result：對于一個SELECT語句，這類似于Creating sort index，但是對于非臨時表。

statistics：服務器正在計算統計信息以開發查詢執行計劃。如果一個線程長時間處于這種狀態，服務器可能是磁盤綁定的，執行其他工作。

update：線程正在準備開始更新表。

Updating：線程正在搜索要更新的行并正在更新它們。

updating main table：服務器正在執行多表更新的第一部分，它僅更新第一個表，并保存用于更新其他（引用）表的列和偏移量。

updating reference tables：服務器正在執行多表更新的第二部分，并從其他表更新匹配的行。

User lock：線程將要求或正在等待通過GET_LOCK()呼叫請求的咨詢鎖定 。因為 SHOW PROFILE，這個狀態意味著線程正在請求鎖定（不等待它）。

User sleep：線程調用了一個 SLEEP()調用。

• 故障診斷狀態（State）值（個人提取）

logging slow query：線程正在向慢查詢日志寫入語句。

altering table：服務器正在執行就地ALTER TABLE。

Receiving from client：服務器正在從客戶端讀取數據包。

Copying to tmp table：服務器正在復制磁盤到內存的臨時表，是直接在磁盤創建的臨時表而并非從內存轉到磁盤的臨時表。

Copying to tmp table on disk：對于線程將臨時表從內存中更改為基于磁盤的格式存儲以節省內存后，又把臨時表從磁盤復制到內存時的狀態。

Creating tmp table：線程正在內存或磁盤上創建臨時表。如果表在內存中創建，但后來轉換為磁盤表，則該操作中的狀態將為Copying to tmp table on disk。

Sending data：線程正在讀取和處理SELECT語句的行，并將數據發送到客戶端。由于在此狀態期間發生的操作往往執行大量的磁盤訪問（讀取），所以在給定查詢的整個生命周期內通常是最長的運行狀態。

Sending to client：服務器正在向客戶端寫入數據包。

Waiting for commit lock：FLUSH TABLES WITH READ LOCK正在等待提交鎖。

Waiting for global read lock：FLUSH TABLES WITH READ LOCK正在等待全局讀鎖定或read_only正在設置全局系統變量。

Waiting for tables：線程得到一個通知，表格的底層結構已經改變，需要重新打開表以獲得新的結構。但是，要重新打開表格，必須等到所有其他線程都關閉該表。如果另一個線程已使用FLUSH TABLES或下面的語句之一：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或OPTIMIZE TABLE都會發生通知。

Waiting for table flush：線程正在執行FLUSH TABLES并正在等待所有線程關閉它們的表，或者線程得到一個通知，表中的底層結構已經改變，并且需要重新打開表以獲得新的結構。但是，要重新打開表，必須等到所有其他線程都關閉該表。如果另一個線程已使用FLUSH TABLES或下面的語句之一：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或OPTIMIZE TABLE都會發出這個通知。

Waiting for lock_type lock：服務器正在等待THR_LOCK從元數據鎖定子系統獲取鎖或鎖，其中lock_type指示鎖的類型。THR_LOCK狀態表示：Waiting for table level lock；這些狀態表示等待元數據鎖定：Waiting for event metadata lock、Waiting for global read lock、Waiting for schema metadata lock、Waiting for stored function metadata lock、Waiting for stored procedure metadata lock、Waiting for table metadata lock、Waiting for trigger metadata lock。

Writing to net：服務器正在將數據包寫入網絡，如果一個線程長時間在執行并且一直處于Writing to net狀態，那么一直在發送數據包到網絡，可以試著調整max_allowed_packet大小。另外，這可能會導致其他線程大量阻塞。

Waiting on cond：線程等待條件成為true的一般狀態，沒有特定的狀態信息可用。

System lock：線程已經調用mysql_lock_tables() ，且線程狀態從未更新。這是一個非常普遍的狀態，可能由于許多原因而發生。例如, 線程將請求或正在等待表的內部或外部系統鎖。當InnoDB在執行鎖表時等待表級鎖時, 可能會發生這種情況。如果此狀態是由于請求外部鎖而導致的，并且不使用正在訪問相同表的多個mysqld服務器MyISAM，則可以使用該–skip-external-locking選項禁用外部系統鎖 。但是，默認情況下禁用外部鎖定，因此這個選項很有可能不起作用。因為SHOW PROFILE，這個狀態意味著線程正在請求鎖定（不等待它）。對于系統表，使用Locking system tables狀態。

• 查詢緩存狀態（State）值

checking privileges on cached query：服務器正在檢查用戶是否具有訪問緩存查詢結果的權限。

checking query cache for query：服務器正在檢查當前查詢是否存在于查詢緩存中。

invalidating query cache entries：查詢緩存條目被標記為無效，因為底層表已更改。

sending cached result to client：服務器正在從查詢緩存中獲取查詢的結果，并將其發送給客戶端。

storing result in query cache：服務器將查詢結果存儲在查詢緩存中。

Waiting for query cache lock：當會話正在等待采取查詢緩存鎖定時，會發生此狀態。這種情況可能需要執行一些查詢緩存操作，如使查詢緩存無效的INSERT或DELETE語句，以及RESET QUERY CACHE等等。

• 事件調度器線程狀態（State）值

這些狀態適用于事件調度程序線程，創建用于執行調度事件的線程或終止調度程序的線程。

Clearing

調度程序線程或正在執行事件的線程正在終止，即將結束。

Initialized

調度程序線程或將執行事件的線程已初始化。

Waiting for next activation

調度程序具有非空事件隊列，但下一次激活是將來。

Waiting for scheduler to stop

線程發出SET GLOBAL event_scheduler=OFF并正在等待調度程序停止。

Waiting on empty queue

調度程序的事件隊列是空的，它正在休眠。

• 其他

除了上述幾類，還有如復制主線程狀態（State）值、復制從庫IO線程狀態（State）值、復制從庫SQL線程（State）值、復制從庫Connect線程（State）值、詳情可見：MySQL主從復制線程狀態轉變

看完了這篇文章，相信你對“MySQL中SHOW PROCESSLIST協助故障診斷全過程的示例分析”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！