Practical Guide to MySQL and PostgreSQL Backup Recovery Performance Optimization

Scenario

Enterprise core databases (MySQL 8.0 / PostgreSQL 14) experience prolonged backup times (>6 hours) and recovery failures (RTO not met) during peak hours. The operations team needs to improve backup/restore efficiency without service interruption.

Symptoms

• Backup jobs time out repeatedly, monitoring shows CPU/IO spikes.\n- Recovery tests fail due to data inconsistency, preventing service startup.\n- MySQL: SHOW PROCESSLIST shows many ALTER TABLE statements blocking backup threads.\n- PostgreSQL: pg_stat_activity has long-running VACUUM or ANALYZE.

Diagnosis

1. Backup Strategy: Determine if using logical (mysqldump/pg_dump) or physical (xtrabackup/pg_basebackup) backup. Logical backups perform poorly with large data volumes.\n2. Storage and I/O: Use iostat -x 1 to check disk I/O wait time (%util>90% indicates bottleneck). Monitor network throughput if backing up to remote storage.\n3. Database Activity: Check for long transactions or table locks. MySQL: SELECT * FROM information_schema.innodb_trx; PostgreSQL: SELECT * FROM pg_stat_activity WHERE state <> 'idle'.\n4. Configuration: Review MySQL max_allowed_packet, net_buffer_length; PostgreSQL max_wal_size, checkpoint_completion_target.

Commands and Optimizations

MySQL Optimization

• Use Physical Backup: xtrabackup --backup --parallel=4 --compress --compress-threads=4 (parallel compression).\n- Tune mysqldump: mysqldump --single-transaction --quick --max_allowed_packet=512M --net_buffer_length=16384.\n- Adjust InnoDB Buffer Pool: SET GLOBAL innodb_buffer_pool_size=16G; (adjust based on memory).\n- Disable Binary Log Temporarily: Stop application writes before backup or use --master-data=2.

PostgreSQL Optimization

• Use pg_basebackup: pg_basebackup -h localhost -U replica -D /backup -X stream -P --compress=9 --format=tar (compression and streaming).\n- Tune max_wal_size: ALTER SYSTEM SET max_wal_size = '16GB'; then SELECT pg_reload_conf();.\n- Parallel Restore: pg_restore -j 4 -d dbname backup.dump.\n- Enable Async I/O: Set effective_io_concurrency reasonably (e.g., 200).

Risk Controls

• Check disk space before backup: df -h.\n- Use low-privilege backup user (grant only necessary privileges like SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT).\n- Always test in staging before production.\n- Apply resource limits (e.g., nice -n 19 to lower priority).

Rollback Plan

If optimization fails or causes issues:\n1. Restore original configuration (from backup config files or ALTER SYSTEM RESET).\n2. Revert to previous backup scripts.\n3. For physical backup tools, uninstall and remove upgraded version.

Verification

• Backup Integrity: MySQL mysqlcheck --check-upgrade; PostgreSQL pg_checksums -c -f.\n- Recovery Time: Record actual duration, compare to RTO.\n- Data Consistency: Query sample tables and compare with source.\n- Performance Impact: Monitor QPS and latency during backup.

When to Submit an OpsGlobal Ticket

• Backup/restore times consistently exceed RTO/RPO (e.g., >4 hours).\n- Backup failure rate >1%.\n- Underlying storage or network bottlenecks require infrastructure changes.\n- Data corruption or consistency check failures need manual recovery intervention.