Get the average execution time of all select queries

Question

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?

Answer 1

You need to look into mk-query-digest

This tool can actually run against mysql for a specific length of time you designate and it will produce a map of the top 20 queries by query pattern and give you average running times.

Here is an excellent YouTube video on how to use it as an adhoc slow log

UPDATE 2011-07-21 15:58 EDT

I have a pleasant surprise for you !!! If you are using MySQL 5.1, you can convert the general log and slow log into tables. I am using it right now with a big client. Here is how you can do it:

In the mysql schema, there are the tables general_log abd slow_log.

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.05 sec)

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.11 sec)

You can activate the use of them by setting this in /etc/my.cnf

[mysqld]
log-output=TABLE

Of course, who wants a CSV file for a general log and slow log ??? SURPRISE : CONVERT THEM TO MYISAM AND ADD AN INDEX TO THE TIME OF EACH ENTRY !!!

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL slow_query_log = @old_log_state;
SHOW CREATE TABLE mysql.slow_log\G

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
SET GLOBAL slow_query_log = @old_log_state;

Please note that the general log has the column event_time and the slow log has the column start time. Only the slow log has the column query_time.