MySQL Performance Schema in Action

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