当前位置:网站首页>MySQL performance schema性能分析实战
MySQL performance schema性能分析实战
2022-08-10 22:22:00 【天然玩家】
1 缘起
最近的某一天,我在某平台看到一篇文章,是关于SQL优化的,
其中,讲到如何查看SQL执行情况,常用的工具有EXPLAIN查看执行计划,
profiling查看执行时间,于是,我打算实战一下,
我使用的MySQL版本为5.7.34,发现,配置profiling属性时,提示该方法将来会被移除,
现在已处于Deprecated状态(不过还能用),但是,我并没有尝试使用,
因为,MySQL官方既然废弃profiling功能,肯定会有替代的方案,
于是,开始搜索,找到了替代方案:Performance Schema(MySQL5.6.x开始支持),
当我搜索中文如何使用Performance Schema时,只有一点点理论上的语句,
并没有完整的测试案例,如通过Performance Schema检测具体某一条SQL的执行时间,
所以,参考官网文档,理论和实践相结合,使用Performance Schema实战监控SQL相关执行过程,
分享如下,提供实践参考案例,帮助读者理解和使用Performance Schema。
官方文档:https://dev.mysql.com/doc/refman/5.6/en/performance-schema.html
2 Performance Schema
2.1 特点
- Performance Schema提供监测服务器在运行时执行情况服务。Performance Schema通过PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现监控功能。Performance Schema主要关注数据性能,而INFORMATION_SCHEMA主要关注元数据。
- Performance Schema监控服务器事件。“事件”即服务器需要耗时完成并且可以采集到时间的任何事件。通常,事件可以是函数调用、操作系统等待、SQL描述执行阶段(如解析、排序或者完整的SQL描述、一组SQL描述)。事件采集为服务器以及多个存储引擎提供对同步调用(如互斥)文件或表I/O,表锁等信息的访问。
- Performance Schema事件不同于写入服务器的binary日志事件(日志事件描述数据变更)和事件调度器事件(调度器事件是程序存储类型)。
- Performance Schema事件指向确定的MySQL服务器。Performance Schema表被当做服务器本地表,这些表的变更不会复制或写入binary日志。
- 当前事件是可得到的,如历史记录和统计事件。这可以开发人员决定采集数据次数以及采集数据的时长。事件信息可用于展示线程活跃数或者关联的活跃对象。
- PERFORMANCE_SCHEMA存储引擎通过服务器源码中的“instrumentation points”采集事件数据。
- 采集的事件数据存储在performance_schema数据库中,数据查询与普通的表查询一样,通过SELECT。
- Performance Schema动态配置可以直接更新对应的表,如上所述,可直接使用UPDATE更新,即时生效。
- Performance Schema中的表是不使用持久化磁盘存储的内存表。表内容在服务启动时填充,服务关闭时清除。
- MySQL支持的所有平台均可查看监控。
- 数据采集通过修改服务器源码添加采集器。Performance Schema与数据复制或时间调度等不同的地方是没有单独的线程。
2.2 设计规则
Performance Schema为提供访问服务器执行信息而设计,同时消耗最少的服务器资源,具体设计原则如下:
- 激活Performance Schema不会改变任何服务器功能,如,不会改变定时线程,不会改变执行计划。
- 除了服务器启动期间产生内存分配外,其他时间不进行内存分配。早期通过固定内存尺寸分配架构,不需要对内存扩缩容或重新分配,这对于实现运行时高性能是至关重要的。
- 服务器监控开销小,持续且无感知地进行。激活Performance Schema不影响服务可用性。
- 不改变解析器。不产生新的关键字或者描述。
- Performance Schema即使出现异常也不能影响服务器代码的正常运行。
- 在最初事件采集或后面进行的事件检索之间选择处理时,优先考虑加快采集速度,因为,采集一直存在,然而,检索是按需进行,也可能不需要。
- 方便添加采集点。
- 采集器版本化(兼容性)。如果采集器更新,之前的代码应该继续正常运作。这有利于第三方开发者,因为不需要与Performance Schema最新版始终保持一致。
3 配置
3.1 查看版本
-- 查看MySQL版本信息
SHOW variables like "%version%";
3.2 配置profiling
3.2.1 查看profiling状态
-- 查看profile
SHOW variables like "%prof%";
profiling为OFF,此时无法查看SQL执行时间。
3.2.2 开启profiling支持
-- 开启profiling
set profiling=1;
开启profiling之后,发现,该MySQL版本(5.7.34)提示profiling已标识为deprecated,未来MySQL版本将移除该功能,如下图所示。
于是,搜索了一下相关信息,在Github上发现profile未来将被删除,使用Performance Schema替代,github截图如下图所示。
https://github.com/phpmyadmin/phpmyadmin/issues/15209
3.3 Performance Schema
3.3.1 查看performance schema状态
-- 查看performance schema
SHOW VARIABLES LIKE 'performance_schema';
由结果可知,performance schema默认开启。
3.3.2 wait配置
- 查看采集器wait
-- 查询等待事件采集器状态
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE 'wait%';
- 开启消费者wait属性
-- 开启等待事件采集器
UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE 'wait%';
- 查看消费者wait
-- 查询等待事件消费者状态
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%wait%';
- 开启消费者wait
-- 开启等待事件消费者
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE '%wait%';
3.3.3 采集器描述
- 查看采集器描述
-- 查询
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%statement/%';
- 开启采集器描述
-- 更新
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';
3.3.4 采集器阶段
- 查看采集器阶段
-- 查询
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%stage/%';
- 开启采集器阶段
-- 更新
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%stage/%';
3.3.4 消费者事件描述
- 查看消费者事件描述
-- 查看消费者事件状态
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%events_statements_%';
- 开启消费者事件描述
-- 开启消费者事件状态
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_statements_%';
3.3.5 消费者事件阶段
- 查看消费者事件阶段
-- 查看alter
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%events_stages_%';
- 开启消费者事件阶段
--
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_stages_%';
4 实战
4.1 查询
-- 查询测试
SELECT * FROM db_monkey_run.tb_sys_user where user_id = "0x001";
先看下执行计划:
-- 查看执行计划
EXPLAIN SELECT * FROM db_monkey_run.tb_sys_user where user_id = "0x001";
执行计划结果如下图所示。由结果可知,该条SQL为简单查询,无分区,type为ref可知使用非主键索引等值扫描。
4.2 获取SQL事件ID
执行一条SQL后,Performance Schema会生成该条SQL的事件,通过事件ID可以查询该条SQL的相关执行信息,
查询该SQL的事件语句如下:
-- 查看sql对应的event_id
SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%0x001%';
由上述语句可知,将时间TIMER_WAIT处理为秒,保留6位有效数字,
Performance Schema中时间单位为皮秒,1秒=10^12皮秒,
因此,TIMER_WAIT除以10^12。
查询SQL的结果如下图所示,事件ID:EVENT_ID为1286,可根据该事件ID查询SQL执行信息,
该条SQL总耗时为0.000444秒。
4.3 查询SQL各阶段耗时
上面获取SQL执行的事件ID为1286,因此,根据该ID查询SQL各阶段执行时间,
语句如下:
-- 根据event_id查询各阶段耗时
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=1286;
SQL各阶段耗时如下图所示。由图可知,SQL执行共拆解为15个阶段,
其中,
SQL
优化耗时:optimizing为0.000008秒。
准备耗时:preparing为0.000009秒。
执行耗时:executing为0.000002秒。
发送数据:Sending data为0.000054秒。
由此可知,一条SQL完整生命周期耗时不单单是执行SQL耗时,还包括打开表、优化SQL、关闭表、发送数据等过程,
SQL的优化可以针对不同的阶段进行,如果SQL自动优化时间长,可以重新设计SQL等。
序号 | 阶段 | 描述 |
---|---|---|
1 | starting | 启动 |
2 | checking permissions | 检查权限 |
3 | Opening tables | 打开表 |
4 | init | 初始化 |
5 | System lock | 系统锁 |
6 | optimizing | 优化SQL |
7 | statistics | 统计 |
8 | preparing | 准备SQL |
9 | executing | 执行SQL |
10 | Sending data | 发送数据 |
11 | end | 发送结束 |
12 | query end | 查询结束 |
13 | closing tables | 关闭表 |
14 | freeing items | 释放item |
15 | cleaning up | 清理 |
5 小结
核心:
(1)Performance Schema用于监控SQL执行,如各阶段耗时、阶段的锁等待、索引访问、物理I/O和逻辑I/O等;
(2)Performance Schema中的表是内存表,随MySQL服务器启动载入数据,关闭清除数据,资源消耗少,配置即时生效;
(3)Performance Schema的异常不影响SQL的正常执行;
(4)使用Performance Schema需要配置采集器和消费者,具体要开启:events_statements_history_long和events_stages_history_long等。
6 Q&A
11:03:46 UPDATE performance_schema.setup_instruments SET ENABLED=‘YES’, TIMED=‘YES’ WHERE NAME LIKE ‘wait%’ Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.000 sec
-- 关闭安全更新
SET SQL_SAFE_UPDATES=0;
边栏推荐
猜你喜欢
随机推荐
音乐播放器(未完成版本)
高学历毕业生,该学单片机还是plc?
【640. 求解方程】
云服务器基于 SSH 协议实现免密登录
VLAN huawei 三种模式
CIKM2022 | Sequence Recommendation Based on Bidirectional Transformers Contrastive Learning
String类的常用方法
罗克韦尔AB PLC RSLogix5000中计数器指令使用方法介绍
"DevOps Night Talk" - Pilot - Introduction to CNCF Open Source DevOps Project DevStream - feat. PMC member Hu Tao
Pro-test is effective | A method to deal with missing features of risk control data
爬虫request.get()出现错误
port forwarding
元宇宙社交应用,靠什么吸引用户「为爱发电」?
阿里云贾朝辉:云XR平台支持彼真科技呈现国风科幻虚拟演唱会
实例052:按位或
pytorch手撕CNN
FPGA - Memory Resources of 7 Series FPGA Internal Structure -03- Built-in Error Correction Function
Lambda
诺诚健华通过注册:施一公家族身价15亿 高瓴浮亏5亿港元
What is Jmeter? What are the principle steps used by Jmeter?