Get the average execution time of all select queries

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.

Get the average execution time of all select queries

Related

Recent Posts