Solusi Checkpoints Are Occurring Too Frequently Every 30 Seconds pada PostgreSQL

Pengaturan parameter shared_buffers di PostgreSQL berfungsi untuk mengalokasikan memori sebagai buffer cache untuk data yang sering diakses. Jika nilai ini terlalu kecil, performa database bisa terganggu karena PostgreSQL lebih sering mengandalkan disk dibandingkan memori.

Contoh Masalah

Aplikasi terasa lambat, terutama saat melakukan query besar.

Monitoring menunjukkan banyak akses disk (disk I/O) yang tinggi, sementara penggunaan memori rendah.

Log PostgreSQL menunjukkan pesan seperti:

LOG: checkpoints are occurring too frequently (every 30 seconds)

HINT: Consider increasing the configuration parameter "shared_buffers".

Langkah-Langkah Perbaikan

1. Periksa Konfigurasi Saat Ini

Cek nilai parameter shared_buffers yang sedang digunakan:

Login ke PostgreSQL:

sudo -u postgres psql

Jalankan perintah berikut:

SHOW shared_buffers;

Contoh output:

shared_buffers

---------------

128MB

2. Pahami Rekomendasi Konfigurasi shared_buffers

Nilai optimal shared_buffers biasanya sekitar 25%-40% dari total memori server.

• Server dengan Memori Kecil (< 2GB): Setidaknya 128MB - 256MB.
• Server dengan Memori Besar (> 8GB): Bisa diatur hingga 2GB atau lebih.

3. Ubah Nilai shared_buffers

Buka file konfigurasi PostgreSQL (postgresql.conf):

sudo nano /etc/postgresql/<version>/main/postgresql.conf

Cari parameter shared_buffers dan ubah nilainya:

shared_buffers = 512MB

Contoh:

Untuk server dengan memori 4GB:

shared_buffers = 1GB

Untuk server dengan memori 16GB:

shared_buffers = 4GB

Simpan perubahan dan keluar dari editor.

4. Restart PostgreSQL

Setelah mengubah konfigurasi, restart layanan PostgreSQL:

sudo systemctl restart postgresql

5. Verifikasi Perubahan

Cek kembali nilai shared_buffers setelah restart:

SHOW shared_buffers;

6. Monitor Kinerja Setelah Perubahan

Cek Performa Query:

Gunakan perintah berikut untuk memantau waktu eksekusi query:

EXPLAIN ANALYZE SELECT * FROM large_table;

Pantau Penggunaan Memori:

Gunakan alat seperti htop atau free -m untuk memastikan PostgreSQL memanfaatkan memori dengan baik.

Pantau Checkpoints:

Jalankan query ini untuk melihat frekuensi checkpoints:

SELECT * FROM pg_stat_bgwriter;

Jika nilai checkpoints_timed terlalu tinggi, sesuaikan lagi parameter shared_buffers.

Pengaturan Tambahan untuk Mendukung shared_buffers

Tingkatkan work_mem

Parameter ini mengatur memori per operasi (sort atau hash).

Tambahkan di file postgresql.conf:

work_mem = 16MB

Atur effective_cache_size

Ini membantu PostgreSQL memperkirakan cache yang tersedia pada server. Biasanya diatur 50%-75% dari total memori.

Tambahkan di file postgresql.conf:

effective_cache_size = 4GB

Optimalkan maintenance_work_mem

Parameter ini digunakan untuk operasi seperti VACUUM atau CREATE INDEX.

Tambahkan di file postgresql.conf:

maintenance_work_mem = 512MB

Tips Optimalisasi Lanjutan

1. Gunakan Alat Monitoring: Alat seperti pgAdmin, Grafana, atau pg_stat_statements dapat membantu memantau penggunaan buffer.
2. Jangan Gunakan Nilai Ekstrem: Nilai shared_buffers yang terlalu besar bisa menyebabkan masalah alokasi memori.
3. Uji dengan Beban Nyata: Lakukan pengujian kinerja setelah perubahan untuk memastikan konfigurasi sesuai dengan kebutuhan aplikasi Anda.

Mengoptimalkan parameter shared_buffers pada PostgreSQL sangat penting untuk meningkatkan kinerja database. Dengan mengalokasikan memori yang memadai, PostgreSQL dapat meminimalkan akses disk yang mahal dan memaksimalkan penggunaan memori untuk caching data. Pastikan untuk selalu memonitor kinerja setelah melakukan perubahan konfigurasi. Semoga bermanfaat, mohon maaf jika ada informasi yang tidak sesuai.