LogsDB
MySQL logo

MySQL Slow Query Log

Queries that exceed the long_query_time threshold for performance analysis

Edit this page

Quick Facts

Default Path (Linux)
/var/log/mysql/mysql-slow.log
Default Format
text
JSON Native
No
Rotation
logrotate

Log Example

Default format: Default Text Format

Example Log Entrylog
# Time: 2025-12-20T14:32:18.123456Z
# User@Host: app_user[app_user] @ 192.168.1.100 []  Id: 12345
# Query_time: 15.123456  Lock_time: 0.000123 Rows_sent: 1000  Rows_examined: 1000000
SET timestamp=1734702738;
SELECT * FROM large_table WHERE status = 'active';

Structure:

Multi-line format with metadata and query

Paths by Platform

Debian / Ubuntu
/var/log/mysql/mysql-slow.log
RHEL / CentOS
/var/log/mysqld-slow.log

Available Formats

Default Text Format

Default

Example:

# Time: 2025-12-20T14:32:18.123456Z
# User@Host: app_user[app_user] @ 192.168.1.100 []  Id: 12345
# Query_time: 15.123456  Lock_time: 0.000123 Rows_sent: 1000  Rows_examined: 1000000
SET timestamp=1734702738;
SELECT * FROM large_table WHERE status = 'active';

Structure:

Multi-line format with metadata and query

Fields Reference

FieldTypeDescriptionExample
Time
datetime
When the query completed2025-12-20T14:32:18.123456Z
User
string
MySQL user who ran the queryapp_user
Host
string
Client host192.168.1.100
Query_time
float
(seconds)		Total query execution time15.123456
Lock_time
float
(seconds)		Time waiting for locks0.000123
Rows_sent
integer
Rows returned to client1000
Rows_examined
integer
Rows examined by the query1000000
query
string
The SQL querySELECT * FROM large_table WHERE status = 'active'

Parsing Patterns

Collector Configurations

pt query digestyaml
1# Analyze slow query log with Percona Toolkit
2pt-query-digest /var/log/mysql/mysql-slow.log
3
4# Top 10 queries by time
5pt-query-digest --limit=10 /var/log/mysql/mysql-slow.log
6
7# Output as JSON
8pt-query-digest --output json /var/log/mysql/mysql-slow.log

Configuration

Enable Logging

Enable slow query logging

/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1           # Log queries > 1 second
log_queries_not_using_indexes = 1  # Also log queries without indexes

Log Rotation

Tool: logrotate | Config: /etc/logrotate.d/mysql

/etc/logrotate.d/mysql
/var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    missingok
    create 640 mysql adm
    compress
    sharedscripts
    postrotate
        mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-logs slow
    endscript
}

Use Cases

Slow query identification

Find queries needing optimization

Query_time
query

Index analysis

Queries examining many rows

Rows_examined
Rows_sent
query
Rows_examined / Rows_sent > 1000

Lock contention

Queries waiting on locks

Lock_time

Troubleshooting

Tested On

v8.4.0 on Ubuntu 24.04
mysql_dba - 2025-12-10
Last updated: 2025-12-10 by mysql_dba
3 contributors156 upvotes
Validated

Community Discussions

Help improve this documentation

Found an error or want to add more examples? Contributions are welcome!

EditReport Issue