Troubleshooting Best Practices

在MySQL疑难解答网络研讨会系列中,我介绍了许多监控和日志记录工具,例如:
-常规、慢速、审核、二进制、错误日志文件
性能方案
信息方案
系统变量
Linux实用程序
InnoDB监视器
-PMM
但是,我没有花太多时间来研究这些工具对MySQL整体性能的影响。它们确实有影响。
这就是许多人面临的冲突。MySQLServer用户尝试探索这些监控工具,发现它们减慢了安装速度,并关闭了它们。这是不幸的。如果能够帮助您解决问题的工具关闭,您将无法获得帮助您了解问题发生的时间、方式和原因的良好和必要信息。在最好的情况下,您将重新启用检测并等待下一次灾难发生。在最坏的情况下,如果解决问题或不解决问题,您可以尝试不同的修复选项,而不需要任何实际知识。
这就是为什么了解监控工具对数据库的影响以及如何将其最小化是很重要的。
在本次网络研讨会中,我介绍了为什么某些监控工具会影响性能,以及如何在不关闭仪器的情况下最小化影响。您将学习如何安全有效地监控。

展开查看详情

1.Troubleshooting Best Practices Monitoring the Production Database Without Killing Performance June, 27, 2018 Sveta Smirnova

2.Table of Contents •Introduction: Between Desire and Reality •Why Monitoring is Expensive? •How to Find ”Golden Ratio”? •Turn ON by Demand •Summary 2

3.Introduction: Between Desire and Reality

4.Desire • When disaster happens we need 4

5.Desire • When disaster happens we need • As many information as possible 4

6.Desire • When disaster happens we need • As many information as possible • For this exact case 4

7.Desire • When disaster happens we need • As many information as possible • For this exact case • Any slow down is not acceptable! 4

8.Reality • Almost any monitoring is not free 5

9.Reality • Almost any monitoring is not free • Think about • Memory • Disk space • CPU • All of them 5

10.Testing: Internal Tools • Everything is ON: general-log innodb-monitor-enable=all innodb-print-all-deadlocks=1 innodb-status-file=1 innodb-status-output=1 slow-query-log performance-schema=1 userstat=1 thread-statistics=1 6

11.Testing: Internal Tools • OFF: general-log=0 innodb-monitor-disable=all innodb-print-all-deadlocks=0 innodb-status-file=0 innodb-status-output=0 slow-query-log=0 performance-schema=0 userstat=0 thread-statistics=0 7

12.Testing: Performance Schema • Instruments: ”counters” • Locks • Temporary tables • Time spent • ... 8

13.Testing: Performance Schema • Instruments: ”counters” • Consumers • Tables contain data • In memory • You can tune their size 8

14.Testing: Performance Schema • Instruments: ”counters” • Consumers • Instruments and Consumers • You can turn any of them ON or OFF 8

15.Testing: Internal Tools • Percona-Server-5.7.17-13 • Standard OLTP SysBench test: for i in ‘echo 1 2 4 8 16 32 36 64 72 128 144 256 512 1024‘ do echo $i LD_PRELOAD=/data/sveta/5.7.17/lib/mysql/libjemalloc.so /data/sveta/sbkk/bin/sysbench \ --test=/data/sveta/sysbench/sysbench/tests/db/oltp_prepared.lua --db-driver=mysql \ --oltp-tables-count=8 --oltp-table-size=10000000 --mysql-table-engine=$engine \ --mysql-user=msandbox --mysql-password=msandbox --mysql-socket=/tmp/mysql_sandbox5717.sock \ --num-threads=$i --max-requests=0 --mysql-db=$db --max-time=300 --percentile=0 \ run >> $engine-oltp-prepared-$suffix.log done 9

16.Monitoring is OFF 10

17.Monitoring is ON 11

18.Only Performance Schema 12

19.Performance Schema: defaults, MDL and Memory 13

20.Why Monitoring is Expensive?

21.Log Files Require Disk Space • General or binary log can use all free space 15

22.Log Files Require Disk Space • General or binary log can use all free space • Imagine write load 40GB/hour? 15

23.Log Files Require Disk Space • General or binary log can use all free space • Imagine write load 40GB/hour? • Rotate policy 15

24.Memory • Performance Schema • Requires pre-allocated memory 16

25.Memory • Performance Schema • Requires pre-allocated memory • Memory allocated 5.7: After first use 5.6: At server startup Never gets free 16

26.Memory • Performance Schema • Requires pre-allocated memory • Memory allocated 5.7: After first use 5.6: At server startup Never gets free • Size of history tables is limited 16

27.Memory • Performance Schema • Information Schema • Some queries require too much memory MySQL bug #72322 Fixed in 5.5.44, 5.6.25, 5.7.8, 8.0 16

28.Memory • Performance Schema • Information Schema • Some queries require too much memory • MySQL 8.0 re-designed tables No temporary tables for each query Index support MySQL 8.0.0 Release Notes 16

29.Memory • Performance Schema • Information Schema • Some queries require too much memory • MySQL 8.0 re-designed tables • Upgrade! 16