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:

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:

  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):

[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:

  sudo systemctl restart mariadb

• Ensure Log File Permissions:

  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:

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:

  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

SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

• Query the Slow Log Table:

  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:

SELECT * FROM large_table WHERE name = 'example';

• Step 1: Analyze with EXPLAIN:

  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:

  CREATE INDEX idx_name ON large_table(name);

• Step 3: Re-run EXPLAIN:

  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):

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

#!/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:

  ./analyze_slow_queries.sh

Example 6: SQL Query for Table-Based Log Analysis

If using log_output = TABLE:

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:

  /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:

  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!

[file-name 000142_2025-05-20_14-56-07.txt]