Deep Dive into MySQL and PostgreSQL Backup Recovery Performance: Optimization Strategies for SREs

Scenario

An e-commerce platform uses MySQL 8.0 and PostgreSQL 13 with logical backups (mysqldump/pg_dump). As databases grow to 500GB, restore time increased from 20 minutes to 2 hours, exceeding the RTO of 1 hour.

Symptoms

• CPU usage reaches 100% during restore, disk I/O wait time >50%
• Network bandwidth saturated during backup file transfer
• Data consistency errors (e.g., foreign key violations) after restore

Diagnosis

1. Backup method: Logical backup scans row-by-row; physical backups (Xtrabackup/pg_basebackup) are block-level and faster. Check current method with mysqldump --version or pg_dump --version.
2. I/O bottleneck: Use iostat -x 1 to examine avgqu-sz (>10) and await (>30ms), indicating slow storage.
3. Compression vs CPU: Backup uses gzip/pigz; decompression becomes CPU-bound during restore. Time each phase with time command.
4. Database parameters: Check if MySQL innodb_buffer_pool_size or PostgreSQL shared_buffers are too large, causing memory contention.

Commands

• MySQL:
• Cancel restore: KILL QUERY + ROLLBACK (statement level)
• Disable binary log: SET SQL_LOG_BIN=0; (reduces log writes)
• Parallel restore with mysqlpump: mysqlpump --parallel-schemas=4:dbname --default-parallelism=4
• PostgreSQL:
• Adjust checkpoint_completion_target=0.9 to reduce IO spikes
• Parallel restore: pg_restore -j 4 -d dbname backup.dump
• Disable synchronous commit: SET synchronous_commit=off;
• System: nice -n19 tar czf - /backup | pv -b -t -e > /dev/null to lower I/O priority

Risk Controls

• Perform restore during maintenance windows with a recent snapshot available
• Use ionice -c2 -n7 to set backup process I/O priority to idle
• Verify backup integrity: MySQL CHECKSUM TABLE; PostgreSQL pg_checksums
• Monitor system load with sar -u -b 1

Rollback

If restore fails or exceeds time: 1. Stop restore process (pkill pg_restore) 2. Switch traffic to a read replica (e.g., a prepared standby) 3. Rebuild primary from snapshot: tar overwrite physical files (requires database shutdown) 4. Verify data: SELECT COUNT(*) FROM key_table;

Verification

• Row counts: SELECT COUNT(*) FROM key_table; on source and restored
• Checksum: MySQL pt-table-checksum; PostgreSQL pg_comparator
• Application tests: Run typical queries and check latency
• Replication: SHOW SLAVE STATUS or pg_replication_slots

When to Submit an OpsGlobal Ticket

• Restore time consistently exceeds business RTO despite optimizations
• Lack of internal database or storage performance tuning expertise
• Need cross-region recovery (e.g., from a remote DR site)
• Compliance requirements (e.g., GDPR) demand third-party audit
• When backup strategy (e.g., full backup frequency) requires expert redesign

When submitting, include: database version, table space size, backup command, restore duration, resource monitoring data (CPU/IO/network), and steps already attempted.