MySQL 效能分析工具 Performance Schema 解析

Performance Schema 作為 MySQL 內建的效能分析工具，能提供伺服器執行時的詳細資訊，對於 DBA 和開發者診斷效能瓶頸至關重要。它透過標準化查詢陳述式，彙總效能資料，例如執行時間和資源消耗，讓開發者能快速掌握資料函式庫的效能狀況。Performance Schema 提供多種表格，例如例項表格記錄檔案存取資訊，設定表格用於調整監控選項，以及其他表格提供額外的執行階段資訊。然而，由於資料儲存在記憶體中，需要謹慎設定以避免過度消耗資源。搭配 sys schema，使用者可以更方便地查詢和分析 Performance Schema 的資料，而無需直接操作底層表格。此外，Performance Schema 還能監控所有執行緒的活動，提供內部執行緒 ID 和作業系統執行緒 ID 的對應關係，方便進行連線管理。理解 Performance Schema 的架構和功能，能有效協助開發者提升資料函式庫效能和穩定性。

MySQL 效能分析工具：Performance Schema 詳細解析

MySQL 的 Performance Schema 是用於監控與分析伺服器效能的重要工具，能夠提供詳細的執行階段資訊與資源消耗資料。本篇文章將探討 Performance Schema 的架構、功能、限制及其實際應用。

查詢標準化與執行效能監控

在 Performance Schema 中，查詢陳述式會被標準化處理以便於統計分析。例如：

SELECT user,birthdate FROM users WHERE user_id=19;
SELECT user,birthdate FROM users WHERE user_id=13;
SELECT user,birthdate FROM users WHERE user_id=27;

上述查詢會被轉換為標準形式：

SELECT user,birthdate FROM users WHERE user_id=?

這種標準化處理使得 Performance Schema 能夠彙總相同結構查詢的效能資料，例如延遲時間，而無需儲存每個變化的查詢例項。

內容解密：

1. 查詢標準化：將具有相同結構的查詢陳述式標準化，能夠有效降低資料儲存量並提升統計分析效率。
2. 效能指標彙總：透過標準化查詢，Performance Schema 可針對特定型別的查詢進行整體效能監控，例如計算平均延遲。
3. 資源消耗分析：透過監控這些標準化查詢的執行情況，能夠進一步瞭解系統資源的使用狀況。

資料例項與設定表格

Performance Schema 包含多種表格型別，主要分為以下幾類別：

1. 例項表格（Instances）
   例如 file_instances 表格，用於記錄檔案名稱及存取這些檔案的執行緒數量。

2. 設定表格（Setup）
   用於執行階段的效能監控設定，可用來啟用或停用特定的監控項。

3. 其他表格
   例如 metadata_locks 表格，用於記錄元資料鎖定資訊。

內容解密：

1. 例項表格 提供系統資源的使用狀況，例如檔案存取情況。
2. 設定表格 允許管理員動態調整 Performance Schema 的監控行為。
3. 其他表格 則提供額外的執行階段資訊，有助於深入分析特定問題。

資源消耗與限制

Performance Schema 的資料儲存在記憶體中，可以透過設定消費者（consumers）的最大記憶體使用量來限制其資源消耗。部分表格支援自動擴充套件記憶體，但一旦分配後，即使停用特定監控項，記憶體也不會被釋放。

內容解密：

1. 記憶體使用管理：透過設定最大記憶體限制或使用自動擴充套件機制來控制 Performance Schema 的記憶體佔用。
2. CPU 使用影響：每一個被監控的操作都會增加額外的系統呼叫，因此監控專案的數量與型別會直接影響 CPU 使用率。
3. 資源消耗考量：需要根據實際情況平衡監控的詳細程度與系統效能之間的關係。

sys Schema：簡化 Performance Schema 的使用

MySQL 從 5.7 版本開始提供 sys schema，這是一個建立在 Performance Schema 之上的檢視與預存程式集合，簡化了效能資料的查詢與分析。

內容解密：

1. sys schema 功能：提供更友善的資料查詢介面，無需直接操作底層的 Performance Schema 表格。
2. 資料來源：sys schema 的資料完全來自 Performance Schema，因此在使用前需確保相關監控專案已啟用。
3. 使用限制：需瞭解 sys schema 僅是對 Performance Schema 的封裝，若需要更底層的資料，仍需直接查詢 Performance Schema。

執行緒管理與監控

MySQL 是多執行緒軟體，每個元件都可能建立背景或前景執行緒。Performance Schema 中的 threads 表格記錄了所有執行緒的詳細資訊，包括內部執行緒 ID（THREAD_ID）與作業系統執行緒 ID（THREAD_OS_ID）。

SELECT NAME, THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID 
FROM performance_schema.threads;

內容解密：

1. 執行緒資訊：threads 表格提供了豐富的執行緒資訊，包括內部 ID 與作業系統層級的 ID。
2. THREAD_ID 與 PROCESSLIST_ID 的區別：THREAD_ID 是內部唯一識別碼，而 PROCESSLIST_ID 對應到客戶端連線 ID，在 SHOW PROCESSLIST 中可見。
3. 實際應用：透過查詢 threads 表格，可以獲得連線 ID 以進行連線管理，例如終止持有鎖的連線。

MySQL 效能結構（Performance Schema）設定與管理

MySQL 的效能結構（Performance Schema）是一個強大的工具，用於監控和分析 MySQL 伺服器的效能。它提供了豐富的資訊，幫助開發者和資料函式倉管理員瞭解伺服器的內部運作，找出效能瓶頸並進行最佳化。

連線 threads 表格以取得額外資訊

threads 表格可以用來連線其他多個表格，以提供有關執行查詢的額外資訊，例如查詢資料、鎖定、互斥鎖或開啟的表格例項。

設定 Performance Schema

Performance Schema 的某些部分只能在伺服器啟動時更改，例如啟用或停用 Performance Schema 本身，以及與記憶體使用和收集的資料限制相關的變數。Performance Schema 的儀器（instruments）和消費者（consumers）可以動態啟用或停用。

啟用和停用 Performance Schema

要啟用或停用 Performance Schema，需要設定變數 performance_schema 為 ON 或 OFF。這是一個唯讀變數，只能在組態檔中或透過命令列引數在 MySQL 伺服器啟動時更改。

啟用和停用儀器

儀器可以被啟用或停用。要檢視儀器的狀態，可以查詢 setup_instruments 表格：

mysql> SELECT * FROM performance_schema.setup_instruments
    -> WHERE NAME='statement/sql/select'\G
*************************** 1. row ***************************
           NAME: statement/sql/select
        ENABLED: NO
          TIMED: YES
     PROPERTIES:
     VOLATILITY: 0
DOCUMENTATION: NULL
1 row in set (0.01 sec)

從結果中可以看到，ENABLED 為 NO，表示目前未對 SELECT 查詢進行檢測。

有三種方法可以啟用或停用 Performance Schema 儀器：

1. 使用 setup_instruments 表格。
2. 呼叫 sys 架構中的 ps_setup_enable_instrument 儲存程式。
3. 使用啟動引數 performance-schema-instrument。

方法一：使用 UPDATE 陳述式

mysql> UPDATE performance_schema.setup_instruments
    -> SET ENABLED='YES' WHERE NAME='statement/sql/select';
Query OK, 1 rows affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

也可以使用萬用字元來啟用所有 SQL 陳述式檢測：

mysql> UPDATE performance_schema.setup_instruments
    -> SET ENABLED='YES' WHERE NAME LIKE 'statement/sql/%';
Query OK, 167 rows affected (0.00 sec)
Rows matched: 167 Changed: 167 Warnings: 0

#### 內容解密：

此段落程式碼展示如何使用 UPDATE 陳述式來啟用特定的 Performance Schema 儀器。首先，我們查詢 setup_instruments 表格以確認 statement/sql/select 儀器的狀態。然後，我們使用 UPDATE 陳述式將 ENABLED 狀態設為 'YES'，以啟用對 SELECT 陳述式的檢測。最後，我們可以使用萬用字元來批次啟用符合特定模式的儀器，例如所有 SQL 陳述式檢測。

方法二：使用 sys 儲存程式

mysql> CALL sys.ps_setup_enable_instrument('statement/sql/select');
+
---
-
---
-
---
-
---
-
---
---
+
| summary              |
+
---
-
---
-
---
-
---
-
---
---
+
| Enabled 1 instrument |
+
---
-
---
-
---
-
---
-
---
---
+
1 row in set (0.01 sec)

#### 內容解密：

此段落程式碼展示如何使用 sys 架構中的儲存程式來啟用特定的 Performance Schema 儀器。我們呼叫 ps_setup_enable_instrument 程式，並傳入要啟用的儀器名稱，例如 'statement/sql/select'。該程式會傳回一個摘要，顯示已啟用的儀器數量。

方法三：使用啟動選項

可以使用 performance-schema-instrument 組態引數來設定 Performance Schema 儀器。此引數支援多種語法，可以指定多個儀器的狀態。

performance-schema-instrument='statement/sql/select=ON'

#### 內容解密：

此段落程式碼展示如何在 MySQL 伺服器啟動時使用 performance-schema-instrument 組態引數來設定 Performance Schema 儀器。我們可以指定特定的儀器名稱和其狀態，例如 'statement/sql/select=ON'，以在伺服器啟動時啟用對 SELECT 陳述式的檢測。

啟用和停用消費者

與儀器類別似，消費者也可以被啟用或停用。有三種方法可以做到這一點：

1. 更新 Performance Schema 中的 setup_consumers 表格。
2. 使用 sys 架構中的儲存程式 ps_setup_enable_consumer 和 ps_setup_disable_consumer。
3. 設定 performance-schema-consumer 組態引數。

#### 內容解密：

此段落程式碼介紹瞭如何啟用和停用 Performance Schema 的消費者。消費者是用於收集和處理 Performance Schema 資料的元件。我們可以使用多種方法來控制消費者的狀態，包括更新 setup_consumers 表格、使用儲存程式或設定組態引數。

consumers及其用途列表

#### 內容解密：

此表格列出了 Performance Schema 中常見的消費者及其用途。每個消費者負責收集特定型別的效能資料，例如陳述式統計、交易資訊或等待事件。瞭解這些消費者的功能有助於我們更好地組態和使用 Performance Schema，以滿足特定的效能監控需求。

MySQL效能分析工具的調整與最佳化

MySQL的Performance Schema是一個強大的工具，能夠幫助開發人員和資料函式倉管理員監控及最佳化資料函式庫效能。本文將探討如何調整Performance Schema以滿足特定的監控需求。

針對特定物件的監控調整

Performance Schema允許對特定型別的物件進行監控調整，例如事件（EVENT）、函式（FUNCTION）、程式（PROCEDURE）、表格（TABLE）和觸發器（TRIGGER）。這些設定儲存在setup_objects表中。

程式碼範例：調整觸發器的監控

-- 停用test資料函式庫中所有觸發器的Performance Schema監控
INSERT INTO performance_schema.setup_objects
(OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, ENABLED)
VALUES ('TRIGGER', 'test', '%', 'NO');

-- 為特定的觸發器my_trigger啟用監控
INSERT INTO performance_schema.setup_objects
(OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, ENABLED)
VALUES ('TRIGGER', 'test', 'my_trigger', 'YES');

內容解密：

1. 第一個INSERT陳述式將test資料函式庫中所有觸發器的ENABLED狀態設為'NO'，表示停用這些觸發器的Performance Schema監控。
2. 第二個INSERT陳述式為名為my_trigger的觸發器設定例外，允許對其進行監控。
3. Performance Schema會根據設定的規則檢查物件是否需要被檢測，首先搜尋更具體的規則，然後回退到較不具體的規則。

調整執行緒監控

setup_threads表包含了可以被監控的背景執行緒列表。ENABLED欄位指定是否啟用特定執行緒的檢測，而HISTORY欄位則控制是否將檢測到的事件儲存在歷史表中。

程式碼範例：停用事件排程器的歷史記錄

UPDATE performance_schema.setup_threads 
SET HISTORY='NO'
WHERE NAME='thread/sql/event_scheduler';

內容解密：

1. 此UPDATE陳述式將事件排程器（event_scheduler）的歷史記錄功能停用。
2. setup_threads表不儲存使用者執行緒的設定；使用者執行緒的設定儲存在setup_actors表中。

調整Performance Schema的記憶體大小

Performance Schema使用PERFORMANCE_SCHEMA引擎儲存資料於記憶體中。某些表預設會自動調整大小，而其他表則有固定的列數。可以透過修改啟動變數來調整這些選項。

關鍵組態變數

• performance_schema_events_stages_history_size：定義每個執行緒在events_stages_history表中儲存的階段數量。
• performance_schema_max_memory_classes：定義可以使用的最大記憶體檢測類別數量。

使用Performance Schema進行SQL陳述式檢查

Performance Schema提供了一套豐富的檢測工具來檢查SQL陳述式的效能。要啟用陳述式檢測，需要啟用相應的檢測工具，如下表所示：

陳述式檢測工具及其描述

使用 Performance Schema 進行查詢最佳化

Performance Schema 是 MySQL 中一個非常有用的工具，用於監控和最佳化資料函式庫效能。其中，events_statements_history 和 events_statements_history_long 表格記錄了 SQL 陳述式的執行資訊，可以用來找出需要最佳化的查詢。

事件陳述式歷史記錄

events_statements_history 表格記錄了最近執行的 SQL 陳述式的詳細資訊，包括執行時間、鎖定時間、掃描行數等。以下是一個範例記錄：

THREAD_ID: 3200 EVENT_ID: 22 END_EVENT_ID: 23 EVENT_NAME: statement/sql/select SOURCE: init_net_server_extension.cc:94 TIMER_START: 878753511280779000 TIMER_END: 878753544491277000 TIMER_WAIT: 33210498000 LOCK_TIME: 657000000 SQL_TEXT: SELECT film.film_id, film.description FROM sakila.film INNER JOIN ( SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5 ) AS lim USING(film_id) DIGEST: 2fdac27c4a9434806da3b216b9fa71aca738f70f1e8888a581c4fb00a349224f DIGEST_TEXT: SELECT film . film_id , film . description FROM sakila . film INNER JOIN ( SELECT film_id FROM sakila . film ORDER BY title LIMIT?, … ) AS lim USING ( film_id )

最佳化指標

在 events_statements_history 表格中，有一些欄位可以用來識別需要最佳化的查詢。以下是一些重要的欄位：

CREATED_TMP_DISK_TABLES

該查詢建立了這個數量的磁碟臨時表。如果這個值大於零，可能需要最佳化查詢或增加記憶體臨時表的最大大小。

#### 內容解密：
- 當查詢需要建立臨時表時，MySQL會先在記憶體中建立，如果資料量太大，則會轉移到磁碟上。
- 磁碟臨時表的建立會影響查詢效能，因此需要檢查查詢是否可以最佳化。

SELECT_FULL_JOIN

如果 JOIN 操作沒有使用索引，則會進行全表掃描。如果表很大，這將是一個嚴重的效能問題。

#### 內容解密：
- 當 JOIN 操作沒有使用索引時，MySQL需要掃描整個表來找到匹配的資料。
- 這種情況通常發生在缺乏適當的索引或查詢條件不佳的情況下。

NO_INDEX_USED

如果查詢沒有使用索引，則這個值為 1。這通常是查詢效能不佳的跡象。

#### 內容解密：
- 索引是用於加速查詢的重要工具。
- 如果查詢沒有使用索引，則可能需要檢查查詢條件和索引設計。

識別需要最佳化的查詢

要找出需要最佳化的查詢，可以使用上述欄位來進行篩選。例如：

SELECT THREAD_ID, SQL_TEXT, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES, NO_INDEX_USED, NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_history_long
WHERE NO_INDEX_USED > 0 OR NO_GOOD_INDEX_USED > 0;

這個查詢會找出所有沒有使用索引或使用了不佳索引的查詢。

內容解密：

• Performance Schema 提供了一個完整的監控和最佳化工具集。
• 透過分析事件陳述式歷史記錄，可以找出需要最佳化的查詢並進行改進。
• 最佳化查詢可以提高資料函式庫的整體效能和穩定性。