掌握MySQL性能监控 · performance_schema 使用快速入门

请在此添加图片描述

performance_schema 使用快速入门

在数据库性能调优的过程中，performance_schema 是一个非常有用的工具，它可以帮助我们深入分析 MySQL 内部的性能表现。通过合理使用 performance_schema，我们可以发现数据库中的性能瓶颈并做出优化。本文将以 MySQL 5.8 为例，详细介绍如何使用 performance_schema。

检查数据库是否支持 performance_schema

在 MySQL 5.8 中，performance_schema 默认已启用，可以直接开始使用它。但是，为了确保其正常工作，首先我们需要检查当前数据库实例是否支持 performance_schema。

可以使用以下两种方法来确认：

使用 INFORMATION_SCHEMA.ENGINES 表查询：

SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE = 'PERFORMANCE_SCHEMA';

请在此添加图片描述

使用 SHOW ENGINES 命令：

SHOW ENGINES;

请在此添加图片描述

在查询结果中，若 PERFORMANCE_SCHEMA 的 Support 字段值为 YES，则表明该功能在当前数据库中可用。

如果执行 SHOW ENGINES，结果如下：

+--------------------+---------+------------------------+
| Engine             | Support | Comment                |
+--------------------+---------+------------------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema     |
+--------------------+---------+------------------------+

这意味着 performance_schema 已启用，可以开始使用它来分析数据库性能。

启用或关闭 `performance_schema`

虽然 MySQL 5.8 默认启用 performance_schema，但在某些情况下，可能需要手动启用或关闭它。这可以通过配置文件或 SQL 语句来完成。

在配置文件中启用或关闭 **performance_schema**打开 MySQL 的配置文件 my.cnf，添加以下配置来显式

启用：performance_schema=ON 或关闭：performance_schema=OFF

启动 MySQL 后查看启用状态 启动 MySQL 之后，可以通过下面的语句确认 performance_schema 是否成功启用：

SHOW VARIABLES LIKE 'performance_schema';

请在此添加图片描述

如果返回值为 ON，则说明 performance_schema 已成功启用；如果为 OFF，则表示功能未启用。

SQL 语句临时修改 performance_schema 的状态（而不是永久修改配置文件），可以使用以下命令：

SET GLOBAL performance_schema = ON;

示例：执行以下 SQL 语句：SHOW VARIABLES LIKE ‘performance_schema’;

+-------------------+-------+
| Variable_name      | Value |
+-------------------+-------+
| performance_schema | ON    |
+-------------------+-------+

这里的 ON 表示 performance_schema 已启用。

performance_schema 表的分类

performance_schema 是由一系列表组成的，这些表主要用于收集数据库运行过程中的各种性能数据。为了更方便地使用，这些表按照不同维度进行分类。了解这些表的分类和用途，可以帮助在实际使用中迅速找到所需的信息。

以下是常用的 performance_schema 表分类：

语句事件记录表：

events_statements_current：记录当前执行的 SQL 语句。
events_statements_history：记录最近执行过的 SQL 语句，最多保存 10 条历史记录。
events_statements_history_long：记录更长时间的 SQL 语句历史，最多保存 10000 条记录。
相关 summary 表：这些汇总表提供了 SQL 语句的执行情况摘要，可以根据用户、主机、线程等维度进行分析。

等待事件记录表：

这些表用于记录等待事件的详细信息，如线程在等待 I/O 操作或锁时的情况。与语句事件记录表类似，也有 current 和 history 表。

阶段事件记录表：

记录 SQL 语句执行过程中各个阶段的细节，如优化、执行、提交等阶段的情况。

事务事件记录表：

专门记录与事务相关的事件，如事务开始、提交、回滚等操作。

示例：可以通过以下 SQL 查询语句，找出占用执行时间最长的 SQL 语句：

SELECTDIGEST_TEXT,COUNT_STAR,SUM_TIMER_WAIT
FROMPERFORMANCE_SCHEMA.events_statements_summary_by_digest
ORDER BYSUM_TIMER_WAIT DESC
LIMIT 5;

这个查询可以帮助快速定位哪些 SQL 语句是系统的性能瓶颈，特别是哪些 SQL 语句执行得最慢、执行次数最多。

请在此添加图片描述

注意：

如果在查询时收到“Table ‘PERFORMANCE_SCHEMA.events_statements_summary_by_digest’ doesn’t exist”的错误消息，可能有以下几个原因：

performance_schema 未启用：

确保 performance_schema 已经启用。可以通过以下命令检查其状态：

SHOW VARIABLES LIKE 'performance_schema';

如果返回结果显示 Value 为 OFF，则需要启用它。可以通过编辑 MySQL 配置文件或在运行时使用 SQL 命令来启用它。

采集器未启用：

即使 performance_schema 已启用，某些表也可能因为相关的采集器未启用而不可用。需要确保相关的采集器和消费者已经启用。例如，要启用语句事件的采集器，可以运行以下命令：

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

消费者未启用：

同样，确保相关的消费者也已经启用。例如，要启用语句事件的消费者，可以运行以下命令：

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements_summary_by_digest%';

MySQL 版本问题：

确保使用的 MySQL 版本支持 events_statements_summary_by_digest 表。这个表在较新的 MySQL 版本中才可用。可以通过以下命令检查 MySQL 版本：

SELECT VERSION();

performance_schema 的配置与使用

默认情况下，MySQL 不会启用所有的事件采集器（instruments）和数据消费者（consumers）。需要手动配置，以便收集所需的性能数据。

**启用采集器：**如果希望监控某个特定的事件（比如等待事件），可以通过以下 SQL 语句来打开相关采集器：

UPDATE PERFORMANCE_SCHEMA.setup_instruments
SET ENABLED = 'YES',TIMED = 'YES'
WHERENAME LIKE 'wait/%';

**启用数据保存：**同样，还需要打开消费者来保存相关的事件数据：

UPDATE PERFORMANCE_SCHEMA .setup_consumers
SET ENABLED = 'YES'
WHERENAME = 'events_waits_current';

**查询事件表：**配置完成后，可以通过查询事件表来查看性能数据。例如，查看当前的等待事件：

SELECT * FROM performance_schema.events_waits_current;

请在此添加图片描述

示例：如果想查看所有等待事件的汇总信息，可以运行以下 SQL 查询：

SELECTEVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT
FROMPERFORMANCE_SCHEMA .events_waits_summary_global_by_event_name
ORDER BYSUM_TIMER_WAIT DESC;

请在此添加图片描述

这将列出所有等待事件，并按等待时间总和排序，帮助识别哪些操作是导致性能瓶颈的主要原因。

小结

performance_schema 是 MySQL 提供的强大性能监控工具，特别是在 MySQL 5.8 中，它默认启用且功能强大。通过合理配置 performance_schema，可以详细了解数据库的运行状况，识别性能瓶颈并优化系统。无论是分析 SQL 语句、等待事件还是事务执行情况，performance_schema 都能提供精确的数据支持，是进行数据库性能调优的利器。

最后，虽然 performance_schema 提供了大量的性能数据，但我们通常不会直接操作这些表，而是借助 sys schema 下的视图来获取性能报告。sys schema 的数据主要来源于 performance_schema 和 INFORMATION_SCHEMA，因此熟悉 performance_schema 是高效使用 sys schema 的基础。