MySQL Performance Schema in 1 hour

下载 0

寒冰

发布于

1761

人观看

#信息技术

2010年，MySQL8.0性能模式作为“低级别监控服务器执行的功能”而诞生，随着性能修复和面向DBA的功能的发展，它是一种成熟的工具，供人类和监控产品使用。在本次网络研讨会中，我将概述性能模式，重点介绍其调优、性能和可用性。
性能模式有助于解决查询性能、复杂的锁定问题、内存泄漏、资源使用、由不适当的设置引起的问题行为等问题。它有数百个选项，可以精确地调整要检测的内容。
性能模式是一个有效且非常复杂的工具。在大多数情况下，它不会影响性能，收集大量数据，有时这些数据很难读取。
在本次网络研讨会中，我将指导您了解主要的性能模式特性、设计和配置。你将学会如何充分利用它。我将介绍它的配套系统模式和图形监控工具。

展开查看详情

1 .MySQL Performance Schema in 1 hour March, 21, 2019 Sveta Smirnova

2 .Table of Contents •Why Performance Schema? •How it Works? •How to Tune? •How to Ease? 2

3 .Why Performance Schema?

4 .Metadata vs Runtime Metadata Runtime Schema Resource usage Supported charsets Acquired locks etc. Running queries Difference 4

5 .Metadata vs Runtime Metadata Runtime Always existed Was not perfect • Status variables • SHOW commands • Extensions for • Information Schema Information Schema History 4

6 .Metadata vs Runtime Metadata Runtime Information Schema Performance Schema Now 4

7 .Performance Schema • No design issues like in Information Schema • No locking reads Almost 5

8 .Performance Schema • No design issues like in Information Schema • No locking reads • Optimized • It took one and a half major versions 5

9 .Performance Schema • No design issues like in Information Schema • No locking reads • Optimized • It took one and a half major versions • Consistently extending • New features in every release Easy to add your own 5

10 .Performance Schema • No design issues like in Information Schema • No locking reads • Optimized • It took one and a half major versions • Consistently extending • New features in every release Easy to add your own • Being mature 5

11 .Performance Schema Over Years 16 31 Percona Server Variables 8.0 42 44 5.7 5.6 17 5.5 52 Tables 87 102 237 589 Instruments 1,067 1,229 6

12 . Features 5.5: Server insights $ iostat -p nvme0n1 5 ... avg-cpu: %user %nice %system %iowait %steal %idle 73.02 0.00 12.03 6.37 0.00 8.59 Device tps kB_read/s kB_wrtn/s kB_read kB_wrtn nvme0n1 1235.80 5.60 16662.40 28 83312 nvme0n1p3 1158.00 5.60 16662.40 28 83312 7

13 . Features 5.5: Server insights mysql> select thread_id, EVENT_NAME, sum(NUMBER_OF_BYTES/1024) sum_kb from events_waits_history -> where NUMBER_OF_BYTES > 0 group by thread_id, EVENT_NAME order by sum_kb desc; +-----------+---------------------------------------------------+-------------+ | thread_id | EVENT_NAME | sum_kb | +-----------+---------------------------------------------------+-------------+ | 10 | wait/io/file/innodb/innodb_parallel_dblwrite_file | 1312.0000 | | 117 | wait/io/file/sql/binlog | 3.4023 | | 113 | wait/io/file/sql/binlog | 1.7012 | | 58 | wait/io/file/sql/query_log | 0.2031 | +-----------+---------------------------------------------------+-------------+ 4 rows in set (0.00 sec) 7

14 . Features 5.5: Server insights 5.6: Statements mysql> SELECT THREAD_ID TID, SUBSTR(SQL_TEXT, 1, 50) SQL_TEXT, ROWS_SENT RS, -> ROWS_EXAMINED RE,CREATED_TMP_TABLES,NO_INDEX_USED,NO_GOOD_INDEX_USED -> FROM performance_schema.events_statements_history -> WHERE NO_INDEX_USED=1 OR NO_GOOD_INDEX_USED=1\G ********************** 1. row ********************** TID: 10124 SQL_TEXT: select emp_no, first_name, last_name from employee RS: 97750 RE: 397774 CREATED_TMP_TABLES: 0 NO_INDEX_USED: 1 NO_GOOD_INDEX_USED: 0 ... 7

15 . Features 5.5: Server insights 5.6: Statements 5.6: Stages mysql> SELECT eshl.event_name, sql_text, eshl.timer_wait/1000000000000 w_s -> FROM performance_schema.events_stages_history_long eshl -> JOIN performance_schema.events_statements_history_long esthl -> ON (eshl.nesting_event_id = esthl.event_id) -> WHERE eshl.timer_wait > 1*10000000000\G *************************** 1. row *************************** event_name: stage/sql/Sending data sql_text: SELECT COUNT(emp_no) FROM employees JOIN salaries USING(emp_no) WHERE hire_date=from_date w_s: 0.8170 1 row in set (0.00 sec) 7

19 . Features 5.7: Metadata Locks mysql> select id, db, state, info, time from information_schema.processlist; +----+--------------------+---------------------------------+----------------------------+------+ | id | db | state | info | time | +----+--------------------+---------------------------------+----------------------------+------+ | 96 | sbtest | | NULL | 65 | | 4 | NULL | Waiting on empty queue | NULL | 5451 | | 94 | performance_schema | executing | select id, db, state, i... | 0 | | 95 | sbtest | Waiting for table metadata lock | alter table sbtest1 add... | 53 | +----+--------------------+---------------------------------+----------------------------+------+ 4 rows in set (0.00 sec) 7

21 . Features 5.7: Memory usage $ pidstat -r -p 13103 Linux 4.15.0-43-generic (delly) 01/20/2019 _x86_64_ (4 CPU) 09:36:55 PM UID PID minflt/s majflt/s VSZ RSS %MEM Command 09:36:55 PM 1001 13103 3.09 0.02 12133668 8228772 50.97 mysqld 7

22 . Features 5.7: Memory usage mysql> select event_name, count_alloc, count_free, -> CURRENT_NUMBER_OF_BYTES_USED/1024/1024 as used_mb -> from memory_summary_global_by_event_name where CURRENT_NUMBER_OF_BYTES_USED > 0 -> order by used_mb desc limit 8; +-------------------------------+-------------+------------+---------------+ | event_name | count_alloc | count_free | used_mb | +-------------------------------+-------------+------------+---------------+ | memory/sql/JOIN_CACHE | 15 | 6 | 9216.00000000 | | memory/innodb/ut0link_buf | 2 | 0 | 24.00004578 | | memory/innodb/ut0new | 6 | 0 | 16.07953835 | | memory/innodb/lock0lock | 6241 | 0 | 10.92063904 | | memory/mysys/KEY_CACHE | 3 | 0 | 8.00205994 | | memory/innodb/buf0dblwr | 378 | 370 | 5.80024719 | | memory/innodb/memory | 15686935 | 15682985 | 4.90249634 | | memory/innodb/ut0pool | 1 | 0 | 4.000022 +-------------------------------+-------------+------------+----------- 7 8 rows in set (0.04 sec)

23 . Features 5.7: Replication mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: root Master_Port: 13000 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 63810611 Relay_Log_File: slave-relay-bin-master@002d1.000004 Relay_Log_Pos: 1156 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: 7 ...

24 . Features 5.7: Replication • IO Thread replication connection status replication connection status • SQL Thread replication applier configuration replication applier filters replication applier global filters replication applier status replication applier status by coordinator replication applier status by worker • Group replication replication group member stats 7 replication group members

25 . Features 5.7: Variables mysql> select * from variables_by_thread where variable_name=’join_buffer_size’; +-----------+------------------+----------------+ | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE | +-----------+------------------+----------------+ | 129 | join_buffer_size | 262144 | | 132 | join_buffer_size | 262144 | | 133 | join_buffer_size | 262144 | | 134 | join_buffer_size | 262144 | | 135 | join_buffer_size | 262144 | ... | 144 | join_buffer_size | 262144 | | 145 | join_buffer_size | 262144 | | 146 | join_buffer_size | 262144 | | 147 | join_buffer_size | 262144 | | 148 | join_buffer_size | 1073741824 | +-----------+------------------+----------------+ 7 18 rows in set (0.06 sec)

26 . Features 5.7: Variables mysql> select * from status_by_thread -> where variable_name=’Handler_write’; +-----------+---------------+----------------+ | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE | +-----------+---------------+----------------+ | 71 | Handler_write | 94 | | 83 | Handler_write | 477 | -- Most writes | 84 | Handler_write | 101 | +-----------+---------------+----------------+ 3 rows in set (0.00 sec) 7

27 . Features 5.7: Variables mysql> select * from user_variables_by_thread; +-----------+---------------+----------------+ | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE | +-----------+---------------+----------------+ | 71 | baz | boo | | 84 | foo | bar | +-----------+---------------+----------------+ 2 rows in set (0.00 sec) 7

28 . Features 8.0: Errors mysql> select ERROR_NUMBER NUM, ERROR_NAME, SUM_ERROR_RAISED SUM, FIRST_SEEN, LAST_SEEN -> from events_errors_summary_global_by_error where SUM_ERROR_RAISED > 0 -> order by SUM_ERROR_RAISED desc; +------+---------------------------+-------+---------------------+---------------------+ | NUM | ERROR_NAME | SUM | FIRST_SEEN | LAST_SEEN | +------+---------------------------+-------+---------------------+---------------------+ | 1213 | ER_LOCK_DEADLOCK | 17986 | 2019-01-20 20:51:34 | 2019-01-20 23:05:43 | | 1287 | ER_WARN_DEPRECATED_SYNTAX | 207 | 2019-01-20 20:51:41 | 2019-01-20 23:03:29 | | 1158 | ER_NET_READ_ERROR | 96 | 2019-01-20 20:57:36 | 2019-01-20 23:05:44 | | 1205 | ER_LOCK_WAIT_TIMEOUT | 72 | 2019-01-20 20:52:19 | 2019-01-20 23:04:57 | | 1295 | ER_UNSUPPORTED_PS | 64 | 2019-01-20 20:51:33 | 2019-01-20 23:03:26 | | 3719 | ER_DEPRECATED_UTF8_ALIAS | 33 | 2019-01-20 20:50:33 | 2019-01-20 20:50:34 | | 1064 | ER_PARSE_ERROR | 2 | 2019-01-20 21:11:33 | 2019-01-20 21:11:33 | | 1317 | ER_QUERY_INTERRUPTED | 2 | 2019-01-20 21:24:23 | 2019-01-20 21:29:37 | | 1054 | ER_BAD_FIELD_ERROR | 1 | 2019-01-20 22:14:57 | 2019- | 1066 | ER_NONUNIQ_TABLE | 1 | 2019-01-20 21:23:45 | 2019 7 | 1160 | ER_NET_ERROR_ON_WRITE | 1 | 2019-01-20 21:51:36 | 2019- ...

29 . Features 5.7: Prepared statements 5.7: Stored routines 5.7: Metadata Locks 5.7: Memory usage 5.7: Replication 5.7: Variables 8.0: Errors 7 • More

0点赞

0收藏

0下载