Boosting MySQL and PostgreSQL Backup & Recovery Performance: A Practical Guide

Scenario

An e-commerce platform uses MySQL for core transactions and PostgreSQL for analytics. Data growth increased backup time from 30 minutes to 4 hours, with recovery exceeding 8 hours, impacting SLAs. The ops team needs to optimize without affecting production.

Symptoms

• MySQL: mysqldump slowdown, binlog accumulation, replica lag.
• PostgreSQL: pg_dump causes CPU/IO spikes, pg_basebackup saturates network.
• General: I/O wait near 100%, application response degradation during backups.

Diagnosis

1. Backup method: Is MySQL using physical backup (XtraBackup) vs logical? PostgreSQL parallel backup enabled?
2. Resource usage: Check iostat, vmstat, top for bottlenecks.
3. Compression & transfer: Compression enabled? Network bandwidth sufficient?
4. DB configs: MySQL innodb_flush_log_at_trx_commit, sync_binlog; PostgreSQL wal_level, max_wal_size.

Commands

MySQL Optimization

# Percona XtraBackup for physical hot backup with parallel compression
xtrabackup --backup --parallel=4 --compress --compress-threads=4 --target-dir=/backup/mysql

# Restore with parallel decompression
xtrabackup --prepare --parallel=4 --target-dir=/backup/mysql

PostgreSQL Optimization

# pg_dump with parallel compression
pg_dump -Fd -j 4 -Z 9 -f /backup/pg_dump mydb

# Efficient physical backup with pgBackRest
pgbackrest --stanza=myapp --type=full backup

Risk Controls

• During backup: MySQL use --lock-ddl-per-table to reduce locks; PostgreSQL use --snapshot for consistent views.
• Test restores: Regularly perform recovery drills in isolated environments.
• Throttle: Use nice or ionice to lower priority, or cgroups to limit resources.

Rollback

• If backup causes production issues, terminate the process immediately.
• MySQL: Delete incomplete backup files, check replica status and re-enable sync.
• PostgreSQL: If pg_start_backup() is stuck, execute pg_stop_backup() or restart the database.

Verification

1. Data integrity: pt-table-checksum (MySQL) or pg_checksums (PG).
2. Recovery time: Measure full restore duration against RTO.
3. Performance baseline: Compare TPS/QPS after recovery with baseline.

When to Submit an OpsGlobal Ticket

• Internal optimizations still fail to meet SLA.
• Data corruption or inconsistency after recovery.
• Need to migrate to professional backup tools (pgBackRest, MySQL Enterprise Backup).
• Design cross-region or disaster recovery solutions.

Following these steps, the platform reduced backup to 45 minutes and recovery to under 2 hours. Regular performance reviews and automated testing are key to maintaining high availability.