MySQL and PostgreSQL Backup and Recovery Performance: A Practical Guide

Scenario

An e-commerce platform running MySQL 5.7 saw daily full backups taking over 8 hours, with weekly restore drills exceeding 12 hours, severely impacting backup windows and RTO. Similarly, a PostgreSQL 12 database using streaming replication experienced high CPU spikes during pg_dump logical backups, causing primary query latency.

Common Symptoms

• Slow Backup: mysqldump or pg_dump runs far longer than expected, disk I/O near 100%, high CPU load.
• Slow Restore: Import from backup is much slower than anticipated, especially with large tables or many indexes.
• Log Explosion: Binary logs (MySQL) or WAL logs (PostgreSQL) grow abnormally, leading to disk space issues.

Diagnosis

MySQL

1. Check backup command options: mysqldump --single-transaction --quick --compress --skip-lock-tables can speed up logical backup.
2. Use SHOW ENGINE INNODB STATUS to look for long transactions blocking backup.
3. Monitor disk performance: iostat -x 1 for %util and await values to identify bottlenecks.
4. Analyze backup queries using general log or pt-query-digest.

PostgreSQL

1. Verify pg_dump parallelism: pg_dump -j 4 for parallel dump (PG 9.4+).
2. Check WAL archiving: compare pg_current_wal_lsn() with archive location.
3. Use pg_stat_activity to see if backup process is blocked by other queries.
4. Tune parameters: wal_buffers, max_wal_size, checkpoint_completion_target affect write performance.

Example Commands

MySQL Optimized Backup

# Physical backup using innobackupex (faster)
innobackupex --user=backup --password --parallel=4 --no-timestamp /backup/

# Throttle backup rate to avoid impact
pvb -i 300m -r 100m | mysqldump ... > dump.sql

PostgreSQL Optimized Backup

# Parallel pg_dump (only PG 9.4+)
pg_dump -j 4 -Fd -f /backup/dump_dir dbname

# Physical backup with pg_basebackup
pg_basebackup -D /backup -X stream -P -v -z -Z 6

Risk Controls

• Avoid DDL during backup (MySQL: lock_wait_timeout, PG: lock_timeout).
• Limit IOPS: ionice -c2 -n7 to lower backup priority.
• Monitor progress: MySQL PROCESSLIST’s Time column; PG pg_stat_progress_* views.
• Use transaction snapshot isolation: MySQL --single-transaction, PG default repeatable read.

Rollback Plan

Always retain the last full backup and incremental backups. If a restore fails: 1. Stop the restore process to avoid overwriting data. 2. Reload from cold backup or replica. 3. Verify backup file integrity: mysqlcheck --all-databases or pg_checksums.

Verification

• After restore, run SELECT COUNT(*) to compare row counts with original table.
• Use pt-table-checksum (MySQL) or pg_verifybackup (PG) to check consistency.
• Check recent transaction timestamps for sanity.

When to Submit an OpsGlobal Ticket

• Backup/restore performance issues persist beyond 2 hours.
• Data corruption or loss is suspected.
• Expert tuning of database parameters or backup strategy is needed.
• Backup tool version incompatibility causes errors.