can I enable the slow query log specifically for just one database?

What I've done currently is to take the entire log into excel and then run a pivot report to work out which database is the slowest. So i've gone and done some changes to that application in the hope of reducing the slow query occurence. rather than running my pivot report again which took a bit of time to cleanse the data i'd rather just output slow queries from the one database



I have something a little unorthodox if you really want to have a slow query log for a particular database. Keep in mind that what I am about to suggest works for MySQL 5.1.30 and above:

Step 01) Start off by adding these to /etc/my.cnf


log-output lets you specify the output of general logs and slow logs to be tables rather than text files.

Step 02) service mysql restart

There is a table in the mysql schema called slow_log

In mysql run this

SHOW CREATE TABLE mysql.slow_log\G

It should be a CSV table.

Step 03) Convert it to MyISAM and Index the Table on the start_time column

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

It should look like this

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `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,
  KEY `start_time` (`start_time`)
1 row in set (0.00 sec)

Notice that one of the columns is db. You should add an additional index as follows:

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

From here you could perform one of three(3) options

OPTION 1 : Query mysql.slow_log by the database you want

mysql> SELECT * FROM mysql.slow_log WHERE db='whateverdbiwant';

OPTION 2 : Delete all entries in mysql.slow_log that does not come from whateverdbiwant

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
DELETE FROM mysql.slow_log WHERE db <> 'whateverdbiwant';
SET GLOBAL slow_query_log = @old_log_state;

OPTION 3 : Copy all entries into another slow_log table for you to query from

DROP TABLE IF EXISTS mysql.my_slow_log;
CREATE TABLE mysql.my_slow_log LIKE mysql.slow_log;
ALTER TABLE mysql.my_slow_log DROP INDEX db;
ALTER TABLE mysql.my_slow_log DISABLE KEYS;
INSERT INTO mysql.my_slow_log SELECT * FROM mysql.slow_log db='whateverdbiwant';
ALTER TABLE mysql.my_slow_log ENABLE KEYS;

Give it a Try !!!

UPDATE 2011-06-20 18:08 EDT

I have an additional unorthodox idea you might like.

Try moving the MyISAM table's .MYD and .MYI for the slow log over to another disk volume. Them symlink /var/lib/mysql/mysql/slow_log.MYD and /var/lib/mysql/mysql/slow_log.MYI to the new location of the real .MYD and .MYI.

If you thought that was crazy, here is yet another unorthodox idea you might like.

If you have binary logging turned on already, setup replication to move the the slow to another box. How in the world do you do that ???

Step 01) Setup replication slave with this option


Step 02) Activate the slow log on the slave

Step 03) Run this command on the master


By using the BLACKHOLE storage engine, This eliminates disk I/O to the slow log on the master. The slave is setup so that its sole purpose is to collect entries for mysql.slow_log.

  • It should works, but logging to tables is horrible for performances.
    – Kedare
    Jun 20 '11 at 21:51
  • logging slow-query-log shouldn't affect performance. Likely, the frequency of slow queries logged scales with the load and power of the server.
    – thinice
    Jun 20 '11 at 22:13
  • great effort thanks. I will give you feedback once it's collected some data
    – Jason
    Jun 21 '11 at 11:40

I don't think you have such options available in MySQL, but there are ways you can get it, I searched for you in google and found some interesting links that might help you achieving your problem.

Hope this is helpful:



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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