Explanation of monitoring parameters in performance and SYS schemas in MySQL5.7

MySQL performance and sys monitoring parameters in the MySQL5.7

1. Performance Schema: Introduction

In MySQL5.7, the performance schema has been greatly improved, including the introduction of a large number of newly added monitoring items, reduced footprint and load, and a significant increase in ease of use through the new SYS schema mechanism. In terms of monitoring, the performance schema has the following functions:

①: Meta data Lock:

It is important to understand the dependencies between session metadata locks. Starting from MySQL5.7.3, we can get the information about metadata lock through the Metadata_locks table.

--which sessions have which metadata locks

--which sessions are waiting for metadata locks

--which requests are discarded due to deadlocks being killed, or lock waiting timeout

②: Progress Tracking:

Track the progress of long-time operations (such as ALTER TABLE), starting with MySQL5.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;

③: Transaction:

Monitor all aspects of service layer and storage engine layer transactions. Starting with MySQL5.7.3, a new events_transactions_current table is available that enables transaction monitoring through the Setup_consumers and setup_instruments tables, querying the status of the current transaction through the table. If the on-line database encounters a large increase in undo log and a sharp decrease in database performance, you can use this table to query whether there are currently uncommitted transactions. If the state in which a large number of transactions are found is active, you can determine that the database has a large number of transactions uncommitted;

④: Memory Usage:

Provides statistics on memory usage to help understand and adjust the memory consumption of the server. Starting from MySQL5.7.2, the performance schema added memory-related statistics, from the account, access to host, thread, user and event point of view of the memory of the use of the process;

⑤: Stored program:

Detectors for stored procedures, storage methods, event schedulers, and table triggers. In the Setup_objects table in MySQL5.7, a detector for event, function, procedure, trigger is added. The performance schema is used to detect objects in the table that match Object_schema and object_name;

2. SYS Schema Introduction:

The SYS schema is added to the MySQL5.7. A schema that consists of a series of objects (views, stored procedures, storage methods, tables, and triggers) that itself does not capture and store what information, but rather performance_schema and Information_ The data in the schema is summed up as "views" in a way that is easier to understand.

The---SYS schema can be used for typical tuning and diagnostic use cases, including the following three objects:

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

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

③: A storage function for querying performance mode configuration and providing a format service

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

3. The classification of the table in the SYS schema:

①: Host-related information:

The view that starts with Host_summary, mainly summarizes the IO delay information, from the host, the file event type, the statement type and so on displays the file IO information;

②:innodb Related information:

A view starting with InnoDB, summarizing the InnoDB buffer page information and the transaction waiting for InnoDB lock information;

③:io Use cases:

The view starting with IO summarizes the information of the IO user, including waiting for IO, IO usage, and grouping from various angles.

④: Memory Usage:

A view that begins with a memory, showing the use of memories from a host, thread, user, and event perspective;

⑤: Connect to session information:

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

⑥: Table Related information:

The view that starts with schema_table, displays the table statistics from the full table scan, the InnoDB buffer pool and so on;

⑦: Index Information:

It contains a view of index, statistics on the use of indexes, as well as repeated indexes and unused indexes;

⑧: statement-related information:

A view starting with statement, statistics of the normalized statement usage, including the number of errors, the number of warnings, perform a full table scan, the use of temporary tables, the execution of sorting information;

⑨: User-related information:

A view that starts with user, statistics the file IO used by users, executes the statement statistic information, etc.

⑨: Wait for event-related information:

A view that begins with a wait, showing the latency of the waiting class event from the host and event perspective;

4. SYS schema uses the following:

---View the amount of access to a table: (You can monitor the amount of traffic per table, or monitor changes in the number of accesses 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 indexes and unused indexes: (schema_redundant_indexes and schema_unused_indexes View indexes)

SELECT * FROM Sys.schema_redundant_indexes\g

SELECT * from Sys.schema_unused_indexes;

(if there are redundant indexes and long unused indexes, they should be cleaned up in time)

---View the table self-increment ID usage:

SELECT * FROM Schema_auto_increment_columns\g

(with the Schema_auto_increment_columns view, you can easily find out the self-increment usage of each table, even to the self-increment of a table)

---The SQL statement that monitors the 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 a full table scan, where Exec_count is the number of executions, and so on)

---View 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;

(View Io_global_by_file_by_bytes view to check the cause of excessive disk I/O consumption, locating problems)

This article is from the "stupid Child's DBA path" blog, please be sure to keep this source http://fengfeng688.blog.51cto.com/4896812/1952334

Explanation of monitoring parameters in performance and SYS schemas in MySQL5.7