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

Foreword:

A new sys schema,sys is introduced in MySQL 5.7. it is a system library that comes with MySQL. When you install a version of MySQL after 5.7and initialize with mysqld, the sys library is automatically created.

The tables, views, functions and stored procedures in the sys library can make it easier and faster for us to understand some information about MySQL, such as which statements use temporary tables, which SQL does not use indexes, which schema has redundant indexes, find the SQL that uses full table scans, find the IO occupied by users, and so on. Most of the data in these views in the sys library is obtained from performance_schema. The goal is to reduce the complexity of performance_schema, so that we can understand the operation of DB faster.

Overview of 1.sys Library

This article is based on the experiment of MySQL version 5.7.23. Open the sys library (I hope you can do it with me), and we will find that sys schema contains 1 table, 100 views, 48 stored procedures and functions, as shown in the following figure:

Cdn.nlark.com/yuque/0/2019/png/119537/1569308615078-35b82427-93e5-4abe-b1e9-8c832f7faabc.png ">

In fact, we often use the view under sys schema, the following will mainly introduce the role of each view, we find that the view in sys schema is mainly divided into two categories, one is normal to start with a letter, a total of 52, and the other is to start with x$, a total of 48. The view at the beginning of the letter shows formatted data, which is easier to read, while the view at the beginning of x$ is suitable for the tool to collect data, showing the original unprocessed data.

Below we will analyze the 52 views that begin with letters by category:

Host_summary: this is at the server level, grouped by IP, for example, the view host_summary_by_file_io;user_summary: this is at the user level, grouped by users, for example, the view user_summary_by_file_io;innodb: this is at the InnoDB level, such as the view innodb_buffer_stats_by_schema;io: this is the statistics of the Imax O layer, such as the view io_global_by_file_by_bytes Memory: about memory usage, such as view memory_by_host_by_current_bytes;schema: statistics about schema level, such as schema_table_lock_waits;session: about session level, there are fewer such views, only session and session_ssl_status;statement: about statement level, such as statements_with_errors_or_warnings;wait: about waiting, such as view waits_by_host_by_latency. two。 Introduction to common queries

1. Check how many resources are consumed by connections from each client IP.

Mysql > select * from host_summary

2 to see how many IO requests have occurred on a data file.

Mysql > select * from io_global_by_file_by_bytes

3. See how many resources each user consumes.

Mysql > select * from user_summary

4 to see how much memory is allocated in total.

Mysql > select * from memory_global_total

5. Where does the database connection come from, and what are the requests made by these connections to the database?

View the current connection.

Mysql > select host, current_connections, statements from host_summary

6. Viewing the currently executed SQL is equivalent to executing show full processlist.

Mysql > select conn_id, user, current_statement, last_statement from session

7. Which SQL in the database is executed frequently?

Execute the following command to query the hottest SQL of TOP 10.

Mysql > select db,exec_count,query from statement_analysis order by exec_count desc limit 10

Which file produces the most IO, read more or write more?

Mysql > select * from io_global_by_file_by_bytes limit 10

9, which table has the most IO requests?

Mysql > select * from io_global_by_file_by_bytes where file like'% ibd' order by total desc limit 10

10, which table is accessed the most? First visit statement_analysis and find the corresponding data table according to the sort of popular SQL.

Mysql > select * from statement_analysis order by avg_latency desc limit 10

11, which SQL performed a full table scan or a sort operation?

Mysql > select from statements_with_sorting

Mysql > select from statements_with_full_table_scans

12, which SQL statements use temporary tables and which use disk temporary tables?

You can check which SQL in statement_analysis has tmp_tables and tmp_disk_tables values greater than 0.

Mysql > select db, query, tmp_tables, tmp_disk_tables from statement_analysis where tmp_tables > 0 or tmp_disk_tables > 0 order by (tmp_tables+tmp_disk_tables) desc limit 20

13, which table takes up the most buffer pool?

Mysql > select * from innodb_buffer_stats_by_table order by allocated desc limit 10

14, how much buffer pool is occupied by each database?

Mysql > select * from innodb_buffer_stats_by_schema order by allocated desc limit 10

15, how much memory is allocated per connection?

Make use of session table and memory_by_thread_by_current_bytes allocation table for associated query.

Mysql > select b.user, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated,current_statement from memory_by_thread_by_current_bytes a, session b where a.thread_id = b.thd_id

16 what is the maximum value of the self-growing field and the value that has been used by MySQL?

Mysql > select * from schema_auto_increment_columns

What are the redundant indexes and useless indexes of 17J MySQL?

Mysql > select from schema_redundant_indexes

Mysql > select from schema_unused_indexes

18. Check the transaction waiting status

Mysql > select * from innodb_lock_waits

Summary:

This article mainly introduces the relevant contents of the sys library, in fact, the sys library has many useful queries that can help you easily understand the operation of the database. Originally, you need to find multiple tables in performance_schema to get the data, but now you can query a view. Of course, the sys library requires you to understand it in detail and summarize the query methods you need.

Reference:

Https://blog.csdn.net/l1028386804/article/details/89521908

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.