故障分析

作者：王祥
愛可生 DBA 團隊成員，主要負責 MySQL 故障處理和效能優化。對技術執著，為客戶負責。
本文來源：原創投稿
*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

--

背景資訊

業務監控發現交易的平均響應時間比之前慢了近一倍，需要排查一下資料庫是不是響應慢了。生產MySQL版本為8.0.18，一主3從半同步複製。

首先對比檢視了交易正常時段與出現異常的時段各項監控指標（cpu、qps、tps、磁碟IO等）都未發現明顯的變化。接下來檢視slow log發現了較多的慢SQL，而且是普通的insert語句，執行時長超過1秒。進一步觀察對比發現，每次insert慢都是出現在同一秒，insert慢語句條數基本在30條左右，而且出現的間隔都是兩分鐘或兩分鐘的倍數。根據這個規律第一感覺是不是定時任務引起的問題。經過對定時任務的排查最終定位到監控指令碼，監控指令碼為兩分鐘執行一次。接下來需要排查一下，具體是哪部分導致insert慢。為了快速復現問題，直接在一個從庫上使用mysqlslap進行壓測。從業務那得知問題insert語句每秒會有60-80次的寫入量，壓測語句如下：

mysqlslap -h127.0.0.1 -uroot -p --concurrency=80 --iterations=10 --create-schema=userdb --query=/root/test.sql --engine=innodb --number-of-queries=50000

#test.sql
insert into userdb.ps (clo1, clo2, clo3, clo4, clo4, clo5, clo6) values (substring(MD5(RAND()),1,20), 'fffffdddddddddd', '0', '', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaddddddddd', '2022-06-17 16:00:38.145', 34);

在壓測期間執行監控指令碼，一邊檢視slow log，能穩定復現生產的現象。通過排除法，最終定位到幾個使用information_schema.processlist表的語句導致了insert慢。那information_schema.processlist為什麼會導致insert慢呢？帶著這個問題去檢視一下官方對information_schema.processlist的描述。

The default SHOW PROCESSLIST implementation iterates across active threads from within the thread manager while holding a global mutex. This has negative performance consequences, particularly on busy systems. The alternative SHOW PROCESSLIST implementation is based on the Performance Schema processlist table. This implementation queries active thread data from the Performance Schema rather than the thread manager and does not require a mutex.

根據官方的說明：在使用預設的show processlist會持有全域性互斥鎖，在業務繁忙的系統上會導致效能問題。同時也給出瞭解決辦法，使用Performance Schema中的processlist代替，此方式不會產生全域性互斥鎖。

performance_schema_show_processlist是MySQL 8.0.22版本引入的新功能。接下來我們來看看官方對Performance Schema中的processlist描述。

The SHOW PROCESSLIST statement provides process information by collecting thread data from all active threads. The performance_schema_show_processlist variable determines which SHOW PROCESSLIST implementation to use:
The default implementation iterates across active threads from within the thread manager while holding a global mutex. This has negative performance consequences, particularly on busy systems.

The alternative SHOW PROCESSLIST implementation is based on the Performance Schema processlist table. This implementation queries active thread data from the Performance Schema rather than the thread manager and does not require a mutex.

如果開啟引數performance_schema_show_processlist，show processlist使用Performance Schema中的processlist避免了全域性互斥鎖的問題，如果不開啟該引數則show processlist使用information_schema.processlist會產生全域性鎖。

在配置檔案[mysqld]下加上performance_schema_show_processlist=on配置。配置完成後，檢視performance_schema下的processlist。

root@localhost:mysql.sock [(none)]> show variables like 'performance_schema_show_processlist';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| performance_schema_show_processlist | ON    |
+-------------------------------------+-------+
#資訊與information_schema.processlist下保持一致
root@localhost:mysql.sock [(none)]> select * from performance_schema.processlist\G
*************************** 1. row ***************************
     ID: 5
   USER: event_scheduler
   HOST: localhost
     DB: NULL
COMMAND: Daemon
   TIME: 354
  STATE: Waiting on empty queue
   INFO: NULL
*************************** 2. row ***************************
     ID: 8
   USER: root
   HOST: localhost
     DB: NULL
COMMAND: Query
   TIME: 0
  STATE: executing
   INFO: select * from performance_schema.processlist
2 rows in set (0.00 sec)

總結

1.使用MySQL 8.0.22之前的版本，在業務繁忙的敏感系統上執行show processlist需要謹慎。

2.使用MySQL 8.0.22之後版本，可以開啟performance_schema_show_processlist避免該問題。但依舊不建議頻繁查詢會話資訊。

另外查詢processlist表導致MySQL 例項crash問題，請參考文章：https://mp.weixin.qq.com/s/qR...

參考：

https://dev.mysql.com/doc/ref...

故障分析 | show processlist 引起的效能問題

背景資訊

故障分析

總結

相關文章