How to Get Query Statistics from MySQL for Performance Monitoring

Here at Mindsize, the performance of your online store is of the highest priority to us. We employ several methods of monitoring performance and gathering metrics. I want to highlight one method that gives us insight into the number of queries running on your MySQL database server. It is useful on a single site, and it really shined during a recent audit we did to discover database performance issues on a database server housing the contents for several sites.

Through this article, you will learn how to calculate the number of queries per second, minute, hour, and day for SELECT, INSERT, UPDATE and DELETE.

Something to keep in mind is that MySQL stores the total number since the last flush, so the results are averaged through the day. If you need details for a specific time frame another method that tracks query analytics will be required.

I mentioned the numbers we will get are since the last flush but did not explain what that meant. MySQL periodically, or on-demand, resets itself and the query counts go back to zero. Lucky for us, MySQL stores the last flush in a variable called

'Uptime_since_flush_status'.

You can find the uptime with the query:

select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status’; 

This number will be handy later when generating the report with time breakdowns.

To retrieve the numbers per query type we need to dig into the information_schema table. The following query will show the total count since last flush:

select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME in ('Com_select', 'Com_delete', 'Com_update', 'Com_insert’);

We now have the two key pieces needed to create the aforementioned number breakdown. To more easily facilitate this, let’s set up a view. The following code looks scary, but all it is doing is taking the total counts and dividing it into the requested time frames. Easy peasy

CREATE OR REPLACE view _dba_query_stats AS
SELECT Substring(variable_name, 5)      AS query_type,
       variable_value                   AS total_count,
       Round(variable_value /
       (
              SELECT variable_value
              FROM   information_schema.global_status
              WHERE  variable_name = ‘Uptime_since_flush_status’), 2) AS per_second,
       Round(variable_value / (
       (
              SELECT variable_value
              FROM   information_schema.global_status
              WHERE  variable_name = ‘Uptime_since_flush_status’) / (60))) AS per_minute,
       Round(variable_value / (
       (
              SELECT variable_value
              FROM   information_schema.global_status
              WHERE  variable_name = ‘Uptime_since_flush_status’) / (60*60))) AS per_hour,
       Round(variable_value / (
       (
              SELECT variable_value
              FROM   information_schema.global_status
              WHERE  variable_name = ‘Uptime_since_flush_status’) / (60*60*24))) AS per_day,
       From_unixtime(Round(Unix_timestamp(Sysdate())              
       (
              SELECT variable_value
              FROM   information_schema.global_status
              WHERE  variable_name = ‘Uptime_since_flush_status’)))    report_period_start,
       Sysdate()                                                    AS report_period_end,
       Time_format(Sec_to_time(
       (
              SELECT variable_value
              FROM   information_schema.global_status
              WHERE  variable_name = ‘Uptime_since_flush_status’)),‘%Hh %im’) AS report_period_duration
FROM   information_schema.global_status
WHERE  variable_name IN (‘Com_select’,
                         ‘Com_delete’,
                         ‘Com_update’,

To use the view, run the following query:

select * from _dba_query_stats;

You will get a chart that looks similar to this

This data is useful to get a general idea of what is going on inside your database server. In the case of this example, with 280 select queries running per second on a machine hosting both the web server and the database server, I would start looking at solutions to take some load off the database server. Perhaps a caching solution. In another case, it could perhaps indicate an area of the codebase to be optimized, or maybe it is a way to prove your database server is operating well under capacity.

I hope this article has been insightful into one of the methods we use to monitor and audit database performance. If you found it useful, share this with a friend and let us know on social media your results.

Useful Resources


Leave a Comment





This site uses Akismet to reduce spam. Learn how your comment data is processed.