Boosting Backup and Recovery Performance in MySQL and PostgreSQL

Scenario: As a database administrator for a growing e-commerce platform, you need to perform daily backups of a 500GB MySQL database and a 200GB PostgreSQL database. The backup window is limited to 4 hours, but current backups take over 6 hours, and restore times exceed 12 hours, risking SLA violations.

Symptoms: Backup duration >6 hours; restore >12 hours; high I/O wait (wa>30); replication lag spikes during backup; disk utilization at 100% during backup; CPU idle low due to compression.

Diagnosis: Check disk throughput with iostat (e.g., $ iostat -x 1): look for high avgqu-sz and await. For MySQL, run SHOW ENGINE INNODB STATUS and check LOG sequence number age; for PostgreSQL, use pg_stat_progress_vacuum and pg_stat_activity. Identify if backup is I/O-bound (sequential reads), CPU-bound (compression), or network-bound (remote backup). Example: mysqldump on single thread is slow; pg_dump default is single-threaded.

Commands: For MySQL, use Percona XtraBackup with parallel threads: xtrabackup --backup --parallel=4 --compress --compress-threads=4 --target-dir=/backup. For faster restores, use --decompress and --parallel. For logical backup, use mysqldump with --single-transaction (no lock) and split tables: for i in $(mysql -e 'show tables' -N db); do mysqldump db $i & done; wait. For PostgreSQL, use pg_dump with directory format and jobs: pg_dump -Fd -j 4 -f /backup/db. For physical backup, use pg_basebackup with multiple WAL workers: pg_basebackup -D /backup -X stream --progress. Or use pgBackRest with parallel archiving and restore: pgbackrest --stanza=db --type=full backup; pgbackrest --stanza=db --type=full restore. Tune compression: replace gzip with pigz (parallel gzip) or use --compress-level=1 for faster but less compression.

Risk Controls: Always test backup and restore on a staging environment first. Avoid running backups during peak hours. Use separate storage for backups (e.g., NFS, S3) to reduce load on primary disks. Ensure transaction logs are retained for PITR. Monitor backup progress with tools like mylvmbackup or custom scripts. For MySQL, use --lock-ddl-per-table to allow DDL during backup. For PostgreSQL, ensure recoverability by archiving WAL continuously.

Rollback: If a backup fails, ensure the previous successful backup is available and verified. Have automated alerts for failure. For recovery, if a restore is corrupted, have an older backup ready. Use incremental backups to reduce full backup frequency. In case of a failed restore, stop and check logs, then restore from another valid backup.

Verification: After backup, validate integrity: for MySQL, use xtrabackup --verify-backup; for PostgreSQL, use pg_verifybackup (for backup manifests). Perform a periodic restore test to a separate instance and measure time. Check that restored data is consistent (e.g., count rows, checksum tables). For logical backups, restore to a test database and run a sample query.

When to Submit an OpsGlobal Ticket: If backup or restore times consistently exceed your SLA by 50% after tuning, if you encounter corruption errors, if you need to set up a disaster recovery solution across regions, or if you require custom backup scripts with monitoring dashboards. OpsGlobal SREs can audit your configuration, implement parallel backup strategies, and automate backup validation.