Boosting MySQL and PostgreSQL Backup Recovery Performance: A Practical SRE Guide

Scenario

An OpsGlobal client runs MySQL and PostgreSQL on Kubernetes, with backups streamed to object storage. Recently, backup jobs have been taking progressively longer, and recovery processes require hours, putting the recovery time objective (RTO) at risk. The client needs to accelerate backup and recovery without jeopardizing production performance.

Symptoms

• Backup job duration increased from 30 minutes to over 2 hours.
• CPU and I/O utilization spike during backups, affecting live traffic.
• Recovery tests show that restoring from backups takes more than 4 hours.
• Users report slower query responses, especially while backups are running.

Diagnosis

We started by collecting performance baselines. Tools like top, iostat, and vmstat were used to monitor system resources, while database slow query logs and status variables were reviewed.

For MySQL, we checked SHOW ENGINE INNODB STATUS and waited events in performance_schema. For PostgreSQL, we relied on pg_stat_statements and pg_stat_activity.

Key metrics included: - Backup tool throughput (MB/s) - Compression ratio - Network bandwidth utilization - Write latency to the target storage

The analysis revealed that backup tools were running in serial mode, not leveraging multi-core CPUs. Additionally, backups had no throttling, causing I/O contention with production workloads.

Commands and Optimization

MySQL (using XtraBackup)

Enabling parallel backups with xtrabackup drastically improves speed:

xtrabackup --backup --parallel=4 --target-dir=/backup/mysql \
  --throttle=100 --compress --compress-threads=4

• --parallel controls the number of threads copying InnoDB files.
• --throttle limits I/O operations per second to protect production.
• --compress-threads enables parallel compression.

PostgreSQL (using pg_basebackup)

pg_basebackup supports rate limiting and parallel compression:

pg_basebackup -h localhost -U replicator -D /backup/pg \
  --max-rate=100M --compress=zstd:3 --pgdata /backup/pg \
  --wal-method=stream

• --max-rate caps the transfer rate.
• --compress uses zstd compression; zstd:3 is the level.

Additionally, tune database parameters for recovery speed: - MySQL: increase innodb_buffer_pool_size and innodb_log_file_size; temporarily raise innodb_flush_log_at_trx_commit during recovery. - PostgreSQL: adjust max_wal_senders and wal_keep_size; consider recovery_parallelism (PostgreSQL 16+).

Risk Controls

• Validate tuning in a pre-production environment before applying to production.
• Always use throttling parameters to avoid overwhelming production storage.
• Ensure backup encryption and secure transfer, but note that encryption consumes CPU.
• Use incremental or physical backups to reduce data volume.
• Always run a recovery drill after backups to confirm availability.

Rollback

If tuning causes issues (e.g., backup failures or performance degradation), follow these steps: 1. Restore original backup scripts or parameters. 2. If using pg_basebackup compression options, verify target version support. 3. Restart database services if necessary to clear configuration changes. 4. Re-run backup tests to verify performance returns to baseline.

Verification

1. Perform a recovery test and measure the actual time to restore a new instance.
2. Use mysqlbinlog or PostgreSQL's pg_verifybackup to validate backup integrity.
3. Monitor resource consumption during recovery to ensure no starvation.
4. Compare RTO before and after optimization to confirm target achievement.

When to Submit an OpsGlobal Ticket

If your team lacks in-depth database tuning expertise, or if backup/recovery issues involve complex high-availability architectures, submit a ticket to OpsGlobal. We provide 24/7 SRE support to help design backup strategies, optimize performance, and execute recovery drills to ensure your data is safe.