Shulou(Shulou.com)05/31 Report--

This article will explain in detail what are the monitoring parameters of performance and sys schema in MySQL5.7. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1. Performance schema: introduction

In MySQL5.7, performance schema has made great improvements, including the introduction of a large number of new monitoring items, reduced footprint and load, and significantly improved ease of use through the new sys schema mechanism. In terms of monitoring, performance schema has the following functions:

①: metadata lock:

It is essential to understand the dependencies of metadata locks between sessions. Starting with MySQL5.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 abandoned because the deadlock was killed or the lock wait timed out

②: progress tracking:

Track the progress of long-term operations (such as alter table). Starting with MySQL5.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 MySQL5.7.3, a new events_transactions_current table is added, through which transaction monitoring can be turned on through setup_consumers and setup_instruments tables, through which the status of the current transaction can be queried. 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 the state with a large number of transactions is in active, you can be sure that the database has a large number of transactions uncommitted.

④: memory usage:

Provide memory usage statistics, which is helpful to understand and adjust the memory consumption of the server. Starting from MySQL5.7.2, performance schema adds new memory-related statistics, which statistics the memory usage process from the perspective of account, access host, thread, user and event.

⑤: stored procedures:

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

2. Sys schema introduction:

New sys schema in MySQL5.7. Schema, which consists of a series of objects (views, stored procedures, storage methods, tables, and triggers), does not collect and store any information itself, but sums up the data in performance_schema and information_schema as "views" in a more understandable way.

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

①: aggregating performance pattern data into a more understandable view

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

③: a storage function for querying performance mode configurations and providing formatting services

-what is the function of sys schema in the query to view 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 that starts with host_summary mainly summarizes the information of IO latency, showing the information of file IO from the point of view of host, file event type, statement type and so on.

②: innodb related information:

The view that starts with innodb summarizes innodb buffer page information and transaction waiting innodb lock information

③: IO usage:

The view, which begins with IO, summarizes the information of IO users, including waiting for IO, IO usage, and grouping display from various angles.

④: memory usage:

View that starts with memory to show memory usage from the perspective of hosts, threads, users, and events

⑤: connection and session information:

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

⑥: table related information:

Views that start with schema_table show table statistics from full table scans, innodb buffer pools, and so on.

⑦: index information:

It contains views of index, counting index usage, as well as duplicate and unused indexes

⑧: statement related information:

Views that start with statement, statistics of normalized statement usage, including errors, warnings, full table scans, use of temporary tables, sorting, etc.

⑨: user-related information:

The view that starts with user counts the files used by users, IO, statements executed, and so on.

⑨: waiting for event-related information

:

The view starting with wait shows the latency of waiting events from the host and event point of view

4. Sys schema uses columns:

-View the number of visits to the table: (you can monitor the number of visits to each table or 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 look at indexes) select * from sys.schema_redundant_indexes\ Gselect * from sys.schema_unused_indexes (if you have redundant indexes and indexes that have not been used for a long time, you should clean them in time.)-check the self-increment ID usage of tables: select * from schema_auto_increment_columns\ G (you can use the schema_auto_increment_columns view to easily find the self-increment usage of each table. It can even be accurate to the self-increment of a table)-sql statement to monitor full table scans: 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 times performed Wait for information)-check the disk Imax 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 reasons for the excessive consumption of disk performance O, and locate the problem.) this is the end of this article on "what are the monitoring parameters of performance and sys schema in MySQL5.7?" I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.