MySQL 5.8 Performance Schema 配置详解

不惑_

于 2024-09-23 10:13:11 发布

阅读量1.1k

点赞数 30

分类专栏：实战系列文章标签： mysql 数据库

本文链接：https://blog.csdn.net/u012263509/article/details/142258586

版权

实战系列专栏收录该内容

67 篇文章 0 订阅

订阅专栏

请在此添加图片描述

MySQL 5.8 Performance Schema 配置详解

MySQL 的 Performance Schema 是一个用于监控和优化数据库性能的子系统，专门用来收集 MySQL 服务器的运行情况和性能指标。它的核心原理是通过“生产者”和“消费者”的概念来采集和存储数据库中的事件信息，帮助开发者和数据库管理员分析并优化系统性能。

本文将详细介绍如何在 MySQL 5.8 中配置 Performance Schema，涵盖编译时配置、启动时配置以及运行时配置。为了让大家更容易理解，我还会加入具体的操作示例。

基本概念

在介绍配置细节之前，首先理解两个关键概念：

Instruments（生产者）: 这些组件负责采集 MySQL 中各种操作产生的事件信息。它们是性能数据的生产者，例如对文件操作、锁等待、SQL 执行等进行记录。
Consumers（消费者）: 这些组件负责存储从生产者那里收集到的性能数据，并在需要时供用户查询。消费者是性能数据的存储单元，例如存储各类事件的历史记录。

编译时配置

当我们从源代码编译 MySQL 时，Performance Schema 的一些功能模块可以通过编译选项进行定制。如果有特定的需求（例如只监控某些类型的事件），可以通过 cmake 选项来选择要包含或排除哪些监控功能。

使用 cmake 选项指定是否开启 Performance Schema 的某些功能。例如，如果我们希望排除某些监控功能，可以执行以下命令：

cmake . -DWITHOUT_PERFORMANCE_SCHEMA=1

注意：

一般情况下，不建议手动禁用 Performance Schema 的模块，除非明确知道这些功能不会被使用。在大多数情况下，MySQL 默认配置的 Performance Schema 足以应对大部分场景。

如果已经安装好 MySQL 并不确定是否支持 Performance Schema，可以使用以下命令验证：

mysqld --help --verbose | grep performance_schema

或者登录 MySQL 实例后，使用以下命令检查：

SHOW ENGINES;

请在此添加图片描述

如果输出中显示 performance_schema，则说明 MySQL 支持这个功能。

启动时配置

Performance Schema 的配置在 MySQL 启动时生效，这意味着任何在运行过程中收集的数据都会保存在内存中，一旦 MySQL 实例停止，这些数据会丢失。因此，想要持久化配置项，我们可以在 MySQL 配置文件（通常为 my.cnf）中添加相关配置，确保每次启动时自动加载这些配置。

启动选项

MySQL 启动时，有一系列与 Performance Schema 相关的配置选项，它们可以控制哪些事件采集器和数据消费者在启动时启用。虽然这些配置在启动之后不能通过 SHOW VARIABLES 命令查看，但可以在 MySQL 配置文件中定义这些选项。

常见的启动选项有以下几种：

performance_schema_consumer_events_statements_current=TRUE

这个选项在 MySQL 启动时会启用 events_statements_current 表，用于记录当前正在执行的 SQL 语句信息。通过此功能，我们可以实时监控 MySQL 中执行的语句。

[mysqld] 
performance_schema_consumer_events_statements_current=TRUE

performance_schema_consumer_events_statements_history=TRUE

记录 SQL 语句事件的历史信息。与 current 不同，history 表会保存已经执行完毕的 SQL 语句事件。

performance_schema_consumer_global_instrumentation=TRUE

启用全局事件的监控，比如文件操作、锁等待、线程等。

performance_schema_instrument[=name]

通过该选项，可以指定具体的 instruments（生产者）要监控哪些事件，支持通配符。例如，如果只想监控文件操作，可以这样配置：

[mysqld] 
performance_schema_instrument='wait/io/file/%'

这些选项在 MySQL 启动时被解析和加载。确保在配置文件中设置 performance_schema=ON，否则这些选项不会生效。

系统变量

`Performance Schema` 提供了多种系统变量，用于控制消费者表的存储限制和性能监控的细节。这些系统变量只能在 MySQL 启动时设置，且在启动之后不可更改。

常见的系统变量包括：

performance_schema=ON

这是控制 Performance Schema 是否启用的开关。自 MySQL 5.7 版本起，默认启用此功能。

performance_schema_digests_size=10000

该变量控制 events_statements_summary_by_digest 表的最大行数。如果摘要数据超过该行数，将停止记录新的摘要信息。

[mysqld] 
performance_schema_digests_size=10000

performance_schema_max_sql_text_length=1024

控制 SQL 语句在 SQL_TEXT 列中保存的最大长度。如果 SQL 语句太长，会被截断。适当增加这个值可以提高 SQL 语句的完整性。

performance_schema_events_statements_history_size=10

控制每个线程的 events_statements_history 表中最大保存的历史事件数。超过该数目时，最早的记录会被覆盖。

运行时配置

MySQL 在运行时允许我们动态调整 Performance Schema 的设置，这通过 setup_instruments 和 setup_consumers 表来实现。可以动态启用或禁用特定的事件监控器或消费者。例如，如果想要开始监控某个特定的文件操作，可以执行以下 SQL 语句：

UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/io/file/sql/handler';

同样的，如果不再需要某些数据的采集，可以通过以下命令禁用：

UPDATE setup_instruments SET ENABLED = 'NO' WHERE NAME = 'wait/io/file/sql/handler';

这些更改会立即生效，而不需要重新启动 MySQL 实例。

总结

MySQL 5.8 的 Performance Schema 是一个非常强大的工具，可以帮助开发者和管理员深入分析数据库的运行状态，并优化性能。通过了解如何配置和调整 Performance Schema，可以更好地利用这些功能进行性能调优。在实际运维过程中，推荐结合实际业务需求合理调整 Performance Schema 的配置，以达到最佳的性能监控效果。