Penyetelan autovacuum di Azure Database for PostgreSQL - Server Fleksibel
BERLAKU UNTUK: Azure Database for PostgreSQL - Server Fleksibel
Artikel ini memberikan gambaran umum tentang fitur autovacuum untuk server fleksibel Azure Database for PostgreSQL dan panduan pemecahan masalah fitur yang tersedia untuk memantau pemblokir database bloat dan autovacuum. Ini juga menyediakan informasi tentang seberapa jauh database dari situasi darurat atau penanganan masalah.
Apa itu autovacuum
Autovacuum adalah proses latar belakang PostgreSQL yang secara otomatis membersihkan tuple mati dan memperbarui statistik. Ini membantu mempertahankan performa database dengan menjalankan dua tugas pemeliharaan utama secara otomatis:
- VACUUM - Mengosongkan ruang disk dengan menghapus tuple mati.
- ANALYZE - Mengumpulkan statistik untuk membantu Pengoptimal PostgreSQL memilih jalur eksekusi terbaik untuk kueri.
Untuk memastikan autovacuum berfungsi dengan baik, parameter server autovacuum harus selalu diatur ke AKTIF. Saat diaktifkan, PostgreSQL secara otomatis memutuskan kapan harus menjalankan VACUUM atau ANALYZE pada tabel, memastikan database tetap efisien dan dioptimalkan.
Autovacuum internal
Autovacuum membaca halaman yang mencari tuple mati, dan jika tidak ada yang ditemukan, autovacuum membuang halaman. Ketika autovacuum menemukan tupel mati, tupel akan dihapus. Biayanya didasarkan pada:
Parameter | Deskripsi |
---|---|
vacuum_cost_page_hit |
Biaya membaca halaman yang sudah ada di buffer bersama dan tidak memerlukan pembacaan disk. Nilai default diatur ke 1. |
vacuum_cost_page_miss |
Biaya mengambil halaman yang tidak ada di buffer bersama. Nilai default diatur ke 10. |
vacuum_cost_page_dirty |
Biaya menulis ke halaman ketika tuple mati ditemukan di dalamnya. Nilai default diatur ke 20. |
Jumlah pekerjaan yang dilakukan autovacuum tergantung pada dua parameter:
Parameter | Deskripsi |
---|---|
autovacuum_vacuum_cost_limit |
Jumlah autovacuum kerja dilakukan dalam sekali jalan. |
autovacuum_vacuum_cost_delay |
Jumlah milidetik yang autovacuum tertidur setelah mencapai batas biaya yang ditentukan oleh autovacuum_vacuum_cost_limit parameter . |
Dalam semua versi Postgres yang saat ini didukung, nilai default untuk autovacuum_vacuum_cost_limit
adalah 200 (sebenarnya, diatur ke -1, yang membuatnya sama dengan nilai reguler vacuum_cost_limit
, yang secara default, adalah 200).
autovacuum_vacuum_cost_delay
Adapun , dalam Postgres versi 11 default ke 20 milidetik, sementara di Postgres versi 12 dan di atasnya default ke 2 milidetik.
Autovacuum bangun 50 kali (50*20 ms=1000 md) setiap detik. Setiap kali bangun, autovacuum membaca 200 halaman.
Itu berarti dalam satu detik, autovacuum dapat melakukan:
- ~80 MB/Detik [ (200 halaman/
vacuum_cost_page_hit
) * 50 * 8 KB per halaman] jika semua halaman dengan tupel mati ditemukan di buffer bersama. - ~8 MB/Detik [ (200 halaman/
vacuum_cost_page_miss
) * 50 * 8 KB per halaman] jika semua halaman dengan tupel mati dibaca dari disk. - ~4 MB/Detik [ (200 halaman/
vacuum_cost_page_dirty
) * 50 * 8 KB per halaman] autovacuum dapat menulis hingga 4 MB/detik.
Memantau autovacuum
Gunakan kueri berikut untuk memantau autovacuum:
select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;
Kolom berikut membantu menentukan apakah autovacuum mengejar aktivitas tabel:
Parameter | Deskripsi |
---|---|
dead_pct |
Persentase tuple mati jika dibandingkan dengan tuple hidup. |
last_autovacuum |
Tanggal terakhir kali tabel divakum otomatis. |
last_autoanalyze |
Tanggal terakhir kali tabel dianalisis secara otomatis. |
Kapan PostgreSQL memicu autovacuum
Tindakan autovacuum ( baik ANALYZE atau VACUUM) memicu ketika jumlah tupel mati melebihi angka tertentu yang bergantung pada dua faktor: jumlah total baris dalam tabel, ditambah ambang tetap. ANALYZE, secara default, memicu ketika 10% tabel ditambah 50 baris berubah, sementara VACUUM memicu ketika 20% tabel ditambah 50 baris berubah. Karena ambang VACUUM dua kali lebih tinggi dari ambang BATAS ANALYZE, ANALYZE akan dipicu lebih awal dari VACUUM. Untuk versi >PG =13; ANALYZE secara default, memicu ketika 20% tabel ditambah 1000 sisipan baris.
Persamaan yang tepat untuk setiap tindakan adalah:
- Analisis otomatis = autovacuum_analyze_scale_factor * tuple + autovacuum_analyze_threshold atau autovacuum_vacuum_insert_scale_factor * tuple + autovacuum_vacuum_insert_threshold (Untuk versi >PG = 13)
- Autovacuum = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold
Misalnya, jika kita memiliki tabel dengan 100 baris. Persamaan berikut kemudian memberikan informasi tentang kapan analisis dan pemicu vakum:
Untuk Pembaruan/penghapusan: Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70
Analisis pemicu setelah 60 baris diubah pada tabel, dan Vakum memicu saat 70 baris diubah pada tabel.
Untuk Sisipan: Autoanalyze = 0.2 * 100 + 1000 = 1020
Menganalisis pemicu setelah 1.020 baris disisipkan pada tabel
Berikut adalah deskripsi parameter yang digunakan dalam persamaan:
Parameter | Deskripsi |
---|---|
autovacuum_analyze_scale_factor |
Persentase sisipan/pembaruan/penghapusan yang memicu ANALYZE pada tabel. |
autovacuum_analyze_threshold |
Menentukan jumlah minimum tuple yang disisipkan/diperbarui/dihapus untuk MENGANALISIS tabel. |
autovacuum_vacuum_insert_scale_factor |
Persentase sisipan yang memicu ANLYZE pada tabel. |
autovacuum_vacuum_insert_threshold |
Menentukan jumlah minimum tuple yang disisipkan untuk MENGANALISIS tabel. |
autovacuum_vacuum_scale_factor |
Persentase pembaruan/penghapusan yang memicu VACUUM pada tabel. |
Gunakan kueri berikut untuk mencantumkan tabel dalam database dan mengidentifikasi tabel yang memenuhi syarat untuk proses autovacuum:
SELECT *
,n_dead_tup > av_threshold AS av_needed
,CASE
WHEN reltuples > 0
THEN round(100.0 * n_dead_tup / (reltuples))
ELSE 0
END AS pct_dead
FROM (
SELECT N.nspname
,C.relname
,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
,pg_stat_get_live_tuples(C.oid) AS n_live_tup
,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
,C.reltuples AS reltuples
,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.relkind IN (
'r'
,'t'
)
AND N.nspname NOT IN (
'pg_catalog'
,'information_schema'
)
AND N.nspname !~ '^pg_toast'
) AS av
ORDER BY av_needed DESC ,n_dead_tup DESC;
Catatan
Kueri tidak mempertimbangkan bahwa autovacuum dapat dikonfigurasi per tabel menggunakan perintah DDL "alter table".
Masalah umum autovacuum
Tinjau daftar kemungkinan masalah umum berikut dengan proses autovacuum.
Tidak mengimbangi server yang sibuk
Proses autovacuum memperkirakan biaya setiap operasi I/O, mengumpulkan total untuk setiap operasi yang dilakukannya dan menjeda setelah batas maksimal biaya tercapai. autovacuum_vacuum_cost_delay
dan autovacuum_vacuum_cost_limit
adalah dua parameter server yang digunakan dalam proses.
Secara default, autovacuum_vacuum_cost_limit
diatur ke –1, yang berarti batas biaya autovacuum adalah nilai yang sama dengan parameter vacuum_cost_limit
, yang defaultnya adalah 200. vacuum_cost_limit
adalah biaya vakum manual.
Jika autovacuum_vacuum_cost_limit
diatur ke -1
, maka autovacuum menggunakan vacuum_cost_limit
parameter , tetapi jika autovacuum_vacuum_cost_limit
itu sendiri diatur ke lebih besar dari -1
parameter yang kemudian autovacuum_vacuum_cost_limit
dipertimbangkan.
Jika autovacuum tidak mengikuti, parameter berikut dapat diubah:
Parameter | Deskripsi |
---|---|
autovacuum_vacuum_cost_limit |
Default: 200 . Batas biaya mungkin ditingkatkan. Pemanfaatan CPU dan I/O pada database harus dipantau sebelum dan sesudah membuat perubahan. |
autovacuum_vacuum_cost_delay |
Postgres Versi 11 - Default: 20 ms . Parameter mungkin dikurangi menjadi 2-10 ms .Postgres Versi 12 ke atas - Default: 2 ms . |
Catatan
- Nilai
autovacuum_vacuum_cost_limit
didistribusikan secara proporsional di antara pekerja autovacuum yang sedang berjalan, sehingga jika ada lebih dari satu, jumlah batas untuk setiap pekerja tidak melebihi nilaiautovacuum_vacuum_cost_limit
parameter. autovacuum_vacuum_scale_factor
adalah parameter lain yang dapat memicu vakum pada tabel berdasarkan akumulasi tuple mati. Default:0.2
, Rentang yang diizinkan:0.05 - 0.1
. Faktor skala bersifat khusus beban kerja dan harus diatur berdasarkan jumlah data di tabel. Sebelum mengubah nilai, selidiki beban kerja dan volume tabel individual.
Autovacuum terus berjalan
Autovacuum yang terus berjalan dapat memengaruhi pemanfaatan CPU dan IO di server. Berikut adalah beberapa kemungkinan alasannya:
maintenance_work_mem
Daemon autovacuum menggunakan autovacuum_work_mem
yang secara default diatur ke -1
, artinya autovacuum_work_mem
akan memiliki nilai yang sama dengan parameter maintenance_work_mem
. Dokumen ini mengasumsikan autovacuum_work_mem
diatur ke -1
dan maintenance_work_mem
digunakan oleh daemon autovacuum.
Jika maintenance_work_mem
rendah, mungkin ditingkatkan hingga 2 GB pada server fleksibel Azure Database for PostgreSQL. Aturan umum praktis adalah dengan mengalokasikan 50 MB ke maintenance_work_mem
untuk setiap 1GB RAM.
Jumlah database yang besar
Autovacuum mencoba memulai pekerja pada setiap database setiap autovacuum_naptime
detik.
Misalnya, jika server memiliki 60 database dan autovacuum_naptime
diatur ke 60 detik, maka pekerja autovacuum dimulai setiap detik [autovacuum_naptime/Jumlah database].
Ada baiknya untuk meningkatkan autovacuum_naptime
jika ada lebih banyak database dalam kluster. Pada saat yang sama, proses autovacuum dapat dibuat lebih agresif dengan meningkatkan autovacuum_cost_limit
dan mengurangi parameter autovacuum_cost_delay
dan meningkatkan autovacuum_max_workers
dari nilai default 3 menjadi 4 atau 5.
Kesalahan kehabisan memori
Nilai maintenance_work_mem
yang terlalu agresif dapat secara berkala menyebabkan kesalahan memori habis di sistem. Penting untuk memahami RAM yang tersedia di server sebelum perubahan apa pun pada maintenance_work_mem
parameter dibuat.
Autovacuum terlalu mengganggu
Jika autovacuum menggunakan lebih banyak sumber daya, tindakan berikut dapat dilakukan:
Parameter autovacuum
Evaluasi parameter autovacuum_vacuum_cost_delay
, autovacuum_vacuum_cost_limit
, autovacuum_max_workers
. Mengatur parameter autovacuum secara tidak benar dapat menyebabkan skenario di mana autovacuum menjadi terlalu mengganggu.
Jika autovacuum terlalu mengganggu, pertimbangkan tindakan berikut:
- Tingkatkan
autovacuum_vacuum_cost_delay
dan kurangiautovacuum_vacuum_cost_limit
jika diatur lebih tinggi dari default 200. - Kurangi jumlah
autovacuum_max_workers
jika diatur lebih tinggi dari default 3.
Terlalu banyak pekerja autovacuum
Meningkatkan jumlah pekerja autovacuum tidak meningkatkan kecepatan vakum. Memiliki jumlah pekerja autovacuum yang tinggi tidak disarankan.
Meningkatkan jumlah pekerja autovacuum menghasilkan lebih banyak maintenance_work_mem
konsumsi memori, dan tergantung pada nilai , dapat menyebabkan penurunan performa.
Setiap proses pekerja autovacuum hanya mendapatkan (1/autovacuum_max_workers) dari total autovacuum_cost_limit
, sehingga memiliki jumlah pekerja yang tinggi menyebabkan tiap pekerja menjadi lebih lambat.
Jika jumlah pekerja ditingkatkan, autovacuum_vacuum_cost_limit
juga harus ditingkatkan dan/atau autovacuum_vacuum_cost_delay
harus dikurangi untuk membuat proses vakum lebih cepat.
Namun, jika kita mengatur parameter pada tingkat autovacuum_vacuum_cost_delay
tabel atau autovacuum_vacuum_cost_limit
parameter, maka pekerja yang berjalan pada tabel tersebut dikecualikan dari yang dipertimbangkan dalam algoritma penyeimbangan [autovacuum_cost_limit/autovacuum_max_workers].
Perlindungan wraparound ID transaksi autovacuum (TXID)
Saat database mengalami perlindungan wraparound ID transaksi, pesan kesalahan seperti kesalahan berikut dapat diamati:
Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.
Catatan
Pesan kesalahan ini adalah pengawasan jangka panjang. Biasanya, Anda tidak perlu beralih ke mode pengguna tunggal. Sebagai gantinya, Anda dapat menjalankan perintah VACUUM yang diperlukan dan melakukan penyetelan agar VACUUM berjalan cepat. Meskipun Anda tidak dapat menjalankan bahasa manipulasi data (DML) apa pun, Anda masih dapat menjalankan VACUUM.
Masalah wraparound terjadi ketika database tidak dikosongkan atau ada terlalu banyak tuple mati yang tidak dihapus oleh autovacuum. Alasan masalah ini mungkin:
Beban kerja yang berat
Beban kerja dapat menyebabkan terlalu banyak tupel mati dalam waktu singkat yang menyulitkan autovacuum untuk mengimbanginya. Tupel mati dalam sistem semakin menumpuk yang menyebabkan penurunan performa kueri dan mengarah ke situasi wraparound. Salah satu alasan untuk situasi ini muncul mungkin karena parameter autovacuum tidak diatur secara memadai dan tidak mengikuti server yang sibuk.
Transaksi jangka panjang
Setiap transaksi jangka panjang dalam sistem tidak memungkinkan tuple mati dihapus saat autovacuum berjalan. Mereka adalah pemblokir proses vakum. Menghapus transaksi jangka panjang akan memungkinkan tupel mati dihapus saat autovacuum berjalan.
Transaksi jangka panjang dapat dideteksi menggunakan kueri berikut:
SELECT pid, age(backend_xid) AS age_in_xids,
now () - xact_start AS xact_age,
now () - query_start AS query_age,
state,
query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY 2 DESC
LIMIT 10;
Pernyataan yang disiapkan
Jika ada pernyataan yang disiapkan yang tidak diterapkan, mereka akan mencegah tuple mati dihapus.
Kueri berikut membantu menemukan pernyataan yang disiapkan yang tidak dikomit:
SELECT gid, prepared, owner, database, transaction
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;
Gunakan COMMIT PREPARED atau ROLLBACK PREPARED untuk menerapkan atau menggulung balik pernyataan ini.
Slot replikasi yang tidak digunakan
Slot replikasi yang tidak digunakan mencegah autovacuum mengklaim tupel mati. Kueri berikut membantu mengidentifikasi slot replikasi yang tidak digunakan:
SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;
Gunakan pg_drop_replication_slot()
untuk menghapus slot replikasi yang tidak digunakan.
Ketika database mengalami perlindungan wraparound ID transaksi, periksa pemblokir apa pun seperti yang disebutkan sebelumnya, dan hapus pemblokir secara manual agar autovacuum dapat dilanjutkan dan diselesaikan. Anda juga dapat meningkatkan kecepatan autovacuum dengan mengatur autovacuum_cost_delay
ke 0 dan meningkatkan autovacuum_cost_limit
ke nilai yang lebih besar dari 200. Namun, perubahan pada parameter ini tidak berlaku untuk pekerja autovacuum yang ada. Anda bisa memulai ulang atau membunuh pekerja yang ada secara manual untuk menerapkan perubahan parameter.
Persyaratan khusus tabel
Parameter autovacuum mungkin diatur untuk tabel individual. Ini sangat penting untuk tabel kecil dan besar. Misalnya, untuk tabel kecil yang hanya berisi 100 baris, autovacuum memicu operasi VACUUM saat 70 baris berubah (seperti yang dihitung sebelumnya). Jika tabel ini sering diperbarui, Anda mungkin melihat ratusan operasi autovacuum sehari, mencegah autovacuum mempertahankan tabel lain di mana persentase perubahan tidak signifikan. Atau, tabel yang berisi satu miliar baris perlu mengubah 200 juta baris untuk memicu operasi autovacuum. Mengatur parameter autovacuum dengan tepat akan mencegah skenario tersebut.
Untuk mengatur pengaturan autovacuum per tabel, ubah parameter server seperti contoh berikut:
ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);
Beban kerja khusus sisipan
Dalam versi PostgreSQL <= 13, autovacuum tidak berjalan pada tabel dengan beban kerja khusus sisipan, karena tidak ada tuple mati dan tidak ada ruang kosong yang perlu diklaim kembali. Namun, autoanalyze berjalan untuk beban kerja khusus sisipan karena ada data baru. Kerugiannya adalah:
- Peta visibilitas tabel tidak diperbarui, dan dengan demikian performa kueri, terutama di mana ada Pemindaian Hanya Indeks, mulai menderita dari waktu ke waktu.
- Database dapat mengalami perlindungan wraparound ID transaksi.
- Bit petunjuk tidak diatur.
Solusi
Versi <Postgres = 13
Dengan menggunakan ekstensi pg_cron, pekerjaan cron dapat disiapkan untuk menjadwalkan analisis vakum berkala pada tabel. Frekuensi pekerjaan cron tergantung pada beban kerja.
Untuk panduan langkah demi langkah menggunakan pg_cron, tinjau Ekstensi.
Postgres 13 dan versi yang lebih tinggi
Autovacuum berjalan pada tabel dengan beban kerja khusus sisipan. Dua parameter server baru autovacuum_vacuum_insert_threshold
dan autovacuum_vacuum_insert_scale_factor
membantu mengontrol saat autovacuum dapat dipicu pada tabel khusus sisipan.
Panduan Pemecahan Masalah
Menggunakan panduan pemecahan masalah fitur yang tersedia di portal server fleksibel Azure Database for PostgreSQL dimungkinkan untuk memantau kembung di tingkat database atau skema individual bersama dengan mengidentifikasi pemblokir potensial ke proses autovacuum. Dua panduan pemecahan masalah tersedia yang pertama adalah pemantauan autovacuum yang dapat digunakan untuk memantau kembung pada tingkat database atau skema individual. Panduan pemecahan masalah kedua adalah pemblokir dan wraparound autovacuum, yang membantu mengidentifikasi potensi pemblokir autovacuum. Ini juga menyediakan informasi tentang seberapa jauh database di server berasal dari situasi wraparound atau darurat. Panduan pemecahan masalah juga berbagi rekomendasi untuk mengurangi potensi masalah. Cara menyiapkan panduan pemecahan masalah untuk menggunakannya mengikuti panduan pemecahan masalah penyiapan.
Rekomendasi Azure Advisor
Rekomendasi Azure Advisor adalah cara proaktif untuk mengidentifikasi apakah server memiliki rasio bloat tinggi atau server mendekati skenario wraparound transaksi. Anda juga dapat mengatur pemberitahuan untuk rekomendasi menggunakan pemberitahuan Buat Azure Advisor pada rekomendasi baru menggunakan portal Azure
Rekomendasi-rekomendasi tersebut adalah:
Rasio Bloat Tinggi: Rasio kembung tinggi dapat memengaruhi performa server dalam beberapa cara. Salah satu masalah penting adalah bahwa Pengoptimal Mesin PostgreSQL mungkin berjuang untuk memilih rencana eksekusi terbaik, yang mengarah ke performa kueri yang terdegradasi. Oleh karena itu, rekomendasi dipicu ketika persentase kembung pada server mencapai ambang batas tertentu untuk menghindari masalah performa tersebut.
Pembungkus transaksi: Skenario ini adalah salah satu masalah paling serius yang dapat dihadapi server. Setelah server Anda dalam keadaan ini, server mungkin berhenti menerima transaksi lagi, menyebabkan server menjadi baca-saja. Oleh karena itu, rekomendasi dipicu ketika kita melihat server telah melewati ambang 1 miliar transaksi.
Konten terkait
Saran dan Komentar
https://aka.ms/ContentUserFeedback.
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat:Kirim dan lihat umpan balik untuk