詳解MySQL線程狀態

文章目錄

• • 一、show processlist
  • 二、command命令類型
  • 三、用戶線程狀態
  • 四、dump線程狀態
  • 五、IO線程狀態
  • 六、SQL 線程狀態
  • 七、 主從連接線程狀態

  • 八、事件調度線程狀態

相關免費學習推薦：mysql視頻教程

一、show processlist

• Id：連接進程標識符。是由 CONNECTION_ID() 函數返回的值
• User：執行語句的 MySQL 用戶名稱。如果顯示的是“system user”，它指的是由MySQL生成的非客戶端線程正在執行內部任務。例如主備復制中從
  庫上使用的 I/O 或 SQL 線程或延遲行處理程序的線程。“unauthenticated user”指的是客戶端已經和服務端建立了 TCP/IP 連接但是還沒有對客戶端的用戶進行用戶密碼認證的線程。“event_scheduler”是指監視計劃任務調度事件的線程。
• Host：執行語句的客戶端的主機名，以 host_name：client_port顯示 (如果啟用了 skip_name_resolve 參數，則顯示為 ip:client_port 格式)
• Db：客戶端連接的默認數據庫（如果連接時指定了庫名），否則顯示為 NULL 。
• Command：線程正在執行的命令的類型。
• Time：線程處于當前狀態的時間數（以秒為單位）。對于從庫 SQL 線程，該值是最后復制事件的時間和從庫的實際時間之間的秒數。
• State：提示線程正在做什么樣的操作，事件或狀態。
• Info：線程正在執行的語句。

二、command命令類型

• Binlog Dump：主庫線程用于將二進制日志內容發送到從庫
• Change user：線程正在執行更改用戶操作
• Close stmt：線程正在關閉一個預編譯好的語句
• Connect：從庫線程已經連接到主庫
• Connect Out：從庫正在連接到主庫
• Create DB：線程正在執行一個建庫操作
• Daemon：這個是 server 內部線程，不是客戶端連接的線程
• Debug：線程正在生成調試信息
• Delayed insert：是一個延遲插入處理程序的線程
• Drop DB：線程正在執行 drop database 操作
• Execute：線程正在執行一個預編譯好的語句
• Fetch：線程正在執行語句并從中獲取結果集
• Field List：線程正在檢索表列的信息
• Init DB：線程正在選擇默認數據庫
• Kill：線程正在殺死其他線程
• Long Data：線程在執行語句并從中檢索并返回長字段（大字段）類型的數據結果集
• Ping：線程正在處理服務器 ping 請求
• Prepare：線程正在執行預編譯一個語句
• Processlist：線程正在生成有關 server 線程的信息
• Query：線程正在執行查詢語句
• Quit：線程正在終止
• Refresh：線程正在刷新表，日志或高速緩存，或重置狀態變量或復制 server 信息
• Register Slave：線程正在主庫上注冊從庫
• Reset stmt：線程正在重置預編譯語句
• Set option：線程正在設置或重置客戶端語句執行選項
• Shutdown：線程正在執行關閉 server
• Sleep：線程正在等待客戶端向其發送一個新語句請求
• Statistics：線程正在生成 server 狀態信息
• Table Dump：線程正在將表內容發送到從庫

三、用戶線程狀態

• After create：當線程創建一個表完成時（包括內部臨時表），會出現這種狀態。
  即使由于某些錯誤而導致創建表最終出錯，也會出現此狀態
• Analyzing：線程正在 ANALYZE TABLE
• checking permissions：正在 server 中檢查線程是否具有執行語句所需的權限
• Checking table：線程正在執行表檢查操作
• cleaning up：線程已經執行完成了一個命令，并準備釋放所占用的內存和重置某些狀態變量
• closing tables：線程正在將表發生更改的數據刷新到磁盤并關閉表。
• converting HEAP to MyISAM：線程正在將內部臨時表從 MEMORY 引擎表轉換為磁盤 MyISAM 引擎的臨時表
• copy to tmp table：線程正在執行 ALTER TABLE 語句。此狀態發生在新結構的表已經創建好之后，執行 copy 舊表數據到新表中之前出現
• Copying to group table：如果語句使用了不同的 ORDER BY 和 GROUP BY 條件列，則按照 group by 對這些行數據進行排序，并將排序結果復制到臨時表
• Copying to tmp table：server 正在復制數據到內存臨時表
• altering table：server 正在執行 in-place 的 ALTER TABLE 的過程
• Copying to tmp table on disk：server 正在復制數據到磁盤臨時表。因為臨時結果集太大，所以，線程正在將內存臨時表轉換為基于磁盤的臨時表，以節省內存
• Creating index：線程正在執行一個 ALTER TABLE … ENABLE KEYS 語句
• Creating sort index：線程正在執行 SELECT 且使用到了內部臨時表
• creating table：線程正在創建表。包括創建臨時表時也會使用此狀態
• Creating tmp table：線程正在內存或磁盤上創建一個臨時表。如果表在內存中創建，但后來被轉換為磁盤表，則該操作期間的狀態將為“Copying to tmp table on disk”
• committing alter table to storage engine：server 已執行完成 in-place 算法的 ALTER TABLE 語句，正在提交
• deleting from main table：server 正在執行多表刪除語句中的第一部分。看到這個
  狀態表示正在從第一個表中刪除，并保存后續用于刪除其他表的列數據和偏移量
• deleting from reference tables：server 正在執行多表刪除語句的第二部分，從其他表中刪除匹配的行
• 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：線程正在執行一個初始化系統變量的語句
• freeing items：線程已經執行完成了一個命令。釋放一些涉及到 query cache 狀態
  的 items。這種狀態后通常緊隨 cleaning up 狀態之后
• FULLTEXT initialization：server 正在準備執行自然語言全文搜索
• init：這在 ALTER TABLE，DELETE，INSERT，SELECT 或 UPDATE 語句初始化之前發生的狀態。server 在此狀態下執行的操作包括刷新二進制日志，InnoDB 日志和一些查詢緩存清理操作。對于這個狀態結束時，可能會有如下一些操作：
  當表中的數據更改后刪除查詢緩存條目
  將事件寫入二進制日志
  釋放內存緩沖區，包括 blob
• Killed：向線程發起一個 kill 操作，線程應該執行終止操作。在 MySQL 的每個主循環中檢查線程的 kill 標志，但在某些情況下，殺死線程可能只需要很短的時間。但如果被 kill 的線程被其他線程鎖定，則需要等待其他線程釋放鎖之后，kill 命令才會生效并執行。
• logging slow query：線程正在向慢查詢日志寫一條語句
• login：連接線程的初始狀態，直到客戶端成功通過身份驗證
• manage keys：server 正在啟用或禁用表索引
• NULL：此狀態用于 SHOW PROCESSLIST 語句
• Opening tables：線程正嘗試打開一個表。打開表操作應該非常快，除非打開操作被阻止。例如，ALTER TABLE 或 LOCK TABLE 語句可以防止打開表，直到該語句完成。另外也可能是 table_open_cache 不夠大導致不能打開表。
• optimizing：server 正在對查詢執行初始優化
• preparing：此狀態發生在查詢優化期間
• Purging old relay logs：線程正在刪除不需要的中繼日志文件
• query end：此狀態出現在執行查詢語句之后但在釋放該查詢語句相關狀態 items 之前
• Reading from net：server 正在從網絡讀取數據包。在 MySQL 5.7.8 之后該狀態叫做“Receiving from client” – Receiving from client：server 正在從客戶端讀取數據包。在 MySQL 5.7.8 叫做“Reading from net”
• Removing duplicates：查詢使用 SELECT DISTINCT 語句時，使 MySQL 無法在早期階段優化掉 distinct 操作。因此，MySQL 需要一個額外的階段來刪除所有重復的行，然后將結果發送到客戶端
• removing tmp table：線程在 SELECT 語句執行完成后，正在刪除內部臨時表。如果 SELECT 語句未創建臨時表，則不會出現此狀態
• rename：線程正在執行 rename 語句重命名表
• rename result table：線程正在執行 ALTER TABLE 語句重命名表，已經創建完成新表，并正在使用新表替換舊表名稱
• Reopen tables：線程獲得了表鎖，但是獲得鎖后，發現基礎表結構已經被改變了。
  于是釋放表鎖，并關閉表，嘗試重新打開表
• Repair by sorting：修復代碼正在使用排序來創建索引
• preparing for alter table：server 正在準備執行 in-place 算法的 ALTER TABLE 語 句 – Repair done：該線程已完成 MyISAM 表的多線程修復
• Repair with keycache：修復代碼正在使用通過 key cache 逐個創建 key 的方法修復索引。這比通過排序索引修復的方法慢得多
• Rolling back：線程正在回滾事務
• Saving state：對于 MyISAM 表操作（如修復或分析），線程正在將新表狀態保存到.MYI 文件頭。狀態包括：表數據行數,AUTO_INCREMENT 計數器和 key
  分布之類的信息
• Searching rows for update：線程正在進行第一階段查找所有匹配的行，然后再更新它們。如果 UPDATE 正在更改用于查找涉及的行的索引，則必須先把 update 滿足匹配的行先查找出來
• Sending data：線程正在讀取和處理 SELECT 語句產生的數據行，并將數據發送到客戶端。因為在此狀態期間發生的操作可能產生大量的磁盤訪問（讀取），所以它通常是給定查詢的生存期內最長的運行狀態
• Sending to client：server 正在向客戶端寫入數據包。在 MySQL 5.7.8 之前叫做“Writing to net”
• setup：線程正在執行 ALTER TABLE 操作
• Sorting for group：線程正在執行一個 GROUP BY 排序操作
• Sorting for order：線程正在執行一個 ORDER BY 排序操作
• Sorting index：線程正在排序索引頁面，以便在 MyISAM 表優化操作期間實現更高效的訪問
• Sorting result：對于 SELECT 語句，這類似“Creating sort index”狀態，但是針對于非臨時表
• statistics：server 正在計算統計信息以優化查詢執行計劃。如果一個線程在這個狀態很長一段時間，server 可能是磁盤執行其他工作而阻塞了統計信息的操作，也有可能發生了鎖等待。
• System lock：線程調用了mysql_lock_tables()，線程狀態從未更新過。這是一個非常常見的狀態，出現該狀態的原因有很多。例如，線程將請求或正在等待表的內部或外部系統鎖定。當 InnoDB 在執行 LOCK TABLES 期間等待表級鎖時，可能會發生這種情況。如果此狀態是由外部鎖請求引起的，如果您不使用多個mysqld 服務器訪問同一 MyISAM 表，則可以使用–skip-external-locking 選項禁用外部系統鎖。但是，默認情況下外部鎖定是禁用的，因此此選項可能無效。
  對于 SHOW PROFILE，此狀態表示線程正在請求鎖定
• update：線程準備開始更新表
• Updating：線程搜索且正在更新數據行
• updating main table：server 正在執行多表更新語句的第一部分。該狀態表示正在
  更新第一個表，并保存列值和偏移量以用于更新其他（引用）表
• updating reference tables：server 正在執行多表更新語句的第二部分，更新其他表
  的匹配行
• User lock：線程將請求或正在等待通過 GET_LOCK() 調用請求的建議鎖。對于SHOW PROFILE，此狀態表示線程正在請求鎖定（無需等待）
• User sleep：線程已調用 SLEEP() 調用
• 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：server 正在等待獲得一個 THR_LOCK 鎖或者從元數據鎖定子系統中獲取一個 MDL 鎖，其中 lock_type 表示正在等待獲得的 MDL 鎖的類型，THR_LOCK 只有一種（Waiting for table level lock），MDL 鎖有如下幾種：

• 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

• Waiting on cond：線程正在等待條件變為 true 的通用狀態。沒有特定的狀態信息可用
• Writing to net：server 正在向網絡寫入數據包。從 MySQL 5.7.8 之后叫做“Sending to client”

四、dump線程狀態

• Finished reading one binlog; switching to next binlog：線程已經完成讀取 binlog 文
  件，并切換到下一個 binlog 文件
• Master has sent all binlog to slave; waiting for more updates：線程已經從二進制日志中讀取了所有剩余的更新日志，并將它們發送到從庫。線程當前處于空閑狀態，正在等待新的更新數據的事件寫入二進制日志中
• Sending binlog event to slave：線程已經從二進制日志中讀取了一個事件，現在將其發送到從庫（二進制日志由事件組成，一個事件通常是由發生更新的數據和一些其他信息組成）
• Waiting to finalize termination：線程停止時發生的非常短暫的狀態，線程正在執行停止線程相關的動作

五、IO線程狀態

• Checking master version：在建立與主庫的連接之后非常短暫的狀態，表示正在檢查主庫的版本號
• Connecting to master：線程嘗試連接到主庫
• Queueing master event to the relay log：線程已讀取一個事件，并將其復制到中繼日志，以便 SQL 線程進行重放
• Reconnecting after a failed binlog dump request：線程正在嘗試重新連接到主庫
• Reconnecting after a failed master event read：線程正在嘗試重新連接到主庫，當重連連接成功時，狀態將變為“Waiting for master to send event” – Registering slave on master：在連接到主庫成功之后非常短暫的狀態，表示正在向主庫注冊從庫的連接信息（如從庫的 IP 和端口信息等）
• Requesting binlog dump：在與主庫建立連接成功之后非常短暫的狀態，使用當
  前的 I/O 線程位置，向主庫發送從當前位置開始的二進制日志的內容的請求
• Waiting for its turn to commit：如果啟用了 slave_preserve_commit_order 參數，則
  表示從庫 I/O 線程正在等待較舊的工作線程提交數據
• Waiting for master to send event：線程已經連接到主庫并且正在等待新的二進制
  日志事件，如果主庫空閑，這可能持續很長時間。如果等待時間持續超過slave_net_timeout 秒，則從庫 I/O 線程發生超時。此時，從庫 I/O 線程認為主庫的連接斷開，會嘗試重新連接主庫
• Waiting for master update：連接到主庫之前的初始狀態
• Waiting for slave mutex on exit：線程停止時短暫發生的狀態，表示正在回收 I/O 線程的相關互斥資源
• Waiting for the slave SQL thread to free enough relay log space：如果 relay_log_space_limit 變量設置值不為 0，那么當中繼日志總大小增長到超過此值時。 I/O 線程會等待，直到 SQL 線程通過重放中繼日志內容并刪除重放完成的中繼日志以釋放中繼日志占用的空間，使其滿足中繼日志中大小不大于 relay_log_space_limit 變量的值時，I/O 線程才可以繼續寫入中繼日志操作。
• Waiting to reconnect after a failed binlog dump request：如果二進制日志 dump 請求失敗（由于斷開連接），那么線程在進入 sleep 狀態，此時出現此狀態，然后I/O 線程定期嘗試重新連接主庫。重試之間的間隔時間可以使用 CHANGE MASTER TO 語句的 MASTER_CONNECT_RETRY 選項指定

• 要注意，從庫的 I/O 線程連接主庫是有心跳機制的，當主庫超過這個心跳時間沒有發送新的 event 到 slave 上時，I/O 線程就對主庫發起一個心跳請求，如果請求成功就重置心跳時間，當主庫有新的 event 發送到 slave 時，這個心跳時 間 也 會 進 行 重 置 。 心 跳 時 間 由 change master 語句的MASTER_HEARTBEAT_PERIOD 選項設置（以秒為單位），范圍 0 到 4294967 秒，分辨率（毫秒）最小非零值為0.001，表示 1 毫秒。將間隔設置為 0 時表示禁用心跳。默認值是slave_net_timeout 配置參數的二分之一。so，理論上是不會出現主從數據庫正常的情況下因為主庫沒有寫數據而導致從庫 I/O 線程斷開的情況。

• Waiting to reconnect after a failed master event read：讀取主庫 binlog 時發生錯誤（由于斷開連接）。I/O 線程在嘗試重新連接主庫之前，線程正在以 CHANGE MASTER TO 語句的 MASTER_CONNECT_RETRY 選項（默認為 60）設置的秒數進行 sleep（該時間是重連失敗之后的重試間隔時間）

六、SQL 線程狀態

• Killing slave：線程正在處理 STOP SLAVE 語句
• Making temporary file（append）before replaying LOAD DATA INFILE：線程正在執行 LOAD DATA INFILE 語句，并將從庫將要讀取的數據添加到臨時文件中
• Making temporary file（create）before replaying LOAD DATA INFILE：線程正在執行 LOAD DATA INFILE 語句，且正在創建臨時文件，臨時文件中包含了從庫將要讀取行數據。注意：只有在 MySQL 5.0.3 之前的版本中，主庫記錄了原始LOAD DATA INFILE 語句時，才能遇到此狀態
• Reading event from the relay log：線程正在從中繼日志中讀取事件，以便進行重放
• Slave has read all relay log; waiting for more updates：線程已重做完所有的中繼日志文件中的所有事件，正在等待 I/O 線程向中繼日志中寫入新的事件
• Waiting for an event from Coordinator：從庫使用多線程復制時（slave_parallel_workers 大于 1），此狀態表示一個 slave works 線程正在等待協調器線程（Coordinator 線程）分配日志事件
• Waiting for slave mutex on exit：線程停止時發生的非常短暫的狀態
• Waiting for Slave Workers to free pending events：當 Workers 線程處理的事件的總數量大小超過 slave_pending_jobs_size_max 系統變量的大小時，會發生等待操作（協調器線程不進行分配事件給 worker 線程）。當 Workers 線程處理的事件的總數量大小低于 slave_pending_jobs_size_max 限制時，協調器恢復調度。只有當 slave_parallel_workers 設置為大于 0 時，此狀態才會出現
• Waiting for the next event in relay log：“Reading event from the relay log”狀態之前的初始狀態
• Waiting until MASTER_DELAY seconds after master executed event：SQL 線程已讀取事件，但并沒有進行應用，而是正在等待從庫設置的延遲復制時間失效。此延遲時間使用 CHANGE MASTER TO 的 MASTER_DELAY 選項設置
  ? SQL 線程的 Info 列也可以顯示語句的文本。這表示線程已經從中繼日志中讀取了一個事件，并從中提取了 SQL 語句，當前可能正在執行這個語句對應的事件。

七、 主從連接線程狀態

• Changing master：線程正在處理 CHANGE MASTER TO 語句
• Killing slave：線程正在處理 STOP SLAVE 語句
• Opening master dump table：此狀態發生在主庫創建 dump 表之后
• Reading master dump table data：”Opening master dump table”狀態之后出現的狀態，表示正在從主庫 dump 表讀取數據
• Rebuilding the index on master dump table：“Reading master dump table data”狀態之后出現的狀態，表示正在重建主庫 dump 表索引

八、事件調度線程狀態

• Clearing：調度程序線程正在停止執行事件
• Initialized：調度程序線程已初始化完成，將要執行調度事件
• Waiting for next activation：調度程序具有非空事件隊列時，正在等待未來某個時間點激活隊列中的某個事件，以便進行調度并執行
• Waiting for scheduler to stop：線程發出 SET GLOBAL event_scheduler = OFF 并等待調度程序停止
• Waiting on empty queue：調度程序的事件隊列為空，因此調度程序處于休眠狀態

相關免費學習推薦：mysql數據庫(視頻)