Scenario

Imagine you are the on-call SRE for an e-commerce platform running a mix of MySQL and PostgreSQL databases. At 2 AM, a misconfigured job drops a crucial table. You scramble to restore from the latest backup, only to discover that the backup was taken 12 hours ago, and the restore process is crawling. The full database is 500 GB, and your current backup tool takes 6 hours to dump and 4 hours to restore. Your RTO is 2 hours. This is a classic backup/recovery performance failure.

In this post, we will walk through how to diagnose and eliminate backup and recovery performance bottlenecks for MySQL and PostgreSQL, using concrete commands and architectural changes. We'll also define the boundaries of what you can safely do yourself, and when to hand over to OpsGlobal.

Symptoms

Common symptoms that indicate backup/recovery performance issues:

• Backup jobs consistently overrun their maintenance window.
• Restores take multiple hours, exceeding RTO.
• CPU and I/O utilization spike during backups, impacting production.
• Network bandwidth is saturated when streaming backups to remote storage.
• Restores fail due to corrupted or inconsistent backup files.
• The backup size is unexpectedly large due to unoptimized compression.

Diagnosis

Before throwing hardware at the problem, identify the exact bottleneck.

1. Tool selection: Determine if you are using logical or physical backups. Logical backups (mysqldump, pg_dump) are slower and generate inconsistent snapshots unless correctly handled. Physical backups (Percona XtraBackup, pg_basebackup) are faster and capture data files directly.
2. Configuration: Check compression, parallelism, and buffer sizes. Defaults are often conservative.
3. I/O subsystem: Measure disk throughput with iostat, iotop, and network with iftop during a test backup.
4. Database locks: Check for long-running transactions that block shared locks needed for consistent logical backups.
5. Backup integrity: Validate that backups are restorable. Many teams skip this until disaster strikes.

Commands

We'll now provide concrete commands to accelerate backup and recovery for both MySQL and PostgreSQL.

MySQL

Optimise logical backup (mysqldump)

• Use a single transaction with --single-transaction for InnoDB consistency without blocking writes.
• Increase --quick to avoid buffering large result sets.
• Compress with gzip to reduce I/O and transfer time, but note it adds CPU cost.
• Use --routines and --triggers to avoid missing objects.

Example:

mysqldump --single-transaction --quick --routines --triggers --compress --database mydb | gzip > /backup/mybackup.sql.gz

Use physical backup (Percona XtraBackup)

For large databases, physical backup is the only practical option.

Backup command with parallel compression:

xtrabackup --backup --parallel=4 --compress --compress-threads=4 --stream=xbstream --target-dir=/backup

Restore and prepare:

xtrabackup --prepare --parallel=4 --target-dir=/restore

For faster streaming, add --socket and --no-version-check.

Tuning MySQL for faster recovery

• During restore, increase innodb_buffer_pool_size if enough RAM.
• Use innodb_parallel_read (available in 8.0) to parallelize reads.
• Set innodb_flush_log_at_trx_commit=2 temporarily during load (risk of losing recent transactions on OS crash).

PostgreSQL

Logical backup (pg_dump)

• Use parallel dump: pg_dump -j 8 -Fd -Z 9 -f /backup/dump mydb
• Use directory format -Fd to enable parallel restore with pg_restore -j.
• Use -Z for compression.

Physical backup (pg_basebackup)

• For point-in-time recovery, use WAL archiving. pg_basebackup is fast and captures raw files.

Example:

pg_basebackup -D /backup/base -X stream -P -z -Z 5

Restore with parallel job support

pg_restore -j 4 -d mydb /backup/dump

PostgreSQL configuration tuning

• shared_buffers should be set to 25% of RAM for better recovery performance.
• Increase checkpoint_completion_target to 0.9 to spread writes.
• Use max_wal_size and min_wal_size appropriately to avoid excessive checkpoints.

Risk Controls

• Always test restores: Schedule a monthly restore drill to ensure backup integrity.
• Use PITR (Point-in-Time Recovery): For MySQL, enable binlog; for PostgreSQL, use WAL archiving. This allows you to roll back to a specific time after the last full backup.
• Parallelise without resource exhaustion: Limit the number of parallel threads to avoid saturating CPU/disk and impacting production.
• Monitor and alert on backup duration: Set up Prometheus/Grafana or similar tools to track backup time and size.
• Network throttling: If streaming backups, use tools like pv or OS-level traffic shaping to avoid bandwidth saturation.

Rollback

If a restore fails or produces inconsistent data, you need rollback procedures:

• Keep the last known good backup in a separate location.
• For MySQL, use binary logs to apply transactions since the backup time. If binary logs are compromised, you may have to accept data loss.
• For PostgreSQL, use the WAL archive to replay transactions. Ensure archive is complete and accessible.
• If physical backup preparation fails, restore to a backup taken before the failed attempt and then apply incremental changes.

Verification

After a restore, perform the following checks:

• For MySQL: mysqlcheck -a -c to verify table integrity.
• For PostgreSQL: pg_amcheck to check for corruption.
• Query critical tables and compare row counts with the source (if available).
• Validate application functionality with smoke tests.
• Ensure that the database is in the expected state (e.g., recent transactions present).

When to Submit an OpsGlobal Ticket

You should consider raising a ticket with OpsGlobal when:

• Your RTO cannot be met with your current backup and recovery process, even after tuning.
• You need to redesign your backup architecture for multi-database environments or Kubernetes deployments.
• You're unsure how to set up secure, reliable PITR or WAL archiving for your databases.
• You require hands-on help with performance testing, benchmarking, and automation of backup/recovery.
• You need fallback support during critical migrations or disaster recovery drills.

Our SREs are available 24/7 to help you implement robust backup strategies, tune performance, and ensure your data is safe and recoverable.