How to configure MariaDB slow query log on Ubuntu 24.04

This tutorial shows you how to enable the slow query log, set the execution time threshold, and configure log rotation for MariaDB on Ubuntu 24.04. The steps target MariaDB Server 11.4 and Ubuntu 24.04 LTS. You will edit the configuration file, restart the service, and verify that queries exceeding the threshold are recorded.

Prerequisites

• Ubuntu 24.04 LTS installed and updated.
• MariaDB Server 11.4 installed via the official MariaDB repository.
• Root or sudo privileges to edit system files and restart services.
• Access to the MariaDB server via SSH or local terminal.

Step 1: Edit the MariaDB configuration file

Open the main MariaDB configuration file using a text editor like nano or vim. Add or modify the slow query log settings to define the log file path and the time threshold.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add the following lines to the end of the file:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 0

These settings enable the log, specify the file path, set the threshold to 2 seconds, and disable logging for queries that do not use indexes unless you change that later.

Save and exit the editor. If using nano, press Ctrl+O, Enter to save, then Ctrl+X to exit.

Step 2: Create the log directory and set permissions

Ensure the directory for the slow query log exists and has the correct permissions. The MariaDB user must be able to write to this directory.

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysql

Create the log file if it does not exist and set ownership to the MariaDB user:

sudo touch /var/log/mysql/slow.log
sudo chown mysql:mysql /var/log/mysql/slow.log
sudo chmod 640 /var/log/mysql/slow.log

This ensures the MariaDB process can write to the file without permission errors.

Step 3: Configure log rotation

Set up log rotation to prevent the log file from growing indefinitely. Create a new configuration file for the slow query log in the logrotate directory.

sudo nano /etc/logrotate.d/mariadb-slow

Add the following configuration to rotate the log daily, compress old logs, and keep five rotated files:

/var/log/mysql/slow.log {
    daily
    missingok
    rotate 5
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

Save and exit the file. The postrotate command flushes the log after rotation to avoid data loss during the switch.

Step 4: Restart the MariaDB service

Apply the new configuration by restarting the MariaDB service. This ensures the server reads the updated settings and starts writing to the new log file.

sudo systemctl restart mariadb

Verify that the service restarted successfully:

sudo systemctl status mariadb

You should see Active: active (running) in the output.

Verify the installation

Run a query that exceeds the 2-second threshold to test the slow query log. Wait for the query to complete, then check the log file for the entry.

mysql -u root -p -e "SELECT SLEEP(3);"

Check the slow query log to confirm the query was recorded:

sudo tail -n 10 /var/log/mysql/slow.log

The output should show a query similar to this:

# Time: 2024-06-15T10:30:45.123456Z
# User@Host: root[root] @ localhost []
# Query_time: 3.012345  Lock_time: 0.000123  Rows_sent: 1  Rows_examined: 0
SELECT SLEEP(3);

If you see this entry, the slow query log is working correctly.

Troubleshooting

If the log file is empty or you do not see entries, check the following common issues.

Error: "Access denied for user 'root'@'localhost'"
This occurs if the MariaDB user does not have permission to execute the test query. Ensure you are using a user with sufficient privileges, or grant the necessary permissions before running the test.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

Error: "Cannot open file '/var/log/mysql/slow.log'"
This happens if the directory or file permissions are incorrect. Re-run the commands from Step 2 to ensure the directory exists and the MariaDB user owns the file.

Error: "Configuration file '/etc/mysql/mariadb.conf.d/50-server.cnf' not found"
Verify that the configuration file exists at the specified path. If the file is missing, create it using the commands from Step 1. Ensure the file is named 50-server.cnf and is located in /etc/mysql/mariadb.conf.d/.

Log file is growing too fast
If the log file grows too quickly, increase the long_query_time value to a higher threshold, such as 5 or 10 seconds. This reduces the number of queries logged and prevents disk space issues.

long_query_time = 5

Queries are not being logged even though they exceed the threshold
Check that the slow_query_log variable is set to ON by running the following SQL command:

SHOW VARIABLES LIKE 'slow_query_log';

The output should show ON. If it shows OFF, the service may have cached the old configuration. Restart the service again after editing the file.

Log rotation fails
If log rotation fails, check the postrotate script. Ensure that mysqladmin is installed and accessible. If the command fails, install the MariaDB client package:

sudo apt-get install mariadb-client

After installing the client, retry the log rotation test manually:

sudo logrotate -f /etc/logrotate.d/mariadb-slow

Review the /var/log/mysql/slow.log file after rotation to ensure it was compressed and rotated correctly.

By following these steps and troubleshooting tips, you will have a fully configured and functional slow query log on Ubuntu 24.04. Adjust the threshold and rotation settings as needed for your workload.