5.1 Query Response Time index (QRTi)

QRTi stands for "Query Response Time index". It is a "quality of service" measurement for each query, and it uses the Apdex formula for that calculation: http://en.wikipedia.org/wiki/Apdex

How QRTi is defined

The three measurement conditions are "optimum", "acceptable", and "unacceptable", which are defined as:

Table 5.1 QRTi value definitions

TypeDefault time valuesAssigned valueDescriptionColor
Optimum100ms1.00 (100%)The optimal time frameGreen
Acceptable4 * Optimum -- 100ms to 400ms0.50 (50%)An acceptable time frameYellow
UnacceptableExceeds Acceptable -- greater than 400ms0.00 (0%)An unacceptable time frameRed

An example calculation

From there, we calculate an average to determine the final QRTi value. For Example, if there are 100 executions of the digested/canonical query, where 60 finished below 100ms (the optimal time frame), 30 between 100ms and 400ms (the acceptable time frame), and the remaining 10 took longer than 400ms (unacceptable time), then the QRTi score is:

      ( (60 + (30 / 2) + (10*0) ) / 100) = 0.75.
    

Reading the QRTi value from the MySQL Enterprise Monitor User Interface.

The queries listed on the Query Analyzer page also have a color coded pie chart representing a breakdown of the values used in the QRTi calculation (green representing the optimal time frame, yellow the acceptable time frame, and red the unacceptable). You can mouse over the pie chart itself to see the total number of query executions that fell within each category, as well as the percentage of query executions that fell within that group.

So when doing query optimization, you want to start with the ones that have a QRTi visual pie chart that is 100% red, which means that they also have an actual QRTi value of 0. This means that *all* executions of that query took longer than the acceptable time frame (400ms by default). You can then click on the query to get more information, such as the maximum and average query times, the average number of rows examined, the average lock wait time, examine a sample query, look at an example EXPLAIN plan, see if full table scans were done, examine index usage, etc.

You can then work your way up from the queries with a QRTi value of 0, towards those that have a value of 1 (1 meaning that all instances of the query executed within the optimal time frame). Once you get to the point that you no longer have any queries with a QRTi value of less than 1, then you can go into the Query Analysis Reporting Advisor configuration, and adjust the QRTi Threshold (the target time) down, say to 50ms, and start the process all over again.