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
|
1 2 3 4 5 6 7 8 |
time count 0.000001 53 0.000010 204840 0.000100 12064767 0.001000 8095309 0.010000 2889362 0.100000 19341 1.000000 7040 |
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
|
1 2 3 4 5 6 7 |
time count 0.000001 1 0.000010 102818 0.000100 2057031 0.001000 242722 0.010000 188 0.100000 3 |
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
|
1 2 3 4 5 6 7 8 |
time count 0.000001 21 0.000010 203637 0.000100 18723323 0.001000 8366615 0.010000 177225 0.100000 5077 1.000000 2240 |
With 32 thread percent of queries in interval (0.1ms, 1ms] increased to 30%, and (0.01ms, 0.1ms] dropped to 68%
128 threads
|
1 2 3 4 5 6 7 8 |
time count 0.000001 53 0.000010 204840 0.000100 12064767 0.001000 8095309 0.010000 2889362 0.100000 19341 1.000000 7040 |
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.
