Explanation of monitoring parameters in performance and sys schema in MySQL 5.7 (recommended)

1. Performance schema: Introduction

In MySQL 5.7, the performance schema has been greatly improved, including the introduction of a large number of new monitoring items, reducing the space and load, and significantly improving ease of use through the new sys schema mechanism. In terms of monitoring, the performance schema has the following functions:

①: Metadata lock:

Essential for understanding metadata lock dependencies between sessions. Starting from MySQL 5.7.3, you can use the metadata_locks table to obtain information about metadata locks.

--Which sessions own which metadata locks
--Which sessions are waiting for metadata locks
--Which requests were killed due to deadlock or abandoned due to lock waiting timeout

②: Progress tracking:

Track the progress of long operations (such as alter table). Starting from MySQL 5.7.7, the 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 layer and storage engine layer transactions. Starting from MySQL 5.7.3, the events_transactions_current table has been added. You can enable transaction monitoring through the setup_consumers and setup_instruments tables, and query the status of the current transaction through this table. If the online database encounters a situation where the undo log increases significantly and the database performance drops sharply, you can use this table to query whether there are any uncommitted transactions. If it is found that there are indeed a large number of transactions in the active state, it can be determined that there are a large number of uncommitted transactions in the database;

④: Memory usage:

Providing memory usage statistics helps to understand and adjust the server's memory consumption. Starting from MySQL 5.7.2, the performance schema adds memory-related statistics, which counts the memory usage from the perspectives of accounts, access hosts, threads, users, and events.

⑤: Storage program:

Detectors for stored procedures, stored methods, event schedulers, and table triggers. In the setup_objects table in MySQL 5.7, detectors for event, function, procedure, and trigger are added. The performance schema is used to detect objects in the table that match object_schema and object_name;

2. Introduction to sys schema:

The sys schema added in MySQL 5.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 as a "view".

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

①: Aggregate performance mode data into more easily understandable views;

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

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

---The function of sys schema in query is to check the usage of database service resources? Which hosts have the most traffic 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 latency information and displays the file IO information from the perspectives of host, file event type, statement type, etc.

②: InnoDB related information:

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

③: IO usage:

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

④: Memory usage:

Views starting with "memory" display memory usage from the perspectives 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 displays table statistics from aspects such as full table scan and innodb buffer pool.

⑦: Index information:

It contains index views, which count index usage, duplicate indexes, and unused indexes.

⑧: Sentence related information:

Views starting with "statement" count the usage of normalized statements, including the number of errors, warnings, full table scans, temporary tables, sorts, and other information.
⑨: User related information:

Views starting with "user" count the file IO used by users, statistics on executed statements, etc.

⑨: Waiting event related information:

Views starting with "wait" show the delay of wait events from the perspective of the host and events.

4. Examples of sys schema usage:

--- 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 certain 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 redundant indexes and unused indexes: (schema_redundant_indexes and schema_unused_indexes check the index status)
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.)
--- Check the usage of the table's auto-increment ID:
select * from schema_auto_increment_columns\G 
(You can use the schema_auto_increment_columns view to easily check the auto-increment usage of each table, and even the auto-increment usage of a specific table)
---Monitor the SQL statement for full table scan:
select * from sys.statements_with_full_table_scans where db='test2'\G
(Use the statements_with_full_table_scans view to see which table queries use full table scans, where exec_count is the number of executions, and other information)
--- Check the disk I/O consumed by the instance: ()
select file,avg_read+avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10;
(Check the io_global_by_file_by_bytes view to check the cause of excessive disk I/O consumption and locate the problem)

Summarize

The above is the explanation of the monitoring parameters in performance and sys schema in MySQL 5.7. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!