MySQL InnoDB 訊號量爭用案例分析

– 11 分鐘閱讀

在高負載的 MySQL 資料函式庫環境中，InnoDB 訊號量爭用是常見的效能瓶頸。本文將透過實際案例，深入剖析如何診斷並解決此類別問題。首先，建立可重現問題的測試環境，並觀察系統在高負載下的行為表現。接著，利用 InnoDB 提供的監控工具，例如 innodb_rwlock_% 指標、InnoDB 監控器和 Mutex 監控器，找出爭用的具體位置和原因。分析鎖定型別、等待執行緒和相關程式碼，有助於釐清問題根源。最後，根據分析結果，提出對應的解決方案，例如調整 innodb_adaptive_hash_index_parts 引數、關閉自適應雜湊索引，或最佳化查詢以降低對自適應雜湊索引的依賴，最終提升資料函式庫的整體效能。

InnoDB 訊號量爭用案例分析：監控與診斷

在處理 InnoDB 訊號量（semaphore）爭用問題時，首先需要建立一個能夠重現該問題的測試環境。透過模擬實際的工作負載，可以觀察到系統在高負載下的行為，從而更有效地進行故障排除。

建立測試環境與重現問題

為了模擬訊號量爭用問題，首先需要設定一個測試環境。測試指令碼會不斷執行查詢，以重現爭用情況。為了避免快取幹擾測試結果，將 innodb_old_blocks_time 設定為 0，以增加緩衝池的壓力，使爭用問題更容易被重現。

SET GLOBAL innodb_old_blocks_time = 0;

此設定將在測試結束後還原為預設值（1000）。測試的執行時間會比設定的執行時間稍長，因為執行時間檢查是在每個查詢迴圈開始時進行的。

調查與分析

當訊號量爭用發生時，第一步是檢視監控系統以瞭解爭用的發生情況。InnoDB 提供了多種工具來監控和診斷訊號量爭用問題。

InnoDB RW-Lock 指標

首先，可以檢視 innodb_rwlock_% 指標，這些指標可以從 information_schema.INNODB_METRICS 或 sys.metrics 中取得。這些指標分為三組，分別對應分享鎖、分享互斥鎖和獨佔鎖。每組指標包括自旋等待次數、自旋輪次和作業系統等待次數。

圖 18-1 和圖 18-2 分別顯示了分享鎖和獨佔鎖的自旋等待次數和自旋輪次。在測試過程中，自旋輪次在特定時間點顯著增加，同時作業系統等待次數也隨之增加，這表示自旋等待超過了 innodb_sync_spin_loops 的預設值（30）。

InnoDB 監控器與 Mutex 監控器

當確定爭用發生的時間後，需要進一步確定爭用發生的具體位置。主要的工具包括 InnoDB 監控器和 Mutex 監控器。InnoDB 監控器的輸出結果如 Listing 18-2 所示，其中包含了 SEMAPHORES 段的詳細資訊。

mysql> SHOW ENGINE INNODB STATUS\G
...
---
-
---
---
SEMAPHORES
---
-
---
---
OS WAIT ARRAY INFO: reservation count 36040
--Thread 35592 has waited at btr0sea.ic line 92 for 0 seconds the semaphore:
X-lock on RW-latch at 000001BD277CCFF8 created in file btr0sea.cc line 202
a writer (thread id 25492) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.ic line 122
Last time write locked in file btr0sea.ic line 92

圖表分析與解說

此圖示為 InnoDB RW-Lock 指標的變化趨勢圖，橫軸表示測試時間，縱軸表示自旋等待次數和自旋輪次。 此圖示顯示了在測試過程中，分享鎖和獨佔鎖的自旋等待次數和自旋輪次的變化趨勢。可以觀察到，自旋輪次在特定時間點顯著增加，同時作業系統等待次數也隨之增加。

詳細解說

  1. 指標分析：透過分析 innodb_rwlock_% 指標，可以瞭解不同型別的鎖在測試過程中的行為。
  2. 監控器輸出：InnoDB 監控器的輸出提供了詳細的訊號量爭用資訊，包括等待執行緒、鎖模式和最後一次鎖定資訊。
  3. 圖表呈現：透過圖表呈現指標變化趨勢，可以更直觀地觀察到爭用發生的時間點和相關指標的變化。

MySQL InnoDB 訊號量爭用分析與調優

在 MySQL InnoDB 中，訊號量（semaphore）爭用是一種常見的效能瓶頸。本文將透過一個實際案例，深入分析 InnoDB 訊號量爭用的原因和解決方案。

問題背景

在某個 MySQL 資料函式庫例項中，觀察到 InnoDB 訊號量爭用導致的效能問題。透過分析 InnoDB monitor 輸出，發現爭用主要集中在 btr0sea.cc 的第 202 行。

分析過程

  1. 檢視 InnoDB monitor 輸出

    首先，分析 InnoDB monitor 的輸出，瞭解訊號量爭用的情況。輸出顯示，多個執行緒正在等待 btr0sea.cc 第 202 行建立的 RW-latch。

    --Thread 22184 has waited at btr0sea.ic line 122 for 0 seconds the semaphore:
S-lock on RW-latch at 000001BD277CCFF8 created in file btr0sea.cc line 202
a writer (thread id 25492) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0

  2. 檢視原始碼

    接下來，檢視 MySQL 原始碼中 btr0sea.cc 檔案的第 202 行。發現該行程式碼與自適應雜湊索引（Adaptive Hash Index）的建立有關。

    rw_lock_create(btr_search_latch_key, btr_search_latches[i],
               SYNC_SEARCH_SYS);

  3. 使用 Mutex Monitor

    使用 SHOW ENGINE INNODB MUTEX 命令取得 Mutex 的統計資訊。輸出顯示，btr0sea.cc:202 處的 RW-latch 爭用最為嚴重。

    mysql> SHOW ENGINE INNODB MUTEX;
+

-+

-+

–+ | Type | Name | Status | +

-+

-+

–+ | InnoDB | rwlock: btr0sea.cc:202 | waits=7730 | | InnoDB | rwlock: btr0sea.cc:202 | waits=934 | … +

-+

-+

–+


### 解決方案

1. **分析工作負載**

最後一步是確定導致爭用的工作負載。可以使用監控工具或 Performance Schema 表來收集查詢資訊。

```sql
-- 使用 sys.session 或 Performance Schema 表
SELECT * FROM sys.session;
SELECT * FROM performance_schema.events_statements_current;

  1. 調優建議

    • 如果自適應雜湊索引不是必要的，可以考慮關閉它。
    • 調整 innodb_adaptive_hash_index_parts 引數，增加分割槽數量以減少爭用。
    • 最佳化查詢陳述式，減少對自適應雜湊索引的依賴。

內容解密：

上述解決方案的核心是理解 InnoDB 訊號量爭用的原因，並根據具體情況進行調優。

  1. 自適應雜湊索引的作用：自適應雜湊索引是 InnoDB 用於加速查詢的機制，但它可能成為爭用的熱點。
  2. innodb_adaptive_hash_index_parts 引數的作用：增加該引數可以將自適應雜湊索引分割槽，減少爭用。
  3. 查詢最佳化：最佳化查詢陳述式可以減少對自適應雜湊索引的依賴，從而降低爭用。

透過以上步驟，可以有效地解決 MySQL InnoDB 中的訊號量爭用問題，提升資料函式庫的效能和穩定性。

案例研究：訊號量（Semaphores）與效能分析

在MySQL的效能調校過程中，訊號量（Semaphores）和互斥鎖（Mutexes）的爭用（Contention）是常見的效能瓶頸之一。本章節將探討一個真實案例，分析如何識別和解決訊號量爭用問題。

識別訊號量爭用

在生產環境中，MySQL的效能問題往往難以診斷，尤其是在每秒執行超過10萬個查詢且每分鐘有超過1萬個唯一查詢摘要的情況下。要識別訊號量爭用的來源，需要仔細分析系統的監控資料和查詢行為。

幸運的是，透過分析爭用的互斥鎖和訊號量，可以初步判斷出問題的根源。在本案例中，爭用發生在自適應雜湊索引（Adaptive Hash Index）上，這主要與次級索引（Secondary Indexes）的使用有關。因此，重點應該放在使用次級索引的查詢上。

查詢計畫分析

以下是一個讀取密集型查詢的執行計畫範例，如清單18-3所示。

EXPLAIN
SELECT dept_name, MIN(salary) min_salary,
       AVG(salary) AS avg_salary, MAX(salary) AS max_salary
FROM employees.departments
INNER JOIN employees.dept_emp USING (dept_no)
INNER JOIN employees.salaries USING (emp_no)
WHERE dept_emp.to_date = '9999-01-01'
  AND salaries.to_date = '9999-01-01'
GROUP BY dept_no
ORDER BY dept_name;

內容解密：

此查詢計畫顯示了三個表的連線操作，分別是departments、dept_emp和salaries。透過EXPLAIN陳述式，我們可以看到：

  1. 對dept_emp和salaries表的連線操作使用了次級索引，分別是idx_concurrency_book_0和idx_concurrency_book_2。
  2. departments表使用了主鍵索引，但額外的操作包括使用臨時表和檔案排序。

這表明查詢嚴重依賴次級索引，這可能是導致自適應雜湊索引爭用的原因之一。

解決與預防

與前面的案例研究不同，解決訊號量爭用問題通常沒有直接的方法。相反，需要測試和驗證各種可能的系統變更。以下是一些可能的解決方案：

  • 停用自適應雜湊索引
  • 增加分割槽數量
  • 增加執行緒暫停前的旋轉次數
  • 將工作負載分散到不同的副本

停用自適應雜湊索引

停用自適應雜湊索引是最直接的解決方案，但在此之前，需要評估是否真的存在效能問題。自適應雜湊索引可以加快查詢速度，但如果查詢等待時間過長，則可能需要停用它。

圖18-3顯示了分享鎖和獨佔鎖的平均旋轉次數。對於獨佔鎖，每次等待花費80到100次旋轉，這是一個相對較大的數字。預設情況下，InnoDB在30次旋轉後會暫停執行緒，這使得喚醒查詢的成本更高。

此圖示展示了檢測和解決訊號量爭用的流程。

內容解密：

  1. 首先需要檢測系統是否存在訊號量爭用。
  2. 分析查詢行為，以確定哪些查詢可能導致爭用。
  3. 評估自適應雜湊索引的使用情況，包括其命中率和等待時間。
  4. 如果必要，停用自適應雜湊索引，並監控效能變化。

總之，解決MySQL中的訊號量爭用問題需要綜合運用監控資料分析、查詢行為分析和系統引數調整等多種手段。透過仔細分析和測試，可以找到合適的解決方案，從而提升系統的整體效能。

自適應雜湊索引（Adaptive Hash Index）效能分析與最佳化

在InnoDB儲存引擎中，自適應雜湊索引（Adaptive Hash Index）是一項重要的效能最佳化功能，旨在加速查詢效能。然而，在某些情況下，它可能會成為效能瓶頸，尤其是當出現訊號量爭用（Semaphore Contention）問題時。本章將探討自適應雜湊索引的工作原理、效能監控方法以及如何根據實際工作負載進行最佳化。

自適應雜湊索引的工作原理

自適應雜湊索引是InnoDB根據查詢模式自動建立的雜湊索引，用於加速對B樹索引的查詢。當查詢條件符合雜湊索引的建構條件時，InnoDB會優先使用雜湊索引進行查詢，從而減少B樹搜尋的次數。

監控自適應雜湊索引效能

要評估自適應雜湊索引的效能，可以使用sys.metrics表中的相關指標。以下是一個查詢範例：

SELECT variable_name, variable_value AS value, enabled
FROM sys.metrics
WHERE type = 'InnoDB Metrics - adaptive_hash_index'
ORDER BY variable_name;

輸出結果如下：

+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
---
+
---
-
---
--+
| variable_name | value | enabled |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
---
+
---
-
---
--+
| adaptive_hash_pages_added | 0 | NO |
| adaptive_hash_pages_removed | 0 | NO |
| adaptive_hash_rows_added | 0 | NO |
| adaptive_hash_rows_deleted_no_hash_entry | 0 | NO |
| adaptive_hash_rows_removed | 0 | NO |
| adaptive_hash_rows_updated | 0 | NO |
| adaptive_hash_searches | 51488882 | YES |
| adaptive_hash_searches_btree | 10904682 | YES |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
---
+
---
-
---
--+

內容解密：

  • adaptive_hash_searches：表示透過雜湊索引完成的搜尋次數。
  • adaptive_hash_searches_btree：表示需要回退到B樹搜尋的次數。
  • 命中率（Hit Rate）可以透過以下公式計算：(adaptive_hash_searches / (adaptive_hash_searches + adaptive_hash_searches_btree)) * 100%。

分析自適應雜湊索引的命中率

根據前面的查詢結果，命中率為82.5%，這可能對於某些工作負載來說仍然偏低。低命中率意味著更多的查詢需要回退到B樹搜尋，這可能會影響整體效能。

圖表分析

透過繪製adaptive_hash_searches和adaptive_hash_searches_btree的時間序列圖，可以更直觀地觀察自適應雜湊索引的效能變化。如圖18-4所示，在測試初期，雜湊索引非常有效，但隨後其效能急劇下降。

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title MySQL InnoDB 訊號量爭用案例分析

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

此圖示說明瞭自適應雜湊索引在測試過程中的效能變化。

最佳化建議

  1. 停用自適應雜湊索引：如果發現自適應雜湊索引對效能提升不明顯，甚至造成負面影響，可以考慮停用它，以騰出緩衝池記憶體用於快取B樹索引。

SET GLOBAL innodb_adaptive_hash_index = OFF;


2. **增加雜湊索引分割區**：如果訊號量爭用是由於多個連線存取相同的雜湊分割區引起的，可以嘗試增加`innodb_adaptive_hash_index_parts`的值來分割雜湊索引。

## MySQL 平行效能調校：鎖定與交易深度解析

MySQL 的平行處理能力對於資料函式庫效能至關重要，尤其是在處理大量交易和查詢請求時。有效的鎖定機制和交易管理可以顯著提升系統的整體效能和穩定性。本文將探討 MySQL 中的鎖定機制、交易處理以及相關的效能調校技巧。

### 鎖定機制與平行控制

MySQL 使用多種鎖定機制來確保資料的一致性和完整性，主要包括表鎖（Table Locks）、行鎖（Row Locks）以及後設資料鎖（Metadata Locks）。這些鎖定機制對於支援平行操作至關重要，但不當的使用或組態可能會導致效能瓶頸。

#### InnoDB 鎖定機制

InnoDB 儲存引擎是 MySQL 中最常用的交易安全型儲存引擎，它支援行級鎖定和交易。InnoDB 的鎖定機制包括分享鎖（Shared Locks）和獨佔鎖（Exclusive Locks），用於控制對資料的平行存取。

```sql
-- 查詢 InnoDB 鎖定狀態
SELECT * FROM sys.innodb_lock_waits;

適應性雜湊索引（Adaptive Hash Index）

適應性雜湊索引是 InnoDB 用於提升查詢效能的機制之一，但它也可能成為平行存取中的瓶頸。當多個查詢競爭存取相同的雜湊索引時，可能會導致訊號量爭用（Semaphore Contention），進而影響系統效能。

交易處理與隔離級別

MySQL 支援多種交易隔離級別，包括 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。選擇合適的交易隔離級別對於平衡平行度和資料一致性至關重要。

交易隔離級別對平行度的影響

  • READ UNCOMMITTED：最低的隔離級別，可能會出現髒讀（Dirty Read）。
  • READ COMMITTED：避免髒讀，但可能會出現不可重複讀（Non-Repeatable Read）。
  • REPEATABLE READ：InnoDB 的預設隔離級別，避免不可重複讀，但可能會出現幻讀（Phantom Read）。
  • SERIALIZABLE：最高的隔離級別，完全避免平行問題，但可能會嚴重影響平行度。

效能調校技巧

  1. 最佳化查詢和索引：確保查詢陳述式高效，並使用適當的索引，以減少鎖定的範圍和時間。
  2. 調整交易隔離級別：根據應用需求，選擇合適的交易隔離級別，以平衡平行度和資料一致性。
  3. 監控和調整鎖定相關引數：如 innodb_sync_spin_loops，以減少上下文切換和等待時間。
  4. 使用讀寫分離：將讀操作和寫操作分離到不同的資料函式庫例項，以提高整體平行處理能力。
玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。