tl;dr: How can I get the average query execution time of select statements of a running MySQL server?
At them moment we have several database servers, with some slaves treated as read-only servers. To hopefully increase performance, we've set up a test with a partitioned table (which contains often-accessed new data, and an extreme amount of historical data that the powers that be have decided should stay in that table).
Now, we've partitioned based on a datetime column, and in some manual testing, some queries are faster, some are slower (obviously, as a good portion explicitly limits the result on that column, and some queries explicitly don't want that).
All fine, it all seems to work, with a slightly higher then average slow-query count on the partitioned server then on the non-partitioned server. That was to be expected, but usually, those queries also have a lower priority then the ones that do want the latest data. It is however quite difficult to objectively compare performance between the 2 slaves. Most queries that are sped up weren't in the slow log to begin with, average load on the servers is about the same etc.
Ideally I'd want the average execution time of all select queries on the 2 slaves, so I can compare whether there is an overall speedup or degrade after partioning. In mysqlreport
or mysqladmin ext
nothing springs out as a value I could use for this, and not even the general query log seems to contain this. I've thought about setting the long_query_time
for 0 for a while, but that would really slow the servers down, so I'm open to other options, if there are any?
general_log
option include query times?