Looking for the causes of database brakes using sys schema in MySQL 5.7

We have a web application. Relatively large and old - a lot of code, in which a lot of different database queries. At the same time, we are not Google, but several thousand requests per second to the database server are necessary.

Well, our application certainly grows in all directions - new features are added, old ones are wrapped around and complicated, the number of clients grows and, accordingly, the amount of data in the database. And in one not very beautiful moment comes the understanding that something our application slows down. So it is necessary or to find out what exactly is loading the database, although it could not be loading, well, or if there is nothing like that, then take more servers, more powerful.

The standard tip on how to find what loads MySQL is to enable the slow-query-log and see which queries will get there. But in MySQL 5.7, by default, the best tool is present - sys schema , which aggregates data from the performance schema and allows you to get it with simple queries, literally like “Ok, MySQL, show me the top queries by the maximum total execution time”

First, what problems are there with using slow-query-log:
')

• When MySQL slows down, there are hundreds of queries that are not slow during periods of normal load - for example, we see there from time to time sampling on the primary key.
• If, for example, you put long_query_time = 3, it will not receive a request that is executed in 0.5 seconds, but there are a lot of calls.

It appeared in MySQL 5.5, and the performance schema developed in newer versions, but it is not as easy to use as we would like.

This ends the introduction and proceeds to how to use the sys schema, and examples of what can be seen.

First of all, if your queries are more complicated than “SELECT a FROM b WHERE c =?”, You should correct the restrictions on the length of the query text stored in the performance schema. By default, it is 1024 - suddenly this is enough for you, but it turned out to be not enough for me.

Add to my.cnf:

max_digest_length=10240 performance_schema_max_sql_text_length=10240 performance_schema_max_digest_length=10240 

Variables are not dynamic, so after that you will need to restart the server. This, of course, a minus.

Next, we change the maximum displayed length of the text of the requests in the sys schema responses (by default it is 64 - and here I don’t understand at all who this will be enough)

 use sys; update sys_config set value=10240 WHERE variable='statement_truncate_len'; 

Well, then you can use.

Previously mentioned top queries for the maximum total execution time:

 mysql> select * from statement_analysis limit 10 \G *************************** 1. row *************************** query: SELECT `AccSettingValue` FROM `accsetting` WHERE `accsetting` . `AccSettingName` = ? db: mydb full_scan: exec_count: 2065339 err_count: 0 warn_count: 0 total_latency: 1.75 m max_latency: 16.52 ms avg_latency: 50.72 us lock_latency: 48.90 s rows_sent: 0 rows_sent_avg: 0 rows_examined: 0 rows_examined_avg: 0 rows_affected: 0 rows_affected_avg: 0 tmp_tables: 0 tmp_disk_tables: 0 rows_sorted: 0 sort_merge_passes: 0 digest: 229c950384bddbaa0e537f54beaa1ac4 first_seen: 2018-03-19 20:20:43 last_seen: 2018-03-21 12:27:21 

Using the example of this query, we see that the% _latency values ​​are returned in a human-readable form, which is convenient for reading the result, but inconvenient if you want to sort by it. For this, all tables in the sys schema have twins of the form x $ table_name.

A selection of queries sorted by avg_latency, creating temporary tables on the disk, will look something like this:

 mysql> select * from x$statement_analysis WHERE tmp_disk_tables > 0 ORDER BY avg_latency DESC limit 10 \G 

A couple more examples of what's useful can be seen using sys schema.

Requests, the average execution time of which is in the top 5%:

 mysql> select * from statements_with_runtimes_in_95th_percentile LIMIT 10\G 

Queries that create temporary tables:

 mysql> select * from statements_with_temp_tables limit 10\G 

Requests that make a full table scan

 mysql> select * from statements_with_full_table_scans limit 10\G 

Unused indexes (the server should work for a sufficiently long time so that this data can be trusted):

 mysql> select * from schema_unused_indexes limit 10; 

This is what was most useful for me personally, if you are interested - detailed documentation and usage examples are on github or in the official documentation .