Slow Query log for just one database

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?

Solution 1:

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.

Solution 2:

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

Slow Query log for just one database

Solution 1:

Solution 2:

Related

Recent Posts