Database Slow Query Monitoring Tutorial: Complete MySQL and PostgreSQL Performance Analysis Setup for Linux VPS in 2026

Understanding Database Slow Query Monitoring for VPS Performance

Database slow query monitoring forms the foundation of effective VPS performance optimization. When MySQL or PostgreSQL starts showing response delays, slow query logs provide the diagnostic data you need.

You can identify bottlenecks before they impact your applications. Most production databases handle thousands of queries per hour.

Without proper monitoring, problematic queries consume server resources. They create deadlocks and degrade user experience.

Setting up comprehensive slow query monitoring lets you catch these issues early. This maintains optimal database performance.

This tutorial covers complete setup for both MySQL and PostgreSQL on Ubuntu, Debian, and AlmaLinux VPS environments. You'll configure logging, set up analysis tools, and establish monitoring workflows that scale with your hosting needs.

MySQL Slow Query Log Configuration

MySQL's slow query log captures queries that exceed your specified execution time threshold. Start by checking your current MySQL version and configuration directory:

mysql --version
sudo find /etc -name "my.cnf" -o -name "mysql.conf"

Edit your main MySQL configuration file, typically located at /etc/mysql/my.cnf or /etc/my.cnf. Add these settings to 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
min_examined_row_limit = 1000

The long_query_time setting defines your threshold in seconds. Queries exceeding 2 seconds will appear in the slow log.

For high-traffic applications, consider starting with 1 second. Adjust based on your baseline performance.

Create the log directory and set proper permissions:

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

Restart MySQL to apply the configuration changes:

sudo systemctl restart mysql
sudo systemctl status mysql

Verify slow query logging is active by checking the MySQL variables:

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

PostgreSQL Query Logging Setup

PostgreSQL provides extensive logging capabilities through its postgresql.conf configuration file. Locate your PostgreSQL configuration directory:

sudo -u postgres psql -c "SHOW config_file;"
sudo find /etc -name "postgresql.conf" 2>/dev/null

Open the configuration file and modify these logging parameters:

log_destination = 'stderr,csvlog'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 2000
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_statement = 'mod'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

The log_min_duration_statement value is in milliseconds, so 2000 equals 2 seconds. PostgreSQL will log any statement that runs longer than this threshold.

Create the PostgreSQL log directory with proper ownership:

sudo mkdir -p /var/log/postgresql
sudo chown postgres:postgres /var/log/postgresql
sudo chmod 750 /var/log/postgresql

For VPS environments running HostMyCode Database Hosting, ensure your log retention policies align with your storage capacity and backup schedules.

Reload PostgreSQL configuration without restarting:

sudo systemctl reload postgresql
sudo -u postgres psql -c "SELECT pg_reload_conf();"

Verify the configuration changes:

sudo -u postgres psql -c "SHOW log_min_duration_statement;"
sudo -u postgres psql -c "SHOW logging_collector;"

Installing and Configuring MySQL Query Analysis Tools

Raw slow query logs require analysis tools to extract meaningful insights. Install mysqldumpslow and pt-query-digest from Percona Toolkit for comprehensive query analysis.

On Ubuntu/Debian systems:

sudo apt update
sudo apt install percona-toolkit

On AlmaLinux/RHEL systems:

sudo dnf install epel-release
sudo dnf install percona-toolkit

Use pt-query-digest to analyze your slow query log:

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

This command generates a detailed report showing:

• Query execution time statistics
• Most frequently executed slow queries
• Resource consumption patterns
• Index usage recommendations

Create a daily analysis script at /usr/local/bin/mysql-slow-analysis.sh:

#!/bin/bash
LOG_FILE="/var/log/mysql/mysql-slow.log"
REPORT_DIR="/var/log/mysql/reports"
DATE=$(date +%Y%m%d)

mkdir -p "$REPORT_DIR"

if [[ -f "$LOG_FILE" ]]; then
    pt-query-digest "$LOG_FILE" > "$REPORT_DIR/slow-query-analysis-$DATE.txt"
    echo "Slow query analysis completed: $REPORT_DIR/slow-query-analysis-$DATE.txt"
fi

Make the script executable and add it to your cron schedule:

sudo chmod +x /usr/local/bin/mysql-slow-analysis.sh
sudo crontab -e

Add this line to run analysis daily at 2 AM:

0 2 * * * /usr/local/bin/mysql-slow-analysis.sh

PostgreSQL Log Analysis with pgBadger

pgBadger provides comprehensive PostgreSQL log analysis capabilities. Install it directly from the package repository:

sudo apt install pgbadger  # Ubuntu/Debian
sudo dnf install pgbadger  # AlmaLinux/RHEL

Generate an HTML report from your PostgreSQL logs:

sudo pgbadger /var/log/postgresql/*.log -o /tmp/postgres-report.html

pgBadger analyzes multiple log formats and produces reports containing:

• Slowest queries by average execution time
• Database connection statistics
• Checkpoint and vacuum activity
• Lock wait events and timeouts

For more detailed analysis of specific queries, you can examine PostgreSQL Log Analysis Tutorial for advanced troubleshooting techniques.

Create an automated pgBadger analysis script at /usr/local/bin/postgres-analysis.sh:

#!/bin/bash
LOG_DIR="/var/log/postgresql"
REPORT_DIR="/var/log/postgresql/reports"
DATE=$(date +%Y%m%d)

mkdir -p "$REPORT_DIR"

if ls "$LOG_DIR"/*.log 1> /dev/null 2>&1; then
    pgbadger "$LOG_DIR"/*.log -o "$REPORT_DIR/postgres-analysis-$DATE.html"
    echo "PostgreSQL analysis completed: $REPORT_DIR/postgres-analysis-$DATE.html"
fi

Setting Up Real-Time Query Performance Monitoring

Real-time monitoring helps you catch performance issues as they occur. Install and configure mytop for MySQL and pg_top for PostgreSQL live monitoring.

Install monitoring tools:

sudo apt install mytop postgresql-contrib  # Ubuntu/Debian
sudo dnf install mytop postgresql-contrib  # AlmaLinux/RHEL

Configure mytop by creating ~/.mytop:

host=localhost
db=mysql
delay=5
port=3306
socket=/var/run/mysqld/mysqld.sock

Run mytop to see real-time MySQL query activity:

mytop

For PostgreSQL, use the built-in pg_stat_activity view to monitor active queries:

sudo -u postgres psql -c "
SELECT pid, usename, application_name, client_addr, 
       state, query_start, 
       now() - query_start AS duration, 
       query 
FROM pg_stat_activity 
WHERE state != 'idle' 
ORDER BY duration DESC;
"

Create a monitoring script that checks for long-running queries every 60 seconds:

#!/bin/bash
# Save as /usr/local/bin/monitor-long-queries.sh

THRESHOLD=300  # 5 minutes in seconds

while true; do
    echo "=== $(date) ==="
    
    # MySQL long queries
    mysql -e "SELECT id, user, host, db, command, time, state, info 
              FROM information_schema.processlist 
              WHERE time > $THRESHOLD AND command != 'Sleep' 
              ORDER BY time DESC;"
    
    # PostgreSQL long queries
    sudo -u postgres psql -c "
    SELECT pid, usename, application_name, 
           EXTRACT(EPOCH FROM (now() - query_start)) as duration_seconds,
           query 
    FROM pg_stat_activity 
    WHERE state != 'idle' 
      AND EXTRACT(EPOCH FROM (now() - query_start)) > $THRESHOLD 
    ORDER BY duration_seconds DESC;"
    
    sleep 60
done

Automated Alert Configuration for Critical Queries

Set up automated alerts to notify you when queries exceed acceptable performance thresholds. Create a monitoring script that sends notifications via email or system logs.

Install mail utilities for alert notifications:

sudo apt install mailutils  # Ubuntu/Debian
sudo dnf install mailx      # AlmaLinux/RHEL

Create an alert script at /usr/local/bin/query-alert.sh:

#!/bin/bash
THRESHOLD=600  # 10 minutes
ALERT_EMAIL="admin@yourdomain.com"
LOG_FILE="/var/log/slow-query-alerts.log"

# Function to log and send alerts
send_alert() {
    local message="$1"
    local timestamp=$(date '+%Y-%m-%d %H:%M:%S')
    
    echo "[$timestamp] $message" >> "$LOG_FILE"
    echo "$message" | mail -s "Database Performance Alert" "$ALERT_EMAIL"
}

# Check MySQL for extremely long queries
long_mysql=$(mysql -s -N -e "
    SELECT COUNT(*) FROM information_schema.processlist 
    WHERE time > $THRESHOLD AND command != 'Sleep';
")

if [[ "$long_mysql" -gt 0 ]]; then
    send_alert "MySQL: $long_mysql queries running longer than $THRESHOLD seconds"
fi

# Check PostgreSQL for extremely long queries
long_postgres=$(sudo -u postgres psql -t -c "
    SELECT COUNT(*) FROM pg_stat_activity 
    WHERE state != 'idle' 
      AND EXTRACT(EPOCH FROM (now() - query_start)) > $THRESHOLD;
")

if [[ "$long_postgres" -gt 0 ]]; then
    send_alert "PostgreSQL: $long_postgres queries running longer than $THRESHOLD seconds"
fi

Schedule the alert script to run every 5 minutes:

sudo chmod +x /usr/local/bin/query-alert.sh
sudo crontab -e

Add this cron entry:

*/5 * * * * /usr/local/bin/query-alert.sh

Query Performance Optimization Based on Monitoring Data

Your monitoring data reveals optimization opportunities that directly impact VPS performance. Use this data to identify the most critical issues first.

Common optimization strategies based on monitoring findings:

Index Optimization

Queries scanning large tables without indexes show up prominently in slow query logs. For MySQL, use the EXPLAIN statement to analyze query execution plans:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

For PostgreSQL, use EXPLAIN ANALYZE for detailed execution statistics:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

When you see "Full Table Scan" or "Seq Scan" in the output, consider adding appropriate indexes.

Query Rewriting

Sometimes slow queries can be rewritten more efficiently. Common patterns include:

• Replacing correlated subqueries with JOINs
• Using LIMIT clauses for pagination
• Avoiding SELECT * in favor of specific columns
• Using appropriate WHERE clause ordering

Connection Pool Tuning

High connection counts in your monitoring data indicate potential connection pool issues. Review our guide on Database Connection Pool Configuration for optimization strategies.

Log Rotation and Storage Management

Slow query logs grow rapidly on busy databases. Configure log rotation to prevent disk space issues while maintaining sufficient history for analysis.

Create a logrotate configuration for MySQL slow logs at /etc/logrotate.d/mysql-slow:

/var/log/mysql/mysql-slow.log {
    daily
    rotate 14
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-logs
    endscript
}

For PostgreSQL, create /etc/logrotate.d/postgresql-slow:

/var/log/postgresql/*.log {
    daily
    rotate 14
    missingok
    compress
    delaycompress
    notifempty
    create 640 postgres postgres
    postrotate
        /usr/bin/systemctl reload postgresql
    endscript
}

Test your logrotate configuration:

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

Frequently Asked Questions

How often should I analyze slow query logs?

Analyze slow query logs daily for production databases. You can also trigger analysis immediately after performance degradation reports.

Set up automated analysis scripts to generate reports every 24 hours. Use real-time monitoring for queries exceeding critical thresholds like 10+ seconds.

What's the optimal long_query_time threshold for VPS databases?

Start with 2 seconds for most VPS applications. Then adjust based on your baseline performance.

High-traffic e-commerce sites might use 1 second. Analytical databases might use 5-10 seconds.

Monitor your typical query patterns for 1-2 weeks before finalizing the threshold.

How much disk space do slow query logs typically consume?

Slow query logs can consume 10-100MB daily on busy databases. This depends on your threshold settings and query complexity.

Plan for 1-2GB monthly log storage. Implement log rotation to maintain 14-30 days of history without consuming excessive disk space.

Can slow query monitoring impact database performance?

Properly configured slow query logging has minimal performance impact. It typically creates less than 1-2% overhead.

Avoid logging all queries or setting extremely low thresholds (under 0.1 seconds) on high-traffic databases. This can create significant log file I/O and storage consumption.

Should I monitor both MySQL and PostgreSQL the same way?

While both databases support slow query logging, their optimal configurations differ. MySQL's slow query log works well for basic monitoring.

PostgreSQL's extensive logging options provide more detailed analysis capabilities. Use database-specific analysis tools like pt-query-digest for MySQL and pgBadger for PostgreSQL for best results.