Scenario

Your organization operates a fleet of MySQL and PostgreSQL databases in Kubernetes. The nightly backup jobs are exceeding their maintenance windows, and disaster recovery drills consistently fail to meet RTO. Restores that should take minutes are stretching into hours. This post walks through a real-world engagement where we optimized backup and recovery performance for both engines.

Symptoms

• mysqldump or pg_dump jobs run for > 4 hours.
• Physical backups with XtraBackup or pg_basebackup generate high I/O, causing latency spikes in production.
• Recovery (restore) times are exponentially longer than backup times.
• Transaction logs grow unexpectedly during backups.
• Monitoring shows CPU near 100% on backup hosts, but database nodes are idle.

Diagnosis

Check whether you are using logical or physical backup: - Logical (mysqldump, pg_dump) is CPU-bound and inefficient for large datasets. - Physical (XtraBackup, pg_basebackup) copies raw files and is I/O-bound, but requires careful tuning.

For MySQL: 1. Review innodb_buffer_pool_size: if too small, XtraBackup reads more from disk. 2. Check backup tool parallelism: XtraBackup --parallel=N speeds up data files transfer. 3. Consider using --compress to reduce network transfer, but it adds CPU overhead. 4. Analyze I/O capacity: sequential reads and writes should be aligned with storage type.

For PostgreSQL: 1. pg_basebackup sends entire data directory over the replication protocol; use --jobs=N for parallel transfer. 2. wal_level=replica and max_wal_senders must be high enough to support backup and replication. 3. Check archive_timeout and archive_command: if too low, WAL files accumulate. 4. Use tools like pgBackRest which supports encryption, compression, and parallel uploads.

Commands

MySQL with Percona XtraBackup

# Physical backup with parallelism and compression
xtrabackup --backup --parallel=8 --compress --compress-threads=4 --target-dir=/backup/mysql

# Prepare the backup (also parallelizable)
xtrabackup --prepare --parallel=8 --target-dir=/backup/mysql

# Incremental backup (assuming full backup exists)
xtrabackup --backup --parallel=8 --incremental-basedir=/backup/mysql/base --target-dir=/backup/mysql/inc

# Restore from full + incremental
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/base
xtrabackup --prepare --apply-log-only --incremental-dir=/backup/mysql/inc --target-dir=/backup/mysql/base
xtrabackup --prepare --target-dir=/backup/mysql/base

PostgreSQL with pg_basebackup

# Parallel physical backup
pg_basebackup -h primary-host -D /backup/pgsql -U replicator -Ft -z -j 8 -X stream

# Restore from base backup
tar -xzf /backup/pgsql/base.tar.gz -C /var/lib/postgresql/16/main
tar -xzf /backup/pgsql/pg_wal.tar.gz -C /var/lib/postgresql/16/main/pg_wal
touch /var/lib/postgresql/16/main/recovery.signal

Risk Controls

• Always throttle backups: use --throttle for XtraBackup or ionice on backup processes.
• Run backups from a replica to avoid production load.
• For physical backups, ensure enough disk space; compression reduces space but increases CPU.
• Regularly verify backup integrity with --verify-backup for XtraBackup and pg_verifybackup for base backups.
• Keep multiple copies of WAL/archive files; use separate storage for backups to avoid single point of failure.

Rollback

If performance tuning causes instability: - Revert innodb_buffer_pool_size and backup parallelism to previous values. - For PostgreSQL, reduce max_wal_senders or archive_command to original settings. - If a restore fails, document the error and fall back to the last known good backup. - Always keep the previous backup set until the new one is fully validated.

Verification

• Measure backup and restore times before/after changes using time.
• Validate backups by restoring to a staging instance and running CHECKSUM TABLE for MySQL or pg_checksums --enable for PostgreSQL.
• Compare record counts and incremental log sequence numbers.
• Monitor I/O latency and CPU usage during backup windows.

When to Submit an OpsGlobal Ticket

If your team lacks experience with xtrabackup or pgBackRest tuning, or if bottlenecks persist despite following this guide, contact OpsGlobal. Our SREs can perform a backup assessment, implement disaster recovery automation, and run chaos tests to ensure RTO/RPO compliance.