CODEWITHSUNDEEP
mysqlxampperror-logmysql-slow-query-log
RGriffiths
RGriffiths

Reputation: 5970

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'

Upvotes: 3

Views: 5295

Answers (3)

Paul Gobée
Paul Gobée

Reputation: 521

It might be obvious but it took me time before I realized my mistake: in the my.ini file you should put the slow_query_log settings in the [mysqld] group, not simply at the end of the my.ini file....

Upvotes: 0

user9050678
user9050678

Reputation: 323

Go to xampp control panel click on config button for mysql and select my.ini then add these lines in my.ini file

 slow_query_log = 1
 slow-query-log-file=/path/of/the/log/file.log

I put above two lines under the log_error = "mysql_error.log". the modified part of the my.ini file should look like this

    # The MySQL server
    [mysqld]
    port= 3306
    socket = "C:/xampp/mysql/mysql.sock"
    basedir = "C:/xampp/mysql" 
    tmpdir = "C:/xampp/tmp" 
    datadir = "C:/xampp/mysql/data"
    pid_file = "mysql.pid"
    # enable-named-pipe
    key_buffer = 16M
    max_allowed_packet = 1M
    sort_buffer_size = 512K
    net_buffer_length = 8K
    read_buffer_size = 256K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 8M
    log_error = "mysql_error.log"
    slow_query_log = 1
    slow-query-log-file=/var/log/mysql-slow.log

Then Restart the MySQL server in xampp control panel. and now slow_query_log should be enabled, you can confirm it by running following command in the MySQL shell

show variables like '%slow%';

Upvotes: 4

Drew
Drew

Reputation: 24960

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

Upvotes: 6

Related Questions