MySQL Slow Query Log

Why, oh why, is this so complicated?!

Well, it’s not… just a bit confusing when you don’t know where to look.

TL,DR: (Ubuntu 16.04, otherwise YMMV, read below)

Add the following to your active configuration file for mysql

[mysqld]
slow_query_log
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

What could go wrong?

Wrong configuration file:

on Ubuntu 16.04 the file can be in /etc/mysql/my.cnf or /etc/mysql/conf.d/mysql.cnf or even /etc/mysql/mysql.conf.d/mysqld.cnf

to find which one is “active”, run: mysqld --verbose --help |grep -A 1 "Default options"

the result is something like the following:
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

if you open /etc/mysql/my.cnf, you will find at the bottom:
!includedir /etc/mysql/conf.d/

That’s where the actual files are stored.

Wrong variable names:

If you follow different tutorials on the Internet you will find configurations that mention:
log_slow_queries=/var/log/mysql/slow-query.log
DO NOT use that, it’s deprecated.
the new name of the variable is `slow_query_log_file` for the actual log file, and YOU SHOULD have `slow_query_log` as a boolean variable (ON, 1, or just mentioned in the file as per the code above).

Troubleshooting

Tail the /var/log/mysql/error.log in a separate terminal, see what’s failing. Example:

[Warning] option 'slow_query_log': boolean value '/var/log/mysql/mysql-slow.log' wasn't recognized. Set to OFF.
Obviously the variable is set wrongly to the filename. They are now 2 separate variables.

Similar Posts:




No Comments


You can leave the first : )



Leave a Reply

Your email address will not be published. Required fields are marked *