Optimizing MySQL and PostgreSQL Backup and Recovery Performance in Production

Scenario

An e-commerce platform uses a 2TB PostgreSQL database and a 500GB MySQL database. Backup jobs take hours and RTOs are frequently exceeded. During backups, database responsiveness degrades, impacting business.

Symptoms

• Backup duration increases steadily from 30 minutes to 2 hours.
• System monitoring shows disk I/O wait at 90% during backups.
• Recovery drills from full backups take longer than the specified RTO (4 hours) for 1TB data.

Diagnosis

PostgreSQL

• Check WAL lag using pg_stat_replication.
• Review checkpoint stats in pg_stat_bgwriter.
• Monitor I/O with htop or iostat.

MySQL

• Use SHOW ENGINE INNODB STATUS\G to examine log sequence number (LSN) and checkpointing.
• Use performance_schema.file_summary_by_instance to identify high-I/O files.

Commands and Tuning Parameters

PostgreSQL Backup Optimization

# Use pg_basebackup with compression and multiple workers
pg_basebackup -h localhost -U replicator -D /backup -Ft -z -P --wal-method=stream --compress=9 --workers=4 --max-rate=100M

Parameter adjustments in postgresql.conf: - wal_compression = on - checkpoint_completion_target = 0.9 - max_wal_senders = 10

MySQL Backup Optimization

# Use Percona XtraBackup with parallel threads
xtrabackup --backup --target-dir=/backup --parallel=4 --compress --compress-threads=4

Parameter adjustments in my.cnf: - Set innodb_buffer_pool_size to 70% of physical memory to avoid I/O thrashing. - Increase innodb_log_file_size to 4GB to reduce log switches. - Tune innodb_io_capacity to match disk performance.

Risk Controls

• Always back up from a replica to avoid primary load.
• Use ionice or cgroups to throttle backup I/O priority.
• Ensure system resources (CPU, memory, disk) have at least 20% headroom before backup.

Rollback and Verification

Rollback

If backup fails or becomes corrupt, immediately stop and clean up temp files. Restore from the last known good full backup:

# PostgreSQL
pg_ctl start -D /data -o '-P 1' # enter offline recovery
# MySQL
systemctl stop mysql
cp -r /backup/latest /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Verification

• Run data integrity checks: pg_checksums -c -D /data (PG); mysqlcheck -A (MySQL).
• Execute baseline queries to verify response times.

When to Submit an OpsGlobal Ticket

• Environments with hybrid cloud or complex network topologies where backup bandwidth cannot be feasibly optimized.
• Database size exceeds 5TB and current tools cannot meet RTO.
• Data inconsistency or repeated failures during backup/restore requiring expert forensic analysis.

OpsGlobal offers 24/7 remote SRE support to help you design efficient backup strategies and resolve performance bottlenecks.