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.

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

possible?

share|improve this question
    
I added more suggestions !!! –  RolandoMySQLDBA Jun 20 '11 at 22:08
add comment

2 Answers

up vote 5 down vote accepted

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

[mysqld]
log-output=TABLE
slow-query-log
slow-query-log-file=slow-queries.log

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` (
  `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,
  KEY `start_time` (`start_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
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

[mysqld]
replicate-do-db=mysql

Step 02) Activate the slow log on the slave

Step 03) Run this command on the master

SET SQL_LOG_BIN=0;
ALTER TABLE mysql.slow_log ENGINE=BLACKHOLE;

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.

share|improve this answer
    
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
add comment

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:

http://code.google.com/p/mysql-log-filter/

http://www.maatkit.org/doc/mk-query-digest.html

share|improve this answer
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.