参数performance_schema设置最佳实践

2021-09-17 1605

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 最早开源MySQL从5.5开始支持performance_schema(下文简称PFS)，又在后续版本不断持续完善、优化，PFS已经成为了性能诊断优化的利器，使SQL问题、锁等待事件等比较清晰地展现出来，但打开PFS也会带来相应的性能成本，本篇就来看下PFS相比其他工具及不打开PFS的性能差异。

概述

最早开源MySQL从5.5开始支持performance_schema(下面简称PFS)，又在后续版本不断持续完善、优化，PFS已经成为了性能诊断优化的利器，使SQL问题、锁等待事件等比较清晰地展现出来，但打开PFS也会带来相应的性能成本，本篇就来看下PFS相比其他工具及不打开PFS的性能差异。

PFS功能

先说功能，PFS作为内置的问题诊断分析工具功能支持的比较多；DAS(https://www.aliyun.com/product/hdm)是外置的工具，如果想详细分析的话可以根据需要打开审计日志和DAS。

编号	功能	PFS	DAS	其他工具	备注
1	哪类的SQL执行最多	Y	Y
2	哪类SQL的平均响应时间最多	Y	Y
3	哪类SQL排序记录数最多	Y	N
4	哪类SQL扫描记录数最多	Y	Y
5	哪类SQL使用临时表最多	Y	N
6	哪类SQL返回结果集最多	Y	N
7	哪个表物理IO最多	Y	N
8	哪个表逻辑IO最多	Y	N
9	哪个索引访问最多	Y	N
10	哪个索引从来没有用过	Y	N	Y	log_queries_not_using_indexes
11	哪个等待事件消耗时间最多	Y	N
12	剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息	Y	N
13	查看每个阶段的时间消耗	Y	N	Y	profiling,optimize trace
14	查看每个阶段的锁等待情况	Y	N

性能

测试参数模板：异步参数模板

测试规格:

测试场景：

表数量：20
表大小：10000000
压测并发：8,16,32,64,128,256,512

数据结论先行

除非要查比较难排查的问题，通常情况不建议打开PFS。DAS对DB Server的影响可以忽略不计。
CPU达到瓶颈前性能大致相当，CPU达到瓶颈后pfsoff的性能优势逐渐凸显，pfson_conon_inson的性能最差，其他三种场景性能大致相当。性能方面：pfsoff>pfson_conoff_insoff≈pfson_conon_insoff≈pfson_conoff_inson>pfson_conon_inson

下面CPU达到瓶颈后，以32个并发的性能平均数取值，在其他并发下的性能差异可参加下面详细的数据：

参数配置	read_only	涨跌幅	read_write	涨跌幅	write_only	涨跌幅
pfsoff	72983	基数	41867	基数	37400	基数
pfson_conoff_insoff	63769	↓ 12.62%	37914	↓ 9.44%	34232	↓ 8.47%
pfson_conon_insoff	64964	↓ 10.99%	34740	↓ 17.02%	33305	↓ 10.95%
pfson_conoff_inson	65485	↓ 10.27%	34477	↓ 17.65%	34406	↓ 8%
pfson_conon_inson	53870	↓ 26.19%	29935	↓ 28.5%	26033	↓ 30.39%

说明：

PERFORMANCE SCHEMA 关闭 (pfsoff)
PERFORMANCE SCHMEA 打开, 所有的consumers打开，所有的instrumentation关闭(pfson_conon_insoff)
PERFORMANCE SCHMEA 打开, 所有的consumers关闭，所有的instrumentation打开(pfson_conoff_inson)
PERFORMANCE SCHMEA 打开, 所有的consumers打开，所有的instrumentation打开(pfson_conon_inson)
PERFORMANCE SCHMEA 打开, 所有的consumers和instrumentation关闭 (pfson_conoff_insoff)
涨跌幅是以未开PFS为基准，32个并发的性能数据相减

read-only

并发数	pfsoff	pfson_conon_inson	涨跌幅	pfson_conon_insoff	涨跌幅	pfson_conoff_inson	涨跌幅	pfson_conoff_insoff	涨跌幅
8	24818	22472	↓ 9.45%	22817	↓8.06%	23012	↓7.28%	22825	↓8.03%
16	48343	43879	↓ 9.23%	45335	↓6.22%	45505	↓5.87%	45197	↓6.51%
32	72983	53870	↓26.19%	64964	↓10.99%	65485	↓10.27%	63769	↓12.62%
64	70821	51325	↓27.53%	61304	↓13.44%	61673	↓12.92%	61232	↓13.54%
128	70866	49856	↓29.65%	61130	↓13.74%	60733	↓14.3%	59566	↓15.95%
256	70684	50600	↓28.41%	59412	↓15.95%	60285	↓14.71%	60342	↓14.63%
512	70350	50356	↓28.42%	60047	↓14.65%	60658	↓13.78%	60440	↓14.09%

read-write

并发数	pfsoff	pfson_conon_inson	涨跌幅	pfson_conon_insoff	涨跌幅	pfson_conoff_inson	涨跌幅	pfson_conoff_insoff	涨跌幅
8	15189	13330	↓ 12.24%	13627	↓10.28%	13332	↓12.23%	14832	↓2.35%
16	32181	27559	↓ 14.36%	28566	↓11.23%	28246	↓12.23%	30663	↓4.72%
32	41867	29935	↓28.5%	34740	↓17.02%	34477	↓17.65%	37914	↓9.44%
64	41897	28128	↓32.86%	37275	↓11.03%	38018	↓9.26%	37800	↓9.78%
128	41565	28492	↓31.45%	37424	↓9.96%	37791	↓9.08%	37462	↓9.87%
256	40650	28876	↓28.96%	37264	↓8.33%	37313	↓8.21%	37710	↓7.23%
512	39644	28737	↓27.51%	31066	↓21.64%	30488	↓23.1%	36875	↓6.98%

write-only

并发数	pfsoff	pfson_conon_inson	涨跌幅	pfson_conon_insoff	涨跌幅	pfson_conoff_inson	涨跌幅	pfson_conoff_insoff	涨跌幅
8	13359	11971	↓ 10.39%	13069	↓ 2.17%	12618	↓ 5.55%	12523	↓ 6.26%
16	27379	22122	↓ 19.2%	27556	↓ -0.65%	25347	↓ 7.42%	24439	↓ 10.74%
32	37400	26033	↓ 30.39%	33305	↓ 10.95%	34406	↓ 8%	34232	↓ 8.47%
64	40305	24436	↓ 39.37%	34028	↓ 15.57%	35247	↓ 12.55%	35345	↓ 12.31%
128	40034	23719	↓ 40.75%	33814	↓ 15.54%	34902	↓ 12.82%	34835	↓ 12.99%
256	40014	23442	↓ 41.42%	33771	↓ 15.6%	34370	↓ 14.11%	34733	↓ 13.2%
512	40181	23684	↓ 41.06%	33741	↓ 16.03%	34366	↓ 14.47%	34532	↓ 14.06%

内存变化

打开PFS后PFS申请的内存不会释放，使用PFS内存涨了5%，推测在复杂场景下内存上涨的更多。

扩展

阿里云MySQL开发了实例负载监控、关联分析、性能调优的工具Performance Insight，相关使用方法参考帮助文档 https://help.aliyun.com/document_detail/132200.html.

如何使用PFS

[MySQL 5.6] Performance Schema 之 PS配置项（1）https://developer.aliyun.com/article/41011

[MySQL 5.6] Performance Schema学习：命名规范、状态变量及其他(2) https://developer.aliyun.com/article/41013

[MySQL 5.6] Performance Schema 表类型纵览 (3) https://developer.aliyun.com/article/41015

参考

MySQL Performance-Schema(三) 实践篇 https://www.cnblogs.com/cchust/p/5061131.html