How to configure slow query log in MySQL or MariaDB

Slow query logging highlights SQL statements that consume the most time or resources, making performance regressions easier to spot before they saturate CPU, I/O, or connection pools.

MySQL and MariaDB evaluate each statement as it completes and record entries when execution exceeds long_query_time, optionally adding more candidates when log_queries_not_using_indexes is enabled. Log entries include timing, row counters, and the SQL text, and they can be written to a file via log_output=FILE (or to a table via log_output=TABLE).

The slow query log can grow rapidly and can include full SQL text and literal values, so treat it as sensitive and ensure log rotation is in place. Changing slow_query_log_file to a non-default path can require AppArmor or SELinux policy updates, and a bad path or permission can prevent mysqld from starting. Persistent settings live under the mysqld section of a /my.cnf/ include file, while short-lived testing uses SET GLOBAL.

1. Display the current slow query log variables.

   $ mysql -u root -p -e "SHOW VARIABLES WHERE Variable_name IN ('slow_query_log','slow_query_log_file','long_query_time','log_output','log_queries_not_using_indexes','min_examined_row_limit');"
   Enter password:
   +-------------------------------+--------------------------------------+
   | Variable_name                 | Value                                |
   +-------------------------------+--------------------------------------+
   | log_output                    | FILE                                 |
   | log_queries_not_using_indexes | OFF                                  |
   | long_query_time               | 10.000000                            |
   | min_examined_row_limit        | 0                                    |
   | slow_query_log                | OFF                                  |
   | slow_query_log_file           | /var/lib/mysql/892e91256099-slow.log |
   +-------------------------------+--------------------------------------+

   

   Use

   mysql -u root -p

   when local socket auth is not enabled.

2. Enable slow query logging at runtime for immediate validation.

   mysql> SET GLOBAL log_output='FILE';
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> SET GLOBAL long_query_time=1;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> SET GLOBAL slow_query_log='ON';
   Query OK, 0 rows affected (0.00 sec)

   

   Runtime changes revert after a service restart unless persisted in /my.cnf/.

3. Create the log directory when using /var/log/mysql as the destination.

   $ sudo install -d -o mysql -g mysql -m 0750 /var/log/mysql

   

   Directories outside the default policy may be blocked by AppArmor or SELinux, which can prevent mysqld from starting.

4. Ensure the slow query log file exists with safe ownership and permissions.

   $ sudo touch /var/log/mysql/mysql-slow.log
   $ sudo chown mysql:mysql /var/log/mysql/mysql-slow.log
   $ sudo chmod 0640 /var/log/mysql/mysql-slow.log
   $ sudo ls -l /var/log/mysql/mysql-slow.log
   -rw-r----- 1 mysql mysql 0 Dec 25 00:31 /var/log/mysql/mysql-slow.log

   

   Protect the slow log like application logs because query text can contain sensitive values.

5. Persist slow query log settings in a mysqld drop-in file.

   [mysqld]
   log_output = FILE
   slow_query_log = 1
   slow_query_log_file = /var/log/mysql/mysql-slow.log
   long_query_time = 1
   log_queries_not_using_indexes = 0
   min_examined_row_limit = 0

   

   Place the same block in /etc/my.cnf or /etc/my.cnf.d/slow-query-log.cnf on systems that do not include /etc/mysql/conf.d.

6. Restart the database service to apply persistent configuration changes.

   $ sudo docker restart sg-mysql
   sg-mysql

   

   On non-container hosts, restart the service manager unit for MySQL or MariaDB instead.

7. Check the database service status for an active (running) state.

   $ sudo docker ps --filter name=sg-mysql --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}'
   NAMES      IMAGE       STATUS
   sg-mysql   mysql:8.0   Up 4 seconds

   

   Use the service manager status command on non-container hosts.

8. Re-check variables to confirm the server is logging slow queries with the intended thresholds.

   $ sudo mysql -e "SHOW VARIABLES WHERE Variable_name IN ('slow_query_log','slow_query_log_file','long_query_time','log_output');"
   +---------------------+-------------------------------+
   | Variable_name       | Value                         |
   +---------------------+-------------------------------+
   | log_output          | FILE                          |
   | long_query_time     | 1.000000                      |
   | slow_query_log      | ON                            |
   | slow_query_log_file | /var/log/mysql/mysql-slow.log |
   +---------------------+-------------------------------+

   

9. Execute a deliberately slow statement to force a slow log entry.

   $ sudo mysql -e "SELECT SLEEP(2) AS slept;"
   +-------+
   | slept |
   +-------+
   |     0 |
   +-------+

   

   Set long_query_time below the SLEEP() value when the entry does not appear.

10. Read the most recent entries from the slow query log file.

    $ sudo tail -n 30 /var/log/mysql/mysql-slow.log
    /usr/sbin/mysqld, Version: 8.0.44 (MySQL Community Server - GPL). started with:
    Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
    Time                 Id Command    Argument
    # Time: 2025-12-25T00:31:36.713019Z
    # User@Host: root[root] @ localhost []  Id:     9
    # Query_time: 2.001998  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
    SET timestamp=1766622694;
    SELECT SLEEP(2) AS slept;

    

    Run

    sudo mysql -e "FLUSH SLOW LOGS;"

    when the slow log path changes and the file needs to be reopened.

11. Verify log rotation coverage for the slow query log path.

    $ sudo grep -R "mysql-slow.log" /etc/logrotate.d 2>/dev/null
    /etc/logrotate.d/mysql-server:/var/log/mysql/mysql-slow.log
    /etc/logrotate.d/mariadb:/var/log/mysql/mysql-slow.log

    

    Update the matching logrotate file when slow_query_log_file is set to a non-default location.

12. Disable slow query logging at runtime when collection is complete.

    mysql> SET GLOBAL slow_query_log='OFF';
    Query OK, 0 rows affected (0.00 sec)

    

    Remove or change /etc/mysql/conf.d/slow-query-log.cnf to keep the log disabled after the next restart.