MySQL Slow Query Log – change it without having to restart mysql

How do you enable slow query logging in MySQL without having to restart?

set @@global.slow_query_log = 1;

I prefer to log these things to the mysql.slow_log table rather than to a file. This way you can view the slow queries with this query: "select * from mysql.slow_log":

set @@global.log_output = "TABLE";

Set the threshold above which a query is considered a “slow query” with this (in seconds):

set @@global.long_query_time = .33;

You can view your current settings for all these configs with these queries:



select @@global.slow_query_log;

select @@global.log_output:

select @@global.long_query_time;


You can make these setting permanent so they are set when mysql starts by updating the my.cnf file with these:



--slow_query_log = 1

--log-output = "TABLE"

--long_query_time = .3


http://ift.tt/kChfet

Clear out the slow query data logged to that mysql.slow_log table:

truncate table mysql.slow_log (or any sql delete statement I think)

A note on select @@long_query_time vs. @@global.long_query_time:

@@long_query_time shows your current session settings, while @@global.long_query_time shows global settings for all sessions.

http://ift.tt/1utvdrX

Just use @@global.long_query_time, @@global.slow_query_log, @@global.log_output.

set @@global.log_slow_queries is what was used in older versions of mysql. Don’t use it now though, if only for “staying with the times”.