MySQL Slow Query Log Analysis: Configuration and Troubleshooting Guide

Quick Answer

• Conclusion: Enable MySQL slow query logging with slow_query_log = ON, set long_query_time to 0.1–1.0 seconds, and use a browser-based analysis tool to identify missing indexes and full table scans without uploading logs to third parties.
• First checks: Verify logging is enabled with SHOW VARIABLES LIKE 'slow_query_log%';, confirm the log file path exists and is readable, and ensure long_query_time is low enough to capture problematic queries.
• Minimal config: Add to my.cnf: slow_query_log = 1, slow_query_log_file = /var/log/mysql/slow.log, long_query_time = 1, log_queries_not_using_indexes = 1. Restart MySQL or use SET GLOBAL for dynamic changes.
• Environment: Works with MySQL 5.6+ and MariaDB 10.0+. Browser-based analysis requires no installation; MCP server integration requires Python 3.8+.

What Problem It Solves

Slow query logs capture SQL statements that exceed a configurable execution time threshold. Without this logging, database performance issues remain invisible until users report slow page loads or timeouts. This analysis approach helps:

• Detect missing indexes causing full table scans
• Identify queries that become slow as data grows
• Pinpoint high-frequency queries with marginal performance issues
• Enable natural-language analysis of log patterns using LLMs like Claude or GPT-4

The browser-based tool processes logs entirely client-side, eliminating privacy concerns from uploading sensitive database logs to external services.

When This Error or Setup Appears

Slow query analysis is most valuable in these scenarios:

• Production performance tuning: Users report intermittent slowness, but you need concrete evidence of which queries are problematic
• Post-deployment regression: A new release introduced slow queries that weren't caught in staging
• Capacity planning: Database CPU or I/O is high, and you need to identify the worst offenders
• Index optimization: You suspect missing indexes but need data to justify adding them
• Query rewrite: You want to identify queries that could benefit from restructuring or adding LIMIT clauses

Minimal Working Configuration

MySQL/MariaDB Configuration (my.cnf)

INI
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000

Dynamic Configuration (No Restart Required)

SQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL min_examined_row_limit = 1000;

Verify the settings:

SQL
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
SHOW VARIABLES LIKE 'min_examined_row_limit';

MCP Server Configuration

For integration with LLM-based analysis tools via MCP (Model Context Protocol):

JSON
{
  "mcpServers": {
    "mysql-slow-query-log-analysis": {
      "command": "python",
      "args": [
        "-m",
        "mysql_slow_query_log_analysis.server",
        "--log-path",
        "/var/log/mysql/slow.log",
        "--threshold",
        "1.0",
        "--min-examined-rows",
        "1000"
      ],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_USER": "analyst",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "performance_schema"
      }
    }
  }
}

Parameters and Environment Variables

Environment variables for MCP server:

Root Cause Analysis

Slow queries typically fall into three categories:

1. Missing indexes: Queries with log_queries_not_using_indexes flagged, scanning many rows but returning few. The analysis tool highlights these as index candidates.

2. Data growth: Queries that were fast at 100K rows become slow at 10M rows. Look for queries without LIMIT clauses or those using non-sargable conditions (e.g., WHERE YEAR(created_at) = 2024 instead of WHERE created_at >= '2024-01-01').

3. Lock contention: Queries that are fast individually but slow under concurrency. These appear as many moderately slow queries rather than a few very slow ones.

The min_examined_row_limit parameter helps filter category 3 noise when you're focused on categories 1 and 2.

Common Errors and Fixes

Error: Log file not found or permission denied

Solution: Verify the log file path and permissions:

BASH
# Check if file exists
ls -l /var/log/mysql/slow.log

# Check MySQL user permissions
sudo -u mysql ls -l /var/log/mysql/slow.log

# Ensure the analysis tool user can read it
sudo -u your_user cat /var/log/mysql/slow.log | head -5

Error: Log file is empty or no slow queries recorded

Solution: Confirm logging is active and threshold is reasonable:

SQL
-- Check all slow log settings
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- Temporarily set threshold to 0 to test logging
SET GLOBAL long_query_time = 0;
SELECT SLEEP(1);
-- Check if the SELECT appeared in the log

Error: Connection timeout to MySQL server

Solution: Test network connectivity and firewall rules:

BASH
# Test basic connectivity
telnet your-mysql-host 3306

# Test with MySQL client
mysql -h your-mysql-host -u analyst -p -e "SELECT 1"

Error: Paths not absolute in configuration

Solution: All file paths must use absolute paths:

INI
# Correct
slow_query_log_file = /var/log/mysql/slow.log

# Incorrect - will fail
slow_query_log_file = ./slow.log
slow_query_log_file = slow.log

Production Notes and Security Checks

Log Rotation

MySQL log rotation (logrotate) can interrupt analysis. Configure rotation to copy and truncate rather than move the file:

BASH
/var/log/mysql/slow.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql adm
    postrotate
        # Flush logs after rotation
        mysqladmin flush-logs
    endscript
}

I/O Considerations

High-concurrency environments may experience I/O bottlenecks from slow query logging. Mitigations:

• Place slow_query_log_file on a separate disk from data files
• Use SSDs for the log directory
• Set long_query_time high enough to avoid logging trivial queries (start at 1 second, adjust down gradually)

Security Hardening

• Database user: Create a dedicated read-only user with only FILE and SELECT privileges on performance_schema
• Network: If using MCP server remotely, enforce TLS and IP whitelisting
• Log access: Restrict log file permissions to mysql user and analysis tool user only
• File locking: MySQL holds an exclusive write lock on the log file. For analysis, copy the file first: cp /var/log/mysql/slow.log /tmp/slow-copy.log

Log File Size Limits

Browser-based analysis tools are memory-constrained. For logs exceeding 500MB:

• Use CLI tools like pt-query-digest for initial filtering
• Split logs by date: grep '^# Time: 2024-01-15' slow.log > slow-20240115.log
• Configure MCP server with streaming reads to avoid loading entire file into memory

FAQ

Q: How do I enable slow query logging without restarting MySQL?

A: Use SET GLOBAL commands for dynamic changes:

SQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';

These settings revert after a MySQL restart. To make them permanent, add them to my.cnf.

Q: Can the analysis tool handle GB-sized slow query logs?

A: Browser-based tools are limited by available memory; keep logs under 500MB. For larger logs, use local CLI tools like pt-query-digest or configure the MCP server with streaming reads. Consider splitting large logs by date or query type before analysis.

Q: How do I distinguish between missing indexes and data volume issues?

A: Combine log_queries_not_using_indexes with min_examined_row_limit. If a query doesn't use an index and scans many rows but returns few, it's likely a missing index. If it uses an index but still scans many rows, the issue is data volume or query design (e.g., missing LIMIT, non-sargable WHERE conditions). The analysis tool automatically flags these patterns.