MySQL PERFORMANCE_SCHEMA监控用法详解
原创MySQL PERFORMANCE_SCHEMA监控用法详解
原创
Power
发布于 2025-02-28 21:40:04
发布于 2025-02-28 21:40:04
代码可运行
运行总次数：0
代码可运行
一、PERFORMANCE_SCHEMA库介绍
MySQL 5.5开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。
在MySQL5.6、MySQL5.7中，PERFORMANCE_SCHEMA有很大改进，包括引入大量新加入的监控项、降低占用空间和负载，以及通过新的sys schema机制显著提升易用性。
二、启用PERFORMANCE_SCHEMA监控
2.1 查看MySQL支持的引擎
mysql> select * from information_schema.engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)2.2 开启PERFORMANCE_SCHEMA
（1）修改配置文件，持久化配置
[root@X ~]# vim /etc/my.conf
[mysqld]
performance_schema=ON（2）查看开启状态
mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.00 sec)三、PERFORMANCE_SCHEMA使用技巧
注：由于测试库是空的，并且没有业务，所以查询出来的数据较少！！！以下是PERFORMANCE_SCHEMA库中的表情况：
mysql> use performance_schema;
mysql> show tables;
+------------------------------------------------------+
| Tables_in_performance_schema |
+------------------------------------------------------+
| accounts |
| cond_instances |
| events_stages_current |
| events_stages_history |
| events_stages_history_long |
| events_stages_summary_by_account_by_event_name |
| events_stages_summary_by_host_by_event_name |
| events_stages_summary_by_thread_by_event_name |
| events_stages_summary_by_user_by_event_name |
| events_stages_summary_global_by_event_name |
| events_statements_current |
| events_statements_history |
| events_statements_history_long |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest |
| events_statements_summary_by_host_by_event_name |
| events_statements_summary_by_program |
| events_statements_summary_by_thread_by_event_name |
| events_statements_summary_by_user_by_event_name |
| events_statements_summary_global_by_event_name |
| events_transactions_current |
| events_transactions_history |
| events_transactions_history_long |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name |
| events_transactions_summary_by_thread_by_event_name |
| events_transactions_summary_by_user_by_event_name |
| events_transactions_summary_global_by_event_name |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_by_user_by_event_name |
| events_waits_summary_global_by_event_name |
| file_instances |
| file_summary_by_event_name |
| file_summary_by_instance |
| global_status |
| global_variables |
| host_cache |
| hosts |
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name |
| memory_summary_by_thread_by_event_name |
| memory_summary_by_user_by_event_name |
| memory_summary_global_by_event_name |
| metadata_locks |
| mutex_instances |
| objects_summary_global_by_type |
| performance_timers |
| prepared_statements_instances |
| replication_applier_configuration |
| replication_applier_status |
| replication_applier_status_by_coordinator |
| replication_applier_status_by_worker |
| replication_connection_configuration |
| replication_connection_status |
| replication_group_member_stats |
| replication_group_members |
| rwlock_instances |
| session_account_connect_attrs |
| session_connect_attrs |
| session_status |
| session_variables |
| setup_actors |
| setup_consumers |
| setup_instruments |
| setup_objects |
| setup_timers |
| socket_instances |
| socket_summary_by_event_name |
| socket_summary_by_instance |
| status_by_account |
| status_by_host |
| status_by_thread |
| status_by_user |
| table_handles |
| table_io_waits_summary_by_index_usage |
| table_io_waits_summary_by_table |
| table_lock_waits_summary_by_table |
| threads |
| user_variables_by_thread |
| users |
| variables_by_thread |
+------------------------------------------------------+
87 rows in set (0.00 sec)3.1 查询执行最多的SQL
mysql> SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR desc LIMIT 1\G
*************************** 1. row ***************************
SCHEMA_NAME: NULL
DIGEST_TEXT: SHOW SLAVE STATUS
COUNT_STAR: 1487755
SUM_ROWS_SENT: 0
SUM_ROWS_EXAMINED: 0
FIRST_SEEN: 2021-11-17 11:38:55
LAST_SEEN: 2021-12-09 22:47:17
1 row in set (0.00 sec)3.2 查询响应时间最长的SQL
mysql> SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT desc LIMIT 1\G
*************************** 1. row ***************************
SCHEMA_NAME: mysql
DIGEST_TEXT: GRANT SELECT , INSERT , UPDATE , DELETE ON * . * TO ? @? REQUIRE NONE WITH MAX_QUERIES_PER_HOUR ? MAX_CONNECTIONS_PER_HOUR ? MAX_UPDATES_PER_HOUR ? MAX_USER_CONNECTIONS ?
COUNT_STAR: 1
AVG_TIMER_WAIT: 17413918000
SUM_ROWS_SENT: 0
SUM_ROWS_EXAMINED: 0
FIRST_SEEN: 2021-11-25 17:16:01
LAST_SEEN: 2021-11-25 17:16:01
1 row in set (0.00 sec)3.3 查找逻辑IO最高的表、文件（热数据）
mysql> SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2\G
*************************** 1. row ***************************
FILE_NAME: /opt/udb/instance/mysql-5.7/569ae9f9-53a5-4017-9d7e-31283aa9096a/data/ibtmp1
EVENT_NAME: wait/io/file/innodb/innodb_data_file
COUNT_READ: 0
SUM_NUMBER_OF_BYTES_READ: 0
COUNT_WRITE: 1906642
SUM_NUMBER_OF_BYTES_WRITE: 31250808832
*************************** 2. row ***************************
FILE_NAME: /opt/udb/instance/mysql-5.7/569ae9f9-53a5-4017-9d7e-31283aa9096a/data/ibdata1
EVENT_NAME: wait/io/file/innodb/innodb_data_file
COUNT_READ: 167
SUM_NUMBER_OF_BYTES_READ: 4849664
COUNT_WRITE: 9
SUM_NUMBER_OF_BYTES_WRITE: 294912
2 rows in set (0.00 sec)3.4 查找使用最多的索引
mysql> SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC limit 1;
+-------------+------------+-------------+--------------+--------------+--------------+
| OBJECT_NAME | INDEX_NAME | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+------------+-------------+--------------+--------------+--------------+
| sys_config | PRIMARY | 0 | 0 | 0 | 0 |
+-------------+------------+-------------+--------------+--------------+--------------+
1 row in set (0.00 sec)3.5 查询未使用过的索引
mysql> SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
+---------------+-------------+------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME |
+---------------+-------------+------------+
| sys | sys_config | PRIMARY |
+---------------+-------------+------------+
1 row in set (0.01 sec)3.6 查找最耗时的等待
mysql> SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC LIMIT 1;
+--------------------------------------+------------+----------------+----------------+
| EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | AVG_TIMER_WAIT |
+--------------------------------------+------------+----------------+----------------+
| wait/io/file/innodb/innodb_data_file | 1907387 | 38437677983232 | 20151552 |
+--------------------------------------+------------+----------------+----------------+
1 row in set (0.00 sec)原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
