2025-05-20 14:56:07
Below are practical examples of enabling and using the **slow query log** in MariaDB to identify and analyze slow-performing SQL queries, similar to PostgreSQL's `log_min_duration_statement` functionality. These examples include configuration, log analysis, and optimization steps.
### 1. Enabling the Slow Query Log in MariaDB
The slow query log can be enabled dynamically or via the configuration file (`my.cnf`). Below are both approaches.
#### Example 1: Enable Slow Query Log Dynamically
To enable the slow query log without restarting MariaDB:
```sql
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Log queries taking >1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- Log queries not using indexes
```
- **Verification**:
```sql
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
```
Expected output:
```
+------------------------+----------------+
| 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 |
+-----------------+-------+
```
#### Example 2: Enable Slow Query Log in Configuration File
Edit the MariaDB configuration file (e.g., `/etc/my.cnf` or `/etc/mysql/mariadb.conf.d/50-server.cnf`):
```ini
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_output = FILE -- Log to file (alternatively, TABLE for mysql.slow_log)
```
- **Apply Changes**:
Restart MariaDB:
```bash
sudo systemctl restart mariadb
```
- **Ensure Log File Permissions**:
```bash
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
```
### 2. Example Slow Query Log Output
Once enabled, slow queries are logged to the specified file (e.g., `/var/log/mysql/slow-query.log`). Below is an example of a slow query log entry:
```
/usr/sbin/mysqld, Version: 10.5.15-MariaDB (MariaDB Server). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 2025-05-20T14:30:45.123456Z
# User@Host: app_user[app_user] @ localhost []
# Query_time: 1.234567 Lock_time: 0.000123 Rows_sent: 1000 Rows_examined: 100000
SET timestamp=1742581845;
SELECT * FROM large_table WHERE name = 'example';
```
- **Explanation**:
- **Query_time**: 1.234567 seconds (the query took ~1.23 seconds).
- **Lock_time**: Time spent acquiring locks (0.000123 seconds).
- **Rows_sent**: 1000 rows returned to the client.
- **Rows_examined**: 100,000 rows scanned (indicates potential inefficiency).
- **Query**: The actual SQL statement (`SELECT * FROM large_table WHERE name = 'example'`).
### 3. Analyzing Slow Queries with `mysqldumpslow`
MariaDB includes `mysqldumpslow`, a tool to summarize and analyze slow query logs.
#### Example 3: Using `mysqldumpslow`
Run `mysqldumpslow` to aggregate slow query data:
```bash
mysqldumpslow /var/log/mysql/slow-query.log
```
- **Sample Output**:
```
Count: 10 Time=1.25s (12.5s) Lock=0.00s (0.00s) Rows=1000.0 (10000), app_user[app_user]@localhost
SELECT * FROM large_table WHERE name = 'example'
```
- **Count**: Query appeared 10 times.
- **Time**: Average execution time (1.25s per query, 12.5s total).
- **Rows**: Average rows returned (1000 per query).
- **Filter Specific Queries**:
To focus on queries with high execution time:
```bash
mysqldumpslow -t 5 -s t /var/log/mysql/slow-query.log
```
- `-t 5`: Show top 5 queries.
- `-s t`: Sort by total time.
### 4. Logging to a Table for Query Analysis
Instead of a file, you can log slow queries to the `mysql.slow_log` table for easier querying.
#### Example 4: Enable Table-Based Logging
```sql
SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
```
- **Query the Slow Log Table**:
```sql
SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
WHERE query_time > 1
ORDER BY query_time DESC
LIMIT 5;
```
- **Sample Output**:
```
+---------------------+-----------+-------------+-----------+-----------+---------------+----------------------------------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | sql_text |
+---------------------+-----------+-------------+-----------+-----------+---------------+----------------------------------+
| 2025-05-20 14:30:45 | app_user | 00:00:01.23 | 00:00:00.00 | 1000 | 100000 | SELECT * FROM large_table WHERE name = 'example' |
+---------------------+-----------+-------------+-----------+-----------+---------------+----------------------------------+
```
### 5. Optimizing a Slow Query
Suppose the slow query is:
```sql
SELECT * FROM large_table WHERE name = 'example';
```
- **Step 1: Analyze with `EXPLAIN`**:
```sql
EXPLAIN SELECT * FROM large_table WHERE name = 'example';
```
- **Sample Output**:
```
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | large_table| ALL | NULL | NULL | NULL | NULL | 100000 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
```
- **Issue**: `type: ALL` indicates a full table scan, and no index is used (`key: NULL`).
- **Step 2: Create an Index**:
```sql
CREATE INDEX idx_name ON large_table(name);
```
- **Step 3: Re-run `EXPLAIN`**:
```sql
EXPLAIN SELECT * FROM large_table WHERE name = 'example';
```
- **Improved Output**:
```
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | large_table| ref | idx_name | idx_name | 256 | const | 1 | Using where |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
```
- **Improvement**: `type: ref` and `rows: 1` indicate the index is used, reducing scanned rows.
- **Step 4: Verify Performance**:
Run the query and check the slow query log again. If the query time drops below `long_query_time`, it won’t appear in the log.
### 6. Advanced Configuration: Logging Query Plans
To include query execution plans in the slow query log (similar to PostgreSQL’s `auto_explain`):
```sql
SET GLOBAL log_slow_verbosity = 'query_plan,explain';
```
- **Example Log Entry with Query Plan**:
```
# Time: 2025-05-20T14:32:00.123456Z
# User@Host: app_user[app_user] @ localhost []
# Query_time: 1.500000 Lock_time: 0.000100 Rows_sent: 1000 Rows_examined: 100000
# EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra
# EXPLAIN: 1 SIMPLE large_table ALL NULL NULL NULL NULL 100000 Using where
SET timestamp=1742581920;
SELECT * FROM large_table WHERE name = 'example';
```
- **Use Case**: Helps identify why a query is slow (e.g., full table scans, missing indexes).
### 7. Automating Analysis with a Script
To regularly analyze slow queries, you can use a script to parse the log file or table.
#### Example 5: Bash Script to Summarize Slow Queries
```bash
#!/bin/bash
LOG_FILE="/var/log/mysql/slow-query.log"
mysqldumpslow -t 10 -s t "$LOG_FILE" > /tmp/slow_query_summary.txt
cat /tmp/slow_query_summary.txt
```
- Save as `analyze_slow_queries.sh`, make executable (`chmod +x`), and run:
```bash
./analyze_slow_queries.sh
```
#### Example 6: SQL Query for Table-Based Log Analysis
If using `log_output = TABLE`:
```sql
SELECT sql_text, COUNT(*) as query_count, AVG(query_time) as avg_time, SUM(rows_examined) as total_rows
FROM mysql.slow_log
GROUP BY sql_text
ORDER BY avg_time DESC
LIMIT 5;
```
### Notes
- **Log Rotation**: Use `logrotate` to manage slow query log file size:
```bash
/var/log/mysql/slow-query.log {
weekly
rotate 4
compress
missingok
notifempty
}
```
- **Performance Impact**: Enabling `log_queries_not_using_indexes` or `log_slow_verbosity = 'query_plan'` may increase logging overhead. Test in a non-production environment first.
- **Tools**: Use `Percona Toolkit` (`pt-query-digest`) for advanced slow query analysis:
```bash
pt-query-digest /var/log/mysql/slow-query.log
```
For further details, see the [MariaDB documentation](https://mariadb.com/kb/en/slow-query-log-overview/). If you need help with specific queries or tools, let me know!
Back to list