Detailed example of monitoring parameters in performance and sys schema in MySQL5.7

I hope the content I want to share with you will be helpful to you. In MySQL5.7, the performance schema has been greatly improved, including the introduction of a large number of new monitoring items, reduction of space and load, and the use of the new sys schema. The mechanism significantly improves ease of use. This article will introduce to you the explanation of monitoring parameters in performance and sys schema in MySQL5.7. Friends in need can refer to it.

1. Performance schema: Introduction

In MySQL5.7, performance schema has been greatly improved, including the introduction of a large number of newly added monitoring items, reduction of space and load, and through new The sys schema mechanism significantly improves ease of use. In terms of monitoring, the performance schema has the following functions:

①: Metadata lock:

It is crucial to understand the dependencies of metadata locks between sessions. Starting from MySQL 5.7.3, you can learn about metadata locks through the metadata_locks table;

--Which sessions have which metadata locks
--Which sessions are waiting for metadata locks
--Which requests were killed due to deadlocks or lock waiting timeouts?

②: Progress tracking:

Track the progress of long-term operations (such as alter table), from MySQL5. Starting from 7.7, performance schema automatically provides statement progress information. We can view the progress information of the current event through the events_stages_current table;

③: Transaction:

Monitor all aspects of the service layer and storage engine layer transactions. Starting from MySQL 5.7.3, the events_transactions_current table has been added. Transaction monitoring can be turned on through the setup_consumers and setup_instruments tables, and the status of the current transaction can be queried through this table. If the online database encounters a large increase in undo log and a sharp decline in database performance, you can use this table to query whether there are currently uncommitted transactions. If it is found that a large number of transaction states are active, it can be determined that a large number of transactions are uncommitted in the database;

④: Memory usage:

Provides memory usage information statistics, which is helpful for understanding and Adjust the memory consumption of the server. Starting from MySQL5.7.2, performance schema has added memory-related statistical information, which counts the memory usage process from the perspectives of accounts, access hosts, threads, users and events;

⑤: Stored procedures:

Detectors for stored procedures, stored methods, event schedulers and table triggers. In the setup_objects table in MySQL5.7, new detectors for event, function, procedure, and trigger are added. Performance schema is used to detect objects matching object_schema and object_name in the table;

2. Introduction to sys schema:

New sys schema in MySQL5.7. It is a schema composed of a series of objects (views, stored procedures, stored methods, tables and triggers). It does not collect and store any information itself, but summarizes the data in performance_schema and information_schema in a more understandable way. for "view".

---sys schema can be used for typical tuning and diagnostic use cases. These objects include the following three:

①: Summarize performance mode data into a more understandable view;

②: Stored procedures for operations such as performance mode configuration and generating diagnostic reports

③: Stored functions used to query performance mode configuration and provide formatting services

--- The function of sys schema in query can check the usage of database service resources? Which hosts have the most access to the database server? Memory usage on the instance?

3. Classification of tables in sys schema:

①: Host related information:

The view starting with host_summary mainly summarizes the IO delay information from the host , file event type, statement type and other perspectives to display file IO information;

②: innodb related information:

The view starting with innodb summarizes the innodb buffer page information and transaction waiting for innodb lock Information;

③: IO usage:

The view starting with IO summarizes the information of IO users, including waiting for IO and IO usage, displayed in groups from various angles ;

④: Memory usage:

The view starting with memory shows memory usage from the perspective of host, thread, user, and event;

⑤: Connection and session Information:

Among them, the processlist and session-related views summarize the session-related information;

⑥: Table-related information:

The view starting with schema_table, from the entire Table scans, innodb buffer pools, etc. display table statistical information;

⑦: Index information:

It contains the view of the index, which counts the usage of the index, as well as duplicate indexes and unused indexes Index status;

⑧: Statement related information:

The view starting with statement, statistics of standardized statement usage, including the number of errors, warnings, full table scans, Use temporary tables, execution sorting and other information;
⑨: User related information:

The view starting with user counts the file IO used by the user, the statement statistics executed, etc.;

⑨: Waiting for event related information
:

The view starting with wait shows the delay of waiting events from the perspective of host and event;

4. Sys schema usage examples:

---查看表的访问量：(可以监控每张表访问量的情况，或者监控某个库的访问量的变化)
select table_schema,table_name,sum(io_read_requests+io_write_requests) from schema_table_statistics;
select table_schema,table_name,io_read_requests+io_write_requests as io_total from schema_table_statistics;
---冗余索引和未使用索引的检查：（schema_redundant_indexes和schema_unused_indexes查看索引的情况）
select * from sys.schema_redundant_indexes\G
select * from sys.schema_unused_indexes;
(如果有冗余索引和长期未使用的索引，应该及时清理，)
---查看表自增ID使用情况：
select * from schema_auto_increment_columns\G 
(可以使用schema_auto_increment_columns视图，就能很简单的查到每个表的自增量使用情况，甚至可以精确到某个表的自增量情况)
---监控全表扫描的sql语句：
select * from sys.statements_with_full_table_scans where db='test2'\G
(使用statements_with_full_table_scans视图可以查看哪些表查询使用了全表扫描，其中exec_count是执行的次数，等信息)
---查看实例消耗的磁盘I/O情况：()
select file,avg_read+avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10;
(查看io_global_by_file_by_bytes视图可以检查磁盘I/O消耗过大的原因，定位问题)

Related recommendations:

Several commonly used mysql monitoring script commands

mysql script to realize automatic monitoring and synchronization

mysql innodb monitoring Detailed explanation of the example code of (system layer, database layer)

The above is the detailed content of Detailed example of monitoring parameters in performance and sys schema in MySQL5.7. For more information, please follow other related articles on the PHP Chinese website!