By default, slow queries are not recorded anywhere.
There are three(3) ways to activate the Slow Query Log
ACTIVATION #1 : Text File
To log slow queries to a text file, please add the following to the /etc/my.cnf
[mysqld]
long-query-time=5
log-output=FILE
slow-query-log
slow-query-log-file=/var/log/mysql-slow-queries.log
ACTIVATION #2 : CSV Table (mysql.slow_log)
To log slow queries to mysql.slow_log, please add the following to the /etc/my.cnf
[mysqld]
long-query-time=5
log-output=TABLE
slow-query-log
ACTIVATION #3 : Both Methods (Text File and Table)
[mysqld]
long-query-time=5
log-output=FILE,TABLE
slow-query-log
slow-query-log-file=/var/log/mysql-slow-queries.log
SUMMARY
Once you edit /etc/my.cnf (or my.ini for Windows), you must restart mysql
- For Linux,
service mysql restart
- For Windows
net stop mysqlnet start mysql
If you would like to test the slow log after the mysql restart, simply run this:
SELECT SLEEP(12);
If long-query-time >= 12, this statement should land the the slow log.
Here are the variables used in the above methods
For more information on Slow Query Logging, please read the MySQL Documentation
CAVEAT
If you decide to use mysql.slow_log, keep in mind that the default mysql.slow_log is a CSV table. You can query against mysql.slow_log, but the table has no index.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.05 sec)
mysql>
If you would like the slow log to be a MyISAM so you can query it like any other table, please run the following:
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
Here is what you should see afterwards:
mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE mysql.slow_log ADD INDEX (start_time);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
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)
mysql>
You can read my past answers on this:
