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.

   $ sudo mysql -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');"
   +------------------------------+-------------------------------+
   | 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/log/mysql/mysql-slow.log |
   +------------------------------+-------------------------------+

   

   Use

   mysql -u root -p

   when local socket auth is not enabled.

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

   $ sudo mysql -e "SET GLOBAL log_output='FILE'; SET GLOBAL long_query_time=1; SET GLOBAL slow_query_log='ON';"

   

   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

   

   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 systemctl restart mysql
   $ sudo systemctl restart mariadb

   

   Restart only the service that exists on the host.

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

   $ sudo systemctl status mysql --no-pager
   ● mysql.service - MySQL Community Server
        Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
        Active: active (running) since Fri 2025-12-12 10:18:01 UTC; 7s ago
          Docs: man:mysqld(8)
   ##### snipped #####

   

   Use

   sudo systemctl status mariadb --no-pager

   on MariaDB.

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
    # Time: 2025-12-12T10:19:44.123456Z
    # User@Host: root[root] @ localhost []  Id:   123
    # Query_time: 2.000312  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
    SET timestamp=1765534784;
    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.

    $ sudo mysql -e "SET GLOBAL slow_query_log='OFF';"

    

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