Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Does the MySQL general_log option include query times? –  voretaq7 Jul 21 '11 at 18:59
    
"not even the general query log seems to contain this", so sadly, no. –  Wrikken Jul 21 '11 at 19:03
    
Updated my answer to include stuff on the general log and slow log –  RolandoMySQLDBA Jul 21 '11 at 19:58
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Check, that 3rd option from that page looks a lot like what I want, will record some traffic to the general log to check it out. Do you perhaps know that although it states 'General logs do not report query times so only the cnt aggregate makes sense because all query times are zero.' I think I'm correct in assuming that when I use --execute I can still get those times? Because I'd rather enable the general log for a while and use those queries then abusing the slow log with a long query time of 0. –  Wrikken Jul 21 '11 at 19:46
    
BTW if the slow log is recording stuff with query time zero, set long_query_time in /etc/my.cnf to a higher number. To set it with a restart of mysql, run SET GLOBAL long_query_time = 5; –  RolandoMySQLDBA Jul 21 '11 at 20:00
    
Hah, that table thing may come in handy if it's still there in 5.5 (I assume so), albeit that for the current problem I'll try to get the query time with the mk-query-digest option & --execute (I see no I can group on users, a very big plus). Any benchmarks in performance of file- versus table-based logging? –  Wrikken Jul 21 '11 at 20:03
    
It's in MySQL 5.5 –  RolandoMySQLDBA Jul 21 '11 at 20:30
    
Don't know about any benchmarks against the two methods, but the mk-query-digest stunt may suit your need at best right now –  RolandoMySQLDBA Jul 21 '11 at 20:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.