Panduan SRE Praktikal untuk Meningkatkan Prestasi Pemulihan Sandaran MySQL dan PostgreSQL

Senario

Sebagai pasukan DevOps/SRE untuk platform e-dagang, anda menguruskan pangkalan data MySQL dan PostgreSQL. Pertumbuhan data baru-baru ini menyebabkan kerja sandaran melebihi tetingkap yang dijadualkan, dan latihan pemulihan menunjukkan bahawa memulihkan dari sandaran mengambil masa berjam-jam, menjejaskan kesinambungan perniagaan dan SLA.

Gejala

• Pekerjaan sandaran sentiasa mengambil masa lebih lama daripada ambang yang dipersetujui (cth., dirancang 2 jam tetapi kini mengambil 6 jam).
• Operasi pemulihan terlalu perlahan, menyebabkan sasaran RTO tidak tercapai.
• I/O menunggu tinggi semasa sandaran atau pemulihan menjejaskan respons aplikasi pengeluaran.
• Fail sandaran tidak dijangka besar, mampatan tidak baik, atau arkib menggunakan ruang cakera berlebihan.

Diagnosis

1. Kenal Pasti Kaedah Sandaran dan Pemulihan

MySQL biasanya menggunakan alat sandaran fizikal seperti Percona XtraBackup atau MySQL Enterprise Backup, dan sandaran logik dengan mysqldump. PostgreSQL sandaran fizikal sering menggunakan pg_basebackup atau alat perusahaan, dan sandaran logik menggunakan pg_dump/pg_dumpall. Kaedah ini mempunyai profil prestasi yang sangat berbeza.

2. Semak Perkakasan dan Sumber Sistem

Gunakan iostat, vmstat, top, dan sar untuk memeriksa I/O cakera, CPU, memori, dan rangkaian semasa operasi sandaran. Jika penggunaan cakera hampir 100%, daya tampung storan adalah titik kesesakan. CPU/memori yang tidak mencukupi boleh menghalang mampatan dan pemprosesan data.

3. Analisis Konfigurasi Pangkalan Data

• MySQL: Semak innodb_buffer_pool_size, innodb_log_file_size, innodb_io_capacity, dan max_allowed_packet. Untuk sandaran fizikal, saiz buffer pool mempengaruhi kecekapan bacaan halaman; untuk sandaran logik, max_allowed_packet yang terlalu kecil boleh menyebabkan kegagalan ketika mensirikan pernyataan SQL besar.
• PostgreSQL: Semak maintenance_work_mem, max_wal_size, checkpoint_timeout, dan effective_io_concurrency. Semasa pg_dump, maintenance_work_mem mempengaruhi pengisihan dan memori pembinaan semula indeks; semasa sandaran fizikal, max_wal_size yang terlalu besar menjana terlalu banyak fail WAL, memperlahankan pemulihan.

4. Nilaikan Parameter Alat Sandaran

• mysqldump: Pertimbangkan penggunaan --single-transaction untuk snapshot konsisten InnoDB dan --quick untuk mengelak penimbalan keseluruhan jadual. Untuk kelajuan, anda boleh paip ke gzip, tetapi berhati-hati dengan overhead CPU.
• pg_dump: Lalai adalah satu proses; gunakan -j untuk membolehkan eksport selari, tetapi pastikan anda juga menggunakan --no-owner untuk mengelak konflik kunci.
• XtraBackup / pg_basebackup: Sandaran fizikal biasanya lebih cepat, tetapi ia menyalin keseluruhan direktori data, jadi daya tampung rangkaian dan cakera menjadi kritikal.

Arahan Pengoptimuman

Mampatan Selari untuk Sandaran Logik MySQL

# Mempercepat output mysqldump dengan pigz gzip selari
time mysqldump --single-transaction --quick --all-databases | pigz -p 8 > backup.sql.gz

Penalaan Sandaran Fizikal MySQL (XtraBackup)

# Keselarian dan urutan mampatan
xtrabackup --backup --target-dir=/backup \
  --parallel=8 --compress --compress-threads=8 \
  --throttle=200  # hadkan I/O untuk melindungi pengeluaran

Sandaran Logik PostgreSQL dengan Pekerjaan Selari

# 8 pekerjaan selari untuk meningkatkan daya pemprosesan
pg_dump -U postgres -d appdb -j 8 -Fd -f /backup/appdb.dump

Pengoptimuman Sandaran Fizikal PostgreSQL (pg_basebackup)

# Sandaran asas dimampatkan dan had kadar
pg_basebackup -D /backup/pg_base -Fp -Xs -z -Z 5 --label="mybackup"

Menyesuaikan Parameter Pangkalan Data (Perlu Berhati-hati)

• MySQL: Tingkatkan sementara innodb_io_capacity dan innodb_io_capacity_max untuk mempercepatkan pembersihan cakera, tetapi pantau kesan pengeluaran.
• PostgreSQL: Tingkatkan maintenance_work_mem (cth., kepada 1GB) sebelum sandaran, dan periksa max_wal_size untuk mengelakkan pembentukan WAL berlebihan.

Kawalan Risiko

• Sentiasa sahkan perubahan parameter dalam persekitaran pra-pengeluaran terlebih dahulu.
• Hadkan I/O sandaran menggunakan pilihan alat seperti --throttle atau ionice peringkat OS.
• Gunakan rangkaian atau storan sandaran khusus untuk mengelakkan persaingan dengan trafik perniagaan.
• Sulkankan sandaran jika sensitif, tetapi pertimbangkan overheard CPU.

Gulung Balik

Jika perubahan mengurangkan prestasi atau menyebabkan keabnormalan, pulihkan dengan segera. - MySQL: Edit my.cnf, kemudian jalankan FLUSH PRIVILEGES; dan mulakan semula, atau gunakan SET GLOBAL dinamik untuk memulihkan secara langsung. - PostgreSQL: Edit postgresql.conf dan jalankan pg_ctl reload atau SELECT pg_reload_conf();.

Pengesahan

• Gunakan arahan time untuk mengukur tempoh sandaran dan pemulihan sebenar, kemudian bandingkan dengan asas sebelum pengoptimuman.
• Simulasikan bencana dalam persekitaran ujian dan sahkan RTO dalam sasaran.
• Pantau kemajuan melalui SHOW PROCESSLIST (MySQL) atau pg_stat_activity (PostgreSQL).

Bila Mengemukakan Tiket OpsGlobal

Jika pasukan anda tidak dapat mendiagnosis atau mengoptimumkan untuk mencapai sasaran, atau jika isu melibatkan rangkaian rentas wilayah, perubahan seni bina storan, atau operasi kompleks lain, hantar tiket OpsGlobal dengan segera. Pakar pangkalan data 7×24 kami boleh memulihkan prestasi sandaran dalam masa beberapa jam dan memastikan SLA anda dipenuhi.