Example
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.cnf or 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?
- 10 is so high as to be almost useless;
- 2 is a compromise;
- 0.5 and other fractions are possible;
- 0 captures everything; this could fill up disk dangerously fast, but can be very useful.
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
