Panduan Prestasi Sandaran dan Pemulihan MySQL & PostgreSQL

Senario

Dalam operasi pangkalan data berskala besar, sandaran dan pemulihan sering menyebabkan gangguan perniagaan akibat isu prestasi. Contohnya, mysqldump MySQL mungkin mengalami penguncian jadual atau kesesakan I/O, manakala pg_dump PostgreSQL boleh menjana WAL berlebihan semasa tulis berat, memanjangkan masa pemulihan.

Simptom

• Masa sandaran melebihi tetingkap penyelenggaraan (cth., sandaran 8 jam mengambil 15 jam).
• Pemeriksaan konsistensi perlahan atau penggunaan log redo terhenti semasa pemulihan.
• Beban sistem meningkat (tunggu I/O >50%, CPU 100%).
• Fail sandaran terlalu besar (contohnya, sandaran logik 3x saiz data fizikal).

Diagnosis

MySQL

• Senarai proses: SHOW FULL PROCESSLIST; — cari Waiting for table flush atau Sending data.
• Pantau I/O: iostat -x 1 — %util hampir 100% menunjukkan kesesakan cakera.
• Status InnoDB: SHOW ENGINE INNODB STATUS\G — History list length terlalu panjang menandakan transaksi tertunggak.

PostgreSQL

• Sesi aktif: SELECT * FROM pg_stat_activity WHERE state = 'active'; — kenal pasti konflik kunci.
• Kadar penjanaan WAL: pg_waldump atau periksa ketinggalan replikasi melalui pg_stat_replication.
• Sumber sistem: top/htop dengan pidstat untuk kesesakan CPU/memori.

Arahan & Pengoptimuman

Percepatan Sandaran MySQL

1. Gunakan --single-transaction (InnoDB) untuk elak kunci jadual: mysqldump --single-transaction --skip-lock-tables -u root -p db > backup.sql
2. Mampatan selari: mysqldump ... | pigz -1 > backup.sql.gz (gunakan pelbagai teras).
3. Sandaran berbilang pangkalan data: lancarkan dump berasingan serentak, tetapi pantau I/O.
4. Sandaran daripada replika: laksanakan pada slave baca sahaja untuk kurangkan beban utama.

Percepatan Sandaran PostgreSQL

1. Gunakan format direktori dengan pekerjaan selari: pg_dump -j 4 -Fd -f /backup/db dbname
2. Laraskan tahap --compress (0-9); cadangkan 0-3 untuk imbangan CPU.
3. Elakkan kunci jadual penuh: gunakan --no-blocks atau dayakan pengasingan snapshot (default_transaction_isolation).
4. Sandaran inkremental: gabungkan pg_basebackup dengan pengarkiban WAL.

Percepatan Pemulihan MySQL

• Matikan log binari (jika tidak diperlukan): SET SQL_LOG_BIN=0; sebelum restore.
• Tingkatkan kumpulan penampan InnoDB: innodb_buffer_pool_size = 80% RAM.
• Tetapkan innodb_autoinc_lock_mode=2 (jika tiada sisipan serentak).

Percepatan Pemulihan PostgreSQL

• Matikan mod arkib semasa pemulihan: archive_mode = off.
• Ubah suai parameter WAL: wal_buffers = 16MB, checkpoint_timeout = 1h.
• Gunakan pg_rewind untuk sinkronisasi replika pantas (elak restore sandaran penuh).

Kawalan Risiko

• Sentiasa sahkan pengoptimuman dalam persekitaran pementasan dahulu.
• Gunakan --dry-run jika ada atau periksa parameter secara manual sebelum pengeluaran.
• Tetapkan masa tamat: cth., --max-allowed-packet=1G untuk cegah putus sambungan.
• Pantau ruang cakera: df -h pastikan direktori sandaran >1.5x saiz pangkalan data.

Pemulihan

Jika prestasi semakin merosot, hentikan operasi semasa: - MySQL: KILL QUERY <thread_id>; atau tamatkan proses. - PostgreSQL: SELECT pg_terminate_backend(pid);. - Kembalikan perubahan konfigurasi dan mulakan semula daripada snapshot sandaran yang baik.

Pengesahan

• Integriti sandaran: mysqlcheck -u root -p db atau PostgreSQL pg_verify_checksums.
• Uji restore: lakukan pemulihan penuh dalam persekitaran ujian dan ukur masa.
• Bandingkan metrik: tempoh sandaran/pemulihan, penggunaan I/O, penjanaan WAL sebelum dan selepas talaan.

Bila Hantar Tiket OpsGlobal

Hubungi pakar OpsGlobal jika: - Sandaran tidak dapat disiapkan dalam tetingkap walaupun setelah talaan parameter. - Kerosakan data berlaku semasa pemulihan (cth., kerosakan jadual MySQL atau ralat fail kawalan PostgreSQL). - Migrasi merentas awan atau heterogen (cth., MySQL ke PostgreSQL) diperlukan untuk pemulihan bencana. - Saiz kluster melebihi skala TB dan teknik standard tidak mencukupi.