MariaDB / How to Configure and Read the Slow Query Log

The MariaDB slow query log records SQL that exceeds a time threshold or meets other enabled logging conditions. To inspect a file, enable slow_query_log, include FILE in log_output, and verify the executing connection’s threshold and filters. SQL can change runtime settings without restarting, but persistent settings belong in an option file.

What gets logged, and what do you need?

The log helps examine server-side SQL execution time and rows examined. Enabling it does not make queries faster; use the evidence to investigate execution plans and indexes. A page’s response time also includes networking and application work, so it is not equivalent to SQL execution time.

The usual time threshold is long_query_time, measured in seconds: 1 means one second. An enabled no-index condition can include fast queries, while row limits, filters, and sampling can omit statements that exceed the threshold. The MariaDB slow query log overview explains these conditions.

This guide targets a self-managed Linux server. You need an administrative SQL account and operating-system access to edit configuration. Changing global variables requires the relevant global-setting privilege for your version. If access is denied, use an administrative account rather than granting broad privileges to the application. Managed database services may require provider-specific parameter groups and log viewers.

MariaDB 10.11 introduced new names for several variables; the older names below remain aliases. The examples use those familiar names, with support checked on the actual server. Consult the MariaDB server variable reference for scope and version details.

Enable logging without restarting and verify a test query

Record current values and save the original settings

On a typical Ubuntu installation using local socket authentication, connect with sudo mariadb. For another setup, use its authentication method, such as mariadb -u administrative_account -p. Record the version, data directory, and global settings first. An alias may be displayed under its newer name.

SELECT VERSION(), @@GLOBAL.datadir;
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'slow_query_log', 'log_slow_query',
  'slow_query_log_file', 'log_slow_query_file',
  'long_query_time', 'log_slow_query_time',
  'log_output', 'general_log', 'log_slow_rate_limit',
  'min_examined_row_limit', 'log_queries_not_using_indexes',
  'log_slow_filter', 'log_slow_disabled_statements'
);

For a temporary experiment, save the global values below in the same administrative connection. These @saved_... user variables exist only in that connection. They disappear on disconnect, so also keep the query output in your working notes and run the later restoration SQL in this same connection.

SET @saved_slow = @@GLOBAL.slow_query_log,
    @saved_file = @@GLOBAL.slow_query_log_file,
    @saved_output = @@GLOBAL.log_output,
    @saved_time = @@GLOBAL.long_query_time;

Enable file output and distinguish session settings

The following example uses file output. A relative name such as mariadb-slow.log is resolved inside @@GLOBAL.datadir. If you already operate a slow log, you can retain its existing path instead of changing the name.

SET GLOBAL slow_query_log_file = 'mariadb-slow.log';
SET GLOBAL log_output = 'FILE';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = ON;

log_output also controls the general query log’s output. If your server already uses TABLE, review that requirement before applying the example. Choose FILE,TABLE if both destinations are needed. NONE prevents output even when logging is enabled.

Global values provide defaults for new connections; they do not automatically rewrite every existing session’s threshold. Compare both scopes below. An application connection pool may retain old connections, so plan whether to reconnect them or change the relevant session settings. See GLOBAL and SESSION in SHOW VARIABLES.

SELECT @@GLOBAL.long_query_time AS global_seconds,
       @@SESSION.long_query_time AS session_seconds,
       @@GLOBAL.slow_query_log AS global_log,
       @@SESSION.slow_query_log AS session_log;

Run a controlled probe and restore the session

The next example saves current session settings, then temporarily sets a one-second threshold, zero minimum rows, and sampling of one. It clears filters only for this session. SLEEP examines no table rows, so a positive minimum could exclude it. Also verify that your server’s excluded statement types do not suppress SELECT.

SET @session_log = @@SESSION.slow_query_log,
    @session_time = @@SESSION.long_query_time,
    @session_rows = @@SESSION.min_examined_row_limit,
    @session_rate = @@SESSION.log_slow_rate_limit,
    @session_filter = @@SESSION.log_slow_filter;
SET SESSION slow_query_log = ON;
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 0;
SET SESSION log_slow_rate_limit = 1;
SET SESSION log_slow_filter = '';
SELECT SLEEP(1.2) AS slow_log_probe_13708;

A normally completed call returns 0. After it finishes, the log should contain slow_log_probe_13708 because its execution exceeds the threshold. Actual elapsed time varies with load. This is a logging-path test, not a performance benchmark or an index test.

After checking the file, restore the probe’s session settings in the same SQL connection. Because this restores the original values, verify subsequent real collection separately, for example with a new connection inheriting the intended global defaults.

SET SESSION slow_query_log = @session_log;
SET SESSION long_query_time = @session_time;
SET SESSION min_examined_row_limit = @session_rows;
SET SESSION log_slow_rate_limit = @session_rate;
SET SESSION log_slow_filter = @session_filter;

Make the configuration persistent on Ubuntu or Rocky Linux

SET GLOBAL does not replace a persistent option file. First determine which files and arguments the actual service uses. Review the output for sensitive settings before sharing it.

sudo systemctl cat mariadb
sudo mariadbd --help --verbose
sudo mariadbd --print-defaults

1. Identify and back up the configuration. Typical Ubuntu packages use /etc/mysql/mariadb.conf.d/; typical Rocky Linux packages use /etc/my.cnf.d/. The Rocky Linux MariaDB guide also documents its configuration location. After confirming the include path, you can create a dedicated 60-slow-log.cnf on Ubuntu or slow-query.cnf on Rocky. Inspect any existing file with that name before editing it.
2. Add the following server options. Check for later files or startup arguments overriding them, and resolve conflicting duplicates. A one-second threshold is a starting point, not a universal service target.

[mariadb]
slow_query_log=ON
slow_query_log_file=mariadb-slow.log
log_output=FILE
long_query_time=1
log_slow_rate_limit=1
min_examined_row_limit=0

3. Restart during a maintenance window and check the result. Inspect mariadbd --print-defaults to confirm the file is read, then restart as below. Adapt the last command to your local administrative authentication and use the actual service name.

sudo systemctl restart mariadb
sudo systemctl status mariadb --no-pager
sudo mariadb -e "SHOW GLOBAL VARIABLES LIKE 'slow_query_log%'; SHOW GLOBAL VARIABLES LIKE 'long_query_time'; SHOW GLOBAL VARIABLES LIKE 'log_output';"

Open a new client connection, verify the path, threshold, output destination, and session values, then repeat the probe. An absolute path elsewhere requires the server account’s write access and review of AppArmor on Ubuntu or SELinux on Rocky. Starting with a relative file in the existing data directory avoids introducing a new folder’s access-policy changes. See MariaDB option-file configuration for search order.

Read the log file or table

Inspect the file and summarize by time or frequency

Use the actual path confirmed through SQL. Replace /var/lib/mysql/ below if your data directory differs. tail -F follows the file name and displays new records; press Ctrl+C to stop viewing.

sudo tail -n 80 /var/lib/mysql/mariadb-slow.log
sudo tail -F /var/lib/mysql/mariadb-slow.log

Locate the probe or another statement in the raw file, then examine Query_time, Lock_time, Rows_sent, and Rows_examined together. The mariadb-dumpslow tool groups similar SQL in larger logs. If it is missing, check your distribution’s package contents; it may also be installed under its earlier name, mysqldumpslow.

sudo mariadb-dumpslow -s t -t 10 /var/lib/mysql/mariadb-slow.log
sudo mariadb-dumpslow -s at -t 10 /var/lib/mysql/mariadb-slow.log
sudo mariadb-dumpslow -s c -t 10 /var/lib/mysql/mariadb-slow.log

-s t sorts by cumulative query time, -s at by average query time, and -s c by count. -t 10 requests the top ten groups. Numbers and strings are abstracted by default to form groups. Use the summary to prioritize investigation, then inspect the original SQL and its execution plan.

When TABLE output is configured

If log_output includes TABLE or FILE,TABLE, an authorized account can query mysql.slow_log. With only the FILE example above, an empty table does not mean file logging failed.

SELECT start_time, query_time, lock_time,
       rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 20;

Table output has different capabilities and costs. For example, extended EXPLAIN output is available for file logging only. Do not change the shared output destination casually; review writing MariaDB logs into tables.

Decide what to investigate first

Consider frequency and cumulative time, rather than choosing only the slowest individual call. A query taking five seconds once and a query taking 0.2 seconds a thousand times have different average and cumulative priorities. However, a one-second threshold would omit the latter under the ordinary time condition. A filtered log does not represent every source of database load.

Do not add an index based only on a log entry. A full scan can be sensible for a small table, and indexes increase write costs and storage. Compare execution plans under realistic conditions before and after a change. Diagnostics such as ANALYZE can actually execute statements, so assess their workload and possible changes before using them.

Troubleshoot missing records or excessive logging

• Enabled but empty: check for NONE, the file/table destination, the connected instance, and session enablement and threshold. A running statement may not have its completed record yet.
• The probe is missing: inspect minimum rows, log_slow_filter, disabled statement types, and log_slow_rate_limit. A value of 1000 does not mean every eligible statement is logged; use 1 in the controlled test session.
• Fast queries appear: check log_queries_not_using_indexes or not_using_index in the filter. Raising the time threshold may not suppress this condition.
• File access errors: check the server account, parent-directory access, AppArmor/SELinux denials, and free space. The client user’s file access is different from the server process’s permissions.

These Linux checks use example paths that you must adapt. For failed startup, inspect denied paths and unknown-option errors instead of disabling the entire security policy.

sudo journalctl -u mariadb -b -n 100 --no-pager
sudo journalctl -k -b --no-pager
sudo ls -ld /var/lib/mysql
sudo ls -l /var/lib/mysql/mariadb-slow.log
df -h /var/lib/mysql

Stop collection, restore settings, and manage log files

Disable collection with the following statements. They do not delete existing records. If the option file still enables logging, it will be re-enabled at a later restart, so adjust persistent settings as intended.

SET GLOBAL slow_query_log = OFF;
SET SESSION slow_query_log = OFF;

If the same administrative connection that saved the original values is still open, restore the global settings with the SQL below. After a disconnect, do not run it with missing user variables; use the original values recorded separately. Check existing application sessions and option-file settings independently.

SET GLOBAL slow_query_log = OFF;
SET GLOBAL slow_query_log_file = @saved_file;
SET GLOBAL log_output = @saved_output;
SET GLOBAL long_query_time = @saved_time;
SET GLOBAL slow_query_log = @saved_slow;

For ongoing collection, manage retention, file sizes, and disk space. Merely renaming a file can leave the server writing through its old handle. Review the package’s existing logrotate policy and reopening step. FLUSH SLOW LOGS closes and reopens the slow log; it does not create an archive, compression, or deletion policy. It requires the relevant RELOAD privilege.

FLUSH SLOW LOGS;

Use the MariaDB Linux log-rotation guide and FLUSH reference to align rotation with your operations. If the log is in the data directory, ensure rotation actually covers that path. SQL text can contain personal data or secrets, so restrict access to logs and backups and redact sensitive values before sharing them.