Secara default, PostgreSQL tidak dikonfigurasikan untuk beban kerja. Nilai default ditetapkan untuk memastikan PostgreSQL berfungsi di mana-mana dengan sumber daya paling sedikit. Ada pengaturan default untuk semua pengaturan database. Tanggung jawab utama administrator atau pengembang basis data adalah mengonfigurasi PostgreSQL agar sesuai dengan beban sistem mereka. Di blog ini, kami akan menguraikan rekomendasi dasar untuk menyetel pengaturan database PostgreSQL untuk meningkatkan kinerja database sesuai dengan beban kerja.
Ingatlah bahwa sementara mengoptimalkan konfigurasi server PostgreSQL Anda meningkatkan kinerja, perancang basis data juga harus berhati-hati saat menulis kueri. Jika kueri melakukan pemindaian tabel penuh di mana indeks dapat digunakan, atau melakukan penggabungan berat atau operasi agregasi mahal, maka sistem mungkin masih berfungsi dengan buruk, bahkan jika pengaturan basis data dikonfigurasikan dengan benar. Saat menulis kueri basis data, penting untuk memperhatikan kinerja.
Namun, parameter basis data juga sangat penting, jadi mari kita lihat delapan yang memiliki potensi terbesar untuk meningkatkan kinerja.
Opsi PostgreSQL Kustom
PostgreSQL menggunakan buffer sendiri, dan juga menggunakan kernel IO buffered. Ini berarti bahwa data disimpan dalam memori dua kali, pertama di buffer PostgreSQL, dan kemudian di buffer kernel. Tidak seperti database lain, PostgreSQL tidak menyediakan I / O langsung. Ini disebut buffering ganda. Buffer PostgreSQL disebut
shared_buffer , yang merupakan parameter kustom paling efisien untuk sebagian besar sistem operasi. Parameter ini menetapkan berapa banyak memori yang dialokasikan PostgreSQL akan digunakan untuk caching.
Nilai default untuk shared_buffer diset sangat rendah dan Anda tidak akan mendapat banyak manfaat darinya. Ini karena beberapa mesin dan sistem operasi tidak mendukung nilai yang lebih tinggi. Tetapi di sebagian besar mesin modern Anda perlu meningkatkan nilai ini untuk kinerja yang optimal.
Nilai yang disarankan adalah 25% dari total RAM komputer. Anda harus mencoba beberapa nilai yang lebih rendah dan lebih tinggi, karena dalam beberapa kasus Anda bisa mendapatkan kinerja yang baik dengan pengaturan lebih dari 25%. Tetapi konfigurasi aktual tergantung pada mesin Anda dan dataset yang berfungsi. Jika dataset kerja Anda dapat dengan mudah masuk dalam RAM Anda, Anda dapat meningkatkan nilai shared_buffer sehingga berisi seluruh database Anda dan seluruh dataset kerja dapat di-cache. Namun, Anda jelas tidak ingin memesan semua RAM untuk PostgreSQL.
Diperhatikan bahwa dalam lingkungan produksi, kinerja yang baik benar-benar memberikan kepentingan besar bagi shared_buffer, meskipun pengujian harus selalu dilakukan untuk mencapai keseimbangan yang tepat.
Memeriksa nilai shared_buffertestdb=
Catatan : Hati-hati, karena beberapa kernel tidak mendukung nilai yang lebih besar , terutama pada Windows.wal_buffers
PostgreSQL pertama-tama menulis entri dalam WAL (prerecord log) ke buffer, dan kemudian buffer ini dibilas ke disk. Ukuran buffer default yang ditentukan oleh
wal_buffers adalah 16 MB. Tetapi jika Anda memiliki banyak koneksi bersamaan, maka nilai yang lebih tinggi dapat meningkatkan kinerja.
ukuran efektif_cache_
effective_cache_size menyediakan perkiraan memori yang tersedia untuk cache disk. Ini hanya panduan, bukan jumlah persis dari memori atau cache yang dialokasikan. Itu tidak mengalokasikan memori aktual, tetapi memberitahu optimizer jumlah cache yang tersedia di kernel. Jika parameter ini diset terlalu rendah, perencana kueri dapat memutuskan untuk tidak menggunakan beberapa indeks, bahkan jika mereka berguna. Karena itu, menetapkan nilai besar selalu masuk akal.
work_mem
Pengaturan ini digunakan untuk pengurutan kompleks. Jika Anda perlu melakukan pengurutan yang kompleks, tingkatkan nilai
work_mem untuk mendapatkan hasil yang baik. Mengurutkan dalam memori jauh lebih cepat daripada mengurutkan data pada disk. Menyetelnya ke nilai yang sangat tinggi dapat menyebabkan kemacetan memori untuk lingkungan Anda, karena opsi ini berkaitan dengan operasi pengurutan pengguna. Karena itu, jika Anda memiliki banyak pengguna yang mencoba melakukan operasi penyortiran, maka sistem akan menyoroti:
work_mem * total sort operations
untuk semua pengguna. Pengaturan parameter ini secara global dapat menghasilkan penggunaan memori yang sangat tinggi. Karena itu, Anda sangat disarankan untuk mengubahnya di tingkat sesi.
work_mem = 2MB testdb=
Node pemilahan permintaan awal dievaluasi pada 514431.86. Biaya adalah unit terhitung yang sewenang-wenang. Untuk permintaan di atas, kami hanya memiliki work_mem 2 MB. Untuk tujuan pengujian, mari tingkatkan nilai ini menjadi 256 MB dan lihat apakah ini memengaruhi biaya.
work_mem = 256MB testdb=
Biaya permintaan dikurangi dari 514431,86 ke 360617,36, yaitu turun 30%.
maintenance_work_mem
maintenance_work_mem adalah parameter memori yang digunakan untuk tugas pemeliharaan. Nilai default adalah 64 MB. Mengatur nilai besar membantu dalam tugas-tugas seperti VACUUM, KEMBALIKAN Ulang, MENCIPTAKAN INDEKS, TAMBAHKAN KUNCI LUAR NEGERI dan ALTER TABLE.
maintenance_work_mem = 10MB postgres=
maintenance_work_mem = 256MB postgres=
Waktu pembuatan indeks adalah 170091.371 ms jika parameter maintenance_work_mem diatur hanya 10 MB, tetapi berkurang menjadi 111274.903 ms ketika kami meningkatkan parameter maintenance_work_mem menjadi 256 MB.
syncous_commit
Digunakan untuk memastikan bahwa komit transaksi menunggu WAL untuk menulis ke disk sebelum mengembalikan status penyelesaian yang berhasil ke klien. Ini adalah tradeoff antara kinerja dan keandalan. Jika aplikasi Anda dirancang sedemikian rupa sehingga kinerja lebih penting daripada keandalan, nonaktifkan
syncous_commit . Dalam hal ini, transaksi dilakukan dengan sangat cepat karena tidak akan menunggu file WAL diatur ulang, tetapi keandalannya akan terganggu. Dalam hal terjadi kegagalan server, data dapat hilang bahkan jika klien menerima pesan yang menunjukkan bahwa komit transaksi selesai dengan sukses.
checkpoint_timeout, checkpoint_completion_target
PostgreSQL menulis perubahan pada WAL. Proses pos pemeriksaan mem-flush data ke file. Tindakan ini dilakukan ketika breakpoint (CHECKPOINT) terjadi. Ini adalah operasi yang mahal dan dapat menyebabkan sejumlah besar operasi IO. Seluruh proses ini melibatkan operasi baca / tulis yang mahal ke disk. Pengguna selalu dapat memulai tugas pos pemeriksaan (CHECKPOINT) bila perlu, atau mengotomatiskan mulai menggunakan parameter
checkpoint_timeout dan
checkpoint_completion_target .
Parameter checkpoint_timeout digunakan untuk mengatur waktu antara breakpoint WAL. Pengaturan yang terlalu rendah mengurangi waktu pemulihan setelah kegagalan karena lebih banyak data yang ditulis ke disk, tetapi juga mengurangi kinerja karena setiap pos pemeriksaan pada akhirnya menghabiskan sumber daya sistem yang berharga.
checkpoint_completion_target adalah sebagian kecil waktu antara pos pemeriksaan untuk menyelesaikan sebuah pos pemeriksaan. Pos pemeriksaan frekuensi tinggi dapat mempengaruhi kinerja. Untuk menyelesaikan pekerjaan pos pemeriksaan dengan lancar,
checkpoint_timeout harus rendah. Jika tidak, OS akan mengakumulasi semua halaman kotor sampai rasio diamati, dan kemudian menghasilkan reset besar.
Kesimpulan
Ada lebih banyak opsi yang dapat Anda atur untuk mendapatkan kinerja yang lebih baik, tetapi mereka memiliki dampak lebih kecil daripada yang disorot di sini. Pada akhirnya, kita harus selalu ingat bahwa tidak semua parameter relevan untuk semua jenis aplikasi. Beberapa aplikasi berfungsi lebih baik saat mengatur opsi, dan beberapa tidak. Pengaturan basis data PostgreSQL harus disesuaikan dengan kebutuhan spesifik aplikasi dan sistem operasi yang digunakannya.