The Slow Query Log consists of log events for queries taking up to
long_query_time seconds to finish. For instance, up to 10 seconds to complete. To see the time threshold currently set, issue the following:
SELECT @@long_query_time; +-------------------+ | @@long_query_time | +-------------------+ | 10.000000 | +-------------------+
It can be set as a GLOBAL variable, in
my.ini file. Or it can be set by the connection, though this is unusual. The value can be set between 0 to 10 (seconds). What value to use?
The capturing of slow queries is either turned on or off. And the file logged to is also specified. The below captures these concepts:
SELECT @@slow_query_log; -- Is capture currently active? (1=On, 0=Off) SELECT @@slow_query_log_file; -- filename for capture. Resides in datadir SELECT @@datadir; -- to see current value of the location for capture file SET GLOBAL slow_query_log=0; -- Turn Off -- make a backup of the Slow Query Log capture file. Then delete it. SET GLOBAL slow_query_log=1; -- Turn it back On (new empty file is created)
For more information, please see the MySQL Manual Page The Slow Query Log
Note: The above information on turning on/off the slowlog was changed in 5.6(?); older version had another mechanism.
The "best" way to see what is slowing down your system:
long_query_time=... turn on the slowlog run for a few hours turn off the slowlog (or raise the cutoff) run pt-query-digest to find the 'worst' couple of queries. Or mysqldumpslow -s t