Thoughts on Monitoring the Performance of a Single SQL Query

Sometimes in reports/articles on DBMS performance optimization, the description of the proposed method/tool ​​begins with the event -“we noticed a sharp increase in the execution time of the query/queries and a sharp increase in the number of shared area blocks read“. The following is a description of the process of identifying a resource-intensive query in order to optimize it.

At the data development stage, the scenario is quite justified. The load on the DBMS is determined, the nature of the load is defined and described, the data is constant. Provided that the development team is adequate, it will even be possible to really optimize the query.

But.

During industrial operation, the situation changes fundamentally and radically.

1. The load on the DBMS varies over a wide range and is random in nature.

2. The nature, volume and statistical picture of the data is highly variable.

3. The impact of infrastructure is generally unpredictable.

4. The input data for queries varies widely.

5. There is no way to change the request code, at the very least it is very difficult.

6. As a rule, there is no longer any support for the system from developers.

And this gives rise to essential Problems:

1) How can you determine that the performance of a specific query has degraded?

2) What is the query performance metric? Max time, average time, min time, standard error?

3) How to evaluate the performance of a query on different input data? In one case, the query processes X rows and is executed in x time, in another case, the query processes Y rows and is executed in y time. Is it possible to say about the degradation of the execution performance (y > x) if Y significantly more than X?

4) How to evaluate query performance under different loads on the infrastructure and information system.

Thus, the problem can be formulated approximately as follows:

1. You can collect the history of query performance indicators (group of queries) for any required period of time. There are more than enough tools.

2. How to analyze the collected data?

3. What are the expectations from the test results?