如何启用Performance Schema并验证其状态？

Question

如何启用Performance Schema并验证其状态？ 在MySQL中，默认情况下Performance Schema可能未完全启用。如何通过配置文件或运行时参数正确启用Performance Schema？启用后，如何通过查询`performance_schema`数据库中的相关表（如`setup_instruments`和`setup_consumers`）以及使用`SHOW VARIABLES LIKE 'performance_schema'`来验证其是否已成功启动？此外，若发现某些功能未生效，可能的原因有哪些（如内存限制、权限不足或版本支持问题）？

Answer 1

一、Performance Schema 简介与启用背景

MySQL 的 Performance Schema 是一个用于监控数据库服务器运行时性能的内置功能模块。它从 MySQL 5.5 版本开始引入，并在后续版本中不断增强。与传统的慢查询日志或通用日志不同，Performance Schema 提供了低开销的实时性能数据采集能力，涵盖线程、锁、文件 I/O、内存使用、SQL 执行等多个维度。

然而，默认情况下，某些 MySQL 发行版（如部分云厂商定制版本或默认配置）可能未完全启用 Performance Schema，或者仅启用核心组件。因此，在进行深度性能分析前，必须确认其已正确激活并配置。

二、启用 Performance Schema 的方法

启用 Performance Schema 可通过两种方式实现：配置文件设置和运行时动态参数调整。推荐优先使用配置文件以确保重启后持久生效。

1. 通过配置文件启用（my.cnf 或 my.ini）

编辑 MySQL 配置文件，在 [mysqld] 段落中添加或修改以下参数：

[mysqld]
performance_schema = ON
performance_schema_instrument = 'wait/%=ON'
performance_schema_consumer_events_waits_current = ON
performance_schema_consumer_events_waits_history = ON
performance_schema_consumer_events_waits_history_long = ON
    

上述配置含义如下：

• performance_schema = ON：启用 Performance Schema 引擎。
• performance_schema_instrument：启用所有等待类事件（如 mutex、file io 等）。
• consumer 参数控制事件是否被记录到对应的消费者表中。

2. 运行时参数查看与临时启用

若无法立即重启服务，可先检查当前状态：

SHOW VARIABLES LIKE 'performance_schema';

返回结果示例：

Variable_name	Value
performance_schema	ON

注意：该变量为只读，无法通过 SET GLOBAL 修改，必须在配置文件中设定并重启生效。

三、验证 Performance Schema 是否成功启用

即使变量显示为 ON，仍需进一步验证实际运行状态及功能完整性。

1. 查询 setup_instruments 表确认仪器启用情况

SELECT NAME, ENABLED, TIMED 
FROM performance_schema.setup_instruments 
WHERE NAME LIKE 'wait/synch/%mutex/sql/%' 
LIMIT 5;

关键字段解释：

• NAME：监控项名称，如文件 I/O、互斥锁等。
• ENABLED：是否启用采集。
• TIMED：是否采集时间信息。

2. 检查 consumers 配置状态

SELECT * FROM performance_schema.setup_consumers WHERE ENABLED = 'YES';

常见 consumers 包括：

Consumer Name	Purpose
events_waits_current	当前等待事件
events_waits_history	每个线程最近的N个等待事件
events_waits_history_long	全局历史等待事件缓冲区
file_summary_by_event_name	按事件汇总文件I/O统计
table_io_waits_summary_by_table	表级I/O等待统计
global_instrumentation	全局仪器开关控制

3. 实际观测运行数据

执行以下查询以确认是否有活跃数据流入：

SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT 
FROM performance_schema.events_waits_summary_global_by_event_name 
WHERE COUNT_STAR > 0 
ORDER BY SUM_TIMER_WAIT DESC 
LIMIT 10;

若有非零计数返回，则说明 Performance Schema 正常工作。

四、常见问题排查与原因分析

即便配置正确，仍可能出现“看似启用但无数据”的现象。以下是典型故障点及诊断路径。

1. 内存资源限制

Performance Schema 使用预分配的内存池，若相关参数过小会导致功能受限。

SHOW VARIABLES LIKE 'performance_schema_%size%';

重点关注：

• performance_schema_events_waits_history_size
• performance_schema_events_waits_history_long_size
• performance_schema_max_mutex_instances

建议根据实例负载适当调高这些值（需在配置文件中设置）。

2. 权限不足

用户需具备 PROCESS 和 SELECT 权限访问 performance_schema 数据库：

GRANT SELECT ON performance_schema.* TO 'monitor'@'%';

3. MySQL 版本兼容性问题

不同版本对 Performance Schema 支持程度差异较大：

MySQL Version	P_S Default State	Notes
5.5	OFF	需手动开启，功能有限
5.6	ON (partial)	部分 instruments 默认关闭
5.7+	ON (recommended)	支持自动配置
8.0+	ON by default	增强自动调优机制

4. 动态 Instrument 控制

可通过 SQL 动态启用特定监控项：

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE 'statement/sql/%';

五、自动化检测流程图

以下为判断 Performance Schema 是否正常工作的决策流程：

graph TD
    A[开始] --> B{performance_schema = ON?}
    B -- 否 --> C[修改my.cnf并重启]
    B -- 是 --> D[查询setup_instruments]
    D --> E{是否有ENABLED=YES的instrument?}
    E -- 否 --> F[批量启用instruments]
    E -- 是 --> G[检查consumers是否启用]
    G --> H{events_waits_current等是否启用?}
    H -- 否 --> I[启用对应consumer]
    H -- 是 --> J[查询events_waits_summary_global_by_event_name]
    J --> K{返回非零数据?}
    K -- 否 --> L[检查内存参数/版本兼容性]
    K -- 是 --> M[Performance Schema正常运行]
    

六、高级配置建议

对于生产环境，建议结合监控系统（如 Prometheus + mysqld_exporter）定期采集 Performance Schema 指标。同时，避免过度启用 instruments，以防性能损耗。

可使用如下脚本批量管理 instruments：

-- 启用所有statement类监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE 'statement/%';

-- 启用所有wait事件
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'wait/%';

此外，MySQL 8.0 提供了 performance_schema_setup_timers 表用于调整时间基准，以及更细粒度的内存 instrumentation 支持。