Optimizing Backup Recovery Performance for MySQL and PostgreSQL

Scenario

Your production MySQL or PostgreSQL database has crashed. You initiate a restore from a backup, but it takes hours longer than expected, causing extended downtime and revenue loss. This scenario is common when backup strategies are not optimized for performance.

Symptoms

• Restore throughput is below expected disk throughput.
• High I/O wait during restore.
• Database logs show slow checkpointing or WAL replay.
• CPU utilization is low but I/O is saturated.

Diagnosis

For MySQL

• Use SHOW PROCESSLIST to identify running queries.
• Check InnoDB status: SHOW ENGINE INNODB STATUS\G for history list length, log sequence number.
• Monitor I/O with iostat -x 1.
• Analyze slow queries during restore using slow query log.

For PostgreSQL

• Query pg_stat_activity for active restore processes.
• Use pg_stat_bgwriter and pg_stat_wal to track checkpoint and WAL activity.
• Check pg_current_wal_lsn and replication lag if applicable.
• Use pg_stat_statements if enabled to identify costly queries.

Commands

MySQL

• Backup: xtrabackup --backup --target-dir=/backup
• Restore: xtrabackup --prepare --target-dir=/backup then xtrabackup --copy-back --target-dir=/backup
• Tune InnoDB: SET GLOBAL innodb_buffer_pool_size = 80% of RAM; SET GLOBAL innodb_log_file_size = 4G;
• Parallel restore: Use mysqlpump with --parallel-schemas or mydumper with threads.

PostgreSQL

• Backup: pg_dump -Fc -d dbname > db.dump
• Restore: pg_restore -d dbname -j 4 db.dump (parallel jobs)
• Tune: ALTER SYSTEM SET maintenance_work_mem = '2GB'; ALTER SYSTEM SET max_parallel_maintenance_workers = 4;
• Checkpoint: CHECKPOINT; and pg_switch_wal();

Risk Controls

• Always test restores on a staging environment before production.
• Use backups that are compressed and encrypted to reduce I/O but ensure CPU overhead is manageable.
• Monitor disk performance and consider using SSDs.
• For large databases, use incremental backups to reduce restore time.
• Implement point-in-time recovery (PITR) with WAL archiving for MySQL (binary logs) and PostgreSQL (WAL).
• Set appropriate innodb_buffer_pool_size or shared_buffers to avoid disk thrashing.

Rollback

If the restore fails or takes too long: 1. Stop the restore process. 2. Validate the backup file integrity. 3. Restore from a different backup point if available. 4. If corruption is detected, restore from a full backup and apply logs. 5. Notify team and consider escalating to OpsGlobal.

Verification

• Run CHECKSUM TABLE in MySQL or use pg_checksums in PostgreSQL.
• Compare row counts between tables and expected values.
• Validate application functionality by running a few queries.
• Check replication if using replicas.

When to Submit an OpsGlobal Ticket

• Your backup/restore consistently exceeds the RTO by more than 50%.
• You encounter unexplained performance degradation during restores.
• You need assistance tuning database parameters for recovery.
• You require a comprehensive backup strategy review.