MySQL Slow Query Log Analysis Tutorial (2026): Complete Performance Monitoring Setup for VPS and Dedicated Servers

Understanding MySQL Slow Query Logs for VPS Performance

MySQL slow query logs capture queries that exceed your configured execution time threshold. This diagnostic tool helps you identify performance bottlenecks that hurt user experience and server resource usage.

The slow query log records essential details: query text, execution time, rows examined, and timestamps. These insights let you pinpoint which queries need optimization or additional indexing.

For hosting environments, slow queries directly impact customer satisfaction. A single inefficient query can spike CPU usage and delay response times across multiple websites on your HostMyCode VPS.

Enabling and Configuring Slow Query Logging

MySQL disables slow query logging by default. You'll need to enable it through configuration files or runtime commands.

Check your current slow query log status:

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

Enable slow query logging permanently by editing your MySQL configuration file. On Ubuntu, this file is typically located at /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 = 2
log_queries_not_using_indexes = 1

The long_query_time setting defines the threshold in seconds. Queries taking longer than this value get logged. Set this to 2 seconds for most web hosting scenarios.

Restart MySQL to apply these changes:

sudo systemctl restart mysql

Installing Percona Toolkit for Query Analysis

Percona Toolkit provides pt-query-digest, the industry standard for slow query log analysis. This tool aggregates similar queries and ranks them by impact.

Install Percona Toolkit on Ubuntu:

sudo apt update
sudo apt install percona-toolkit

For CentOS/AlmaLinux systems:

sudo yum install percona-toolkit

Verify the installation:

pt-query-digest --version

The toolkit includes additional utilities like pt-mysql-summary and pt-stalk for comprehensive database monitoring.

Analyzing Query Performance with pt-query-digest

Run pt-query-digest against your slow query log to generate a detailed performance report:

sudo pt-query-digest /var/log/mysql/mysql-slow.log

The output shows three main sections: overall statistics, query rankings, and detailed query analysis. Focus on the query rankings first—these show the most impactful slow queries.

Generate a report for the last 24 hours only:

sudo pt-query-digest --since='1d ago' /var/log/mysql/mysql-slow.log

Save the analysis to a file for later review:

sudo pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_analysis.txt

Each query gets a fingerprint—a normalized version that groups similar queries together. This helps you identify patterns across different parameter values.

Interpreting Query Digest Reports

The digest report ranks queries by several metrics. Exec time shows total time spent executing all instances of this query pattern.

Lock time indicates time waiting for table locks. Rows sent and rows examined reveal efficiency ratios.

A query examining 10,000 rows but returning only 10 suggests missing indexes or poor query structure.

Query time distribution shows percentiles. If the 95th percentile is much higher than the median, you likely have occasional performance spikes worth investigating.

The query examples section shows actual query text with real values. This helps you understand the context and identify specific problematic parameters.

For hosting environments, pay attention to queries from customer databases. Poor queries in one customer's application can affect server performance for all users on your managed VPS hosting platform.

Setting Up Automated Log Rotation

Slow query logs grow continuously and can consume significant disk space. Implement log rotation to prevent storage issues while maintaining historical data.

Create a logrotate configuration for MySQL slow logs:

sudo nano /etc/logrotate.d/mysql-slow

Add this configuration:

/var/log/mysql/mysql-slow.log {
    daily
    missingok
    rotate 30
    compress
    delaycompress
    notifempty
    copytruncate
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

This configuration rotates logs daily, keeps 30 days of history, and compresses old files. The copytruncate option works safely with MySQL's file-based logging.

Test your logrotate configuration:

sudo logrotate -d /etc/logrotate.d/mysql-slow

Creating Performance Monitoring Scripts

Automate slow query analysis with custom scripts. This bash script generates daily reports and alerts you to performance issues:

#!/bin/bash

# Daily slow query analysis
LOG_FILE="/var/log/mysql/mysql-slow.log"
REPORT_DIR="/var/log/mysql/reports"
DATE=$(date +%Y%m%d)

mkdir -p $REPORT_DIR

# Generate daily report
pt-query-digest --since='1d ago' $LOG_FILE > $REPORT_DIR/slow_queries_$DATE.txt

# Check for critical issues
CRITICAL_QUERIES=$(pt-query-digest --since='1d ago' --filter='$event->{Query_time} > 10' $LOG_FILE | wc -l)

if [ $CRITICAL_QUERIES -gt 0 ]; then
    echo "ALERT: $CRITICAL_QUERIES queries exceeded 10 seconds" | mail -s "MySQL Performance Alert" admin@yourdomain.com
fi

Make the script executable and add it to your crontab:

chmod +x /usr/local/bin/mysql_slow_analysis.sh
echo "0 1 * * * /usr/local/bin/mysql_slow_analysis.sh" | crontab -

Optimizing Queries Based on Analysis

Use your MySQL slow query log analysis tutorial results to identify optimization opportunities. Common issues include missing indexes, inefficient WHERE clauses, and unnecessary JOINs.

For queries with high rows examined to rows sent ratios, check if appropriate indexes exist:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2026-01-01';

The EXPLAIN output shows which indexes MySQL uses and the estimated rows examined. Look for "Using filesort" or "Using temporary" in the Extra column—these indicate expensive operations.

Add composite indexes for multi-column WHERE clauses:

ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);

Our MySQL database encryption tutorial covers security considerations when working with database optimization.

Advanced Filtering and Query Patterns

pt-query-digest supports advanced filtering to focus on specific query types or problematic patterns.

Filter queries by database:

pt-query-digest --filter='$event->{db} eq "wordpress_db"' /var/log/mysql/mysql-slow.log

Focus on SELECT queries only:

pt-query-digest --filter='$event->{cmd} eq "Query" && $event->{arg} =~ /^SELECT/' /var/log/mysql/mysql-slow.log

Analyze queries from specific time periods during high traffic:

pt-query-digest --since='2026-01-15 14:00:00' --until='2026-01-15 18:00:00' /var/log/mysql/mysql-slow.log

These filtering capabilities help you correlate query performance with specific applications or traffic patterns.

Integration with Monitoring Systems

Integrate slow query analysis with your existing monitoring infrastructure. Parse pt-query-digest output to extract key metrics for dashboards.

This Python script extracts top queries and sends metrics to monitoring systems:

import re
import subprocess
import json

def parse_digest_output():
    result = subprocess.run(['pt-query-digest', '--output', 'json', '/var/log/mysql/mysql-slow.log'], 
                          capture_output=True, text=True)
    
    if result.returncode == 0:
        return json.loads(result.stdout)
    return None

def send_to_monitoring(data):
    # Send to your monitoring system (Prometheus, Grafana, etc.)
    pass

digest_data = parse_digest_output()
if digest_data:
    send_to_monitoring(digest_data)

Consider setting up alerts for query time spikes or unusual query patterns. Early detection helps prevent performance degradation before it affects users.

Common Issues and Troubleshooting

If slow query logs aren't generating, verify MySQL has write permissions to the log directory:

sudo chown mysql:mysql /var/log/mysql/
sudo chmod 755 /var/log/mysql/

Large log files can make pt-query-digest slow. Process logs incrementally or use the --limit option:

pt-query-digest --limit 95% /var/log/mysql/mysql-slow.log

For servers with heavy query loads, consider increasing the long_query_time threshold temporarily. This helps you focus on the most critical issues first.

Memory usage during analysis scales with log size. On smaller VPS instances, process logs during off-peak hours to avoid resource conflicts.

Security and Best Practices

Slow query logs contain actual query text, including potentially sensitive data. Secure log files with appropriate permissions:

sudo chmod 640 /var/log/mysql/mysql-slow.log
sudo chown mysql:mysql /var/log/mysql/mysql-slow.log

Regular log rotation prevents logs from consuming excessive disk space. Monitor disk usage in your log directory:

du -sh /var/log/mysql/

Consider implementing log anonymization for environments with strict data protection requirements. Replace sensitive values with placeholders before sharing analysis results.

Our database user permission hardening tutorial covers additional security measures for production MySQL installations.

FAQ

How often should I analyze slow query logs?

Analyze slow query logs daily for production servers, or weekly for smaller applications. Set up automated reports to catch performance issues early without manual intervention.

What's a good threshold for long_query_time?

Start with 2 seconds for web applications. Adjust based on your application's requirements—e-commerce sites might use 1 second, while reporting applications might tolerate 5 seconds.

Can slow query logging impact MySQL performance?

Minimal impact on modern systems. Logging adds approximately 1-3% overhead. The performance insights gained far outweigh this small cost for most applications.

Should I log queries not using indexes?

Yes, especially during development and optimization phases. Set log_queries_not_using_indexes = 1 to identify queries that might benefit from additional indexes.

How long should I keep slow query logs?

Keep 30 days of logs for trend analysis and troubleshooting. Compress older logs to save disk space while maintaining historical data for capacity planning.