Looking for database brake causes using sys schema in MySQL 5.7

We have a web application. Relatively large and old - a lot of code in which there are many, many 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 getting better and more complicated, the number of clients and, accordingly, the amount of data in the database are growing. And at one not-so-wonderful moment comes the understanding that something our application is slowing down. So it is necessary either to find what exactly loads the database, although it might not load, well, or if there is nothing like that, then take more servers, more powerful.

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

To begin with, what problems are there using slow-query-log:

• When MySQL slows down, there are hundreds of queries that are not slow during normal load periods - for example, we observe from time to time fetching by primary key.
• If, for example, you put long_query_time = 3, it will not get a query that runs in 0.5 seconds, but which has a lot of calls.

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

We end with the introduction and move on to how to use sys schema, and examples of what you can see.

To begin with, 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

The variables are not dynamic, so after that a server reboot will be required. This, of course, is a minus.

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

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

Well, then you can use it.

The 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 to read the result, but inconvenient if you want to sort by them. For this, all tables in sys schema have twins of the form x $ table_name.

A selection of queries sorted by avg_latency that created temporary tables on 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 is 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

Queries doing full table scan

mysql> select * from statements_with_full_table_scans limit 10\G

Unused indexes (the server must be running 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 to me personally if you were interested - detailed documentation and examples of use are on github or in the official documentation .