2

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

2
  • It could be useful if you paste your my.cnf as well
    – lynxman
    Jan 24, 2011 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 Jan 24, 2011 at 17:47

1 Answer 1

0

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 !!!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .