MySQL PERFORMANCE_SCHEMA监控用法详解
一、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)
作者：UStarGao
链接：https://www.starcto.com/mysql/265.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
UCloud云平台推荐
随便看看
- 2021-08-08UCloud ULB负载均衡https解决方案
- 2021-11-09MySQL区分大小写参数配置
- 2021-08-05Redis持久化机制之AOF日志与RDB快照
- 2021-03-28Hadoop伪分布式系统搭建
- 2021-11-19Linux本地SSD磁盘IO性能压测教程-fio
