PostgreSQL menggunakan statistik akumulasi pada distribusi nilai data dalam tabel target untuk memilih rencana pelaksanaan kueri yang paling efisien.
Ini diperbarui dengan secara eksplisit menjalankan perintah
ANALYZE dan
VACUUM ANALYZE atau di latar belakang dengan proses
autovacuum / autoanalisis . Tetapi jika statistik tidak punya waktu untuk diperbarui - masalah dapat terjadi.
Bagaimana cara mendeteksi dan memperbaiki masalah seperti itu?
Pilihan utama ketika situasi seperti itu bisa terjadi sama sekali adalah jika kumpulan data telah berubah secara dramatis dalam tabel. Artinya, ia
menggerakkan sejumlah besar INSERT / UPDATE / DELETE atau cukup "menuangkan" data ke tabel kosong - misalnya,
ketika memulihkan dari cadangan .
Bantuan untuk
utilitas pemulihan standar
pg_restore bahkan secara eksplisit mengatakan:
Setelah pemulihan, masuk akal untuk menjalankan ANALYZE untuk setiap tabel yang dipulihkan sehingga pengoptimal menerima statistik terbaru.
Karena itu, jika Anda melakukan sesuatu yang mirip dengan database - jangan malas, segera jalankan
ANALYZE pada tabel paling "tebal" atau seluruh database.
Kami menentukan adanya masalah
Apa yang sebenarnya terlihat dari situasi "semua buruk"? Biasanya kira-kira seperti ini:
Kolom
rasio hanya menunjukkan hubungan "pada waktu" antara jumlah catatan yang direncanakan berdasarkan statistik dan jumlah yang benar-benar dibaca:
Bitmap Heap Scan on ... (... rows=14831 ...) (actual ... rows=9 ...)
Semakin tinggi nilai ini, semakin buruk statistik mencerminkan situasi sebenarnya di tabel Anda. Biasanya, biasanya
tidak melebihi ratusan , dalam contoh ini,
satu setengah ribu kali .
Ini mengarah pada pilihan rencana yang tidak efektif dan, sebagai akibatnya,
beban terliar di pangkalan . Untuk menghapusnya dengan cepat, Anda hanya perlu mendengarkan rekomendasi manual dan membaca
ANALYZE di tabel utama.
Berikut ini adalah beban CPU pada server database sebelum dan sesudah operasi ini untuk contoh di atas:
Tabel yang sering diperbarui
Tetapi bagaimana jika tabel benar-benar mengubah sejumlah besar catatan? Misalnya, ini adalah semacam buffer atau antrian pemrosesan di mana catatan baru terus ditambahkan dan yang lama dihapus.
Dalam hal ini,
parameter konfigurasi berikut akan membantu kami:
autovacuum_naptime (integer)
Menetapkan penundaan minimum antara dua proses pembersihan otomatis untuk satu basis data. Daemon pembersihan otomatis memindai basis data pada interval waktu yang ditentukan dan mengeluarkan perintah VACUUM dan ANALYZE bila diperlukan untuk tabel dalam basis data ini. Jika nilai ini ditentukan tanpa unit, itu dianggap ditetapkan dalam detik. Secara default, penundaan adalah satu menit (1 menit). Parameter ini hanya dapat diatur dalam postgresql.conf atau pada baris perintah ketika server dimulai.
autovacuum_analyze_threshold (integer)
Menetapkan jumlah minimum tupel yang ditambahkan, dimodifikasi atau dihapus di mana ANALYZE akan dijalankan untuk satu tabel. Nilai default adalah 50 tupel. Parameter ini hanya dapat diatur dalam postgresql.conf atau pada baris perintah ketika server dimulai. Namun, nilai ini dapat diganti untuk tabel yang dipilih dengan mengubah pengaturan penyimpanannya.
autovacuum_analyze_scale_factor (floating point)
Menentukan persentase ukuran tabel yang akan ditambahkan ke autovacuum_analyze_threshold ketika ambang perintah ANALYZE dipilih. Nilai default adalah 0,1 (10% dari ukuran tabel). Anda dapat mengatur parameter ini hanya di postgresql.conf atau di baris perintah ketika server dimulai. Namun, nilai ini dapat diganti untuk tabel yang dipilih dengan mengubah pengaturan penyimpanannya.
SWSS
Kadang-kadang ketika menyiapkan server,
autovacuum_naptime "
dihancurkan " menjadi "sekali sehari" (1d) sehingga
autoVACUUM berkeliling database lebih jarang dan memakan lebih sedikit sumber daya.
Kadang-kadang, meskipun sangat jarang, bahkan dibenarkan - misalnya, jika Anda memiliki
ribuan tabel / bagian dalam satu database (bahkan jika mereka diletakkan dalam pola yang berbeda).
Karena definisi tabel spesifik mana dari seluruh daftar yang perlu diproses, selama inisialisasi proses autovacuum, ia
dapat mengambil banyak sumber daya dan memperlambat server .
Hanya dalam kasus ini, Anda akan memiliki masalah dengan tabel yang sering diubah.
Di sini - tetapkan interval peluncuran yang lebih memadai, atau kejar ANALYZE pada tabel seperti itu dalam mode "manual" untuk beberapa alasan yang diterapkan (misalnya, timer eksternal atau setelah akhir tahap berikutnya dari pemrosesan antrian).
Kawan, pertahankan statistik terkini!