Search code examples
mysqlxampperror-logmysql-slow-query-log

Enabling mysql slow query log using XAMPP 5.6.21

I have looked at many similar questions to this but I can't seem to find the answer. I would like to set up the slow query log for my MySQL database. I have seen many answers saying I should access the MySQL command line tool. I am not sure exactly how to find this tool but I tried accessing it by going to:

c:/xampp/mysql/bin/mysql -u root -p -h localhost

But here I get MariaDB, which seems to be different from any other answers/tutorials I have seen before. Typing in:

set log_slow_queries = ON;

gives me the error

ERROR 1193 (HY000): Unknown system variable 'log_slow_queries'

Solution 
  • SET GLOBAL slow_query_log=1;

    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