MySQL and PostgreSQL Backup & Recovery: Performance Tuning for Production

Scenario

Production database backups are too slow or restores take too long, affecting SLA. Example: mysqldump backup of a 1TB MySQL database takes over 8 hours, or pg_dump restore for PostgreSQL hangs mid-way.

Symptoms

• Low CPU but high I/O wait during backup (iostat shows %util near 100%)
• Excessive WAL (PostgreSQL) or redo log (MySQL) growth during restore
• Backup process consumes too much memory leading to OOM
• Data inconsistency or index corruption after restore

Diagnosis

MySQL

SHOW ENGINE INNODB STATUS\G
-- Check History list length and Log sequence number
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
-- High buffer pool reads indicates cache misses

Use pt-query-digest to analyze slow query logs for hot queries during backup.

PostgreSQL

SELECT * FROM pg_stat_progress_vacuum;
-- Check vacuum progress, which may block backup
SELECT * FROM pg_stat_replication WHERE state = 'streaming';
-- Check replication lag

Use pg_stat_statements to find high-frequency queries.

Commands

MySQL Optimized Backup

• Use mysqldump --single-transaction --innodb-optimize-keys --compress to reduce locks and network overhead.
• Parallel dumps: mydumper or mysqlpump (8.0+) with --parallel-workers=4.
• Physical backup: xtrabackup --parallel=4 --compress --compress-threads=4.

Accelerated restore:

mysql -e 'SET GLOBAL innodb_flush_log_at_trx_commit=2;'
# Only during restore; see risk controls below

PostgreSQL Optimized Backup

• pg_dump -j 4 --format=directory --compress=9 for parallel compressed dumps.
• Use pgbackrest or barman for physical backups:

pgbackrest --stanza=my_stanza --type=full --process-max=4 backup

Restore skipping WAL:

pg_restore -j 4 --disable-triggers --exit-on-error --dbname=mydb mydump.dmp

Risk Controls

• Limit I/O during backup: Use ionice -c 2 -n 7 to lower priority.
• MySQL restore: Setting innodb_flush_log_at_trx_commit=2 may lose up to 1 second of data on crash; only for non-critical restores.
• PostgreSQL restore: Use --single-transaction or --no-owner to avoid permission issues.
• Always take a snapshot or keep original backup file before restore.

Rollback

• Backup process stuck: Use kill -9 (MySQL) or pg_terminate_backend (PostgreSQL), then clean up leftover objects.
• Restore failure: Rollback to snapshot or re-run with previous successful backup.

Verification

• Backup integrity: mysqlcheck -A (MySQL) or pg_amcheck (PostgreSQL).
• Restore test: Perform restore in isolated environment, run SELECT count(*) and business queries.
• Performance benchmark: Compare QPS before and after backup on production.

When to Submit an OpsGlobal Ticket

• Performance issues persist after tuning.
• Backup consumes >50% system resources impacting business.
• Data corruption or partial loss after restore.
• Need assistance designing automated backup strategy or disaster recovery plan.