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

This article mainly introduces how to use the tables under the sys database to query the database performance of mysql 5.7. it is very detailed and has a certain reference value. Interested friends must read it!

1. Who uses the most resources? Based on ip or user?

This question can be understood from three aspects: host, user and io, and probably who has the most requests. For the problem of using resources, you can directly from one of the following four views

A general understanding.

Select * from host_summary limit 1\ G

Select * from io_global_by_file_by_bytes limit 1\ G

Select * from user_summary limit 1\ G

Select * from memory_global_total\ G

Note: memory part, excluding innodbbuffer pool. Just the memory requested by the server layer

two。 Where most of the connections come from and the sql sent

View the current connection:

Select host, current_connections,statements from host_summary

View the currently executing sql:

Select conn_id, user, current_statement, last_statement from session

3. What are the most sql statements that the machine executes?

Query the most executed top 10 sql in the system:

Select * from statement_analysis order byexec_count desc limit 10\ G

4. Which table has the most io? Which table is visited the most?

Select * from io_global_by_file_by_bytes limit 10

Which table has the most access times? you can refer to the above query which executes the most statements, and then look up the corresponding table. The sql is as follows:

Select * from statement_analysis order by exec_count desc limit 10\ G

5. Which statements have a serious delay?

The highest avg_latency in statement_analysis, sql statement:

Select * from statement_analysis order by avg_latency desc limit 10\ G

6. Which sql statements use disk temporary tables

Use tmp_tables and tmp_disk_tables in statement_analysis to calculate. Refer to sql:

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

7. Which table takes up the most buffer pool

Query the top 10 tables in buffer pool. The sql is as follows:

Select * from innodb_buffer_stats_by_tableorder by pages desc limit 10

8. How many buffer pool are occupied by each library

Select * from innodb_buffer_stats_by_schema

9. How much memory is allocated per connection

Use session table and memory_by_thread_by_current_bytes allocation table to make association query. The sql is as follows:

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 dint session b where a.thread_id = b.thd_id

10. There are now multiple threads running inside the mysql

Type and number of threads within mysql:

Select user, count (*) from processlistgroup by user

These are all the contents of the article "how to query database performance using tables under sys database in mysql 5.7.Thank you for reading!" Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.