MySQL Performance Schema in Action

性能模式是一种强大的诊断工具,用于:
-查询性能
-复杂的锁定问题
-内存泄漏
-资源使用率
-设置不当导致的问题行为
-更多
它有数百个选项,可以精确地调整要检测的内容。超过100个消费者存储收集的数据。
在本教程中,我们将尝试所有重要的仪器。我们将提供测试环境和一些没有性能模式很难解决的典型问题。你不仅要学习如何收集和使用这些信息,而且要有相关的经验。

展开查看详情

1. MySQL Performance Schema in Action November, 5, 2018 Sveta Smirnova, Alexander Rubin with Nickolay Ihalainen

2. Table of Contents •Internal Diagnostic in MySQL •Configuration •5.6+: Statements Instrumentation •5.7+: Prepared Statements •5.7+: Stored Routines •5.7+: Locks Diagnostic •5.7+: Memory Usage •5.7+: Replication •5.7+: Variables •8.0+: Errors Summary 2

3.Who We Are 3

4.Who We Are: Sveta Smirnova • MySQL Support engineer • Author of • MySQL Troubleshooting • JSON UDF functions • FILTER clause for MySQL • Speaker • Percona Live, OOW, Fosdem, DevConf, HighLoad... 4

5. Who We Are: Alexander Rubin • Principal Consultant in Architecture & Projects • With MySQL for > 12 years • Speaker • Percona Live, OOW, HighLoad... 5

6.Who We Are: Nickolay Ihalainen • Senior Support Engineer • In Percona for 7+ years • DBA Expert in • MySQL • MongoDB • PostgreSQL 6

7.Internal Diagnostic in MySQL

8. Old Style • INFORMATION SCHEMA • SHOW commands • Metadata • Storage engine extentions Runtime Can be anything 8

9. Old Style • INFORMATION SCHEMA • SHOW commands • Status variables • Runtime 8

10. Old Style • INFORMATION SCHEMA • SHOW commands • Status variables • Administration statements • ANALYZE • EXPLAIN • CHECK 8

11. Old Style • INFORMATION SCHEMA • SHOW commands • Status variables • Administration statements • No or limited information on • Performance • Internal server operations 8

12. Performance Schema • Introduced in version 5.5 9

13. Performance Schema • Introduced in version 5.5 • Runtime performances statistics 9

14. Performance Schema • Introduced in version 5.5 • Runtime performances statistics • Wraps the diagnosed code locker = PSI_RWLOCK_CALL(start_rwlock_wrwait)( &state, lock->pfs_psi, PSI_RWLOCK_TRYEXCLUSIVELOCK, file_name, static_cast<uint>(line)); ret = rw_lock_x_lock_func_nowait(lock, file_name, line); if (locker != NULL) PSI_RWLOCK_CALL(end_rwlock_wrwait)( locker, static_cast<int>(ret)); 9

15.How Does Performance Schema Works? 10

16.Instruments: Get the Data 11

17.Consumers: Store the Data 12

18. Performance Schema Limitations • Must be supported by the component • Collects data only after they are enabled • Never frees allocated memory 13

19. sys Schema • Views on Performance Schema tables • Stored routines 14

20. sys Schema • Views on Performance Schema tables • Stored routines • Easier configuration • Shortcuts to typical use cases 14

21. sys Schema • Views on Performance Schema tables • Stored routines • Easier configuration • Shortcuts to typical use cases • 5.7+: Installed by default • Before: github.com/mysql/mysql-sys 14

22. What will We Discuss Today? • Statements • Locks • Memory Usage • Replication • Variables • Errors 15

23.Configuration

24. What is Inside? 5.6 5.7 8.0 • 52 tables • 87 tables • 102 tables • 561 instrs • 1029 instrs • 1202 instrs 589 Percona Server 1067 Percona Server 1229 Percona Server • 31 variables • 42 variables • 44 variables 17

25. Performance Schema Defaults • Defaults • ON 5.7: Only global, thread, statements and transactions instrumentation 8.0: Memory and MDL 18

26. Performance Schema Defaults • Defaults • ON 5.7: Only global, thread, statements and transactions instrumentation 8.0: Memory and MDL • All other instruments/consumers disabled 18

27. Prepare • We will turn required instrumentation ON for each exercise separately 19

28. Prepare • We will turn required instrumentation ON for each exercise separately • We will use pattern update performance_schema.setup_consumers set enabled=’yes’ where name like ’OUR_REQUIREMENT_%’; update performance_schema.setup_instruments set enabled=’yes’, timed=’yes’ where name like ’OUR_REQUIREMENT_%’; 19

29. Prepare • We will turn required instrumentation ON for each exercise separately • Or easier call sys.ps_setup_enable_consumer(YOUR_CONSUMER); call sys.ps_setup_enable_instrument(YOUR_INSTRUMENT); 19