使用performance_schema代替profiling

最新推荐文章于 2025-12-03 14:39:35 发布
原创 最新推荐文章于 2025-12-03 14:39:35 发布 · 148 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

本文探讨了两种用于MySQL数据库性能分析的方法：Profile和Performance Schema。通过设置`set profiling=1`来启用Profile，可以显示每个查询的总时间和各阶段耗时。而Performance Schema则提供更详细的资源使用情况，帮助深入理解查询性能瓶颈。尽管使用时可能有警告，但这些工具对于优化数据库查询效率至关重要。

使用profile

set profiling=1;

启动profile

这是一个session级的配置

执行查询

show profiles;

查询每一个查询所消耗的总时间信息

show profile for query N;

查询的每个阶段所耗的时间

show profile cpu for query 1;

但是每次使用都会有一个warning

使用performance_schema

Server 层混杂信息字典表 | 全方位认识 information_schema
woqutechteam的博客
01-11 389
在上一篇《Server层表级别对象字典表 | 全方位认识 information_schema》中，我们详细介绍了information_schema系统库的表级别对象字典表，本期我们将为大家带来系列第四篇《Server层混杂信息字典表 | 全方位认识 information_schema》，下面请跟随我们一起开始information_schema系统库的系统学习之旅吧。   1. GLOB...
MySQL中的系统库
choice~的博客
06-26 2212
MySQL的performance_schema 是运行在较低级别的用于监控MySQL Server运行过程中的资源消耗、资源等待等情况的一个功能特性，它具有以下特点。采集的东西相对比较底层，比如磁盘文件、表I/O、表锁等等。•　performance_schema提供了一种在数据库运行时实时检查Server内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关数据。
使用Performance Schema查看Profiling
cuizeen198835的博客
04-12 304
SHOW PROFILE可以用来MySQL执行语句时候所使用的资源（诸如IO，上下文切换，CPU，Memory等等）,但是从MySQL 5.6.7开始此特性将被移除，而使用Performance Schema代替，如下： ...
mysql优化之profile和Performance Schema
liys0811的博客
07-20 150
好处是Performance Schema比show profile功能更全，当从SQL语句执行时间等的执行过程分析中无法找到原因进行优化时，用Performance Schema可以直接查看表中有哪些线程在运行，从每个线程的包含信息记录中找到耗时原因。通过select * from events_waits_current\G;具体可看：https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html。
MySQL performance schema性能分析实战
天然玩家的博客
08-09 1500
（1）Performance Schema用于监控SQL执行，如各阶段耗时、阶段的锁等待、索引访问、物理I/O和逻辑I/O等； （2）Performance Schema中的表是内存表，随MySQL服务器启动载入数据，关闭清除数据，资源消耗少，配置即时生效； （3）Performance Schema的异常不影响SQL的正常执行； （4）使用Performance Schema需要配置采集器和消费者，具体要开启：events_statements_history_long等。.........
MySQL SQL性能分析 Profiling Using Performance Schema
分享技术，分享知识
03-02 737
MySQL一直以来提供show profile命令来获取某一条SQL执行过程中的资源使用与耗时情况，这个命令对于分析具体SQL的性能瓶颈有非常大的帮助，但是这个功能在MySQL新的版本里将会被废弃，取而代之的是使用Performance Schema来提供同样的功能。本文将介绍如何使用Performance Schema来实现show profile SQL性能分析的功能。 原文地址： htt...
INFORMATION_SCHEMA.PROFILING
weixin_33711647的博客
06-05 396
INFORMATION_SCHEMA PROFILING Table PROFILING表提供了语句分析信息。 其内容对应于SHOW PROFILES和SHOW PROFILE语句生成的信息. INFORMATION_SCHEMA Name SHOW Name Notes QUERY_ID Query_ID 标识 SEQ 具有相同QUERY_ID值的行的显示顺序的序列号 S...
mysql自带的系统库mysql和performance_schema
诸君拔剑吧.
02-04 1231
终端登录mysql数据库，显示全部数据库（或者直接用客户端工具展示）,如下： 四个系统自带库为：information_schema、mysql、performance_schema、sys； 5.6版本自带的库为：information_schema、mysql、performance_schema、test。 一、mysql库 mysql的核心数据库，类似于sql server中的ma...
MySQL 之 performance_schema
fenglepeng的博客
12-20 511
背景： 自 MySQL5.5开始，MySQL新增了一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。此库中表的存储引擎均为PERFORMANCE_SCHEMA（用户不能创建存储引擎为PERFORMANCE_SCHEMA的表）。MySQL5.5默认是关闭此功能的，若要开启，需手动在配置文件里添加： [mysqld] performance_schema=ON ...
MySQL默认库PERFORMANCE_SCHEMA详细介绍
热门推荐
qq_33684555的博客
03-26 1万+
背景： MySQL 5.5开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。MySQL5.5默认是关闭的，需要手动开启，在配置文件里添加： [mysqld] performance_schema=ON 查看是...
MySQL5.6 PERFORMANCE_SCHEMA 说明
Data & Analysis
02-19 502
背景： MySQL 5.5开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。MySQL5.5默认是关闭的，需要手动开启，在配置文件里添加： [mysqld] performance_schema=ON 查看是否开启： mysql>show variables like 'performance_schema'...
使用mysql-proxy实现mysql的读写分离+mysql的日志查询
Logbook的博客
08-15 1192
MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于 读操作量比较大的场景，可减轻master的压力。 使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后 端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析， 判断出是读操作还是写操作，然后分发至对应的mysql服务器上。 实验环境： serv...
如何用 `SHOW PROFILE` 分析 SQL 执行阶段的锁等待时间？
04-09
首先需要确认用户的MySQL版本是否支持，因为较新的版本可能默认使用Performance Schema代替。然后，启用profiling，执行查询，再查看具体的profile信息。在分析结果时，重点查找与锁相关的字段，如Lock_time。 还要...
提高mysql效率
04-03
然后，SQL语句的优化也很关键，比如避免SELECT *，使用连接代替子查询，合理分页。还有执行计划分析，用EXPLAIN来查看索引使用情况，type列的值是否理想，比如ref、range，避免ALL类型。 服务器参数调优部分，需要...
MySQL中的TRUNCATE TABLE命令
2509_94196880的博客
11-30 287
在MySQL数据库管理中，命令是一个用于快速删除表中所有数据的重要工具。相比常规的DELETE命令，具有显著的性能优势和独特的操作特点。本文旨在深入探讨命令的用法、工作原理以及实际应用中的注意事项。MySQL中的命令是一个功能强大且高效的工具，理解其特性和使用场景有助于我们在日常数据库管理和维护工作中做出正确的决策。务必根据实际情况权衡其优势与风险，确保数据安全和系统稳定。
【MYSQL】连接报错Public Key Retrieval is not allowed
2509_94199560的博客
12-01 169
如果您控制 MySQL 用户的创建方式，可以使用 mysql_native_password 身份验证插件来避免这个错误。对于运行 MySQL 8.0 或更高版本的服务器，可以考虑修改 MySQL 配置，使默认身份验证插件为 mysql_native_password。找到并编辑 MySQL 配置文件（通常位于 /etc/mysql/my.cnf 或 /etc/my.cnf）。hostname: 您的 MySQL 服务器地址。方法 3: 修改 MySQL 配置文件。方法 2: 更改用户身份验证方法。
MySQL数据库的数据文件保存在哪？MySQL数据存在哪里
2509_94083514的博客
11-30 778
在安装好MySQL数据库使用一段时间后，会产生许多的数据库和数据。那这些数据库的数据文件存放在本地文件夹的什么位置呢一、默认位置MySQL创建的数据库实际上存储在文件系统的一个特定目录中，该目录通常为MySQL的数据目录。这个目录的默认位置依据操作系统和MySQL的安装方式不同而有所差异。、这个目录通常位于，它可能位于是个隐藏文件夹）**其中“X.X”是MySQL的版本号。目录（每个数据库都有一个与其同名的文件夹，而这个文件夹中包含了该数据库的所有表的文件，而其中的文件代表了数据库中的表。
【JAVA】AES加密
最新发布
随笔
12-03 206
【JAVA】AES加密
MySQL SQL100道基础练习题
2509_94088294的博客
11-30 391
select * from newtable where gz > (select max(gz) from newtable where dept_id = (select id from dept where dept_name = ‘财务部’));select name,(select dept_name from dept where id = dept_id) as ‘职位’,gz from newtable where name in (‘黄语焉’,‘李嘉欣’);
# performance_schema # performance_schema_digests_size = 35000 max_digest_length = 4096 performance_schema_max_digest_length = 4096 performance_schema_max_table_instances=35000
08-28
<think>嗯，用户想了解MySQL performance_schema的四个配置参数：performance_schema_digests_size、max_digest_length、performance_schema_max_digest_length和performance_schema_max_table_instances的含义、作用及优化建议。 用户可能是个DBA或开发人员，正在优化数据库性能。从问题来看，用户对这些参数已有基本了解，但需要更深入的解释和实操建议。这类配置参数通常涉及内存管理和性能监控的平衡，用户可能遇到了内存占用过高或监控数据丢失的问题。 先看第一个参数performance_schema_digests_size。它控制SQL语句摘要的最大存储数量，默认10000条。当系统执行大量不同SQL时，超出这个限制会导致旧摘要被覆盖。对于高并发系统，如果出现"摘要丢失"，应该适当调大这个值，比如20000-50000。但要注意内存消耗，每个摘要约占200字节。 第二个参数max_digest_length比较关键，它决定SQL语句摘要的最大长度，默认1024字节。如果SQL语句很长，超过这个长度会被截断，可能导致不同SQL被误认为相同。通常建议设置为2048，特别是当使用复杂存储过程或长查询时。但设置过大会增加内存开销。 第三个参数performance_schema_max_digest_length是MySQL 8.0新增的，专门控制performance_schema中的摘要长度。它覆盖max_digest_length的设置，更灵活。默认也是1024，优化建议同上。 第四个参数performance_schema_max_table_instances控制被监控表的实例数量上限，默认-1表示自动分配。当监控的表很多时（如分库分表环境），可能触发"无法创建监控"错误。建议根据实际表数量设置，比如分片环境下设为物理表的10倍左右。 内存优化方面，这四个参数都影响内存占用。建议用公式计算：总内存 ≈ (digests_size × max_digest_length) + (max_table_instances × 平均表监控大小)。调整后要通过show status监控摘要命中率和内存使用变化。 用户可能还需要知道如何验证调整效果，比如查询performance_schema中的摘要丢失统计，或用show engine performance_schema status查看内存分配。如果用户环境是MySQL 5.7，要提醒performance_schema_max_digest_length不可用。 最后考虑生成相关问题：用户下一步可能想了解如何监控摘要命中率，或者具体的内存计算公式，也可能需要知道生产环境中的常见配置案例。还应该提醒参数修改需要重启，以及8.0版本的新特性。</think>理解您对MySQL `performance_schema` 关键配置参数的深度需求。这些参数直接影响性能监控的粒度、内存消耗及数据准确性。以下是详细解释与优化建议： ### 一、参数详解与作用 1. **`performance_schema_digests_size`** * **含义：** 控制 `events_statements_summary_by_digest` 表能存储的唯一 SQL 语句摘要(`DIGEST`)的最大数量。 * **作用：** 该表汇总了按 SQL 语句摘要分组（即规范化后结构相同的 SQL）的性能统计信息（如执行次数、总耗时、错误次数等）。此参数限制了内存中能跟踪的不同 SQL 语句模式的数量。 * **优化建议：** * **默认值：** 通常为 `10000`。 * **调大场景：** 应用生成大量**不同** SQL 模式（如高并发动态查询、复杂 ORM 框架），且监控中发现 `events_statements_summary_by_digest` 表中有 `DIGEST` 被覆盖（旧的摘要信息丢失）。增大此值能保留更多 SQL 模式的统计信息。 * **调小场景：** 内存资源紧张，且监控的 SQL 模式相对固定有限。但调小可能导致频繁的摘要覆盖，丢失重要慢查询或高频率查询的统计。 * **计算公式（估算）：** 内存消耗 ≈ `performance_schema_digests_size` * `performance_schema_max_digest_length` * 2 (考虑存储开销)。例如，`10000 * 1024 * 2 ≈ 20MB`。 2. **`max_digest_length`** * **含义：** 控制计算 SQL 语句摘要(`DIGEST`)时，用于规范化处理的原始 SQL 文本的最大长度（字节）。 * **作用：** 影响 `performance_schema` 和 `sys schema` 中 SQL 摘要的生成。超过此长度的 SQL 文本会被截断后再计算摘要。这决定了 SQL 语句能被识别的“精细度”。 * **优化建议：** * **默认值：** 通常为 `1024`。 * **调大场景：** 应用存在**非常长**的 SQL 语句（如复杂报表查询、大 IN 列表），且监控发现不同的长 SQL 因截断后摘要相同而被误汇总。增大此值（如 `2048`, `4096`）可提高摘要区分度，避免误聚合。 * **调小场景：** 几乎不推荐。调小会导致更多不同的长 SQL 被截断后计算成相同的摘要，严重降低统计准确性。 * **内存影响：** 直接影响 `performance_schema_digests_size` 所需内存（见上面公式）。增大 `max_digest_length` 会线性增加内存消耗。 3. **`performance_schema_max_digest_length` (MySQL 5.6.26+, 5.7.8+)** * **含义：** 专门控制 `performance_schema` 内部用于计算和存储 SQL 摘要的最大长度（字节）。**优先级高于 `max_digest_length`**。 * **作用：** 允许为 `performance_schema` 的摘要计算单独设置一个不同于全局 `max_digest_length` 的长度限制。 * **优化建议：** * **默认值：** 通常等于 `max_digest_length` (如 `1024`)。 * **调大场景：** 同 `max_digest_length` 的调大场景，但更灵活。可以在不影响其他可能使用 `max_digest_length` 的地方（如查询缓存、复制过滤等，虽然 MySQL 8.0 已移除查询缓存）的情况下，仅增大 `performance_schema` 的摘要长度。 * **调小场景：** 同 `max_digest_length`，不推荐。 * **与 `max_digest_length` 关系：** `performance_schema_max_digest_length` 的值不能大于 `max_digest_length`。如果设置 `performance_schema_max_digest_length` > `max_digest_length`，MySQL 会自动将其调整为等于 `max_digest_length`。 4. **`performance_schema_max_table_instances`** * **含义：** 控制 `performance_schema` 可以同时监控的表对象（打开的表句柄）的最大数量。 * **作用：** 限制 `table_handles` 和 `table_io_waits_summary_by_table` 等表相关的监控表能跟踪的表实例数量。一个表在内存中打开多次（如被不同线程访问）会被视为多个实例。 * **优化建议：** * **默认值：** 通常为 `-1` (自动调整，基于 `max_connections` 或其他启发式) 或一个固定值（如 `10000`）。 * **调大场景：** 数据库有**大量**表（例如分库分表环境），或者存在大量临时表操作，且监控中发现 `performance_schema` 错误日志提示 `"Cannot create instrument for table ..."` 或相关监控表数据缺失。增大此值确保所有需要监控的表都能被跟踪。 * **调小场景：** 内存资源极其紧张，且确信不需要监控那么多表实例。调小可能导致部分表无法被监控，丢失 I/O 等待、锁等待等重要性能数据。 * **内存影响：** 每个表实例监控需要少量内存（几十到几百字节）。监控 `N` 个实例消耗内存 ≈ `N * 200 bytes` (估算值)。`10000` 个实例约 `2MB`。 ### 二、综合优化建议与配置策略 1. **核心原则：平衡监控粒度与内存消耗。** `performance_schema` 启用后本身有内存开销，这些参数直接决定了特定监控维度的内存占用上限。 2. **监控先行，按需调整：** * 初始使用默认值。 * 监控 `performance_schema` 的状态： ```sql -- 查看摘要相关状态，关注DIGEST_LOST数量是否持续增长 SHOW GLOBAL STATUS LIKE 'Performance_schema_digest_lost'; SHOW GLOBAL STATUS LIKE 'Performance_schema%lost%'; -- 查看表实例相关状态，关注`performance_schema_max_table_instances`是否足够 SHOW GLOBAL STATUS LIKE 'Performance_schema_table_instances_lost'; ``` * 如果 `*_lost` 计数器持续增长，说明相应的限制（`digests_size` 或 `max_table_instances`）已不足，需要调大对应参数。 3. **内存估算与分配：** * 使用前面提到的公式估算 `digests` (`performance_schema_digests_size * performance_schema_max_digest_length * 2`) 和 `table instances` (`performance_schema_max_table_instances * 200`) 的大致内存需求。 * 确保服务器的 `performance_schema` 总内存分配 (`performance_schema_max_memory_classes` 等参数控制，或通过 `SHOW ENGINE PERFORMANCE_SCHEMA STATUS;` 查看实际使用) 足够容纳调整后的参数需求。必要时增加 `performance_schema_max_memory_classes` 等全局内存限制参数[^1][^4]。 4. **针对性调整：** * **高动态 SQL 环境：** 优先关注 `performance_schema_digests_size` 和 `performance_schema_max_digest_length` (或 `max_digest_length`)。确保 `DIGEST_LOST` 接近 0。如果长 SQL 多且需要精确区分，适当增大 `max_digest_length` (或 `performance_schema_max_digest_length`)。 * **大量表/分片环境：** 优先关注 `performance_schema_max_table_instances`。确保 `TABLE_INSTANCES_LOST` 接近 0。 5. **版本差异：** MySQL 5.6 及更早版本可能没有 `performance_schema_max_digest_length`，此时只能调整 `max_digest_length`[^3]。 6. **修改方法：** 这些参数大多是只读的，需要在 `my.cnf` / `my.ini` 配置文件中修改，然后重启 MySQL 实例生效。部分参数在 MySQL 8.0+ 可能是动态参数（通过 `SET GLOBAL` 修改），需查阅对应版本官方文档确认[^2]。 7. **监控验证：** 调整参数并重启后，再次监控 `*_lost` 状态计数器和 `SHOW ENGINE PERFORMANCE_SCHEMA STATUS;` 的输出，确认调整是否有效且内存使用在可接受范围内。 **总结：** 这四个参数是精细控制 `performance_schema` 监控 SQL 语句和表对象的关键杠杆。调整的核心依据是监控到的 `*_lost` 状态和实际业务需求（SQL 复杂度、表数量），目标是在可控的内存开销下，确保关键的性能指标（SQL 执行统计、表 I/O 等待）不被丢失。务必结合内存估算和服务器资源进行配置[^1][^2][^3][^4]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值