Tutorial Penyesuaian Kinerja PostgreSQL – Menyiapkan database dengan benar hanyalah langkah pertama dalam penyetelan kinerja PostgreSQL. Dengan setiap tabel yang Anda tambahkan dan setiap kueri yang Anda jalankan, database Anda akan memerlukan pemeliharaan dan pembaruan untuk memastikan pengoptimalan PostgreSQL yang ideal. Optimalisasi PostgreSQL cukup mudah, namun, ada beberapa hal yang perlu diketahui dari Anda, admin database, agar dapat berjalan secara efektif.
Tutorial Penyesuaian Kinerja PostgreSQL
pgsql – Ketika berbicara tentang penyetelan kinerja PostgreSQL pada aplikasi, satu aturan berlaku: jangan optimalkan lebih awal. Mulailah terlebih dahulu dengan mengimplementasikan database dan skema Anda. Kemudian setelah Anda mendapatkan penyiapan optimal, Anda dapat mulai memantau kueri SQL dengan alat seperti Retrace . Ini penting karena penyetelan kinerja PostgreSQL adalah tentang pertukaran.
Baca Juga : Cara Menyebarkan PostgreSQL sebagai Wadah Docker
Anda mungkin menerima kueri yang berjalan lambat di satu bagian aplikasi Anda yang tidak terlalu sering digunakan untuk mendapatkan waktu respons secepat kilat pada kueri yang sering dilakukan. Unduh uji coba Retrace gratis Anda selama dua minggu hari ini.
Hari ini saya akan memandu Anda melalui beberapa optimasi PostgreSQL yang berbeda. Pertama, kita akan membahas beberapa opsi penyiapan dasar yang tersedia:
- Pembaruan perangkat keras —Perubahan yang dapat Anda lakukan pada server fisik
- Konfigurasi —Modifikasi pada konfigurasi PostgreSQL yang siap pakai
- Penyedotan debu —Cara-cara di mana pengaturan penyedot debu dapat meningkatkan kinerja
Setelah sistem Anda disiapkan, kami akan melihat bagaimana Anda dapat menganalisis dan meningkatkan skema Anda:
- Analisis kinerja kueri Anda —Cara menganalisis kueri individual
- Analisis log Anda —Cara mengeluarkan informasi dari sistem Anda untuk dianalisis
- Indeks untuk kinerja kueri —Cara menambahkan indeks membantu database mengoptimalkan rencana kuerinya
Bagian 1: Praktik Terbaik dan Penyiapan
Di bagian pertama artikel ini, kita akan membahas bagaimana Anda dapat mengubah pengaturan dasar Anda untuk kinerja PostgreSQL yang lebih cepat. Di bagian 2, saya akan membahas cara mengoptimalkan spesifikasi sistem Anda, seperti pengoptimalan kueri. Mari kita lakukan!
Meningkatkan perangkat keras PostgreSQL Anda
Tempat yang jelas untuk memulai dalam hal mengoptimalkan kinerja PostgreSQL adalah dengan melihat perangkat keras dari sistem itu sendiri. Beberapa faktor yang paling penting untuk database Anda adalah memori yang tersedia, CPU dan ruang disk, dan kinerja.
Ketika datang ke pembaruan perangkat keras, Anda harus mempertimbangkan hal berikut:
- Memperbarui memori Anda.
Memori adalah apa yang digunakan sistem Anda untuk secara efektif menyimpan data yang sering dibutuhkan. Penting untuk mengingat memori saat Anda mengoptimalkan kueri Anda nanti. Semakin besar cache Anda, semakin sedikit basis data Anda perlu keluar ke disk—yang dapat mengganggu kinerja. PostgreSQL mencoba menyimpan data yang paling sering diakses di memori untuk membuat peningkatan kinerja berdasarkan cara kueri Anda dilakukan dan konfigurasi yang Anda berikan. Tapi kita akan kembali ke pengoptimalan kinerja berbasis memori nanti.
- Memisahkan aplikasi dari database.
Dengan catatan peningkatan memori, jika Anda menjalankan aplikasi database di server yang sama dengan aplikasi Anda, Anda mungkin ingin mempertimbangkan untuk mengisolasinya. Akan sulit untuk benar-benar menganalisis dan meningkatkan kinerja database Anda jika komponen terpisah memengaruhi metrik dan lingkungan Anda.
Konfigurasi basis data: Apa, mengapa, dan bagaimana
PostgreSQL hadir dengan satu set konfigurasi default. Konfigurasi default ini diatur untuk kompatibilitas dan pada dasarnya merupakan tebakan terbaik yang mencoba menyesuaikan semua kemungkinan kasus penggunaan PostgreSQL. Beruntung bagi Anda, itu berarti ada beberapa potensi kemenangan cepat jika Anda mulai mengubah konfigurasi default.
Sebelum kita menyelami dan membahas beberapa pengoptimalan konfigurasi paling umum yang dapat Anda lakukan, ada baiknya menunjukkan bahwa ada beberapa alat penyetelan kinerja PostgreSQL yang tersedia—seperti PGTune — yang mencoba melakukan beberapa kerja keras konfigurasi ini untuk Anda. Efektivitasnya bervariasi, tetapi mungkin ada baiknya bereksperimen dengan delta antara output alat ini dan konfigurasi database Anda saat ini.
Konfigurasi database di PostgreSQL dibuat langsung di file konfigurasi ( postgresql.conf) , atau dengan menjalankan perintah ALTER SYSTEM . Jika Anda ingin melihat semua konfigurasi database Anda sekarang, cukup jalankan perintah SHOW sebagai berikut:
Perintah ini akan mencantumkan semua konfigurasi yang ada dan pengaturannya. Penting juga untuk dicatat bahwa konfigurasi yang berbeda hanya akan berlaku dalam kondisi tertentu, seperti memulai ulang basis data. Beberapa perubahan konfigurasi akan memerlukan restart server; yang lain akan memerlukan pemuatan ulang konfigurasi.
Ketika Anda telah membuat perubahan konfigurasi ke database Anda, Anda bisa melihat detail seperti apakah restart diperlukan untuk pembaruan konfigurasi Anda dengan menjalankan perintah berikut:
SELECT * FROM pg_settings WHERE pending_restart = true ;
Perubahan konfigurasi penyetelan kinerja PostgreSQL
Anda mungkin ingin memodifikasi banyak konfigurasi yang berbeda untuk mendapatkan hasil maksimal dari database PostgreSQL Anda. Mari kita lihat beberapa konfigurasi utama yang dapat Anda ubah untuk mendapatkan lebih banyak kinerja dari sistem Anda.
max_koneksi
Koneksi adalah cara aplikasi Anda berkomunikasi dengan database Anda. Setiap koneksi akan memerlukan beberapa obrolan dan pengaturan komunikasi untuk dibangun. Namun, setelah dibuat, kueri dapat dikirim ke database Anda. Database PostgreSQL Anda akan datang dengan jumlah koneksi default, yang dapat Anda ubah. Anda ingin memastikan bahwa aplikasi Anda tidak terhubung secara tidak perlu, karena hal ini dapat memakan biaya dan dapat memengaruhi kinerja. Alokasi memori dilakukan pada basis per-koneksi, jadi Anda ingin menyeimbangkan antara mengalokasikan memori dan jumlah koneksi.
checkpoint_segments
Pos pemeriksaan adalah tindakan berkala yang menyimpan informasi tentang sistem Anda. Secara default, pos pemeriksaan akan berjalan setelah sejumlah segmen, tetapi tergantung pada sistem Anda, Anda mungkin ingin meningkatkan nilai ini. Kami akan berbicara nanti di artikel tentang bagaimana Anda dapat keluar dari data pos pemeriksaan Anda, tetapi konfigurasi pos pemeriksaan penting karena ini bisa menjadi operasi yang cukup mahal. Konfigurasi default sering dianggap terlalu agresif dan melakukan pos pemeriksaan terlalu sering, jadi Anda mungkin ingin meningkatkan nilai ini untuk membuat pos pemeriksaan lebih jarang.
work_mem
Seperti yang saya sebutkan sebelumnya, alokasi dan manajemen memori adalah bagian besar dari penyetelan kinerja PostgreSQL. Jika sistem Anda melakukan banyak pengurutan kompleks, meningkatkan memori pengurutan dapat membantu database mengoptimalkan konfigurasinya untuk pengaturan Anda. Hal ini memungkinkan PostgreSQL untuk menyimpan lebih banyak data dalam memori saat melakukan penyortiran, dibandingkan dengan membuat panggilan mahal ke disk.
random_page_cost
Pengaturan ini pada dasarnya adalah jumlah waktu yang harus dihabiskan pengoptimal Anda untuk membaca memori sebelum menjangkau disk Anda. Anda harus mengubah setelan ini hanya jika Anda telah melakukan pengoptimalan berbasis rencana lain yang akan segera kami bahas, seperti menyedot debu, mengindeks, atau mengubah kueri dan skema Anda.
Ini hanyalah beberapa pengoptimalan yang dapat Anda lakukan untuk konfigurasi database, tetapi masih banyak lagi . Sekarang setelah Anda tahu cara mengubah pengaturan basis data Anda, mari kita lihat area lain untuk diselidiki: menyedot debu.
Gunakan vacuuming untuk menghindari bloat
Area berikutnya yang kita lihat adalah menyedot debu. Vakum adalah pemindaian yang menandai tupel tidak lagi diperlukan sehingga dapat ditimpa. Kegagalan untuk melakukan ini dapat berarti bahwa Anda memiliki tupel mati yang tertinggal di sistem Anda. Tuple mati ini sering disebut bloat. Bloat sebagian besar berasal dari catatan yang sedang dihapus, diperbarui, atau dimasukkan.
PostgreSQL mengatur penyedotan debu secara default, tetapi sama seperti Anda dapat mengonfigurasi pengaturan lain, Anda juga dapat mengatur pengaturan penyedotan debu. Anda bahkan dapat mengatur pengaturan penyedotan debu pada basis per-tabel untuk pengoptimalan yang lebih halus.
Jika Anda ingin melihat riwayat penyedot debu yang dijalankan sebelumnya, Anda dapat melakukannya dengan menjalankan perintah berikut:
PILIH * DARI pg_stat_user_tables
Secara umum, menyedot debu secara teratur tidak cukup. Penyedotan debu yang lebih sering membuat kembung seminimal mungkin dan memastikan bahwa kinerja basis data tetap tinggi. Meskipun autovacuuming sudah diatur, Anda mungkin ingin mengubah pengaturan menjadi lebih agresif.
Bagian 2: Menganalisis Kinerja PostgreSQL
Konfigurasi sistem di muka akan membatasi peningkatan kinerja PostgreSQL. Meskipun Anda dapat melakukan beberapa konfigurasi di muka, banyak keputusan konfigurasi Anda akan didorong oleh data dan analisis yang Anda miliki tentang sistem Anda. Anda ingin mengoptimalkan cara penyiapannya, mengetahui cara kuerinya, dan mengoptimalkannya berdasarkan kasus penggunaan ini. PostgreSQL dan komunitas memberi kami beberapa alat praktis untuk melakukan pengoptimalan ini.
Perbarui data algoritma Postgres dengan ANALYZE
Sebelum melanjutkan, penting untuk memahami siklus hidup kueri. Siklus hidup adalah apa yang terjadi dari permintaan awal ke database hingga respons yang dikirimnya. PostgreSQL tidak hanya memasukkan tangannya ke dalam kantong besar memori. Saat Anda memiliki banyak data, pengambilan data secara kasar dapat menyebabkan penurunan performa. Jika Anda memindai database Anda secara berurutan (sering disebut pemindaian tabel) untuk data Anda, kinerja Anda akan diskalakan secara linier— lebih banyak baris, kinerja lebih lambat. Tapi kita bisa melakukan lebih baik dari itu.
Jadi apa siklus hidup kueri? Awalnya, ada transmisi string kueri ke PostgreSQL. String kueri kemudian diuraikan dan rencana dibuat. Rencana sangat penting. Rencana adalah langkah-langkah yang akan diambil PostgreSQL untuk menemukan data yang diminta. Bagaimana Anda mengatur konfigurasi database Anda, skema Anda, dan indeks Anda (lebih lanjut tentang indeks segera) semua akan mempengaruhi seberapa kinerja rencana ini. Jadi, penting bagi kita untuk memahami rencana dan cara mengoptimalkannya. Akhirnya, database akan melakukan rencana dan mengambil data.
Perbedaan dapat terjadi antara paket database yang ingin digunakan PostgreSQL untuk mendapatkan data Anda dan cara sebenarnya mengambil data Anda. Ini karena PostgreSQL mendasarkan rencananya pada metrik dan statistik yang jarang diperbarui. Beberapa data harus dimutakhirkan secara berkala agar statistik yang digunakan untuk rencana tetap mutakhir.
Di sinilah ANALYZE masuk. Menjalankan perintah ANALYZE memperbarui statistik ini sehingga Postgres memiliki kumpulan data baru tentang cara membuat rencananya. Jadi, jika Anda memperbarui tabel atau skema atau menambahkan indeks, ingatlah untuk menjalankan perintah ANALYZE setelahnya sehingga perubahan akan diterapkan.
Sangat memahami kinerja kueri Anda
Salah satu area jelas berikutnya yang harus dituju adalah pengoptimalan kueri. Kueri yang Anda jalankan mungkin tidak efisien karena berbagai alasan, jadi kami memerlukan cara untuk memahami apa yang terjadi dengan kueri ini—di situlah perintah EXPLAIN masuk.
EXPLAIN memberi Anda perincian yang tepat tentang bagaimana PostgreSQL akan mengeksekusi kueri Anda. Rencana tersebut didasarkan pada statistik tentang tabel, dan ini memperhitungkan hal-hal seperti indeks basis data untuk menemukan jalur paling efisien ke data Anda. Tapi EXPLAIN hanya akan menebak dan memberi Anda rencana yang menurutnya akan dijalankan. Anda dapat menjalankan EXPLAIN ANALYZE untuk mendapatkan tidak hanya informasi tentang rencana yang diprediksi, tetapi juga pembaruan tentang kinerja kueri.
Saat Anda memiliki rencana yang coba dijalankan oleh database, Anda dapat mulai menganalisisnya. Beberapa alat ada untuk membuat hidup lebih mudah, seperti PEV, yang memungkinkan Anda untuk memvisualisasikan dan memahami output dari perintah EXPLAIN Anda.
Log sebagai sumber data penyetelan kinerja PostgreSQL berkualitas tinggi
Mari kita asumsikan bahwa Anda memiliki database yang berjalan dan Anda ingin men-debug kinerja yang lambat dalam aplikasi. Salah satu cara Anda ingin melakukan ini adalah melalui log. Log adalah pernyataan informasi singkat yang ditinggalkan aplikasi saat melakukan suatu tindakan. Anda kemudian dapat mengumpulkan tindakan ini dan menganalisisnya dalam alat manajemen log seperti Retrace untuk mendapatkan gambaran tentang bagaimana sistem berperilaku.
Secara default, aplikasi Anda tidak mencatat semua data. Ini karena peningkatan logging juga berpengaruh pada kinerja database. Jadi, sementara kita membahas bagaimana Anda dapat mengubah pengaturan log untuk menganalisis kinerja , ingatlah bahwa pengaturan log itu sendiri sebenarnya dapat memengaruhi kinerja.
Log dipancarkan ke file di sistem Anda, tergantung pada konfigurasi Anda. Ketika Anda telah menemukan di mana log Anda berada, Anda dapat menggunakan alat seperti Retrace untuk menganalisis log ini. Retrace akan memberi tahu Anda statistik seperti kueri yang paling sering dijalankan, berapa lama rata-rata kueri, dan seterusnya. Metrik agregat ini dapat memberi Anda pemahaman yang lebih baik tentang di mana Anda mungkin mengalami kemacetan kinerja dalam sistem Anda.
Jadi, sekarang setelah kita melihat betapa hebatnya log untuk memahami kinerja sistem, bagaimana kita mengaturnya atau mengubahnya?
log_line_prefix
Ini adalah format log database Anda. Untuk alat seperti PGBadger, mereka membutuhkan data yang kaya dan juga perlu mengetahui format data. Awalan baris log Anda memberi tahu PostgreSQL format apa untuk mengeluarkan data lognya. Jika Anda ingin melakukan analisis log yang berarti, Anda harus menyetel nilai ini agar kompatibel dengan alat yang Anda gunakan untuk menganalisis log Anda.
log_pernyataan
Ini adalah level logging yang Anda operasikan. Level log biasanya mengacu pada level detail yang Anda inginkan di log Anda. Misalnya, apakah Anda benar-benar menginginkan semua log, atau hanya log dari jenis kritis tertentu? log_statement memiliki beberapa pengaturan yang berbeda, termasuk:
- ddl , yang hanya mencatat perubahan struktural ke database
- mod , yang mencatat modifikasi ke data yang ada (pada dasarnya semuanya kecuali SELECT)
- semua , yang mencatat … semuanya
log_checkpoints
Seperti yang telah kita bahas di pengaturan konfigurasi, pos pemeriksaan di PostgreSQL adalah tindakan berkala yang menyimpan data tentang sistem Anda. Pos pemeriksaan log ini, jika berlebihan, dapat menyebabkan penurunan kinerja. Jika Anda menduga hal ini mungkin terjadi, dengan mengaktifkan pos pemeriksaan log, Anda akan dapat melihat banyak data tentang pos pemeriksaan tersebut, seperti seberapa sering pos pemeriksaan tersebut berjalan dan apa yang dapat memicunya.
logging_connection
Anda juga mungkin ingin mengetahui informasi tentang koneksi. Jika Anda hanya memiliki satu aplikasi yang terhubung ke database Anda, tetapi Anda melihat banyak koneksi bersamaan, mungkin ada sesuatu yang salah. Terlalu banyak koneksi yang membanjiri database Anda juga dapat berarti bahwa permintaan gagal mencapai database dan dapat memengaruhi pengguna akhir aplikasi Anda.