Menyesuaikan kinerja basis data - pengembang biasanya menyukainya atau membencinya. Saya menikmati ini dan ingin berbagi beberapa metode yang saya gunakan baru-baru ini untuk menyetel kueri yang dieksekusi dengan buruk di PostgreSQL. Metode saya tidak lengkap, melainkan buku teks bagi mereka yang hanya kesal tentang penyetelan.
Cari permintaan lambat
Cara pertama yang jelas untuk mulai menyetel adalah menemukan operator tertentu yang bekerja dengan buruk.
pg_stats_statements
Modul
pg_stats_statements adalah tempat yang tepat untuk memulai. Itu hanya melacak statistik eksekusi pernyataan SQL dan bisa menjadi cara mudah untuk menemukan pertanyaan yang tidak efisien.
Setelah Anda menginstal modul ini, tampilan sistem yang disebut
pg_stat_statements akan tersedia dengan semua propertinya. Setelah ia memiliki kesempatan untuk mengumpulkan data yang cukup, cari kueri yang memiliki nilai
total_time yang relatif tinggi
. Fokus pada operator ini terlebih dahulu.
SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
auto_explain
Modul
auto_explain juga berguna untuk menemukan permintaan yang lambat, tetapi memiliki 2 keuntungan yang jelas: ia mendaftarkan rencana eksekusi aktual dan mendukung rekaman pernyataan bersarang menggunakan opsi
log_nested_statements . Pernyataan bersarang adalah pernyataan yang dieksekusi di dalam suatu fungsi. Jika aplikasi Anda menggunakan banyak fitur, auto_explain sangat berharga untuk mendapatkan detail rencana eksekusi.
Opsi
log_min_duration mengontrol rencana eksekusi permintaan yang dicatat berdasarkan berapa lama mereka berjalan. Misalnya, jika Anda menetapkan nilai ke 1000, semua catatan yang membutuhkan waktu lebih dari 1 detik akan didaftarkan.
Penyetelan Indeks
Strategi penyetelan penting lainnya adalah untuk memastikan bahwa indeks digunakan dengan benar. Sebagai prasyarat, kita harus menyertakan Kolektor Statistik.
Postgres Statistics Collector adalah subsistem kelas satu yang mengumpulkan semua jenis statistik kinerja yang berguna.
Dengan mengaktifkan kolektor ini, Anda mendapatkan banyak
pg_stat _... tampilan yang berisi semua properti. Secara khusus, saya menemukan ini sangat berguna untuk menemukan indeks yang hilang dan tidak terpakai.
Indeks Tidak Ada
Indeks yang hilang mungkin merupakan salah satu solusi termudah untuk meningkatkan kinerja kueri. Namun, mereka bukan peluru perak dan harus digunakan dengan benar (lebih lanjut tentang ini nanti). Jika Anda memiliki pengumpul statistik yang diaktifkan, Anda dapat menjalankan kueri (
sumber ) berikut.
SELECT relname, seq_scan - idx_scan AS too_much_seq, CASE WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000 ORDER BY too_much_seq DESC;
Kueri menemukan tabel yang memiliki lebih banyak pemindaian berurutan (Pemindaian indeks) daripada pemindaian indeks - indikasi yang jelas bahwa indeks akan membantu. Ini tidak akan memberi tahu Anda kolom mana yang digunakan untuk membuat indeks, sehingga akan membutuhkan sedikit lebih banyak pekerjaan. Namun, mengetahui tabel mana yang membutuhkannya adalah langkah pertama yang baik.
Indeks yang Tidak Digunakan
Buat indeks semua entitas, bukan? Tahukah Anda bahwa indeks yang tidak digunakan dapat mempengaruhi kinerja penulisan? Alasannya adalah bahwa ketika membuat indeks Postgres, ia dibebani dengan tugas memperbarui indeks ini setelah operasi penulisan (INSERT / UPDATE / DELETE). Dengan demikian, menambahkan indeks adalah tindakan penyeimbang, karena dapat mempercepat pembacaan data (jika itu dibuat dengan benar), tetapi itu akan memperlambat operasi penulisan. Untuk menemukan indeks yang tidak digunakan, Anda dapat menjalankan kueri berikut.
SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false;
Catatan tentang statistik lingkungan pengembangan
Mengandalkan statistik dari basis data pembangunan lokal bisa menjadi masalah. Idealnya, Anda bisa mendapatkan statistik di atas dari mesin Anda atau menghasilkannya dari cadangan yang dipulihkan. Mengapa Faktor lingkungan dapat mengubah perilaku pengoptimal kueri Postgres. Dua contoh:
- ketika mesin memiliki lebih sedikit memori, PostgreSQL mungkin tidak dapat melakukan Hash Join, jika tidak maka akan dan akan melakukannya lebih cepat.
- jika tidak ada banyak baris dalam tabel (seperti dalam database pengembangan), PostgresSQL mungkin lebih memilih untuk melakukan pemindaian berurutan pada tabel daripada menggunakan indeks yang tersedia. Ketika ukuran tabel kecil, Pemindaian Seq bisa lebih cepat. (Catatan: Anda bisa berlari
SET enable_seqscan = OFF
dalam sesi sehingga pengoptimal memilih untuk menggunakan indeks, bahkan jika pemindaian berurutan bisa lebih cepat. Ini berguna ketika bekerja dengan database pengembangan yang tidak memiliki banyak data)
Memahami Rencana Eksekusi
Sekarang Anda telah menemukan beberapa pertanyaan lambat, saatnya untuk memulai kesenangan.
JELASKAN
Perintah
EXPLAIN tentu diperlukan saat mengatur pertanyaan. Dia memberi tahu Anda apa yang sebenarnya terjadi. Untuk menggunakannya, cukup tambahkan
EXPLAIN ke kueri dan jalankan. PostgreSQL akan menunjukkan kepada Anda rencana eksekusi yang digunakannya.
Saat menggunakan EXPLAIN untuk penyetelan, saya sarankan selalu menggunakan opsi
ANALYZE (
EXPLAIN ANALYZE ), karena memberikan Anda hasil yang lebih akurat. Opsi ANALYZE sebenarnya mengeksekusi pernyataan (bukan hanya mengevaluasinya), dan kemudian menjelaskannya.
Mari kita mulai dan mulai memahami output
EXPLAIN . Berikut ini sebuah contoh:
Simpul
Hal pertama yang harus dipahami adalah bahwa setiap blok indentasi dengan "->" sebelumnya (bersama dengan baris teratas) disebut node. Node adalah unit kerja logis ("langkah", jika Anda suka) dengan biaya terkait dan waktu tunggu. Biaya dan waktu yang disajikan pada setiap simpul bersifat kumulatif dan menyatukan semua simpul anak. Ini berarti bahwa garis paling atas (simpul) menunjukkan total biaya dan waktu aktual untuk seluruh operator. Ini penting karena Anda dapat dengan mudah menelusuri untuk menentukan node mana yang menjadi hambatan.
Biaya
cost=146.63..148.65
Angka pertama adalah biaya awal (biaya memperoleh catatan pertama), dan angka kedua adalah biaya pemrosesan seluruh node (total biaya dari awal hingga selesai).
Faktanya, ini adalah biaya yang estimasi PostgreSQL harus dipenuhi untuk menjalankan pernyataan. Nomor ini tidak berarti berapa lama untuk menyelesaikan permintaan, meskipun biasanya ada hubungan langsung yang diperlukan untuk menyelesaikan. Biaya adalah kombinasi dari 5 komponen kerja yang digunakan untuk mengevaluasi pekerjaan yang diperlukan: pengambilan sampel sekuensial, pengambilan sampel tidak konsisten (acak), pemrosesan baris, operator pemrosesan (fungsi) dan pencatatan indeks pemrosesan. Biaya adalah input / output dan beban prosesor, dan penting untuk mengetahui bahwa biaya yang relatif tinggi berarti PostgresSQL percaya bahwa ia harus melakukan lebih banyak pekerjaan. Pengoptimal memutuskan rencana eksekusi yang akan digunakan berdasarkan biaya. Pengoptimal memilih biaya yang lebih rendah.
Waktu yang sebenarnya
actual time=55.009..55.012
Dalam milidetik, angka pertama adalah waktu mulai (waktu untuk mengambil catatan pertama), dan angka kedua adalah waktu yang diperlukan untuk memproses seluruh simpul (total waktu dari awal hingga selesai). Mudah dimengerti, bukan?
Dalam contoh di atas, butuh 55,009 ms untuk mendapatkan catatan pertama dan 55,012 ms untuk menyelesaikan seluruh node.
Pelajari lebih lanjut tentang rencana eksekusi.
Ada beberapa artikel yang sangat bagus untuk memahami hasil EXPLAIN. Daripada mencoba menceritakannya kembali di sini, saya sarankan meluangkan waktu untuk benar-benar memahaminya dengan pergi ke 2 sumber daya yang luar biasa ini:
Minta penyetelan
Sekarang Anda tahu operator mana yang bekerja dengan buruk dan dapat melihat rencana eksekusi Anda, sekarang saatnya untuk mulai menyesuaikan permintaan Anda untuk meningkatkan kinerja. Di sini Anda membuat perubahan pada permintaan Anda dan / atau menambahkan indeks untuk mencoba mendapatkan rencana eksekusi yang lebih baik. Mulailah dengan kemacetan dan lihat apakah ada perubahan yang dapat Anda lakukan untuk mengurangi biaya dan / atau tenggang waktu.
Cache Data dan Catatan Biaya
Ketika membuat perubahan dan mengevaluasi rencana implementasi, untuk melihat apakah akan ada perbaikan, penting untuk mengetahui bahwa implementasi di masa depan mungkin bergantung pada caching data yang memberikan gambaran tentang hasil terbaik. Jika Anda menjalankan permintaan satu kali, melakukan koreksi dan menjalankannya untuk kedua kalinya, kemungkinan besar itu akan berjalan lebih cepat, bahkan jika rencana eksekusi tidak lebih menguntungkan. Ini karena PostgreSQL dapat men-cache data yang digunakan pada awal pertama, dan dapat menggunakannya pada awal kedua. Oleh karena itu, Anda harus menyelesaikan kueri setidaknya 3 kali dan rata-rata hasilnya untuk membandingkan biaya.
Hal yang saya pelajari dapat membantu meningkatkan rencana pelaksanaan:
- Indeks
- Kecualikan pemindaian sekuensial (Pemindaian Seq) dengan menambahkan indeks (jika ukuran tabel tidak kecil)
- Saat menggunakan indeks multi-kolom, pastikan Anda memperhatikan urutan Anda mendefinisikan kolom yang disertakan - Informasi Lebih Lanjut
- Coba indeks yang sangat selektif untuk data yang sering digunakan. Ini akan membuat penggunaannya lebih efisien.
- Kondisi DI MANA
- Hindari SEPERTI
- Hindari panggilan fungsi dalam klausa WHERE
- Hindari kondisi besar di ()
- BERGABUNG
- Saat bergabung dengan tabel, coba gunakan persamaan persamaan sederhana di klausa ON (mis. A.id = b.person_id). Ini memungkinkan Anda untuk menggunakan metode bergabung yang lebih efisien (mis., Hash Join, bukan Nested Loop Join)
- Konversikan subqueries ke GABUNG pernyataan jika memungkinkan, karena ini biasanya memungkinkan pengoptimal untuk memahami tujuan dan mungkin memilih rencana terbaik.
- Gunakan COMPOUNDS dengan benar: apakah Anda menggunakan GROUP BY atau DISTINCT hanya karena Anda mendapatkan hasil duplikat? Ini biasanya menunjukkan penggunaan JOIN yang tidak benar dan dapat menyebabkan biaya yang lebih tinggi.
- Jika rencana eksekusi menggunakan Hash Join, bisa sangat lambat jika estimasi ukuran tabel salah. Karena itu, pastikan statistik tabel Anda akurat dengan meninjau strategi penghisap debu.
- Hindari subqueries yang berkorelasi bila memungkinkan; mereka dapat secara signifikan meningkatkan biaya permintaan
- Gunakan EXISTS ketika memeriksa keberadaan string berdasarkan kriteria, karena mirip dengan korsleting (berhenti memproses ketika menemukan setidaknya satu kecocokan)
- Rekomendasi umum