返回文章列表
玄貓（BlackCat）

MySQL效能分析Metadata鎖定與交易資訊解析

本文探討 MySQL 效能分析中 Metadata Locks 的應用，解析 metadata_locks 表格中的 OBJECT_TYPE 與 LOCK_TYPE 欄位，並分析交易資訊的相關表格，包含 events_transactions_current、events_transactions_history 與

資料函式庫 效能分析
MySQL 效能調校 Metadata Locks 交易資訊 Performance Schema 鎖定

MySQL 的 Performance Schema 提供了豐富的 metadata locks 資訊，能有效診斷資料函式庫的並發控制與鎖定問題。metadata_locks 表格中的 OBJECT_TYPE 欄位揭示了鎖定的物件型別，例如 GLOBAL、TABLE、SCHEMA 等，而 LOCK_TYPE 欄位則定義了鎖定的存取層級，包含 SHARED、EXCLUSIVE 等。此外，MySQL 也提供了多個表格追蹤交易資訊，例如 events_transactions_current、events_transactions_history 和 INNODB_TRX，這些資訊能幫助開發者找出鎖定等待的原因。Performance Schema 與 sys 架構也提供了多個檢視和預存程式，簡化了效能分析的流程，例如 sys.statement_analysis 可用於識別最耗時的查詢，而 sys.statements_with_full_table_scans 則可找出需要最佳化的全表掃描查詢。透過這些工具，開發者可以更有效地監控資料函式庫效能，並找出潛在的瓶頸。

MySQL 效能分析中的 Metadata Locks 與交易資訊解析

MySQL 的 Performance Schema 提供了豐富的 metadata locks 資訊，可用於診斷並發控制與鎖定問題。本文將探討 metadata_locks 表格中的 OBJECT_TYPE 與 LOCK_TYPE 欄位，並分析交易資訊的相關表格。

Metadata Locks 的物件型別

metadata_locks 表格中的 OBJECT_TYPE 欄位顯示了鎖定的物件型別，主要包含以下幾種常見型別：

  1. GLOBAL：代表全域讀鎖定，通常由 FLUSH TABLES WITH READ LOCK 陳述式觸發。
  2. TABLE：代表資料表或檢視的 metadata 鎖定，是最常遇到的鎖定型別之一。
  3. SCHEMA：代表資料函式庫（schema）的 metadata 鎖定，與資料表的鎖定機制相似但作用範圍在整個資料函式庫。
  4. FUNCTION、PROCEDURE、TRIGGER：分別代表儲存函式、儲存程式與觸發器的 metadata 鎖定。

其他較少見的型別還包括 COLUMN_STATISTICS（用於直方圖與欄位統計資訊）、LOCKING_SERVICE（使用者透過鎖定服務介面取得的鎖定）等。

SELECT DISTINCT OBJECT_TYPE 
FROM performance_schema.metadata_locks;

內容解密：

這段程式碼用於查詢 metadata_locks 表格中所有不同的 OBJECT_TYPE。透過分析這些物件型別，可以瞭解系統中目前有哪些資源正在被鎖定，有助於進行效能調優與並發控制診斷。

Metadata Locks 的鎖定型別

LOCK_TYPE 欄位定義了鎖定的存取層級，包含多種分享與排他鎖的變體：

  1. SHARED：允許多個會話分享存取物件的 metadata，如儲存程式的解析過程。
  2. SHARED_WRITE：允許修改資料但需配合 metadata 的分享鎖定。
  3. EXCLUSIVE：完全禁止其他存取，用於 CREATE TABLE、DROP TABLE 等 DDL 操作。
  4. SHARED_UPGRADABLE：可升級的分享鎖定，用於 ALTER TABLE 的第一階段。
SELECT DISTINCT LOCK_TYPE 
FROM performance_schema.metadata_locks 
WHERE OBJECT_TYPE = 'TABLE';

內容解密：

這段查詢用於取得特定物件型別（如 TABLE）的所有鎖定型別。透過分析這些鎖定型別，可以瞭解系統中目前的並發控制策略，並協助診斷潛在的鎖定爭用問題。

交易資訊的相關表格

MySQL 提供了多個表格來追蹤交易資訊，主要包含：

  1. events_transactions_current：目前進行中的交易，以及已連線但尚未開始新交易的工作階段最新交易資訊。
  2. events_transactions_history：每個執行緒最近的交易記錄（預設儲存最近10筆）。
  3. INNODB_TRX：InnoDB 交易的詳細資訊，是分析進行中 InnoDB 交易的最佳來源。
SELECT * 
FROM information_schema.INNODB_TRX 
WHERE trx_state = 'LOCK WAIT';

內容解密：

這段查詢用於找出目前處於「LOCK WAIT」狀態的 InnoDB 交易。透過分析這些交易的詳細資訊，可以診斷交易鎖定等待的原因，並進一步最佳化交易邏輯或系統組態。

陳述式資訊的相關表格

MySQL 的 Performance Schema 也提供了多個表格來記錄陳述式執行資訊，主要包含：

  1. events_statements_current：目前正在執行的陳述式，或是閒置連線的最新執行陳述式。
  2. events_statements_history：每個連線最近執行的陳述式（預設儲存最近10筆）。
  3. events_statements_history_long：整個例項的最新陳述式記錄（預設儲存最近10000筆）。
SELECT SQL_TEXT, EXECUTION_COUNT 
FROM events_statements_summary_global_by_event_name 
WHERE EVENT_NAME LIKE 'statement/sql/%';

內容解密：

這段查詢用於取得各類別 SQL 陳述式的全域彙總資訊，包括 SQL 文字與執行次數。透過分析這些資料，可以瞭解系統中的 SQL 陳述式執行模式，並找出需要最佳化的熱門陳述式。

MySQL 效能分析工具：深入瞭解 Performance Schema 與 sys 架構

MySQL 的 Performance Schema 是用於監控與分析資料函式庫效能的重要工具。透過它，管理員可以取得豐富的執行階段資訊，從而最佳化查詢效能、診斷問題並提升整體系統穩定性。本文將探討 Performance Schema 的核心表格與 sys 架構中的相關檢視，並介紹如何利用這些工具進行高效的效能分析。

Performance Schema：核心表格解析

Performance Schema 提供了多種表格來記錄不同的執行階段資訊。這些表格主要分為以下幾類別：

  1. 執行緒資訊（threads）
    threads 表格記錄了目前例項中所有執行緒的資訊，包括背景與前景執行緒。它提供了比 SHOW PROCESSLIST 命令更詳細的資料，如執行緒是否被檢測、作業系統執行緒 ID 等。

  2. 陳述式摘要表格（events_statements_summary_by_*）
    這類別表格根據不同的維度（如陳述式摘要、事件名稱、使用者等）對陳述式進行統計分析。例如：

    • events_statements_summary_by_digest：按預設結構描述和陳述式摘要進行統計，是最常用的表格之一。
    • events_statements_summary_by_user_by_event_name：按使用者和事件名稱進行統計，有助於分析不同使用者的查詢行為。

  3. 直方圖統計（events_statements_histogram_by_digest）
    這類別表格提供了查詢執行時間的直方圖統計資訊，用於瞭解查詢效能的分佈情況。

  4. 預備陳述式資訊（prepared_statements_instances）
    記錄了預備陳述式的統計資訊，每個預備陳述式對應一行資料。需注意的是，預備陳述式的資訊不會包含在一般的陳述式統計表格中。

sys 架構：簡化效能分析的檢視

sys 架構建立在 Performance Schema 之上，提供了一系列檢視和預存程式，以簡化效能分析的過程。以下是一些關鍵檢視：

工作階段資訊（session）

  • 此檢視提供了進階的處理程式列表，結合了 threads 和 events_statements_current 表格的資訊。
  • 它顯示了活躍連線的目前陳述式和閒置連線的最後執行陳述式，並按處理程式列表時間和前一陳述式的執行時間進行排序。

陳述式分析（statement_analysis）

  • 此檢視是 events_statements_summary_by_digest 表格的格式化版本，按總延遲時間降序排列。
  • 有助於識別最耗時的查詢並進行最佳化。

錯誤與警告查詢（statements_with_errors_or_warnings）

  • 顯示導致錯誤或警告的陳述式，並按錯誤和警告數量降序排列。
  • 有助於發現潛在的問題查詢。

全表掃描查詢（statements_with_full_table_scans）

  • 列出包含全表掃描的陳述式，並按未使用索引的比例和總延遲時間排序。
  • 可用於最佳化查詢並新增適當的索引。

使用 statement_performance_analyzer() 程式進行效能分析

statement_performance_analyzer() 程式可對 events_statements_summary_by_digest 表格進行快照分析，比較兩個時間點之間的查詢執行情況，並生成報告以展示變化。

等待事件資訊

等待事件表格（如 events_waits_current、events_waits_history）記錄了執行緒等待事件的相關資訊，雖然預設情況下大多數等待事件是停用的，但它們對於深入瞭解系統瓶頸仍然非常有價值。

內容解密：

  1. 查詢 performance_schema.threads 表格：

    • 用途：取得目前 MySQL 例項中的所有執行緒資訊。
    • 分析：此查詢可用於監控目前資料函式庫的連線狀況，包括背景與前景執行緒。

  2. 分析 sys.statement_analysis 檢視：

    • 用途：按總延遲時間排序，找出最耗時的查詢。
    • 分析：此查詢幫助管理員快速識別需要最佳化的查詢，提升系統效能。

  3. 檢視 sys.statements_with_full_table_scans 檢視：

    • 用途：列出包含全表掃描的陳述式，並按相關指標排序。
    • 分析：此查詢可用於發現未有效利用索引的查詢，以便進行索引最佳化。

透過上述工具與方法，管理員可以全面掌握 MySQL 資料函式庫的執行狀況，並採取相應措施最佳化效能。

MySQL效能分析：深入理解等待事件與I/O資訊

MySQL的Performance Schema提供了一套強大的工具，用於監控和分析資料函式庫的效能。其中，等待事件（wait events）和I/O資訊是兩個重要的效能指標，可以幫助我們深入瞭解資料函式庫的運作狀況。

等待事件分析

等待事件是指執行緒在執行過程中等待某種資源或事件發生的時間。Performance Schema提供了多個表格來收集和匯總等待事件的資訊，包括：

  • events_waits_summary_by_account_by_event_name：按照帳戶（使用者名稱和主機名稱）和事件名稱匯總等待事件。
  • events_waits_summary_by_host_by_event_name：按照主機名稱和事件名稱匯總等待事件。
  • events_waits_summary_by_instance：按照事件名稱和物件例項的記憶體位址匯總等待事件。
  • events_waits_summary_by_thread_by_event_name：按照執行緒ID和事件名稱匯總等待事件。
  • events_waits_summary_by_user_by_event_name：按照使用者名稱和事件名稱匯總等待事件。
  • events_waits_summary_global_by_event_name：按照事件名稱匯總等待事件。

這些表格可以幫助我們瞭解哪些事件佔用了最多的等待時間，從而找出效能瓶頸。

程式碼範例：查詢等待事件

SELECT * FROM events_waits_summary_global_by_event_name
ORDER BY SUM_TIMER_WAIT DESC;

內容解密：

  1. SELECT * FROM events_waits_summary_global_by_event_name：查詢events_waits_summary_global_by_event_name表格中的所有欄位。
  2. ORDER BY SUM_TIMER_WAIT DESC：按照SUM_TIMER_WAIT欄位的值進行降序排序，以找出佔用最多等待時間的事件。

表格I/O資訊

表格I/O資訊可以幫助我們瞭解哪些表格的存取最頻繁。Performance Schema提供了兩個表格來收集表格I/O資訊：

  • table_io_waits_summary_by_table：按照表格匯總I/O資訊，包括讀取、寫入、提取、插入和更新等操作。
  • table_io_waits_summary_by_index_usage：按照索引使用情況匯總I/O資訊。

sys schema的檢視可以幫助我們更方便地查詢表格I/O資訊，包括：

  • schema_index_statistics：顯示每個索引的使用情況，包括選擇、插入、更新和刪除等操作。
  • schema_table_statistics：結合table_io_waits_summary_by_table和file_summary_by_instance表格的資訊，傳回表格I/O和檔案I/O的相關資訊。
  • schema_table_statistics_with_buffer：與schema_table_statistics類別似，但還包括緩衝池使用情況的資訊。
  • schema_tables_with_full_table_scans：顯示哪些表格執行了全表掃描。
  • schema_unused_indexes：顯示哪些索引未被使用。

程式碼範例：查詢表格I/O資訊

SELECT * FROM schema_table_statistics
ORDER BY total_latency DESC;

內容解密：

  1. SELECT * FROM schema_table_statistics：查詢schema_table_statistics檢視中的所有欄位。
  2. ORDER BY total_latency DESC：按照total_latency欄位的值進行降序排序，以找出佔用最多I/O時間的表格。

檔案I/O資訊

當工作負載擊中磁碟時，磁碟I/O可能會成為瓶頸。Performance Schema提供了多個表格來收集檔案I/O資訊，包括：

  • events_waits_summary_global_by_event_name：按照事件名稱匯總等待事件，包括檔案I/O事件。
  • file_summary_by_event_name：按照事件名稱匯總檔案I/O資訊，包括讀取、寫入和雜項等操作。
  • file_summary_by_instance：按照實際檔案匯總檔案I/O資訊。

這些表格可以幫助我們瞭解哪些檔案的I/O最頻繁，從而找出效能瓶頸。

程式碼範例：查詢檔案I/O資訊

SELECT * FROM file_summary_by_instance
ORDER BY total_latency DESC;

內容解密：

  1. SELECT * FROM file_summary_by_instance：查詢file_summary_by_instance表格中的所有欄位。
  2. ORDER BY total_latency DESC：按照total_latency欄位的值進行降序排序，以找出佔用最多I/O時間的檔案。

MySQL 效能分析與除錯工具綜覽

MySQL 提供了多種工具和機制來協助使用者分析和除錯資料函式庫效能問題。這些工具涵蓋了從 I/O 效能到錯誤資訊，再到 InnoDB 監控報表的各個方面。

sys Schema 檔案 I/O 檢視

sys schema 提供了一系列檢視，用於分析和理解 MySQL 的檔案 I/O 行為。這些檢視根據 Performance Schema 中的表格，為使用者提供了詳細的 I/O 統計資訊。

主要的 I/O 檢視

  1. io_by_thread_by_latency：此檢視顯示了按執行緒分組的檔案 I/O 統計資料，並按照總延遲時間降序排列。其中包含了背景執行緒的資訊，這些執行緒通常是寫入 I/O 的主要來源。

  2. io_global_by_file_by_bytes：該檢視報告了每個檔案的讀取和寫入操作次數，以及以位元組為單位的 I/O 量。資料按照讀取和寫入總量的降序排列。

  3. io_global_by_file_by_latency：與 io_global_by_file_by_bytes 類別似，但此檢視報告的是 I/O 操作的延遲時間。

  4. io_global_by_wait_by_bytes 和 io_global_by_wait_by_latency：這兩個檢視分別報告了按 I/O 事件名稱分組的位元組數和延遲時間，提供了一個從事件角度分析 I/O 效能的視角。

錯誤資訊

MySQL 的 Performance Schema 也提供了錯誤事件的匯總資料，儲存在一系列以 events_errors_summary 開頭的表格中。這些表格按照不同的維度（如帳戶、主機、執行緒、使用者和全域）匯總了錯誤事件的發生次數。

主要的錯誤資訊表格

  • events_errors_summary_by_account_by_error
  • events_errors_summary_by_host_by_error
  • events_errors_summary_by_thread_by_error
  • events_errors_summary_by_user_by_error
  • events_errors_summary_global_by_error

這些表格對於診斷和分析特定錯誤的發生模式非常有幫助。

狀態變數和 InnoDB 指標

MySQL 提供了多種狀態變數和 InnoDB 指標，用於監控資料函式庫的整體活動狀況。這些資訊可以透過查詢 performance_schema 中的 session_status 和 global_status 表格，以及 information_schema 中的 INNODB_METRICS 表格來取得。

主要的狀態變數和指標

  • performance_schema.session_status 和 performance_schema.global_status
  • information_schema.INNODB_METRICS
  • sys.metrics：一個結合了全域狀態變數、InnoDB 指標和其他指標的檢視。

InnoDB 監控報表

InnoDB 監控報表提供了關於 InnoDB 儲存引擎內部狀態的詳細資訊，可以透過執行 SHOW ENGINE INNODB STATUS 命令來取得。該報表包含了多個部分，每個部分涵蓋了不同的方面，如背景執行緒、旗語統計、最新外部索引鍵錯誤、死鎖偵測等。

主要的 InnoDB 監控報表部分

  1. BACKGROUND THREAD：描述了主背景執行緒的工作情況。

  2. SEMAPHORES：提供了旗語統計資訊，在診斷鎖定爭用問題時非常有用。

  3. LATEST FOREIGN KEY ERROR 和 LATEST DETECTED DEADLOCK：分別記錄了最新的外部索引鍵錯誤和死鎖事件的詳細資訊。

  4. TRANSACTIONS：提供了關於 InnoDB 交易的資訊，包括鎖定的持有情況。

  5. FILE I/O：描述了 InnoDB 使用的 I/O 執行緒的活動狀況。

透過這些工具和機制，MySQL 使用者可以對資料函式庫的效能和行為有更深入的瞭解，從而進行有針對性的最佳化和除錯。