参数performance_schema设置最佳实践-阿里云开发者社区

开发者社区> 阿里云数据库> 正文
登录阅读全文

参数performance_schema设置最佳实践

简介: 最早开源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




性能

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

测试规格:

image

测试场景:

  • 表数量: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

image


并发数

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


image

并发数

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

image

并发数

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%


内存变化

image

打开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

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

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
最新文章
相关文章
链接