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

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

  1. Open the file postgresql.conf file in your favorite text editor.
    To find the file's path, run the command: find / -name postgresql.conf
  2. Search for the line: #log_min_duration_statement = -1
    Un-comment it and replace it with: log_min_duration_statement = 1000
    * this setting will log all queries executing more than 1 second. you can change the value 1000 to the amount of millseconds you want to set the threshold.
  3. Save the file and reload the PostgreSQL configuration: service postgresql reload
  4. The log file will now be written to: /var/lib/pgsql/data/pg_log/

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.