How to Enable the Slow Query Log in MariaDB: Fixing 'long_query_time' and Logging Issues

Understanding MariaDB Slow Query Logging

When optimizing database performance, the first step is identifying which queries are causing bottlenecks. MariaDB provides a built-in mechanism called the slow query log, which records SQL statements that take longer than a specified amount of time to execute. However, DevOps engineers and DBAs frequently encounter issues where they attempt to enable the log, but no queries are recorded, or the long_query_time threshold seems to be ignored.

The most common symptoms include:

• Setting slow_query_log = 1 but the log file remains empty.
• Adjusting long_query_time = 1 but queries taking 3 seconds are not logged.
• MariaDB throwing permission denied errors when trying to write to /var/log/mysql/mariadb-slow.log.

Step 1: Diagnosing the Current State

Before making changes, you need to determine the current state of the slow query log variables. Connect to your MariaDB instance and run the following diagnostic queries:

SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';

If slow_query_log is OFF, the feature is disabled. If it is ON but the file is empty, you must verify the slow_query_log_file path and check if long_query_time is set too high (the default is usually 10 seconds).

Step 2: Enabling the Slow Query Log Dynamically (No Restart)

In a production environment, restarting the database can cause unacceptable downtime. You can enable the slow query log on the fly using SET GLOBAL.

-- Enable the slow query log
SET GLOBAL slow_query_log = 'ON';

-- Set the threshold to 2 seconds (or fractions like 0.5 for 500ms)
SET GLOBAL long_query_time = 2.0;

-- Optional: Log queries that don't use indexes
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Specify the log file location
SET GLOBAL slow_query_log_file = '/var/log/mysql/mariadb-slow.log';

CRITICAL GOTCHA: Changing long_query_time globally does not affect existing connections. If your application uses connection pooling (e.g., PHP PDO, HikariCP, PgBouncer for MySQL equivalents), the existing persistent connections will continue using the old long_query_time value. To force the new value, you either need to restart the application to flush the connection pool or terminate the existing database threads.

Step 3: Making the Configuration Persistent

To ensure the slow query log remains enabled after a database restart, you must add these settings to your MariaDB configuration file. Depending on your OS, this is usually located at /etc/mysql/mariadb.conf.d/50-server.cnf, /etc/my.cnf, or /etc/my.cnf.d/server.cnf.

Open the file and locate the [mysqld] section. Add or modify the following lines:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2.0
log_queries_not_using_indexes = 1

Save the file and restart MariaDB to apply the permanent changes:

sudo systemctl restart mariadb

Step 4: Troubleshooting 'Slow Query Log Not Working'

If you have enabled the slow query log but the file remains empty, run through this troubleshooting checklist.

1. File Ownership and Permissions

MariaDB runs as the mysql user. It must have write permissions to the directory and the log file. If you manually created the log file as root, MariaDB will fail to write to it.

# Check permissions
ls -la /var/log/mysql/mariadb-slow.log

# Fix ownership
sudo chown mysql:mysql /var/log/mysql/mariadb-slow.log
sudo chmod 660 /var/log/mysql/mariadb-slow.log

2. AppArmor or SELinux Restrictions

On Ubuntu/Debian, AppArmor profiles dictate where MariaDB can write. On RHEL/CentOS, SELinux enforces similar policies. If you moved the slow query log to a non-standard location like /custom_logs/slow.log, the security module will block the write operation, even if the file permissions are correct.

For AppArmor (Ubuntu/Debian): Check the syslog for DENIED messages:

sudo dmesg | grep -i apparmor | grep mariadb

If blocked, edit the AppArmor profile /etc/apparmor.d/usr.sbin.mysqld and add: /custom_logs/slow.log rw, Then reload AppArmor: sudo systemctl reload apparmor.

For SELinux (RHEL/CentOS): Check for denials:

sudo grep mysqld /var/log/audit/audit.log | grep denied

If blocked, update the SELinux context:

sudo semanage fcontext -a -t mysqld_log_t "/custom_logs(/.*)?"
sudo restorecon -Rv /custom_logs

3. MIN_EXAMINED_ROW_LIMIT

Check the min_examined_row_limit variable. If a query takes a long time but examines fewer rows than this limit, it will not be logged. The default is usually 0, but if someone changed it, it could filter out your slow queries.

SHOW GLOBAL VARIABLES LIKE 'min_examined_row_limit';

Step 5: Log Output Destination (FILE vs TABLE)

By default, MariaDB writes slow queries to a file. You can change the log_output variable to write them to a table in the mysql database, which makes it easier to query and analyze the slow logs using standard SQL.

SET GLOBAL log_output = 'TABLE';

Once set, you can view the slow queries by running:

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

While logging to a table is convenient, it is generally slower than logging to a file and can impact performance on highly concurrent systems. For production, FILE is recommended, paired with a tool like pt-query-digest to analyze the log file.

Analyzing the Log

Reading raw slow query logs can be tedious. Use mysqldumpslow (included with MariaDB) to summarize the data:

mysqldumpslow -s c -t 10 /var/log/mysql/mariadb-slow.log

This command sorts (-s c) by the count of execution and displays the top 10 (-t 10) queries, helping you prioritize which queries to optimize first with EXPLAIN.