Shulou(Shulou.com)06/01 Report--

mysql performance and sys monitoring parameters in MySQL 5.7

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, reduced footprint and load, and significantly improved ease of use through new sys schema mechanisms. In terms of monitoring, the performance schema has the following functions:

①: Metadata lock:

It is important to understand metadata lock dependencies between sessions. Starting with 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 abandoned due to deadlocks or lock wait timeouts

②: Progress tracking:

Track the progress of long-running operations (such as alter tables). Starting with MySQL 5.7.7, performance schemas automatically provide statement progress information. We can view the progress information of the current event through the events_stages_current table;

3: Business:

Monitor all aspects of service tier and storage engine tier transactions. Since MySQL 5.7.3, the events_transactions_current table has been added. You can open transaction monitoring through setup_consumers and setup_instruments tables, and query the status of the current transaction 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 transactions in the uncommitted state through this table. If it is found that there are indeed a large number of transaction states active, then it can be determined that the database has a large number of uncommitted transactions;

④: Memory usage:

Memory usage statistics are provided to help understand and adjust server memory consumption. From MySQL 5.7.2 onwards, performance schema adds memory-related statistics, respectively from the perspective of accounts, access hosts, threads, users and events.

5: Storage program:

Detectors for stored procedures, stored methods, event dispatchers, and table triggers. In MySQL 5.7, the setup_objects table adds detectors for event, function, procedure, and trigger. 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 MySQL 5.7. A schema that consists of a series of objects (views, stored procedures, stored methods, tables, and triggers) that does not collect and store information itself, but summarizes the data in performance_schema and information_schema in a more understandable way into a "view."

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

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

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

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

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

3. Classification of tables in sys schema:

①: Host related information:

The view starting with host_summary mainly summarizes IO delay information, showing file IO information from the perspective of host, file event type, statement type, etc.;

2: InnoDB related information:

A view that begins with innodb, summarizing innodb buffer page information and transaction waiting innodb lock information;

③: IO usage:

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

④: Memory usage:

A view that begins with memory, showing memory usage from host, thread, user, and event perspectives;

④: Connection and session information:

processlist and session-related views summarize session-related information;

Table Related Information:

Views that begin with schema_table, showing table statistics in terms of full table scans, innodb buffer pools, and so on;

7: Index information:

A view that contains index, statistics on index usage, duplicate indexes, and unused indexes;

8: Sentence related information:

View starting with statement, statistics of normalized statement usage, including error number, warning number, full table scan executed, temporary table used, sorting executed, etc.;

: 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:

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

4. sys schema uses columns:

---View table access: (You can monitor the access of each table, or monitor changes in the access of 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;

---Redundant indexes and unused index checks: (schema_redundant_indexes and schema_unused_indexes check index conditions)

select * from sys.schema_redundant_indexes\G

select * from sys.schema_unused_indexes;

(If there are redundant indexes and long-term unused indexes, they should be cleaned up in time.)

---View table Self-increasing ID usage:

select * from schema_auto_increment_columns\G

(You can use the schema_auto_increment_columns view to easily find out the self-increment usage of each table, or even the exact self-increment of a table)

---sql statement to monitor 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, etc.)

---View disk I/O consumed by instance: ()

select file,avg_read+avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10;

(See io_global_by_file_by_bytes view to check the cause of excessive disk I/O consumption and locate problems)

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.