Halo, orang Khabrovit! Saya membawa perhatian Anda pada terjemahan artikel
"Bagaimana perubahan konfigurasi PostgreSQL tunggal meningkatkan kinerja permintaan lambat sebesar 50x" oleh Pavan Patibandla. Ini banyak membantu saya untuk meningkatkan kinerja PostgreSQL.
Di Amplitude, tujuan kami adalah menyediakan analisis produk interaktif yang mudah digunakan sehingga semua orang dapat menemukan jawaban atas pertanyaan mereka tentang produk. Untuk memastikan kegunaan, Amplitude harus memberikan jawaban ini dengan cepat. Oleh karena itu, ketika salah satu pelanggan kami mengeluh tentang berapa lama waktu yang diperlukan untuk memuat daftar drop-down properti acara di antarmuka pengguna Amplitude, kami memulai studi rinci masalah.
Dengan melacak penundaan pada level yang berbeda, kami menyadari bahwa butuh 20 detik untuk menyelesaikan satu permintaan PostgreSQL tertentu. Ini mengejutkan kami, karena kedua tabel memiliki indeks di kolom gabungan.
Permintaan lambat
Rencana eksekusi PostgreSQL untuk kueri ini tidak terduga bagi kami. Terlepas dari kenyataan bahwa kedua tabel memiliki indeks, PostgreSQL memutuskan untuk melakukan Hash Join dengan pemindaian berurutan dari sebuah tabel besar. Pemindaian tabel besar secara berurutan menghabiskan sebagian besar waktu permintaan.
Rencana Eksekusi Permintaan Lambat
Awalnya saya curiga ini mungkin karena fragmentasi. Tetapi setelah memeriksa data, saya menyadari bahwa data hanya ditambahkan ke tabel ini dan praktis tidak dihapus dari sana. Karena membersihkan tempat dengan VACUUM tidak akan banyak membantu di sini, saya mulai menggali lebih jauh. Kemudian saya mencoba permintaan yang sama pada klien lain dengan waktu respons yang baik. Yang mengejutkan saya, rencana eksekusi permintaan tampak sangat berbeda!
Rencana eksekusi untuk permintaan yang sama pada klien lain
Menariknya, aplikasi A hanya mendapat akses ke data 10 kali lebih banyak daripada aplikasi B, tetapi waktu responsnya 3.000 kali lebih lama.
Untuk melihat rencana kueri PostgreSQL alternatif, saya mematikan koneksi hash dan memulai kembali kueri.
Rencana eksekusi alternatif untuk kueri lambat
Baik di sini! Permintaan yang sama selesai 50 kali lebih cepat saat menggunakan loop bersarang alih-alih bergabung hash. Jadi mengapa PostgreSQL memilih paket terburuk untuk aplikasi A?
Dengan melihat lebih dekat pada perkiraan biaya dan waktu tunggu aktual untuk kedua rencana, estimasi rasio biaya dan waktu tunggu aktual sangat berbeda. Penyebab utama perbedaan ini adalah perkiraan biaya pemindaian sekuensial. PostgreSQL memperkirakan bahwa pemindaian berurutan akan lebih baik dari 4000+ pemindaian indeks, tetapi pada kenyataannya, pemindaian indeks 50 kali lebih cepat.
Ini mengarahkan saya ke
opsi konfigurasi
random_page_cost dan
seq_page_cost . Nilai PostgreSQL default adalah
4 dan
1 untuk
random_page_cost ,
seq_page_cost , yang dikonfigurasi untuk HDD, di mana akses acak ke disk lebih mahal daripada akses berurutan. Namun, biaya ini tidak akurat untuk penggunaan kami menggunakan volume
EBS gp2 , yang merupakan solid state drive. Untuk penyebaran kami, akses acak dan berurutan hampir sama.
Saya mengubah nilai
random_page_cost menjadi
1 dan mencoba
kembali permintaan. Kali ini, PostgreSQL menggunakan Nested Loop, dan kueri berjalan 50 kali lebih cepat. Setelah perubahan, kami juga melihat penurunan signifikan dalam waktu respons maksimum dari PostgreSQL.
Kinerja keseluruhan dari permintaan yang lambat telah meningkat secara signifikan.
Jika Anda menggunakan SSD dan menggunakan PostgreSQL dengan konfigurasi default, saya menyarankan Anda untuk mencoba
mengatur random_page_cost dan
seq_page_cost . Anda mungkin terkejut dengan peningkatan kinerja yang dramatis.
Saya sendiri, saya akan menambahkan bahwa saya menetapkan parameter minimum
seq_page_cost = random_page_cost = 0,1 untuk memberikan prioritas pada data dalam memori (cache) dibandingkan operasi prosesor, karena saya telah mengalokasikan sejumlah besar RAM untuk PostgreSQL (ukuran RAM melebihi ukuran database pada disk). Tidak terlalu jelas mengapa komunitas postgres masih menggunakan pengaturan default yang relevan untuk server dengan sejumlah kecil RAM dan HDD, dan tidak untuk server modern. Semoga ini akan segera diperbaiki.