Query Response time histogram – new feature in Percona Server

Recently we had couple posts dedicated to performance monitoring, i.e. Color code your performance numbers, Performance Optimization and Six Sigma, so you may understand we consider stability of performance numbers as one of important area for database management.

That’s why we decided to add histogram of queries response times into Percona Server, and our software engineer Oleg Tsarev implemented this feature. The feature adds new INFORMATION_SCHEMA table QUERY_RESPONSE_TIME
( full docs are on page response_time_distribution), which looks like

and which basically means that there were 7040 queries with execution time less than 1 sec and more than 0.1 sec.

The feature will be available in next release of Percona Server 5.1-12.0, but you can try it earlier – we setup our BuildBot farm to generate binary snapshots for each commit into lp:percona-server repository, and now you can take binary version for each particular commit from our TESTING stage

And to demonstrate possible usage of histogram of response time, let me show you
how increasing throughput affects response time. There are histograms and pie charts for sysbench oltp runs
for 1, 32 and 128 concurrent threads

1 thread

so as you see in this case 10% of all queries were executed with execution time in interval (0.1ms, 1 ms] and 86% queries had execution time in interval (0.01ms, 0.1ms]

32 threads

With 32 thread percent of queries in interval (0.1ms, 1ms] increased to 30%, and (0.01ms, 0.1ms] dropped to 68%

128 threads

For 128 threads we see now 12% of queries went into 1 ms < resp time <= 10ms, which we did not see previously,
and (0.1ms, 1ms] increased to 35%, while percentage of queries <=0.1ms is now only 52%.

I expect Baron will add this to Cacti templates, so you can setup monitoring of response times.

Share this post

Comments (11)

  • Rob Wultsch Reply

    This will be very useful. I image that it would be useful if there was some way to tag a query with a keyword so that the information is not skewed by slow or fast queries. Without the tagging I imagine that most db’s will have a somewhat broad curve. With tagging I imagine the curve would be much more narrow and it would be easier to discern behavior changes.

    July 11, 2010 at 8:02 pm
  • Vadim Reply


    I agree, tagging of queries would give even more sense for query monitoring.
    I think we will add it eventually.

    July 11, 2010 at 8:06 pm
  • Mark Callaghan Reply

    Excellent. It is great to see our tools and processes get better.

    July 11, 2010 at 8:36 pm
  • matt Reply

    thanks, it’s very useful!

    July 11, 2010 at 8:51 pm
  • Shlomi Noach Reply

    Very good stuff! Keep up the good work!

    July 11, 2010 at 11:24 pm
  • Farid Reply

    Great stuff !

    Wondering if there is any overhead with this function enabled ?

    July 12, 2010 at 1:54 am
  • peter Reply


    This feature acts as a “fingerprint” for a lot of systems – there are going to be some slow queries and some short two, while distribution will stay more or less the same.
    Not all the systems have this property of course some may have cron jobs etc which cause significant behavior variance.

    For systems with stable workload we can monitory such histogram every 5 minutes or so and track for significant variances.

    Periodic spikes in response time is a frequent problem which is often noticed too late – after it significantly impacts production. Though most of such problems start with more subtle changes.

    July 12, 2010 at 9:19 am
  • Dimitri Reply

    Great stuff!! – specially when monitoring live the difference between periods you may point exactly to the moment when the deviation is happening!..

    Will add it to dim_STAT too 🙂


    July 12, 2010 at 11:41 am
  • Sean Reply

    Vadim, is there an eta date on the next release (5.1-12.0)?

    August 16, 2010 at 3:43 pm
  • Vadim Reply


    It should come in next two weeks.

    August 16, 2010 at 6:23 pm
  • Baron Schwartz Reply

    There are now graphs for this in the Cacti templates I maintain:


    Scroll down to these graphs:

    # MySQL Query Response Time (Microseconds)
    # MySQL Query Time Histogram (Count)

    January 23, 2011 at 2:32 pm

Leave a Reply