Ever wonder how to log slow queries to a MySQL table and set an expire time? I personally hate logging slow queries to a log file. Mainly because it gets huge, spikes disk I/O and it’s not as easy to read as logging to a MySQL table. By logging to a table not only is it stored nice and neat, but you can also set how long to keep the records for. The following MySQL settings should be added to your my.cnf within [mysqld] / [mysqld_safe] to enable slow query table logging:
slow_query_log = 1
long_query_time = 3
log_output = TABLE
expire_logs_days = 1
Of course you can change the time after which the records expire and what you consider a slow query in the values above. After adding them to my.cnf, MySQL will have to be restarted. If you do not want to restart MySQL, depending on your version (5.1+) you can execute the following queries to enable the same feature:
set global log_slow_queries = 1;
set global long_query_time = 3;
set global log_output = 'TABLE';
set global general_log = 1;
set global expire_logs_days = 1;
set global log = 1;
Alternatively, you can always add the first set to my.cnf, and reload your MySQL configuration:
service mysql reload
Note: Certain distros will have the init.d script named ‘mysqld’, or ‘mysql-server’ which you should be able to reload in the same manner.
Now that we enabled logging, how about some reading of logs? 🙂
mysql -e "select * from mysql.general_log order by event_time desc limit 10\G"
mysql -e "select * from mysql.slow_log order by start_time desc limit 10\G"
If you wish to log queries that did user an index, you can add the following to my.cnf:
Or update it directly in MySQL using the following query:
set global log_queries_not_using_indexes = 1
Hope this helps! Drop a comment if you have other methods 🙂