作者通過分析源碼定位資料庫異常，梳理參數 innodb_thread_concurrency 設定的注意事項。
作者：李錫超
一個愛笑的江蘇蘇甯銀行資料庫工程師，主要負責資料庫日常運維、自動化建設、DMP 平台運維。擅長 MySQL、Python、Oracle，愛好騎行、研究技術。
- 愛可生開源社群出品，原創内容未經授權不得随意使用，轉載請聯系小編并注明來源。
一、問題現象
研發同學回報某測試應用系統存在異常，分析應用的錯誤日志、CPU、記憶體和磁盤 IO 等名額後，未發現相關異常。請求配合确認資料庫運作情況。
關鍵配置
| 配置項 | 值 |
| 資料庫版本 | MySQL 8.0 |
| 資料庫架構 | 單機 |
| CPU 個數 | 8C |
| 記憶體 | 16G |
| 參數 innodb_thread_concurrency | 16 |
| 參數 innodb_concurrency_tickets | 5000 |
二、初步分析
此類問題，一般是由于 SQL 的效率低下，導緻伺服器的 CPU、IO 等資源耗盡，然後應用發起新的 SQL 請求，會由于無法擷取系統資源，導緻 SQL 請求被堵塞。
為此，檢查 CPU、IO 等資源，發現 CPU 使用率約 5%，IO 幾乎沒有壓力。登陸資料庫檢查連接配接狀态，發現很多連接配接的狀态都在 executing。部分結果如下：
根據上述結果分析：
有 28 個會話狀态為 executing，1 個會話狀态為 updating。如果這些會話都真正在 executing，CPU 壓力應該會很高，但實際情況僅占用很少的 CPU。
1 系統有報錯或者某其它異常？
随後，對 MySQL 錯誤日志、磁盤使用率、磁盤 Inode 使用率、系統 messages 等資訊進行确認，都未發現有相關異常！
2 SQL 語句存在特殊性？
對連接配接中的 SQL 進行了初步分析，發現除了表 t01 所在的 SQL 較為複雜，其它 SQL 都非常簡單，且通路的都是資料表（不是視圖）。表 t02、t03 的資料僅 1 行，應該瞬間執行完成！
由于是測試環境，且問題導緻測試阻斷，于是執行如下指令收集了診斷資料：
| 診斷項 | 執行 SQL |
| 連接配接狀态 | show processlist; |
| 線程狀态 | select * from performance_schema.threads where processlist_info\G |
| 事務資訊 | select * from information_schema.innodb_trx\G |
| InnoDB status | show engine innodb status\G |
| 堆棧資訊 | pstack \<mysqld-pid> |
随後對資料庫執行了重新開機，重新開機完成後，應用系統恢複正常。
三、堆棧與源碼分析
綜合收集的資訊，對連接配接狀态、線程狀态和堆棧資訊進行關聯分析，發現被堵塞的 29 個連接配接中，有 13 個都被卡在函數 nanosleep 中，比較奇怪。其堆棧關鍵資訊如下：
#0 in nanosleep from /lib64/libpthread.so.0
#1 in srv_conc_enter_innodb
#2 in ha_innobase::index_read
#3 in ha_innobase::index_first
#4 in handler::ha_index_first
#5 in IndexScanIterator<false>::Read
#6 in Query_expression::ExecuteIteratorQuery
#7 in Query_expression::execute
#8 in Sql_cmd_dml::execute
#9 in mysql_execute_command
#10 in dispatch_sql_command
#11 in dispatch_command
#12 in do_command
#13 in handle_connection 其中 index_read ⼀般是⾸次通路 index，去找 WHERE ⾥的記錄。更關鍵的，看到了 srv_conc_enter_innodb 函數，并由他調用了 nanosleep，執行了類似“睡眠” 的操作。為此，結合對應版本的源碼進行分析。總結如下：
|-index_read(buf, nullptr, 0, HA_READ_AFTER_KEY) // 入口函數
|-ret = innobase_srv_conc_enter_innodb(m_prebuilt)
|-err = DB_SUCCESS
// STEP-1: 判斷 innodb_thread_concurrency 是否為0, 不為0則進一步判斷。否則直接傳回（即不限制進入innodb的線程數）
|-if (srv_thread_concurrency):
// STEP-2: 判斷事務擁有的 ticket(該值初始為:0) 個數是否大于0，如成立則 --ticket，然後傳回 DB_SUCCESS 至上層函數；否則繼續判斷
|-if (trx->n_tickets_to_enter_innodb > 0): --trx->n_tickets_to_enter_innodb
|-else: err = srv_conc_enter_innodb(prebuilt)
|-return srv_conc_enter_innodb_with_atomics(trx)
|-for (;;):
|-ulint sleep_in_us
|-if (srv_thread_concurrency == 0): return DB_SUCCESS // 再次判斷 innodb_thread_concurrency 是否為0, 滿足則直接傳回 DB_SUCCESS
/* STEP-3: 判斷進入 innodb 的事務是否小于 innodb_thread_concurrency 。
如小于(進入innodb)：則調整innodb中活動線程個數、标記事務進入了innodb、設定事務的ticket個數，然後傳回 DB_SUCCESS 至上層函數；
*/
|-if (srv_conc.n_active.load(std::memory_order_relaxed) < srv_thread_concurrency):
|-n_active = srv_conc.n_active.fetch_add(1, std::memory_order_acquire) + 1
|-if (n_active <= srv_thread_concurrency):
|-srv_enter_innodb_with_tickets(trx): // Note that a user thread is entering InnoDB.
|-trx->declared_to_be_inside_innodb = TRUE
|-trx->n_tickets_to_enter_innodb = srv_n_free_tickets_to_enter
|- // 調整 srv_thread_sleep_delay/
|-return DB_SUCCESS
|-srv_conc.n_active.fetch_sub(1, std::memory_order_release)
/* STEP-4: 否則(未進入innodb)，執行:
a. 設定事務的狀态(information_schema.innodb_trx.trx_operation_state)為"sleeping before entering InnoDB"
b. 根據 innodb_thread_sleep_delay 設定sleep時間
c. 判斷 sleep 時間是否超過上限 innodb_adaptive_max_sleep_delay, 如超過則設定睡眠時間為 innodb_adaptive_max_sleep_delay(1.5s)
d. 調用 nanosleep 進行指定時間的 sleep
e. 設定事務狀态為 “”
f. 自增 sleep 此時
h. 自增睡眠時間
i. 進行下一次for 循環 ------------------ > for
*/
|-trx->op_info = "sleeping before entering InnoDB"
|-sleep_in_us = srv_thread_sleep_delay
|-if (srv_adaptive_max_sleep_delay > 0 && sleep_in_us > srv_adaptive_max_sleep_delay):
|-sleep_in_us = srv_adaptive_max_sleep_delay
|-srv_thread_sleep_delay = sleep_in_us
|-std::this_thread::sleep_for(std::chrono::microseconds(sleep_in_us))
|-nanosleep
|-trx->op_info = ""
|-++n_sleeps
|-if (srv_adaptive_max_sleep_delay > 0 && n_sleeps > 1):
|-++srv_thread_sleep_delay
|-if (trx_is_interrupted(trx)):
|-return DB_INTERRUPTED
|-return err
|-ret = row_search_mvcc(buf, mode, m_prebuilt, match_mode, 0) // 執行查詢操作
|-innobase_srv_conc_exit_innodb(m_prebuilt);
// STEP-5: 判斷是否進入了innodb，且ticket為0(ticket 被耗盡)
|-if (trx->declared_to_be_inside_innodb && trx->n_tickets_to_enter_innodb == 0):
|-srv_conc_force_exit_innodb(trx)
// STEP-6: 标記事務為未進入innodb狀态。以避免不必要的函數調用
|-srv_conc_exit_innodb_with_atomics(trx)
|-trx->n_tickets_to_enter_innodb = 0
|-trx->declared_to_be_inside_innodb = FALSE
|-srv_conc.n_active.fetch_sub(1, std::memory_order_release) 為便于了解，将以上源碼邏輯總結為 4 個場景：
- 場景 1：innodb_thread_concurrency == 0, 執行邏輯：
- 場景 2：innodb_thread_concurrency != 0、事務擁有 ticket, 執行邏輯：
- 場景 3：innodb_thread_concurrency != 0、事務沒有 ticket、進入 innodb 的事務小于 innodb_thread_concurrency , 執行邏輯：
- 場景 4：innodb_thread_concurrency != 0、事務沒有 ticket、進入 innodb 的事務大于 innodb_thread_concurrency , 執行邏輯:
根據堆棧資訊，收影響的會話都被堵塞在 nanosleep 函數；同時，通過事務資訊，看到對應的會話的 ticket 為 0、事務狀态為 sleeping before entering InnoDB，與上述場景 4 基本相符。
小結
故障資料庫配置 innodb_thread_concurrency=16，問題時刻由于資料庫中慢 SQL 持有并發資源，且并發較高（超過 innodb_thread_concurrency），導緻其它事務需要進行 nanosleep 以等待 InnoDB 并發資源。
同時，結合源碼不難看出，對于慢 SQL，其自身也需要頻繁從 innodb 中進出，而當其擁有的 ticket（5000）用完之後，也需要重新進入排隊已等待并發資源，導緻執行 SQL 性能進一步降低，形成劣性循環。
四、問題解決
問題發生後，已認證重新開機的方式臨時解決。但通過與研發同學的溝通，還存在如下問題：
1 如何根本解決解決問題？
綜合以上分析過程，我們可以看到導緻此次故障的根本原因就是問題時刻資料庫存在慢 SQL，耗盡了 InnoDB 的并發資源，是以需要對問題 SQL 進行優化（由于篇幅有限，不在此讨論）。
此外，測試資料庫設定了 innodb_thread_concurrency=16 是導緻發生該現象的直接原因。對于該參數設定建議，簡要總結如下（完整說明參考 MySQL 官方文檔）：
- 如果資料庫的活動并發使用者線程數小于 64，則設定 innodb_thread_concurrency=0；
- 如果壓力一直很重或偶爾出現峰值，首先設定 innodb_thread_concurrency=128，然後将該值降低到 96、80、64，以此類推，直到找到提供最佳性能的線程數；
- Innodb_thread_concurrency 值過高會導緻性能下降，因為這會增加系統内部和資源的争用。
是以，建議将 innodb_thread_concurrency=0 從資料庫層面解決。該參數為動态參數，發生問題後可立即修改，并會立即生效，以避免不必要的重新開機操作。同時，需要盡快對慢 SQL 進行優化，以從根本解決該問題。
2 如何影響到那些本身執行會很快的其它 SQL？
根據源碼分析結果：由于耗盡的是 InnoDB 全局并發線程資源，類似于進入 InnoDB “連接配接” 被耗盡了一樣。是以會影響所有的其它線程。
3 影響的會話到底會被堵塞多久？
對于線上系統，當 InnoDB 并發資源被耗盡後，新發起的 SQL 會進入 nanosleep，直至已進入 InnoDB 事務的 ticket 被耗盡後，才有可能進入 InnoDB（而且好像是最後新發起的 SQL 請求，由于 sleep 時間越短，越容易進入）。除非源頭的慢 SQL 快速執行完成，但由于慢 SQL 在此狀态下，當 ticket 用完後也需要參與排隊，是以其執行時間會進一步加長，導緻源頭 SQL 無法快速完成。是以對于大多數 SQL 請求，都需要參與堵塞，且堵塞的時間會越來越長。問題發生後，建議盡快處理。
4 再次發生後，如何快速确認是該問題？
- 對于該資料庫版本，檢查是否大量的資料庫會話處于 executing, 且部分會話執行的 SQL 可能非常簡單；
-
檢查資料庫事務的狀态，判斷是否有處于 sleeping before entering InnoDB 的事務，且基本滿足：
sleeping before entering InnoDB 的事務個數 = 總的事務個數 - innodb_thread_concurrency
- 檢查 innodb 輸出，示例輸出結果如下：
--------------
ROW OPERATIONS
--------------
16 queries inside InnoDB, 22 queries in queue
....
---------------------------- - 根據前面提供的資訊采集步驟，儲存相關資訊，并結合堆棧和源碼進行确認。
本文關鍵字：#MySQL# #源碼#