MySQL and PostgreSQL Backup and Recovery Performance Optimization

Scenario

An e-commerce platform runs MySQL (500GB) and PostgreSQL (300GB). Full backups take 6 hours, restores 8 hours, exceeding SLAs (RTO<4h, RPO<1h).

Symptoms

• CPU 100%, I/O wait >50% during backups.
• MySQL writes blocked due to table locks.
• PostgreSQL pg_dump slow, WAL archiving lag.
• Recovery spends 70% time on redo log replay.

Diagnosis

MySQL

• SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted'; check write impact.
• iostat -x 1 monitor disk latency.
• Review backup command: prefer --single-transaction --compress --quick.

PostgreSQL

• pg_stat_activity identify waiting sessions.
• pg_statio_user_tables check cache efficiency.
• pg_waldump analyze WAL growth.

Optimization Commands

MySQL

• Physical backup: xtrabackup --backup --parallel=4 --compress --compress-threads=4 --target-dir=/backup.
• Increase innodb_buffer_pool_size to 70% of RAM.

PostgreSQL

• Streaming backup: pg_basebackup -D /backup -X stream -P -v -z -Z 9 --wal-method=stream.
• Tune max_wal_senders and wal_keep_segments.

Risk Controls

• Use --single-transaction for InnoDB consistency without locks.
• For MySQL, disable --lock-tables if already using transactions.
• Test restores monthly in a sandbox environment.

Rollback

• Incremental backups: xtrabackup --incremental-basedir; PostgreSQL pg_basebackup with replication slots.
• Prepare PITR scripts: mysqlbinlog for MySQL; pg_restore --time for PostgreSQL.

Verification

• After restore, run pt-table-checksum (MySQL) or pg_verify_checksums (PostgreSQL).
• Measure actual RTO with simulated failover.

When to Submit an OpsGlobal Ticket

• Backups consistently exceed SLA despite tuning.
• Recovery drills fail; need expert assistance.
• Complex incremental backup chain issues.