Export Slow SQL Queries to MySQL Slow Log File or Table

MySQL allows logging slow queries to either a log file or a table, with a configured query duration threshold. Parsing the slow log with tools such as EverSQL Query Optimizer will allow you to quickly locate the most common and slowest SQL queries in the database. You can optimize these queries automatically using EverSQL Query Optimizer.

The two topics we'll cover in this article are:

Logging slow queries to a file

To enable the slow query log for MySQL/MariaDB, navigate to the configuration file my.cnf (default path: /etc/mysql/my.cnf). Locate the configuration section [mysqld].
At the bottom of that section, add the following configuration values to log all queries with an execution duration of over 1 second.

slow_query_log=1
long_query_time=1
log_output=FILE
slow_query_log_file=/var/lib/mysql/slow.log

A few notes:

  • To modify the logged execution duration threshold, modify long_query_time.
  • The default path for the new slow log file is: /var/lib/mysql/hostname-slow.log.
    To configure the path, modify the configuration key slow_query_log_file to the new path (i.e, slow_query_log_file=/path/filename).

Generate a file from the slow log table

Slow queries can be logged to either a file or a table. If you chose to log them to a table, you are probably wondering how you can use EverSQL to analyze those log entries and optimize those queries.

Using the following SQL query, you can retrieve the information from the mysql.slow_log table and automatically generate a slow query log file, in the same format that MySQL generates, and can be analyzed by EverSQL.

mysql -h X.X.X.X -u USER -p --raw --skip-column-names --quick --silent --no-auto-rehash --compress -e "SELECT CONCAT('# Time: ', DATE_FORMAT(start_time, '%y%m%d %H:%i:%s'), CHAR(10), CHAR(13), '# [email protected]: ', user_host, CHAR(10), CHAR(13), '# Query_time: ', TIME_TO_SEC(query_time), ' Rows_sent: ', rows_sent, ' Rows_examined: ', rows_examined, CHAR(10), CHAR(13), 'SET timestamp=', UNIX_TIMESTAMP(start_time), ';', CHAR(10), CHAR(13), sql_text, ';') FROM mysql.slow_log;" > slow_queries.log

Please note: before running this command, make sure you have the mysql command line client installed on your machine.
Also, please set the host, the user name and the log file path in the command.

Once you got the log file, you can upload it to EverSQL Query Optimizer to visualize the log entries and choose the queries you'd like to optimize.

Summary

A few quick configurations will allow you to log slow queries to either a file or a table. Both options will allow you to analyze the slow SQL queries and optimize them automatically using EverSQL Query Optimizer.