Mengatasi PostgreSQL Bottleneck yang Disebabkan oleh Lalu Lintas Tinggi

Postgresql bottleneck sering kita alami ketika lalu lintas pada database postgresql kita sudah semakin padat. Sama halnya di jalan raya ketika trafik sudah semakin padat maka yang terjadi adalah kemacetan di jalan raya.

postgresql bottleneck

Banyak tumpukan saat ini diimplementasikan dengan mempercayai Object Relational Mapper, ORM, untuk melakukan hal yang benar dengan PostgreSQL sementara seseorang membuat logika bisnis penting pada sisi proses server aplikasi. Untuk sebagian besar postgresql bottleneck, ini berhasil dengan cukup baik tetapi seiring waktu seseorang harus mengunjungi kembali server database saat loadnya tinggi. Yang terjadi pada PostgreSQL, ia dapat mengalami pelambatan seiring peningkatan lalu lintas.

Ada banyak cara untuk mengatasi kemacetan kinerja / postgresql bottleneck, tetapi pada pembahasan berikutini kita bisa melihat hal ini dalam beberapa masalah seperti berikut:

• Tuning Performance Parameters
• Session Connections
• Bloat
• Autovacuum: Basic
• Autovacuum: Advanced
• Data Hotspots
• Competing Application Processes
• Replication Latency
• Server Environment

Tentang “Kategori” dan “Potensi Dampak” PostgreSQL Bottleneck

Kompleksitas yang kita alami mengacu pada tingkat kesulitan dalam mengimplementasikan solusi tertentu. Sedangkan dampak potensial memberi Anda gambaran tentang dampak mitigasi yang ideal pada kinerja sistem Anda. Namun terkadang, karena usianya, jenis sistem, teknis kerja, dll., menggambarkan kompleksitas secara akurat dan potensi dampaknya dapat menjadi masalah. Pada akhirnya, mengingat lingkungan yang sangat kompleks, penilaian Andalah yang membuat keputusan akhir dalam melakukan aktivitas terhadap database postgresql.

Kategori kompleksitas

• Low
• Medium
• High
• Low-Medium-High

Kategori Dampak Potensial

• Low
• Medium
• High
• Low-Medium-High

Mengatasi PostgreSQL Bottleneck dengan Parameter Performance Tuning

Kompleksitas: Low

Potensi Dampak: High

Ada suatu ketika ternyata versi postgres modern masih dapat berjalan di i386. Meskipun setelan default telah diperbarui, parameternya masih disetel untuk menggunakan sumber daya minimal saat penginstalannya.

Pengaturan ini adalah yang paling mudah diatur dan biasanya diperbarui saat layanan diinstal pertama kali. Ketika anda tidak menyesuaikan nilai-nilai ini maka dapat mengakibatkan CPU dan IO tinggi yang merupakan salah satu penyebab postgresql bottleneck:

• ukuran_cache_efektif ~ 50 hingga 75%
• shared_buffers ~ 1/4 – 1/3 total RAM sistem
• work_mem ~ 10MB

Jadi mari kita bahas variabel-variabel ini.

Nilai efektif cache yang disarankan, meskipun tipikal, dapat disetel secara tepat dengan merujuk ke “atas” yaitu RAM + free cache.

Mengatur shared buffer adalah teka-teki yang menarik. Ada dua cara untuk melihat pengaturan ini: dengan asumsi Anda memiliki basis data kecil, seseorang dapat menyetel shared buffer cukup tinggi sehingga pada dasarnya seseorang memiliki sistem basis data residen RAM.

Jika tidak, anda dapat mengkonfigurasi untuk memuat tabel dan indeks tersebut, yang paling sering digunakan oleh layanan, untuk tetap berada di RAM (aturan lama adalah 80/20). Pengaturan 1/3 RAM sistem dulunya adalah pengaturan yang direkomendasikan tetapi seiring waktu aturan tersebut turun menjadi 1/4 karena mesin memperoleh lebih banyak RAM karena ada yang namanya terlalu banyak kinerja ke shared buffer.

RAM yang terlalu sedikit berarti lebih banyak kerja CPU dan IO yang lebih tinggi. Anda akan tahu jika setelan shared buffer terlalu tinggi saat beban CPU dan kinerja IO mencapai titik stabil.

sumber : percona

Faktor lain yang perlu dipertimbangkan sebagai faktor yang berpengaruh atas postgresql bottleneck adalah cache OS; dengan RAM yang cukup, Linux akan men-cache tabel dan indeks dalam RAM dan dapat bergantung pada berbagai pengaturan, mengelabui PostgreSQL agar percaya bahwa ia membaca dari disk bukan dari RAM.

Kinerja meningkat dengan mengorbankan peningkatan redundansi dengan sering menyalin halaman yang sama yang ditemukan di shared buffer ke dalam cache OS, yang merupakan alasan lain untuk menghindari cache shared buffer yang terlalu besar. Untuk perfeksionis di antara kita, lihat ekstensi pg_buffercache yang membaca penggunaan cache secara real-time (TIP: lihat tabel ini).

Menyetel work_mem terlalu rendah menjamin kinerja yang buruk karena diproses sebagai file sementara pada disk. Di sisi lain, meskipun menyetelnya tinggi tidak mempengaruhi kinerja, hal itu berisiko membuat server kekurangan RAM jika terlalu banyak koneksi yang aktif pada satu waktu.

Penggunaan RAM yang sama dengan work mem yang digunakan untuk setiap operasi pengurutan. Anda perlu melakukan sedikit penghitungan aritmatika untuk instance RAM yang digunakan oleh setiap kueri dan sesi. TIPS: gunakan EXPLAIN ANALYZE untuk melihat di mana operasi pengurutan dilakukan dan dengan memvariasikan nilai dalam sesi tertentu, seseorang dapat melihat saat operasi tersebut di tuliskan ke disk.

Mengatasi PostgreSQL Bottleneck dengan Session Connection: Pengelolaan

Kompleksitas: Low

Potensi Dampak: Low-Medium-High

Lalu Lintas Tinggi sering digambarkan sebagai sejumlah besar koneksi yang terjadi dalam interval waktu yang singkat. Terlalu banyak koneksi yang memblokir proses dan dapat menunda respons kueri dan bahkan dapat menyebabkan kesalahan sesi. Kalo sudah begini akan terjadi postgresql bottleneck, mau mencoba menangani akar permasalahannya bisa jadi bukanlah hal yang mudah tanpa menggali informasi dari log postgres.

Perbaikan yang mudah adalah meningkatkan jumlah koneksi:

# postgresql.conf: default is set to 100<br />max_connections

Alternatif untuk mengatasi postgresql bottleneck selanjutnya, pendekatan yang lebih canggih adalah penggabungan koneksi. Ada banyak solusi tetapi teknologi yang paling umum digunakan adalah pgbouncer. Di antara banyak kemampuannya, pgbouncer dapat mengatur sesi koneksi menggunakan salah satu dari tiga mode berikut :

• Session pooling: Metode paling halus/sopan. Kenapa dikatakan metode paling sopan? Ketika klien terhubung, koneksi server akan ditetapkan untuk selama klien tetap terhubung. Ketika klien terputus, koneksi server akan dimasukkan kembali ke dalam pool. Ini adalah metode default.
• Transaction pooling: Sambungan server ditetapkan ke klien hanya selama transaksi. Ketika PgBouncer mengetahui bahwa transaksi telah selesai, koneksi server akan dimasukkan kembali ke dalam pool.
• Statement pooling: Metode paling agresif. Sambungan server akan dimasukkan kembali ke dalam kumpulan segera setelah kueri selesai. Transaksi multi-statement tidak diizinkan dalam mode ini karena akan rusak.

Secure Socket Layer (SSL), adalah pertimbangan lain pada kasus postgresql bottleneck. Ketika dikonfigurasi untuk menggunakan sertifikat SSL, perilaku default PostgreSQL mendorong semua sesi penghubung untuk menggunakan SSL sehingga menghabiskan lebih banyak daya pemrosesan CPU daripada sesi yang tidak terenkripsi.

Untuk case postgresql bottleneck ini, anda dapat mengkonfigurasi aturan otentikasi berbasis host, pg_hba.conf, memaksa sesi klien biasa untuk tidak menggunakan SSL dan sebaliknya memesan penggunaannya untuk tugas-tugas administratif, oleh superuser, atau dengan replikasi streaming.

Mengatasi PostgreSQL Bottleneck dengan Autovacuum: Basic

Kompleksitas: Medium

Potensi Dampak: Low-Medium

Multi-Version Concurrency Control adalah salah satu prinsip dasar yang membuat PostgreSQL menjadi solusi DBMS yang populer. Namun, salah satu kelemahan yang disayangkan adalah bahwa untuk setiap catatan yang diperbarui atau dihapus, tupel mati (sudah tidak digunakan) yang pada akhirnya harus dibersihkan.

Proses autovacuum yang disetel secara tidak tepat, merupakan mekanisme yang menangani dead-tuple, mengurangi kinerja, yaitu membuat server semakin sibuk, semakin signifikan pengaruhnya terhadap postgresql bottleneck.

Ketika anda mengelola daemon autovacuum, anda dapat menggunakan tiga (3) parameter berikut untuk mengatasi terjadinya postgresql bottleneck:

1. autovacuum_max_workers: Meningkatkan jumlah worker autovacuum dari default tiga (3) worker berarti lebih banyak proses tersedia untuk menyedot datacluster, yang merupakan fitur yang sangat berguna untuk dipertimbangkan ketika dihadapkan dengan sejumlah besar tabel yang sangat besar. Idealnya, seseorang membuat satu worker per CPU. Worker tidak boleh melebihi jumlah CPU, karena terlalu banyak worker berpotensi berdampak pada peningkatan penggunaan CPU yang menyebabkan postgresql bottleneck. Biasanya anda bisa menetapkan nomor di antara dua batas ini. Ini adalah tindakan penyeimbangan antara memaksimalkan efisiensi autovacuum versus kinerja sistem secara keseluruhan.
2. maintenance_work_mem: Semakin besar nilainya, semakin efisien penyedotannya. Ingatlah bahwa ada hukum keuntungan yang semakin berkurang. Nilai yang terlalu tinggi pada akhirnya hanya membuang-buang RAM dan yang lebih buruk dapat menghabiskan jumlah RAM yang tersedia untuk seluruh sistem database.
3. autovacuum_freeze_max_age: Parameter ini mengurangi TXID WRAPAROUND. Semakin lama usia semakin jarang berjalan mengurangi jumlah pemuatan sistem. Tetapi seperti semua parameter autovacuum yang disebutkan sejauh ini, ada peringatan. Menunda nilai terlalu lama dan Anda berisiko kehabisan nomor txid sebelum proses selesai yang menyebabkan server mematikan paksa untuk melindungi integritas data. Menentukan nilai yang benar memerlukan tren txid terbesar / terlama terhadap proses autovacuum saat kueri pg_stat_activity untuk aktivitas WRAPAROUND.

Waspadalah terhadap RAM dan CPU yang melakukan over-commit, dapat menjadi postgresql bottleneck. Semakin tinggi nilai yang awalnya ditetapkan, semakin besar jumlah risiko sumber daya yang dikonsumsi habis saat pemuatan sistem meningkat. Mengatur terlalu tinggi dapat mengalami penurunan kinerja secara tiba-tiba saat memuat melebihi titik tertentu.

TIPS: Mirip dengan alokasi RAM terkait work_mem, anda dapat melakukan aritmatika atau benchmark environment untuk mengatur nilai secara optimal untuk menghindari terjadinya postgresql bottleneck.

Mengatasi PostgreSQL Bottleneck dengan Autovacuum: Advanced

Kompleksitas: High

Potensi Dampak: High

Karena banyaknya upaya yang terlibat, seseorang harus mempertimbangkan metode ini ketika risiko sistem database mendorong host ke batas fisiknya, dan pembengkakan yang berlebihan diidentifikasi sebagai masalah yang menyebabkan postgresql bottleneck.

Mengedit parameter runtime autovacuum di postgresql.conf adalah metode paling umum yang digunakan untuk mengontrol perilakunya untuk datacluster. Sayangnya, pendekatan seperti ini mungkin saja cocok untuk semua namun tidak berfungsi dengan baik dalam jangka panjang terutama sebagai skala sistem untuk menghindari postgresql bottleneck.

Parameter Penyimpanan Tabel: Seringkali akan ada tabel yang aktivitasnya mewakili jumlah yang signifikan dari total churn datacluster. Menyesuaikan berbagai parameter autovacuum pada setiap tabel demi tabel adalah cara terbaik untuk mengurangi hubungan hiper-aktif tanpa menggunakan pemanggilan VACUUM manual yang dapat berdampak signifikan pada sistem yang berujung pada postgresql bottleneck.

Setting tabel individual menggunakan SQL COMMAND ini.

ALTER TABLE .. SET STORAGE_PARAMETER

Mengatasi PostgreSQL Bottleneck dengan Bloat

Kompleksitas: Low

Potensi Dampak: Medium-High

Seiring waktu, bahkan dengan niat terbaik, atau tidak, kinerja dapat menurun karena kebijakan penyedotan yang tidak memadai yang menyebabkan “kembung” yang berlebihan bahkan menyetel daemon autovacuum dan secara manual memanggil VACUUM tidak akan mudah diselesaikan. Untuk kasus ini, ekstensi pg_repack akan membantu masalah postgresql bottleneck anda.

pg_repack: membangun kembali dan mengatur ulang tabel dan indeks dalam kondisi produksi.

Mengatasi PostgreSQL Bottleneck dengan Data Hotspots

Kompleksitas: High

Potensi Dampak: Low-Medium-High

Mirip dengan MySQL HotSpots, PostgreSQL experience, dan resolusinya, hot spot bergantung pada pengetahuan luas tentang aliran data dan dapat, pada mitigasi yang paling ekstrim, memperbaiki arsitektur sistem.

Berikut adalah beberapa teknik mitigasi yang lebih populer:

• Indeks: Mengkonfirmasi bahwa kolom kriteria memiliki indeks yang ditetapkan berpeluang besar untuk meningkatkan kinerja kueri. Teknik lainnya adalah dengan mengkueri berbagai katalog dan tampilan pemantauan dan mengonfirmasi bahwa Perintah SQL meminta kolom dengan indeks. TIPS: gunakan tools seperti ekstensi pg_stat_statement dan pgbadger untuk menentukan kinerja kueri.
• Heap Only Tuple (HOT): Ada yang namanya terlalu banyak indeks. Kurangi potensi pembengkakan dan kurangi ukuran tabel dengan menghapus semua indeks yang tidak digunakan pada klausa kuery WHERE dalam kueri SELECT.
• Partisi Tabel: Tidak ada yang mempengaruhi kinerja seperti tabel yang beberapa kali lebih besar dari ukuran tabel rata-rata. Membagi tabel besar menjadi partisi yang lebih kecil misalnya dapat meningkatkan kinerja kueri saat membuat kueri data yang dipartisi menurut tanggal. Dan karena hanya satu worker autovacuum yang diizinkan untuk memproses satu tabel, memecahnya menjadi banyak tabel yang lebih kecil memungkinkan lebih dari satu pekerja untuk melakukan autovacuum. Keuntungan lain dari mempartisi tabel adalah bahwa pembersihan data jauh lebih efisien dan lebih cepat dengan memotong satu tabel yang dipartisi daripada menghapus sejumlah besar baris dari satu tabel berukuran super.
• Parallel Querying: Diperkenalkan dalam versi terbaru postgres, anda dapat menggunakan beberapa CPU yang memproses satu kueri di mana sebelumnya hanya satu prosesor per kueri.
• De-Normalisasi: Bergantung pada spesifikasinya, anda dapat meningkatkan kinerja dengan menggabungkan kolom dari beberapa tabel menjadi tabel tunggal yang lebih besar. Keuntungan kinerja dibuat dengan mengurangi perencanaan kueri tetapi dengan mengorbankan peningkatan redundansi data. Renungkan opsi ini dengan cermat sebelum menggunakannya! Cocok untuk digunakan pada datawarehouse.

Mengatasi PostgreSQL Bottleneck dengan Competing Application Processes

Kompleksitas: Low

Potensi Dampak: High

Aplikasi PHP + Java + Python: Hindari menjalankan aplikasi dan postgres di host yang sama. Kembali ke masa lalu, seseorang dapat dengan mudah menggabungkan layanan web dan RDBMS pada mesin yang sama karena penggunaan sumber daya mereka gratis.

Waspadalah terhadap aplikasi yang berbasis pada bahasa ini karena mereka dapat menghabiskan banyak RAM, terutama bisa menjadi pengumpulan sampah, yang kemudian bersaing dengan sistem database yang mengurangi kinerjanya secara keseluruhan yang berkontribusi dalam postgresql bottleneck .

Mengatasi PostgreSQL Bottleneck dengan Replication Latency

Kompleksitas: Low

Potensi Dampak: High

async vs sync: Versi terbaru dari postgres mendukung replikasi logis dan streaming dalam mode sinkron dan asinkron. Meskipun mode replikasi default adalah asinkron, anda harus mempertimbangkan implikasi penggunaan replikasi sinkronisasi terutama melalui koneksi jaringan dengan latensi kurang dari ideal.

Mengatasi PostgreSQL Bottleneck dengan Server Environment

Terakhir, yang tidak kalah penting untuk mengatasi postgresql bottleneck, adalah pertimbangan yang paling mendasar yaitu membuat host lebih besar dan lebih baik. Mari kita tinjau apa yang diberikan oleh masing-masing sumber daya berikut melalui peningkatan kinerja ke database PostgreSQL yang dapat membantu mengatasi postgresql bottleneck:

• RAM: Semakin banyak semakin baik, ini memungkinkan kita untuk menetapkan lebih banyak RAM ke kueri dan meningkatkan jumlah sesi individu. Lebih banyak RAM berarti lebih banyak database di-cache sehingga mengoptimalkan IO.
• CPU: Lebih banyak CPU berarti lebih banyak proses bercabang yaitu lebih banyak menyedot micro process, koneksi sesi, dll.
• HDD: Optimalisasi ukuran dan kecepatan
  • meningkatkan ukuran database yang diizinkan
  • kinerja kueri keseluruhan meningkat karena IO yang lebih cepat terutama ketika operasi seperti penggabungan jenis penulisan ke disk
• Partisi Disk:
  • Memecah datacluster di beberapa partisi meningkatkan jumlah saluran dan mengisolasi operasi berbeda yang dilakukan postgres pada saat yang bersamaan. Misalnya, anda dapat meletakkan indeks dan tabel pada partisi terpisah yang memiliki karakteristik kinerja berbeda.
  • Tabel sesi sementara dan operasi seperti merge sort dapat didedikasikan untuk satu partisi berkecepatan tinggi atau dijalankan di beberapa partisi untuk meningkatkan IO
  • Pencatatan log dapat diisolasi ke dalam partisi dan jika Anda kehabisan ruang, hal itu tidak akan memengaruhi RDBMS
  • WALL logs, mirip dengan logging biasa, dapat memiliki partisi sendiri untuk operasi write only. Jika kehabisan ruang, seperti yang dapat terjadi saat pengiriman log dan koneksi ke slave terputus, integritas database sepenuhnya terjamin karena tabel berada di tempat lain.

Demikianlah point-point yang ditengarai dapat menimbulkan ataupun menyebabkan prostgresql bottleneck. Apakah tulisan mengenai postgresql bottleneck ini bermanfaat bagi anda ?

Apabila memang tulisan mengenai postgresql bottleneck ini bermanfaat bagi anda, silahkan untuk membaginya dengan rekan-rekan anda supaya dapat lebih banyak memberikan manfaat.

Sumber : Percona