How to enable slow query log for MySQL and PostgreSQL

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.

We'll cover a few topics in this article:

Logging MySQL 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

Do not forget to restart the database to apply these configuration changes. For example, on Ubuntu, you can run the following command:
sudo service mysql restart

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.

Logging PostgreSQL slow queries to a file

  • Open the file postgresql.conf  in your favorite text editor.
    To find the file's path, run the command: find / -name postgresql.conf
  • Search for the line: #log_min_duration_statement = -1
    Un-comment it and replace it with: log_min_duration_statement = 500
    * this setting will log all queries executing for more than 500ms. Please adjust this threshold based on your definition for slow queries.
  • Save the file and execute this command to reload the PostgreSQL configuration:
    service postgresql reload
  • The log file will now be written to: /var/lib/pgsql/data/pg_log/

Summary

For any questions regarding the beta program, please contact us at [email protected]