How to Enable the Slow Query Log in MySQL and MariaDB

The slow query log is a built-in MySQL and MariaDB diagnostic feature that records every SQL statement whose execution time exceeds a configurable threshold. It captures query duration, lock time, rows examined, rows sent, and the full SQL text — giving database administrators and developers a precise, file-based audit trail of every query that is degrading application performance.

Enabling it is one of the highest-leverage actions you can take during database performance tuning. Unlike generic monitoring tools, the slow query log pinpoints the exact statements responsible for latency, making it indispensable for index optimization, query restructuring, and capacity planning on any server — from a single-tenant VPS Hosting environment to a multi-node dedicated database cluster.

Why the Slow Query Log Matters Beyond Basic Monitoring

Most teams reach for EXPLAIN or SHOW PROCESSLIST reactively, after users report slowness. The slow query log works proactively: it accumulates evidence across hours or days of real traffic, capturing intermittent offenders that never appear during a manual inspection window.

Key operational benefits include:

• Bottleneck isolation — distinguishes CPU-bound full-table scans from lock-contention problems using Query_time vs. Lock_time ratios
• Index gap analysis — the log_queries_not_using_indexes flag surfaces every query performing a full scan, regardless of its raw execution time
• Regression detection — comparing log snapshots before and after a deployment reveals whether new code introduced slower query patterns
• Capacity planning evidence — Rows_examined values that are orders of magnitude higher than Rows_sent indicate missing or misused indexes, which compound under load

MySQL vs. MariaDB: Slow Query Log Feature Comparison

Both engines share the same core slow query log infrastructure inherited from MySQL 5.1, but MariaDB has extended it in several meaningful ways.

The log_slow_verbosity and log_slow_rate_limit options in MariaDB are particularly valuable in high-throughput production environments where logging every slow query would itself become a performance liability.

Step 1: Locate the Configuration File

MySQL and MariaDB read their configuration from different default paths depending on the distribution and installation method.

MySQL:

/etc/my.cnf (RPM-based: RHEL, CentOS, AlmaLinux, Rocky Linux)
/etc/mysql/my.cnf (Debian/Ubuntu)
/etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu with mysql-server package)

MariaDB:

/etc/my.cnf.d/server.cnf (RPM-based)
/etc/mysql/mariadb.conf.d/50-server.cnf (Debian/Ubuntu)
/etc/mysql/mariadb.cnf (older Debian layouts)

If you are unsure which file is active, query the running process:
mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"
This prints the exact ordered list of files the daemon reads at startup, including any !includedir directories.
Open the primary configuration file with your preferred editor:
sudo nano /etc/my.cnf
Step 2: Add Slow Query Log Directives to [mysqld]
All slow query log parameters belong in the [mysqld] section. If the section does not exist, create it at the top of the file.
[mysqld]
# Core slow query log settings
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow-query.log
long_query_time         = 1

# Log queries that skip index usage entirely
log_queries_not_using_indexes = 1

# Avoid flooding the log with index warnings on low-traffic tables
min_examined_row_limit  = 100

# Log slow administrative statements (ALTER TABLE, OPTIMIZE TABLE, etc.)
log_slow_admin_statements = 1
Parameter breakdown:

slow_query_log = 1 — activates the feature; set to 0 to disable without removing the block
slow_query_log_file — absolute path to the log file; the MySQL/MariaDB process user (mysql) must have write access to the parent directory
long_query_time = 1 — threshold in seconds, accepts decimal values (e.g., 0.5 for 500 ms); the default of 10 seconds is almost always too permissive for web applications
log_queries_not_using_indexes — logs full-scan queries regardless of long_query_time; combine with min_examined_row_limit to suppress noise from tiny tables
min_examined_row_limit — a query must examine at least this many rows before it qualifies for logging under log_queries_not_using_indexes; prevents trivial single-row lookups from polluting the log
log_slow_admin_statements — captures schema-level operations that block tables and are frequently overlooked as latency sources

MariaDB-specific additions worth enabling in production:
# MariaDB only — extended per-query statistics in the log
log_slow_verbosity      = query_plan,explain

# MariaDB only — log only 1 in every N qualifying queries (rate limiting)
log_slow_rate_limit     = 10
log_slow_verbosity = query_plan,explain appends the optimizer's execution plan directly into each log entry, eliminating the need to re-run EXPLAIN manually after the fact — a significant time saver when diagnosing queries that only appear under production load patterns.
Step 3: Create the Log File and Set Permissions
If the target directory does not exist, create it and assign ownership before restarting the service. Skipping this step is one of the most common reasons the slow query log silently fails to activate.
sudo mkdir -p /var/log/mysql
sudo touch /var/log/mysql/slow-query.log
sudo chown mysql:mysql /var/log/mysql/slow-query.log
sudo chmod 640 /var/log/mysql/slow-query.log
On SELinux-enforcing systems (RHEL, CentOS, AlmaLinux), the file context must also be set correctly:
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?"
sudo restorecon -Rv /var/log/mysql
Failure to set the correct SELinux context causes the daemon to start successfully but silently skip writing to the log file — a frustrating edge case that produces no obvious error in /var/log/messages.
Step 4: Restart the Database Service
Apply the configuration changes by restarting the service. On systemd-based distributions (the standard on any modern Linux server):
# MySQL
sudo systemctl restart mysqld

# MariaDB
sudo systemctl restart mariadb
On older init.d-based systems:
# MySQL
sudo service mysqld restart

# MariaDB
sudo service mariadb restart
After the restart, check that the service came up cleanly:
sudo systemctl status mysqld    # or mariadb
sudo journalctl -u mysqld -n 50 --no-pager
Any misconfiguration in my.cnf will prevent startup and appear in the journal output.
Step 5: Enable the Slow Query Log at Runtime (Without a Restart)
For production servers where a restart is disruptive, MySQL and MariaDB support enabling the slow query log dynamically via SET GLOBAL. Changes made this way take effect immediately but do not persist across a service restart unless also written to my.cnf.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL min_examined_row_limit = 100;
This is the correct approach for emergency diagnostics on a live system — enable it, capture a 15–30 minute sample during peak traffic, then disable it again without touching the configuration file or restarting the daemon.
Step 6: Verify the Configuration
Connect to the MySQL or MariaDB client:
mysql -u root -p
Then run a pattern match against the system variable table:
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
Expected output for a correctly configured instance:
+-------------------------------+-------------------------------+
| Variable_name                 | Value                         |
+-------------------------------+-------------------------------+
| slow_query_log                | ON                            |
| slow_query_log_file           | /var/log/mysql/slow-query.log |
+-------------------------------+-------------------------------+

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 1.000 |
+-----------------+-------+

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
You can also confirm the log is being written to by checking the slow query counter:
SHOW GLOBAL STATUS LIKE 'Slow_queries';
This counter increments every time a query exceeds long_query_time, regardless of whether file logging is active — useful for confirming that slow queries are actually occurring before you spend time analyzing an empty log file.
Step 7: Reading and Interpreting the Raw Log
Use tail to monitor the log in real time during a load test or peak traffic window:
sudo tail -f /var/log/mysql/slow-query.log
A typical log entry looks like this:
# Time: 2024-10-11T12:45:23.489187Z
# User@Host: app_user[app_user] @ 10.0.1.45 []  Id: 1042
# Query_time: 4.561529  Lock_time: 0.000115  Rows_sent: 1  Rows_examined: 847293
# Bytes_sent: 512
SET timestamp=1697030723;
SELECT * FROM orders WHERE customer_email = 'user@example.com' ORDER BY created_at DESC;
What each field tells you:

Query_time — total wall-clock execution time in seconds
Lock_time — time spent waiting for table or row locks; a high ratio of Lock_time to Query_time points to contention, not a missing index
Rows_sent — rows returned to the client
Rows_examined — rows the storage engine scanned to produce the result; a ratio of Rows_examined / Rows_sent above 100:1 is a strong signal of a missing or poorly selective index
Bytes_sent — present in MariaDB extended verbosity; useful for identifying queries returning unnecessarily large result sets

In the example above, the query examined 847,293 rows to return 1 row. Adding an index on customer_email would reduce Rows_examined to approximately 1, cutting execution time from 4.5 seconds to sub-millisecond.
Step 8: Analyze the Log with mysqldumpslow and pt-query-digest
Reading the raw log file is impractical at scale. Two tools aggregate and rank slow queries by total impact.
Using mysqldumpslow (Bundled with MySQL/MariaDB)
# Top 10 queries by total execution time
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by average execution time
sudo mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by rows examined
sudo mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log
mysqldumpslow normalizes query parameters (replacing literal values with N or S) so that structurally identical queries with different parameter values are grouped together — essential for identifying high-frequency patterns.
Using pt-query-digest (Percona Toolkit — Recommended for Production)
# Install Percona Toolkit (Debian/Ubuntu)
sudo apt-get install percona-toolkit

# Install Percona Toolkit (RHEL/CentOS/AlmaLinux)
sudo yum install percona-toolkit

# Generate a full digest report
sudo pt-query-digest /var/log/mysql/slow-query.log

# Show only the top 5 queries by total time
sudo pt-query-digest --limit 5 /var/log/mysql/slow-query.log

# Output to a file for later review
sudo pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow_query_report.txt
pt-query-digest produces a ranked report showing each query's fingerprint, total execution time, average time, call count, and percentile distribution. It is significantly more powerful than mysqldumpslow and is the standard tool used by professional DBAs for slow log analysis.
Step 9: Configure Log Rotation with logrotate
Without rotation, the slow query log grows indefinitely. On a busy server with long_query_time set to 1 second, the file can reach several gigabytes within days.
Create a dedicated logrotate configuration:
sudo nano /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
    daily
    rotate 14
    missingok
    notifempty
    compress
    delaycompress
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs 2>/dev/null || true
    endscript
}
Key directives explained:

rotate 14 — retains 14 days of compressed archives; adjust based on your disk budget and audit requirements
compress / delaycompress — gzips rotated files, but delays compression by one cycle to avoid compressing a file that the daemon may still have open
postrotate — runs mysqladmin flush-logs after rotation, which signals the daemon to close the current log file handle and open a fresh one; without this, MySQL/MariaDB continues writing to the renamed file until the next restart

Force a manual rotation to test the configuration:
sudo logrotate -f /etc/logrotate.d/mysql-slow
Step 10: Disable the Slow Query Log When No Longer Needed
Continuous slow query logging at a low threshold (e.g., 0.5 seconds) on a high-traffic server adds measurable I/O overhead. Disable it once you have collected sufficient data:
Via configuration file (persistent):
[mysqld]
slow_query_log = 0
Then restart the service:
sudo systemctl restart mysqld   # or mariadb
Via runtime variable (immediate, non-persistent):
SET GLOBAL slow_query_log = 'OFF';
The runtime method is preferable during production hours — it takes effect in milliseconds with zero downtime.
Advanced: Using the performance_schema as a Complement
The slow query log captures queries that exceed a time threshold. The performance_schema events_statements_summary_by_digest table captures aggregated statistics for every distinct query pattern, regardless of execution time. Using both together gives a complete picture.
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1e12, 3)     AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 6)     AS avg_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
This query surfaces the top 10 most time-consuming query patterns across the entire statement history — including fast queries that run millions of times and collectively dominate CPU time, which the slow query log would never capture.
Hosting Environment Considerations
The optimal long_query_time threshold depends heavily on the server's role and resource profile:

Shared hosting environments — typically no direct access to my.cnf; use SET GLOBAL if the hosting provider grants SUPER or SYSTEM_VARIABLES_ADMIN privilege, or request slow log access through the control panel
VPS environments — full root access means complete control over all configuration parameters; a VPS with cPanel installation exposes slow query log settings through WHM's MySQL Configuration Editor, which writes directly to my.cnf

If your application stack includes a web frontend managed through a VPS Control Panel, correlating slow query log timestamps with your application's HTTP access log timestamps is an effective method for tracing database latency back to specific user-facing requests.

Practical Decision Matrix: Choosing the Right Threshold

Technical Checklist and Key Takeaways

Before closing out a slow query investigation, verify each of the following:

• The [mysqld] section in my.cnf contains slow_query_log = 1, a valid slow_query_log_file path, and a long_query_time appropriate for your traffic profile
• The log file and its parent directory are owned by the mysql system user with write permissions; on SELinux systems, the file context is set to mysqld_log_t

SHOW VARIABLES LIKE '%slow_query%' confirms slow_query_log = ON and the correct file path after the service restart
SHOW GLOBAL STATUS LIKE 'Slow_queries' shows a non-zero and incrementing counter, confirming that qualifying queries are actually occurring
log_queries_not_using_indexes is enabled and paired with min_examined_row_limit to prevent trivial single-row lookups from flooding the log
log_slow_admin_statements is enabled to capture ALTER TABLE, OPTIMIZE TABLE, and similar DDL operations that are common sources of unexpected table locks
A logrotate configuration is in place with a postrotate hook that calls mysqladmin flush-logs

FAQ

Does enabling the slow query log affect database performance?

At a threshold of 1 second or higher on a typical production workload, the overhead is negligible — usually under 1% of total query execution time. The overhead becomes measurable only when long_query_time is set below 0.1 seconds or when log_queries_not_using_indexes is enabled on a schema with many small, unindexed tables. Use log_slow_rate_limit (MariaDB) or raise min_examined_row_limit to mitigate this.

Can I enable the slow query log without restarting MySQL or MariaDB?

Yes. Use SET GLOBAL slow_query_log = 'ON' and SET GLOBAL long_query_time = 1 from any MySQL client session with SUPER or SYSTEM_VARIABLES_ADMIN privilege. The change takes effect immediately. Write the same values to my.cnf to make them persistent across restarts.

What is the difference between Query_time and Lock_time in the slow query log?

Query_time is the total elapsed wall-clock time from when the server received the query to when it sent the last row to the client. Lock_time is the portion of that total spent waiting to acquire table or row locks. A query with Lock_time close to Query_time is a lock contention problem, not an index problem — the fix involves transaction design or reducing lock scope, not adding indexes.

Why is my slow query log file empty even though slow_query_log = ON?

The most common causes are: (1) no queries have actually exceeded long_query_time yet — verify with SHOW GLOBAL STATUS LIKE 'Slow_queries'; (2) the log file path does not exist or the mysql user lacks write permission; (3) on SELinux systems, the file context is incorrect; (4) the slow_query_log_file variable points to a different path than the file you are inspecting — confirm with SHOW VARIABLES LIKE 'slow_query_log_file'.

How do I find the single most damaging query in the slow query log?

Run pt-query-digest and sort by R/Call (rows examined per call) or Response time (total cumulative time). The query at the top of the Response time ranking is consuming the most aggregate database time and should be the first target for EXPLAIN analysis and index optimization. If pt-query-digest is not available, use mysqldumpslow -s t -t 1 to extract the single highest-total-time query.