The MySQL slow query log enables you to log all queries that take longer than a specified number of seconds to execute. This should help identify any queries that are not performing sufficiently.
All that is required is two additional lines in the MySQL configuration file “my.cnf” in the [mysqld] section.
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 4
The above configuration will log any queries taking longer than 4 seconds to execute in to the specified log file.
MySQL appears to require that the specified log file exists otherwise the logging is disabled and the error below
appears in the MySQL log. (Or it may be that it didn’t have permissions to create the file. I haven’t investigated).
To create the log file I did the following:
chown mysql.mysql /var/log/mysql-slow.log
chmod o-r /var/log/mysql-slow.log
The third command is probably unnecessary, but it meant that the permissions matched the mysqld.log
in the same directory.
Once this is done MySQL needs to be restarted to start the query logging: