Prestasi Sandaran dan Pemulihan MySQL dan PostgreSQL: Panduan Praktikal

Senario

Platform e-dagang dengan MySQL 5.7 menyaksikan sandaran penuh harian mengambil masa lebih 8 jam, dan latihan pemulihan mingguan melebihi 12 jam, menjejaskan tetingkap sandaran dan RTO. PostgreSQL 12 dengan replikasi strim mengalami lonjakan CPU tinggi semasa sandaran logik pg_dump, menyebabkan kependaman pertanyaan utama.

Gejala Biasa

• Sandaran Lambat: mysqldump atau pg_dump berjalan lebih lama daripada jangkaan, I/O cakera hampir 100%, beban CPU tinggi.
• Pemulihan Lambat: Import dari sandaran jauh lebih perlahan daripada jangkaan, terutamanya dengan jadual besar atau indeks banyak.
• Letupan Log: Log perduaan (MySQL) atau log WAL (PostgreSQL) berkembang secara luar biasa, menyebabkan ruang cakera tidak mencukupi.

Diagnosis

MySQL

1. Periksa pilihan arahan sandaran: mysqldump --single-transaction --quick --compress --skip-lock-tables boleh mempercepatkan sandaran logik.
2. Gunakan SHOW ENGINE INNODB STATUS untuk melihat transaksi panjang yang menyekat sandaran.
3. Pantau prestasi cakera: iostat -x 1 untuk %util dan await bagi mengenal pasti kesesakan.
4. Analisis pertanyaan sandaran menggunakan log umum atau pt-query-digest.

PostgreSQL

1. Sahkan keselarian pg_dump: pg_dump -j 4 untuk sandaran selari (PG 9.4+).
2. Periksa pengarkiban WAL: bandingkan pg_current_wal_lsn() dengan lokasi arkib.
3. Gunakan pg_stat_activity untuk melihat sama ada proses sandaran disekat oleh pertanyaan lain.
4. Tala parameter: wal_buffers, max_wal_size, checkpoint_completion_target mempengaruhi prestasi tulis.

Arahan Contoh

Sandaran MySQL Dioptimumkan

# Sandaran fizikal menggunakan innobackupex (lebih pantas)
innobackupex --user=backup --password --parallel=4 --no-timestamp /backup/

# Hadkan kadar sandaran untuk mengelakkan kesan
pvb -i 300m -r 100m | mysqldump ... > dump.sql

Sandaran PostgreSQL Dioptimumkan

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

# Sandaran fizikal dengan pg_basebackup
pg_basebackup -D /backup -X stream -P -v -z -Z 6

Kawalan Risiko

• Elakkan DDL semasa sandaran (MySQL: lock_wait_timeout, PG: lock_timeout).
• Hadkan IOPS: ionice -c2 -n7 untuk menurunkan keutamaan sandaran.
• Pantau kemajuan: MySQL PROCESSLIST lajur Time; PG pandangan pg_stat_progress_*.
• Gunakan pengasingan snapshot transaksi: MySQL --single-transaction, PG lalai bacaan berulang.

Rancangan Gulung Balik

Sentiasa simpan sandaran penuh terakhir dan sandaran tambahan. Jika pemulihan gagal: 1. Hentikan proses pemulihan untuk mengelakkan tindanan data. 2. Muat semula dari sandaran sejuk atau replika. 3. Sahkan integriti fail sandaran: mysqlcheck --all-databases atau pg_checksums.

Pengesahan

• Selepas pemulihan, jalankan SELECT COUNT(*) untuk membandingkan kiraan baris dengan jadual asal.
• Gunakan pt-table-checksum (MySQL) atau pg_verifybackup (PG) untuk memeriksa konsistensi.
• Periksa cap masa transaksi terkini untuk kewarasan.

Bila Perlu Hantar Tiket OpsGlobal

• Isu prestasi sandaran/pemulihan berterusan lebih 2 jam.
• Kerosakan atau kehilangan data disyaki.
• Pelarasan pakar diperlukan untuk parameter pangkalan data atau strategi sandaran.
• Ketidakserasian versi alat sandaran menyebabkan ralat.