Menghapus kemacetan di PostgreSQL di bawah beban tinggi

Halo. Saat ini, OTUS telah membuka perekrutan untuk aliran baru kursus "PostgreSQL" . Dalam hal ini, kami secara tradisional telah menyiapkan terjemahan materi bermanfaat tentang topik tersebut untuk Anda.

---

Mengambil dasar artikel Peter Zaitsev tentang MySQL Performance Bottlenecks , saya ingin berbicara sedikit tentang PostgreSQL.



Framework ORM sering digunakan untuk bekerja dengan PostgreSQL saat ini. Mereka biasanya bekerja dengan baik, tetapi seiring waktu beban meningkat dan menjadi perlu untuk menyesuaikan server database. Seandal PostgreSQL, ini dapat melambat seiring meningkatnya lalu lintas.



Ada banyak cara untuk menghilangkan kemacetan kinerja, tetapi dalam artikel ini kami akan fokus pada hal berikut:

• Parameter server
• Manajemen koneksi
• Pengaturan autovacuum
• Pengaturan autovacuum tambahan
• Meja kembung (bloat)
• Titik panas dalam data
• Server aplikasi
• Replikasi
• Lingkungan server

Tentang "kategori" dan "potensi dampak"

"Kompleksitas" mengacu pada betapa mudahnya mengimplementasikan solusi yang diusulkan. Dan "dampak potensial" memberikan indikasi tingkat peningkatan kinerja sistem. Namun, karena usia sistem, jenisnya, utang teknis, dll. mendeskripsikan kompleksitas dan dampak secara akurat dapat menjadi masalah. Bagaimanapun, dalam situasi sulit, pilihan terakhir selalu menjadi milik Anda.



Kategori:

• Kompleksitas
  
  
  • Rendah
  • Rata-rata
  • Tinggi
  • Rendah sedang Tinggi
• Dampak potensial
  
  
  
  
  • Rendah
  • Rata-rata
  • Tinggi
  • Rendah sedang Tinggi

Parameter server

Kesulitan: rendah.

Potensi Dampak: Tinggi.



Belum lama ini, ada kalanya versi postgres saat ini dapat berjalan di i386. Pengaturan default telah berubah, tetapi mereka masih dikonfigurasi untuk menggunakan sumber daya paling sedikit.



Pengaturan ini sangat mudah diubah dan biasanya dikonfigurasi selama penginstalan awal. Nilai yang salah dari parameter ini dapat menyebabkan penggunaan CPU dan I / O yang tinggi:

• Parameter effective_cache_size ~ 50 hingga 75%
• Parameter shared_buffers ~ 1/4 - 1/3 jumlah RAM
• Parameter work_mem ~ 10MB

Nilai yang direkomendasikan untuk effective_cache_size, meskipun tipikal, dapat dihitung lebih akurat jika kita mengacu pada "top" - free + cache .



Menghitung nilai shared_buffers adalah teka-teki yang menarik. Anda dapat melihatnya dari dua sudut: jika Anda memiliki database kecil, Anda dapat menyetel nilai shared_buffers yang cukup besar agar sesuai dengan seluruh database dalam RAM. Di sisi lain, Anda dapat mengkonfigurasi memuat hanya tabel dan indeks yang sering digunakan ke dalam memori (ingat 80/20). Sebelumnya, disarankan untuk menetapkan nilai ke 1/3 dari jumlah RAM, tetapi seiring waktu, seiring bertambahnya jumlah memori, itu berkurang menjadi 1/4. Jika sedikit memori yang dialokasikan, maka I / O dan beban prosesor akan meningkat. Alokasi memori yang terlalu banyak akan ditunjukkan dengan mencapai dataran tinggi prosesor dan beban I / O.







Faktor lain yang perlu dipertimbangkan adalah cache OS . Dengan RAM yang cukup, Linux akan meng-cache tabel dan indeks dalam memori dan, bergantung pada bagaimana konfigurasinya, mungkin membuat PostgreSQL percaya itu membaca data dari disk daripada RAM. Halaman yang sama ada di buffer postgres dan cache OS, dan ini adalah salah satu alasan untuk tidak membuat shared_buffers menjadi sangat besar. Menggunakan ekstensi pg_buffercacheAnda dapat melihat penggunaan cache secara real time.



Parameter work_mem menentukan jumlah memori yang digunakan untuk operasi pengurutan. Menyetel nilai ini terlalu rendah menjamin kinerja yang buruk, karena pengurutan akan dilakukan menggunakan file sementara pada disk. Di sisi lain, meskipun pengaturan nilai yang besar tidak mempengaruhi kinerja, dengan jumlah koneksi yang banyak terdapat risiko RAM kehabisan. Dengan menganalisis memori yang digunakan oleh semua permintaan dan sesi, Anda dapat menghitung nilai yang diperlukan.



Menggunakan JELASKAN ANALISIS Anda dapat melihat bagaimana operasi pengurutan dilakukan dan, dengan mengubah nilai sesi, menentukan kapan pengosongan ke disk dimulai.



Anda juga bisa menggunakan benchmark sistem.

Manajemen koneksi

Kesulitan: rendah.

Potensi Dampak:



Beban Rendah-Sedang-Tinggi Tinggi biasanya dikaitkan dengan peningkatan sesi klien per unit waktu. Terlalu banyak dari mereka dapat memblokir proses, menyebabkan penundaan, atau bahkan menyebabkan kesalahan.



Solusi sederhananya adalah dengan meningkatkan jumlah maksimum koneksi bersamaan:

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

Tetapi pendekatan yang lebih efisien adalah penggabungan koneksi . Ada banyak solusi, tapi yang paling populer adalah pgbouncer . PgBouncer dapat mengelola koneksi menggunakan salah satu dari tiga mode:

• (session pooling). . , . , . .
• (transaction pooling). . PgBouncer , , .
• (statement pooling). . . , .

Anda juga perlu memperhatikan Secure Socket Layer (SSL). Saat diaktifkan, koneksi akan menggunakan SSL secara default, yang akan meningkatkan beban pada prosesor dibandingkan dengan koneksi yang tidak dienkripsi. Untuk klien biasa, Anda dapat mengonfigurasi otentikasi berbasis host tanpa SSL ( pg_hba.conf), dan menggunakan SSL untuk tugas administratif atau untuk replikasi streaming.

Pengaturan autovacuum

Kesulitan: Sedang.

Potensi Dampak: Rendah-Sedang.



Multi-Version Concurrency Control adalah salah satu prinsip dasar yang membuat PostgreSQL menjadi solusi database yang populer. Namun, salah satu masalah yang mengganggu adalah bahwa untuk setiap record yang diubah atau dihapus, salinan yang tidak digunakan dibuat, yang pada akhirnya harus dibuang. Proses autovacuum yang tidak dikonfigurasi dengan benar dapat menurunkan kinerja. Selain itu, semakin banyak server yang dimuat, semakin banyak masalah yang muncul.



Parameter berikut digunakan untuk mengontrol daemon autovacuum:

• autovacuum_max_workers. ( ). , . . . .
• maintenance_work_mem. , . , . , .
• autovacuum_freeze_max_age TXID WRAPAROUND. , , . , , , . , txid, . / txid pg_stat_activity WRAPAROUND.

Waspadai kelebihan beban RAM dan CPU. Semakin tinggi nilai awal yang ditetapkan, semakin besar risiko penipisan sumber daya saat beban pada sistem meningkat. Jika disetel terlalu tinggi, kinerja dapat turun drastis saat tingkat beban tertentu terlampaui.



Mirip dengan menghitung work_mem , nilai ini dapat dihitung secara aritmatika atau dijadikan tolok ukur untuk mendapatkan nilai yang optimal .

Pengaturan autovacuum tambahan

Kesulitan: tinggi.

Potensi Dampak: Tinggi.



Metode ini, karena kerumitannya, sebaiknya hanya digunakan ketika kinerja sistem sudah di ambang batas fisik host dan ini benar-benar menjadi masalah.



Opsi runtime autovacuum dikonfigurasikan di postgresql.conf. Sayangnya, tidak ada solusi satu ukuran untuk semua yang akan berfungsi di sistem beban tinggi apa pun.



Opsi penyimpanan untuk tabel . Seringkali dalam database, sebagian besar beban jatuh hanya pada beberapa tabel. Menyesuaikan pengaturan autovacuum untuk sebuah meja adalah cara terbaik untuk menghindari keharusan memulai VACUUM secara manual, yang dapat mempengaruhi sistem secara signifikan.



Anda dapat menyesuaikan tabel menggunakan perintah :

ALTER TABLE .. SET STORAGE_PARAMETER

Meja kembung (bloat)

Kesulitan: rendah.

Potensi Dampak: Sedang-Tinggi.



Seiring waktu, kinerja sistem dapat menurun karena kebijakan pembersihan yang tidak tepat karena pembengkakan tabel yang berlebihan. Jadi, bahkan mengatur daemon autovacuum dan memulai VACUUM secara manual tidak menyelesaikan masalah. Ekstensi pg_repack hadir untuk menyelamatkan dalam kasus ini .



Dengan menggunakan ekstensi pg_repack , Anda dapat membangun kembali dan mengatur ulang tabel dan indeks dalam produksi

Titik panas dalam data

Kesulitan: tinggi.

Potensi Dampak: Rendah-Sedang-Tinggi.



Seperti MySQL , PostgreSQL mengandalkan aliran data Anda untuk menghilangkan hotspot dan bahkan dapat mengubah arsitektur sistem Anda.



Pertama-tama, Anda harus memperhatikan hal-hal berikut:

• Indeks . Pastikan ada indeks pada kolom yang dicari. Anda dapat menggunakan Katalog dan tampilan sistem untuk memantau dan memverifikasi bahwa kueri menggunakan indeks. Gunakan ekstensi pg_stat_statement dan pgbadger untuk menganalisis kinerja kueri.
• Heap Only Tuple (PANAS) . Mungkin ada terlalu banyak indeks. Anda dapat mengurangi potensi pembengkakan dan mengurangi ukuran tabel dengan menghapus indeks yang tidak digunakan.
• . , , . , , , . , . , , .
• . postgres. , .
• . , . . , !

Kesulitan: rendah.

Potensi Dampak: Tinggi.



Hindari menjalankan aplikasi (PHP, Java dan Python) dan postgres di host yang sama. Berhati-hatilah dengan aplikasi dalam bahasa ini, karena dapat menghabiskan banyak RAM, terutama pengumpul sampah, yang menyebabkan persaingan dengan sistem database untuk sumber daya dan mengurangi kinerja secara keseluruhan.

Replikasi

Kesulitan: rendah.

Potensi Dampak: Tinggi.



Replikasi sinkron dan asinkron. Versi terbaru dari postgres mendukung replikasi logis dan streaming dalam mode sinkron dan asinkron. Meskipun mode replikasi default adalah asinkron, Anda perlu mempertimbangkan implikasi penggunaan replikasi sinkron, terutama pada jaringan dengan latensi yang signifikan.

Lingkungan server

Last but not least, ini peningkatan sederhana dalam kapasitas host. Mari kita lihat apa yang masing-masing sumber mempengaruhi dalam hal kinerja PostgreSQL:

• . , . . , , -.
• . , , . .
• . .
  
  
  • -, ,
• .
  
  
  
  
  • . , .
  • .
  • . .
  • WAL-, , , . , (log shipping) , , .

: