Explanation of monitoring parameters in performance and sys schema in MySQL 5.7 of recommendation

1. performance schema: Introduction

In MySQL 5.7, performance schema has made great improvements, including introducing a large number of newly added monitoring items, reducing footprint and load, and significantly improving ease of use through the new sys schema mechanism. In terms of monitoring, performance schema has the following functions:

①: Metadata lock:

It is very important to understand the dependencies of metadata locks between sessions. From MySQL5.7. 3 onwards, you can use the metadata_locks table to learn about metadata locks;

--Which sessions have which metadata locks
--Which sessions are waiting for metadata locks
--Which requests were killed due to a deadlock, or which were abandoned due to a lock wait timeout

②: Progress tracking:

Track the progress of long operations (such as alter table), starting with MySQL 5.7. 7, and performance schema automatically provides statement progress information. We can view the progress information of the current event through the events_stages_current table;

③: Affairs:

Monitor all aspects of service tier and storage engine tier transactions. Starting from MySQL5.7. 3, events_transactions_current table has been added, which can open transaction monitoring through setup_consumers and setup_instruments tables, and query the current transaction status through this table. If the online database encounters a large increase in undo log and a sharp decline in database performance, you can query whether there are currently uncommitted transactions through this table. If it is found that state with a large number of transactions is in active, it can be determined that there are a large number of uncommitted transactions in the database;

④: Memory usage:

Provide memory usage information statistics, which is beneficial to understand and adjust the memory consumption of the server. Starting from MySQL5.7. 2, performance schema added statistical information about memory, and counted the usage process of memory from the perspectives of account, visiting host, thread, user and event;

⑤: Stored program:

Detectors for stored procedures, stored methods, event schedulers, and table triggers. Detectors of event, function, procedure and trigger are added to the setup_objects table in MySQL5.7. performance schema is used to detect objects in this table that match object_schema and object_name;

2. Introduction of sys schema:

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

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

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

Stored procedures for operations such as performance mode configuration and generation of diagnostic reports

③: Storage function for querying performance mode configuration and providing formatting service

--sys schema in the query function, can view the database service resource usage? Which hosts have the most access to the database server? What is the memory usage on the instance?

3. Classification of tables in sys schema:

①: Host related information:

The view beginning with host_summary mainly summarizes the information of IO delay, and displays the information of IO from the perspectives of host, file event type and statement type;

②: innodb related information:

A view beginning with innodb that summarizes innodb buffer page information and transaction wait innodb lock information;

③: Usage of IO:

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

④: Memory usage:

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

⑤: Connection and session information:

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

⑥: Table related information:

A view beginning with schema_table shows table statistics from full table scans, innodb buffer pools, and so on;

⑦: Index information:

It contains the view of index, which counts the usage of indexes, duplicate indexes and unused indexes;

8: Sentence related information:

statement at the beginning of the view, statistics of the normalized statement usage, including the number of errors, warnings, the implementation of full table scanning, the use of temporary tables, the implementation of sorting and other information;
Pet-name ruby: User-related information:

user at the beginning of the view, the statistics of the user's use of the file IO, the execution of the statement statistics, and so on;

Pet-name ruby: Waiting for event-related information
:

A view beginning with wait that shows the latency of waiting for class events from a host and event perspective;

4. sys schema uses columns:

--- View the number of visits to the table: ( You can monitor the number of visits to each table, or monitor the changes in the number of visits to a library )
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;
--- Check for redundant and unused indexes: ( schema_redundant_indexes And schema_unused_indexes View the index) 
select * from sys.schema_redundant_indexes\G
select * from sys.schema_unused_indexes;
( If there are redundant indexes and indexes that have not been used for a long time, they should be cleaned up in time. )
--- View table self-increment ID Usage: 
select * from schema_auto_increment_columns\G 
( You can use the schema_auto_increment_columns View, you can easily find out the self-increment usage of each table, and even accurate to the self-increment of a certain table )
--- Object that monitors full table scanning sql Statement: 
select * from sys.statements_with_full_table_scans where db='test2'\G
( Use statements_with_full_table_scans View can see which table queries use a full table scan, where exec_count Is the number of executions, and other information )
--- View the disk consumed by the instance I/O Situation: ()
select file,avg_read+avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10;
( View io_global_by_file_by_bytes View can check the disk I/O Reasons for excessive consumption and positioning problems )

Summarize