MySQL and PostgreSQL Backup & Recovery Performance: Practical Tuning Guide

Scenario

In large-scale database operations, backup and recovery often cause business disruptions due to performance issues. For example, MySQL's mysqldump may suffer from table locking or I/O bottlenecks, while PostgreSQL's pg_dump can generate excessive WAL under heavy writes, prolonging recovery time.

Symptoms

• Backup time exceeds the maintenance window (e.g., 8-hour backup takes 15 hours).
• Slow consistency checks or redo log application stalls during recovery.
• System load spikes (I/O wait >50%, CPU 100%).
• Abnormally large backup files (e.g., logical backup 3x physical data size).

Diagnosis

MySQL

• Check process list: SHOW FULL PROCESSLIST; — look for Waiting for table flush or Sending data.
• Monitor I/O: iostat -x 1 — %util near 100% indicates disk bottleneck.
• InnoDB status: SHOW ENGINE INNODB STATUS\G — History list length too long suggests transaction backlog.

PostgreSQL

• Active sessions: SELECT * FROM pg_stat_activity WHERE state = 'active'; — identify lock conflicts.
• WAL generation rate: pg_waldump or check replication lag via pg_stat_replication.
• System resources: top/htop with pidstat to pinpoint CPU/memory issues.

Commands & Optimization

MySQL Backup Acceleration

1. Use --single-transaction (InnoDB) to avoid table locks: mysqldump --single-transaction --skip-lock-tables -u root -p db > backup.sql
2. Parallel compression: mysqldump ... | pigz -1 > backup.sql.gz (utilize multi-core).
3. Multi-database backup: launch separate dumps simultaneously, but monitor I/O.
4. Backup from replica: execute on a read-only slave to reduce primary load.

PostgreSQL Backup Acceleration

1. Use directory format with parallel jobs: pg_dump -j 4 -Fd -f /backup/db dbname
2. Adjust --compress level (0-9); recommend 0-3 to balance CPU.
3. Avoid full table locks: use --no-blocks or enable snapshot isolation (default_transaction_isolation).
4. Incremental backup: combine pg_basebackup with WAL archiving.

MySQL Recovery Acceleration

• Disable binary logging (if not required): SET SQL_LOG_BIN=0; before restore.
• Increase InnoDB buffer pool: innodb_buffer_pool_size = 80% RAM.
• Set innodb_autoinc_lock_mode=2 (if no concurrent inserts needed).

PostgreSQL Recovery Acceleration

• Disable archiving during recovery: archive_mode = off.
• Tune WAL parameters: wal_buffers = 16MB, checkpoint_timeout = 1h.
• Use pg_rewind for fast replica sync (avoid full base backup restore).

Risk Controls

• Always validate optimizations in a staging environment first.
• Use --dry-run if available or manually inspect parameters before production.
• Set timeouts: e.g., --max-allowed-packet=1G to prevent connection drops.
• Monitor disk space: df -h ensure backup directory has >1.5x database size.

Rollback

If performance degrades further, stop the current operation: - MySQL: KILL QUERY <thread_id>; or terminate the process. - PostgreSQL: SELECT pg_terminate_backend(pid);. - Revert configuration changes and restart from a known good backup snapshot.

Verification

• Backup integrity: mysqlcheck -u root -p db or PostgreSQL pg_verify_checksums.
• Restore test: perform full recovery in a test environment and measure time.
• Compare metrics: backup/restore duration, I/O utilization, WAL generation before and after tuning.

When to Submit an OpsGlobal Ticket

Contact OpsGlobal experts if: - Backup cannot complete within the window despite parameter tuning. - Data corruption occurs during recovery (e.g., MySQL table corruption or PostgreSQL control file errors). - Cross-cloud or heterogeneous migration (e.g., MySQL to PostgreSQL) is required for disaster recovery. - Cluster size exceeds TB scale and standard techniques are insufficient.