您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL Performance Schema 核心配置表深度解析：五大 Setup 表與動態調優實戰

編輯：MySQL 綜合教學

MySQL Performance Schema 核心配置表深度解析：五大 Setup 表與動態調優實戰

Performance Schema 自 5.5 引入，5.6/5.7 持續增強，已成 DBA 效能診斷核心工具。本文聚焦五大 setup_* 配置表，解析層級消費者關係、儀器分類、對象過濾與計時器選型，並提供 my.cnf 持久化配置範例。

五大配置表總覽

mysql> SHOW TABLES LIKE '%setup%';
+----------------------------------------+
| Tables_in_performance_schema (%setup%) |
+----------------------------------------+
| setup_actors                           |
| setup_consumers                        |
| setup_instruments                      |
| setup_objects                          |
| setup_timers                           |
+----------------------------------------+

1. setup_actors — 使用者/主機維度過濾

SELECT * FROM setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| %    | %    | %    |  -- 預設監控所有線程
+------+------+------+

修改此表可限制特定用戶/主機的監控範圍。

2. setup_consumers — 事件消費者層級體系（核心）

SELECT * FROM setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | NO      |
| events_stages_history          | NO      |
| events_stages_history_long     | NO      |
| events_statements_current      | YES     |
| events_statements_history      | NO      |
| events_statements_history_long | NO      |
| events_waits_current           | NO      |
| events_waits_history           | NO      |
| events_waits_history_long      | NO      |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+

消費者層級關係樹（父啟用才會檢查子）

global_instrumentation (根)
  ├─ thread_instrumentation
  ├─ events_waits_current
  │   ├─ events_waits_history
  │   └─ events_waits_history_long
  ├─ events_stages_current
  │   ├─ events_stages_history
  │   └─ events_stages_history_long
  ├─ events_statements_current
  │   ├─ events_statements_history
  │   └─ events_statements_history_long
  └─ statements_digest

實戰原則：欲啟用底層 consumer，必須確保所有祖先均為 YES。預設開啟 global_instrumentation、thread_instrumentation、statements_digest，僅收集全局與線程級統計。

3. setup_instruments — 儀器點位配置（4 大類）

SELECT LEFT(name, 20) AS category, COUNT(*) 
FROM setup_instruments GROUP BY LEFT(name, 5);
+---------------------+----------+
| category            | count(*) |
+---------------------+----------+
| idle                | 1        |
| stage/sql/After cr  | 111      |  -- 語句執行階段
| statement/sql/sel   | 170      |  -- SQL 語句維度
| wait/synch/mutex/s  | 296      |  -- 等待事件（鎖、IO、同步）
+---------------------+----------+

idle：Socket 空閒等待
stage/：語句執行階段（如 Sending data、Sorting result）
statement/：SQL 類型統計（SELECT、INSERT 等）
wait/：底層等待（mutex、rwlock、cond、file IO、socket IO）

4. setup_objects — 監控對象白名單

SELECT * FROM setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| TABLE       | mysql              | %           | NO      | NO    |
| TABLE       | performance_schema | %           | NO      | NO    |
| TABLE       | information_schema | %           | NO      | NO    |
| TABLE       | %                  | %           | YES     | YES   |  -- 預設監控所有使用者資料庫
+-------------+--------------------+-------------+---------+-------+

生產環境建議縮小範圍，僅監控核心業務庫表，減少開銷。

5. setup_timers — 計時器單位選型

SELECT * FROM setup_timers;
+-----------+-------------+
| NAME      | TIMER_NAME  |
+-----------+-------------+
| idle      | MICROSECOND |
| wait      | CYCLE       |  -- 等待事件頻繁，用 CPU 週期最輕量
| stage     | NANOSECOND  |
| statement | NANOSECOND  |
+-----------+-------------+

最終統計表統一換算為皮秒。CYCLE 依賴 CPU 主頻，跨機器比較需注意。

動態修改與持久化配置

運行時動態修改（即時生效，重啟失效）

-- 開啟 wait 類儀器
UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'wait/%';

-- 開啟 events_waits_history consumer（需確保祖先啟用）
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME IN ('global_instrumentation','thread_instrumentation','events_waits_current','events_waits_history');

my.cnf 持久化配置（5.6.4+）

[mysqld]
# 1. 儀器點位
performance_schema_instrument = 'wait/%=ON'
performance_schema_instrument = 'stage/%=ON'
performance_schema_instrument = 'statement/%=ON'

# 2. 消費者
performance_schema_consumer_events_waits_current = ON
performance_schema_consumer_events_waits_history = ON
performance_schema_consumer_events_statements_history_long = ON

# 3. 歷史表大小
performance_schema_events_waits_history_size = 20
performance_schema_events_waits_history_long_size = 15000