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

I have installed MySQL Community Edition 5.1.41 on a windows 2000 server. In my.ini file I have enabled slow query logging and have redirected the output to a table.I have set the long_query_time to 10 seconds. Then after running some queries I checked up the slow query log table and found that all the queries which were executed have been logged and a file called database-slow.log has also been created in the data folder. Can anybody please tell me where I am going wrong. I am using the inbuilt innodb and not activated the innodb plugin.

Thanks

share|improve this question
    
It could be useful if you paste your my.cnf as well –  lynxman Jan 24 '11 at 17:38
    
The following is the relevant part of my.cnf file long_query_time=10 log_output=TABLE slow_query_log=1 log_queries_not_using_index=1 innodb_file_per_table=1 –  Vinayak Mahadevan Jan 24 '11 at 17:47

1 Answer 1

For starters, I placed these lines in /etc/my.cnf

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

When you use the slow log with the log_output being TABLE, the table IS NOT created in /var/lib/mysql. The table is created in the mysql folder, /var/lib/mysql/mysql. The storage engine for the default table-based slow log is CSV. You can check this by doing the following:

use mysql
show tables;

You should see the table slow_log

MySQL> show create table 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.00 sec)

Here is how to convert the CSV file for the slow log table to MyISAM

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
SET GLOBAL slow_query_log = @old_log_state;

Keep in mind that the converted MyISAM does not have any indexes.

This is a column called 'start_time' which is a timestamp. Feel free to index it like this:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL slow_query_log = @old_log_state;

Let us know how this worked out, please !!!

share|improve this answer

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.