Server Monitoring Best Practices: Keeping Your WordPress App and MySQL Clusters Alive on DigitalOcean

Proactive MySQL Replication Lag Monitoring

Replication lag is a silent killer of data consistency in distributed MySQL setups, especially when serving a high-traffic WordPress application. Ignoring it can lead to stale data being served to users, failed writes, and complex recovery scenarios. DigitalOcean’s managed MySQL clusters, while robust, still require vigilant monitoring of replication status. We’ll focus on a practical, script-based approach using standard MySQL tools and `mysqldumpslow` for log analysis.

The core of replication monitoring lies in comparing the `Seconds_Behind_Master` value across all replicas. A non-zero, or worse, a steadily increasing value, indicates a problem. We’ll craft a Bash script that iterates through your replica nodes, queries their replication status, and triggers an alert if the lag exceeds a defined threshold.

Bash Script for MySQL Replication Health Check

This script assumes you have SSH access to your database nodes or can connect to them using a dedicated monitoring user with appropriate privileges. It’s designed to be run periodically by a scheduler like `cron`.

First, define your database credentials and the lag threshold. It’s best practice to use a dedicated monitoring user with read-only access to `SHOW REPLICA STATUS` and `SHOW MASTER STATUS`.

Create a file named `check_mysql_replication.sh` and populate it with the following content:

#!/bin/bash

# --- Configuration ---
DB_USER="monitor_user"
DB_PASSWORD="your_secure_password"
# Array of replica hostnames or IP addresses
REPLICA_HOSTS=("mysql-replica-1.do.digitalocean.com" "mysql-replica-2.do.digitalocean.com" "mysql-replica-3.do.digitalocean.com")
# Replication lag threshold in seconds
LAG_THRESHOLD=60
# Alerting mechanism (e.g., send email, trigger webhook)
ALERT_EMAIL="[email protected]"
ALERT_SUBJECT="CRITICAL: MySQL Replication Lag Detected!"

# --- Script Logic ---
echo "Starting MySQL replication lag check..."

ALERT_MESSAGE=""

for HOST in "${REPLICA_HOSTS[@]}"; do
    echo "Checking replica: $HOST"
    # Execute the MySQL query to get replication status
    # Using --skip-column-names to simplify parsing
    REPLICA_STATUS=$(mysql -h "$HOST" -u "$DB_USER" -p"$DB_PASSWORD" -e "SHOW REPLICA STATUS\G" 2>/dev/null | grep "Seconds_Behind_Master:")

    if [ -z "$REPLICA_STATUS" ]; then
        echo "  ERROR: Could not retrieve replication status for $HOST. Check connection or credentials."
        ALERT_MESSAGE+="Host: $HOST\n  Status: UNREACHABLE or NO REPLICA STATUS FOUND\n\n"
        continue
    fi

    # Extract the numeric value of Seconds_Behind_Master
    LAG_VALUE=$(echo "$REPLICA_STATUS" | awk '{print $2}')

    # Handle cases where Seconds_Behind_Master might be 'NULL' (e.g., not a replica)
    if [[ "$LAG_VALUE" == "NULL" || -z "$LAG_VALUE" ]]; then
        echo "  INFO: $HOST is not configured as a replica or has no active replication."
        continue
    fi

    echo "  Seconds_Behind_Master: $LAG_VALUE"

    # Compare lag with the threshold
    if [ "$LAG_VALUE" -gt "$LAG_THRESHOLD" ]; then
        echo "  ALERT: High replication lag detected on $HOST ($LAG_VALUE seconds)."
        ALERT_MESSAGE+="Host: $HOST\n  Lag: $LAG_VALUE seconds (Threshold: $LAG_THRESHOLD seconds)\n\n"
    fi
done

# --- Alerting ---
if [ -n "$ALERT_MESSAGE" ]; then
    echo -e "Sending alert email to $ALERT_EMAIL..."
    echo -e "MySQL Replication Lag Alert:\n\n${ALERT_MESSAGE}" | mail -s "$ALERT_SUBJECT" "$ALERT_EMAIL"
    echo "Alert email sent."
    exit 1 # Indicate failure
else
    echo "All replicas are within acceptable lag limits."
    exit 0 # Indicate success
fi

Make the script executable:

chmod +x check_mysql_replication.sh

To automate this, add it to your crontab. For example, to run it every 5 minutes:

*/5 * * * * /path/to/your/scripts/check_mysql_replication.sh >> /var/log/mysql_replication_check.log 2>&1

WordPress Application Performance Monitoring (APM) with New Relic

For the WordPress application layer, a robust APM solution is indispensable. New Relic is a powerful choice, offering deep insights into transaction traces, database queries, external service calls, and error rates. On DigitalOcean, integrating New Relic typically involves installing the New Relic agent on your Droplets.

The New Relic PHP agent is installed as a PHP extension. The process generally involves:

• Downloading the New Relic installer script.
• Running the installer, which detects your PHP version and web server.
• Configuring the agent with your New Relic license key.
• Restarting your web server (Apache or Nginx) and PHP-FPM.

Here’s a typical sequence of commands. Replace `YOUR_LICENSE_KEY` with your actual New Relic license key.

# Download the installer script
curl -Ls https://download.newrelic.com/install/newrelic-php5-agent.sh | sudo bash /dev/stdin YOUR_LICENSE_KEY

# The script will prompt for confirmation and detect your PHP installation.
# It typically installs to /usr/bin/newrelic-install and configures php.ini.

# After installation, restart your web server and PHP-FPM
# For Apache:
sudo systemctl restart apache2

# For Nginx with PHP-FPM (e.g., PHP 8.1):
sudo systemctl restart nginx
sudo systemctl restart php8.1-fpm

# Verify installation by checking phpinfo() output for New Relic
# Or by looking for the newrelic.ini file in your PHP configuration directory.
# You can find your PHP config directory with: php --ini

Once the agent is active, New Relic will start collecting data. Key metrics to monitor for WordPress include:

• Transaction Traces: Identify slow PHP functions, WordPress hooks, and plugin execution times. Look for outliers and common slow paths.
• Database Calls: Pinpoint inefficient SQL queries generated by WordPress core, themes, or plugins. Pay attention to the number of queries per request and their execution time.
• External Services: Monitor latency and errors when WordPress communicates with external APIs (e.g., payment gateways, social media APIs).
• Error Rate: Track PHP errors, warnings, and fatal errors. Configure alerts for spikes in error frequency.
• Throughput: Observe the number of requests per minute to understand application load.

Nginx Performance Tuning and Monitoring

Nginx is a high-performance web server, but its configuration is critical for optimal WordPress performance and stability. Monitoring Nginx involves tracking request rates, error logs, and resource utilization.

Key Nginx Configuration Directives for WordPress:

# Increase worker connections to handle more concurrent connections
worker_connections 4096;

# Enable Gzip compression for text-based assets
gzip on;
gzip_vary on;
gzip_proxied any;
gzip_comp_level 6;
gzip_types text/plain text/css application/json application/javascript text/xml application/xml application/xml+rss text/javascript image/svg+xml;

# Enable HTTP/2 for faster multiplexing
http2 on;

# Caching for static assets
location ~* \.(jpg|jpeg|png|gif|ico|css|js|svg|woff|woff2|ttf|eot)$ {
    expires 30d;
    add_header Cache-Control "public, no-transform";
}

# FastCGI buffer sizes for PHP processing
location ~ \.php$ {
    # ... other PHP config ...
    fastcgi_buffers 8 16k;
    fastcgi_buffer_size 32k;
    fastcgi_connect_timeout 300;
    fastcgi_send_timeout 300;
    fastcgi_read_timeout 300;
}

# Keepalive timeout for persistent connections
keepalive_timeout 65;

# Enable access and error logs
access_log /var/log/nginx/access.log;
error_log /var/log/nginx/error.log warn; # Log warnings and above

Monitoring Nginx:

1. Access and Error Logs: Regularly analyze Nginx logs. Tools like `goaccess` or `mysqldumpslow` (adapted for Nginx logs) can provide valuable insights into traffic patterns and errors.

# Example: Using goaccess for real-time log analysis
# Install goaccess: sudo apt-get install goaccess
# Run goaccess on your access log:
goaccess /var/log/nginx/access.log --log-format=combined

2. **Nginx Status Module:** Enable the `ngx_http_stub_status_module` to get real-time metrics. Create a status endpoint in your Nginx configuration:

# In your http block or a specific server block
location /nginx_status {
    stub_status on;
    access_log off;
    allow 127.0.0.1; # Restrict access to localhost
    deny all;
}

Then, reload Nginx (`sudo systemctl reload nginx`) and access `http://your-domain.com/nginx_status`. You’ll see output like:

Active connections: 1234
server accepts handled requests
 1234567 1234567 123456789
Reading: 10 Writing: 5 Waiting: 1000

This data can be scraped by monitoring tools like Prometheus Node Exporter (with the `nginx-exporter` or by parsing the output directly) or Datadog.

3. **Resource Utilization:** Monitor CPU, memory, and network I/O on your Nginx Droplets using standard system monitoring tools (`top`, `htop`, `vmstat`, `iostat`) or cloud provider metrics.

WordPress Specific Health Checks

Beyond infrastructure and web server metrics, it’s crucial to monitor the health of the WordPress application itself. This often involves custom checks.

1. WordPress Heartbeat API Check: The WordPress Heartbeat API can consume significant resources if not managed properly, especially on busy sites. You can monitor its activity or disable it for non-logged-in users.

2. Plugin/Theme Health: A poorly coded plugin or theme can cripple your site. APM tools like New Relic will highlight slow plugin execution. For more direct checks, consider a custom health check endpoint.

3. Cron Job Health: WordPress relies on `wp-cron.php` for scheduled tasks. If your site has low traffic, `wp-cron.php` might not run reliably. Consider disabling the default `wp-cron.php` and setting up a real system cron job to trigger it.

# In wp-config.php:
define('DISABLE_WP_CRON', true);

# Then, set up a system cron job (e.g., every minute):
* * * * * wget -q -O - https://your-domain.com/wp-cron.php?doing_wp_cron >/dev/null 2>&1
# Or using WP-CLI if available:
# * * * * * cd /path/to/your/wordpress && wp cron event run --due-now --quiet

Monitor the execution of this system cron job to ensure it’s running and not encountering errors.

4. Custom Health Check Endpoint: Create a simple PHP file (e.g., `healthcheck.php`) in your WordPress root directory that performs basic checks:

<?php
// healthcheck.php

// Basic check for WordPress core files
if ( ! file_exists( ABSPATH . 'wp-load.php' ) ) {
    header('HTTP/1.1 503 Service Unavailable');
    echo 'WordPress core files missing.';
    exit;
}

// Check database connection
if ( ! defined( 'DB_HOST' ) || ! mysql_connect( DB_HOST, DB_USER, DB_PASSWORD ) ) {
    header('HTTP/1.1 503 Service Unavailable');
    echo 'Database connection failed.';
    exit;
}
mysql_select_db( DB_NAME );

// Check if essential plugins are active (optional)
// Example: require_once( ABSPATH . 'wp-admin/includes/plugin.php' );
// if ( ! is_plugin_active( 'woocommerce/woocommerce.php' ) ) {
//     header('HTTP/1.1 503 Service Unavailable');
//     echo 'WooCommerce plugin is inactive.';
//     exit;
// }

// If all checks pass
header('HTTP/1.1 200 OK');
echo 'WordPress is healthy.';
exit;
?>

You can then use a simple HTTP monitoring tool (like UptimeRobot, Pingdom, or a custom script) to ping this endpoint periodically. Ensure it returns a 200 OK status.

Centralized Logging and Alerting

A fragmented monitoring approach is inefficient. Centralizing logs and alerts is key to effective incident response. Consider using a service like:

• Log Management: Elasticsearch, Logstash, and Kibana (ELK stack), or cloud-native solutions like DigitalOcean’s Managed Databases logging or third-party services like Papertrail, Splunk, or Datadog Logs.
• Alerting: PagerDuty, Opsgenie, or Prometheus Alertmanager integrated with your monitoring tools.

Configure your monitoring scripts and agents to forward relevant logs and alerts to your chosen centralized platform. This allows for correlation of events across different layers (MySQL, Nginx, WordPress) and provides a single pane of glass for operational visibility.