Troubleshooting Best Practices

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