Set Up MySQL Slow Query Log on Ubuntu VPS: Complete Performance Guide

Why MySQL Slow Query Logging Matters for Your VPS

Database performance issues can cripple your website faster than almost anything else. When your MySQL queries start taking too long, users notice immediately. Pages load slowly, forms timeout, and your hosting resources get wasted on inefficient database operations.

The MySQL slow query log is your first line of defense. It captures every query that takes longer than a specified threshold, giving you concrete data about what's slowing down your database. This isn't theoretical performance monitoring—it's real query forensics.

On a Hostperl VPS, you have complete control over your MySQL configuration. You can enable detailed slow query logging, tune the thresholds, and implement the optimizations that matter most for your specific workload.

Prerequisites and System Requirements

You'll need root access to your Ubuntu VPS and MySQL already installed. This tutorial works with MySQL 8.0, MySQL 5.7, or MariaDB 10.x on Ubuntu 20.04, 22.04, or 24.04.

Check your current MySQL version first:

mysql --version

Make sure you have enough disk space for the log files. Slow query logs can grow quickly on busy databases, so plan for at least 1GB of free space initially.

Enable MySQL Slow Query Log

Connect to MySQL as root to check your current slow query configuration:

mysql -u root -p

Run these commands to see your current settings:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';

Most fresh MySQL installations have slow query logging disabled. Enable it dynamically first to test:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

This enables logging for queries taking longer than 2 seconds. You can adjust this threshold based on your application needs. E-commerce sites might use 1 second, while analytical workloads might use 5 seconds.

Configure Persistent Slow Query Settings

Dynamic changes reset on MySQL restart. Make the configuration permanent by editing your MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add these lines under the [mysqld] section:

[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 log_queries_not_using_indexes parameter is crucial. It captures queries that don't use indexes, even if they're fast on small datasets. These queries often become performance killers as your data grows.

Create the log directory and set proper permissions:

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 755 /var/log/mysql

Restart MySQL to apply the configuration:

sudo systemctl restart mysql

Verify the settings are active:

mysql -u root -p -e "SHOW VARIABLES LIKE 'slow%';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'long_query_time';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_queries_not_using_indexes';"

Understanding Slow Query Log Output

Generate some test slow queries to see the logging in action. Connect to MySQL and run a deliberately inefficient query:

mysql -u root -p
SELECT SLEEP(3), 'test slow query';

Check the slow query log:

sudo tail -20 /var/log/mysql/mysql-slow.log

Each slow query entry shows several key pieces of information:

• Query_time: Total execution time
• Lock_time: Time spent waiting for locks
• Rows_sent: Number of rows returned
• Rows_examined: Number of rows MySQL scanned
• Timestamp: When the query executed
• User and Host: Who ran the query

Pay special attention to queries where Rows_examined is much higher than Rows_sent. These indicate inefficient queries that scan lots of data but return few results.

Analyze Slow Queries with mysqldumpslow

Raw slow query logs become unreadable quickly. MySQL includes mysqldumpslow to summarize and analyze the log data:

sudo mysqldumpslow /var/log/mysql/mysql-slow.log

Show the 10 slowest queries:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Show queries sorted by average execution time:

sudo mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

Show only queries that don't use indexes:

sudo mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log | grep "not using indexes"

The output groups similar queries together and shows you patterns. Look for queries that appear frequently or take consistently long execution times.

Set Up Log Rotation to Prevent Disk Space Issues

Slow query logs grow indefinitely without rotation. Set up logrotate to manage the file size:

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

Add this configuration:

/var/log/mysql/mysql-slow.log {
    daily
    rotate 14
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysql -u root -e "FLUSH LOGS" 2>/dev/null || true
    endscript
}

This keeps 14 days of logs, compresses old files, and tells MySQL to create a new log file after rotation.

Test the logrotate configuration:

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

Monitoring and Alerting on Slow Query Patterns

Create a simple monitoring script to alert you when slow query counts spike:

sudo nano /usr/local/bin/check-slow-queries.sh

#!/bin/bash

# Count slow queries from the last hour
LAST_HOUR=$(date -d '1 hour ago' '+%Y-%m-%d %H:%M:%S')
SLOW_COUNT=$(mysql -u root -e "SELECT COUNT(*) FROM mysql.slow_log WHERE start_time > '$LAST_HOUR';" -s -N)

# Alert threshold
THRESHOLD=50

if [ "$SLOW_COUNT" -gt "$THRESHOLD" ]; then
    echo "WARNING: $SLOW_COUNT slow queries detected in the last hour"
    # Add email notification or webhook here
fi

Make it executable and add to cron:

sudo chmod +x /usr/local/bin/check-slow-queries.sh
sudo crontab -e

Add this line to run every 15 minutes:

*/15 * * * * /usr/local/bin/check-slow-queries.sh

This helps you catch performance regressions before they impact users. For more comprehensive server monitoring strategies, consider implementing full system observability.

Common Slow Query Optimization Techniques

Once you've identified slow queries, you need to fix them. Start with the most frequent offenders.

Missing Indexes: If mysqldumpslow shows queries not using indexes, add appropriate indexes:

-- Example: slow query on user lookup
SELECT * FROM users WHERE email = 'user@example.com';

-- Add index to fix it
CREATE INDEX idx_users_email ON users(email);

Inefficient WHERE Clauses: Queries scanning too many rows often have broad WHERE conditions. Make them more specific or add compound indexes.

Large Result Sets: Queries returning thousands of rows might need LIMIT clauses or pagination.

Use EXPLAIN to understand query execution plans:

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE created_at > '2026-01-01';

The JSON format shows detailed information about index usage, join methods, and row estimates.

Advanced Configuration Options

Fine-tune slow query logging for specific scenarios:

Log Administrative Commands:

log_slow_admin_statements = 1

Capture Queries Below the Threshold:

min_examined_row_limit = 1000

Log to Table Instead of File:

log_output = 'TABLE,FILE'

Table logging stores slow queries in mysql.slow_log, making them queryable with SQL. This is useful for programmatic analysis but has higher overhead.

For production environments requiring detailed database performance monitoring, consider integrating with your existing infrastructure monitoring stack.

Frequently Asked Questions

What's a reasonable long_query_time threshold?

Start with 2-5 seconds for most web applications. Lower it to 1 second for high-traffic sites or increase to 10+ seconds for analytical workloads. Monitor your log volume and adjust accordingly.

How much disk space do slow query logs use?

This depends entirely on your query patterns. A busy WordPress site might generate 100MB per day, while a high-traffic application could create several GB. Always implement log rotation to prevent disk space issues.

Can slow query logging impact MySQL performance?

The overhead is minimal for reasonable thresholds (2+ seconds). Logging every query or setting very low thresholds can impact performance, especially with high query volumes. File-based logging has lower overhead than table-based logging.

Should I log queries that don't use indexes?

Yes, especially during development and initial production deployment. These queries often perform well on small datasets but become bottlenecks as data grows. Disable this setting only if the log volume becomes unmanageable.

How do I find the worst performing queries?

Use mysqldumpslow -s t to sort by total time or mysqldumpslow -s at for average time. Focus on queries that appear frequently with high execution times. A query running 1000 times at 0.5 seconds is often more problematic than one query taking 10 seconds.