PostgreSQL Performance Tuning Untuk Eksekusi Kueri Lebih Cepat

Objektif

Tujuan kami adalah untuk membuat eksekusi kueri boneka berjalan lebih cepat pada database PostgreSQL hanya menggunakan alat bawaan yang tersedia
dalam database.

Sistem Operasi dan Versi Perangkat Lunak

• Sistem operasi: Red Hat Enterprise Linux 7.5
• Perangkat lunak: PostgreSQL Server 9.2

Persyaratan

PASTGRESQL Server Base Instal dan berjalan. Akses ke alat baris perintah psql dan kepemilikan database contoh.

Konvensi

• # - mensyaratkan perintah linux yang diberikan untuk dieksekusi dengan hak istimewa root baik secara langsung sebagai pengguna root atau dengan menggunakan sudo memerintah
• $ - Diberikan perintah linux untuk dieksekusi sebagai pengguna biasa

Perkenalan

PostgreSQL adalah basis data sumber terbuka yang andal yang tersedia di banyak repositori distribusi modern. Kemudahan penggunaan, kemampuan untuk menggunakan ekstensi dan stabilitas yang diberikannya semuanya menambah popularitasnya.
Saat memberikan fungsi dasar, seperti menjawab pertanyaan SQL, menyimpan data yang dimasukkan secara konsisten, menangani transaksi, dll. Sebagian besar solusi basis data yang matang menyediakan alat dan pengetahuan tentang cara
Tune database, identifikasi kemungkinan kemacetan, dan dapat menyelesaikan masalah kinerja yang pasti akan terjadi saat sistem yang ditenagai oleh solusi yang diberikan tumbuh.

PostgreSQL tidak terkecuali, dan dalam hal ini
Panduan Kami akan menggunakan alat bawaan menjelaskan Untuk membuat kueri berjalan lambat lengkap lebih cepat. Ini jauh dari database dunia nyata, tetapi orang dapat mengambil petunjuk tentang penggunaan alat bawaan. Kami akan menggunakan Versi Server PostgreSQL 9.2 di Red Hat Linux 7.5, tetapi alat yang ditunjukkan dalam panduan ini hadir dalam versi basis data yang jauh lebih tua dan versi sistem operasi juga.

Masalah yang harus diselesaikan

Pertimbangkan tabel sederhana ini (nama kolom ini jelas):

foobardb =# \ d+ tabel karyawan "publik.Karyawan "Kolom | Ketik | Pengubah | Penyimpanan | Target Statistik | Deskripsi ------------------+---------+------- ----------------------------------------------+--- -------+--------------+------------- Emp_id | numerik | tidak null default nextval ('karyawan_seq' :: RegClass) | Main | | First_name | Teks | Tidak Null | diperpanjang | | last_name | Teks | tidak null | diperpanjang | | lahir_year | numerik | tidak null | main | | lahir_month | numerik | tidak null | main | | lahir_dayofmonth | numerik | Not Null | Main | | Indeks: "karyawan_pkey" kunci utama, btree (emp_id) memiliki oid: tidak 

Dengan catatan seperti:

foobardb =# Pilih * dari Batas Karyawan 2; emp_id | first_name | last_name | Birth_year | Birth_month | lahir_dayofmonth --------+------------+-----------+------------+- -----------+------------------ 1 | Emily | James | 1983 | 3 | 20 2 | John | Smith | 1990 | 8 | 12 

Dalam contoh ini kami adalah perusahaan yang baik, dan menggunakan aplikasi bernama HBAPP yang mengirimkan email "selamat ulang tahun" kepada karyawan pada hari ulang tahunnya. Aplikasi ini menanyakan database setiap pagi untuk menemukan penerima untuk hari itu (sebelum jam kerja, kami tidak ingin membunuh database SDM kami karena kebaikan).
Aplikasi menjalankan kueri berikut untuk menemukan penerima:

foobardb =# pilih emp_id, first_name, last_name dari karyawan di mana lahir_month = 3 dan lahir_dayofmonth = 20; emp_id | first_name | last_name --------+------------+----------- 1 | Emily | James 

Semua berfungsi dengan baik, pengguna mendapatkan surat mereka. Banyak aplikasi lain menggunakan database, dan tabel karyawan di dalam, seperti akuntansi dan BI. Perusahaan yang bagus tumbuh, dan dengan demikian menumbuhkan meja karyawan. Pada saatnya aplikasi berjalan terlalu lama, dan eksekusi tumpang tindih dengan dimulainya jam kerja yang menghasilkan waktu respons basis data yang lambat dalam aplikasi kritis misi. Kami harus melakukan sesuatu untuk membuat kueri ini berjalan lebih cepat, atau aplikasi akan tidak diajukan, dan dengan itu akan ada sedikit kebaikan di perusahaan yang baik.

Untuk contoh ini kami tidak akan menggunakan alat canggih apa pun untuk menyelesaikan masalah, hanya satu yang disediakan oleh pemasangan dasar. Mari kita lihat bagaimana perencana database menjalankan kueri dengan menjelaskan.

Kami tidak menguji dalam produksi; Kami membuat database untuk pengujian, membuat tabel, dan memasukkan dua karyawan ke dalamnya yang disebutkan di atas. Kami menggunakan nilai yang sama untuk kueri selama ini dalam tutorial ini,
Jadi di setiap menjalankan, hanya satu rekor yang akan cocok dengan kueri: Emily James. Kemudian kami menjalankan kueri dengan sebelumnya Jelaskan analisis Untuk melihat bagaimana itu dieksekusi dengan data minimal dalam tabel:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari karyawan di mana lahir_month = 3 dan lahir_dayofmonth = 20; Rencana Kueri ------------------------------------------------- -------------------------------------------------- --- pemindaian seq pada karyawan (biaya = 0.00… 15.40 baris = 1 lebar = 96) (waktu aktual = 0.023… 0.025 baris = 1 loops = 1) Filter: ((lahir_month = 3 :: numeric) dan (lahir_dayofmonth = 20 :: numeric)) baris dihapus dengan filter: 1 total runtime: 0.076 ms (4 baris) 

Itu sangat cepat. Mungkin secepat saat perusahaan pertama kali menggunakan HBAPP. Mari kita meniru keadaan produksi saat ini foobardb Dengan memuat sebanyak mungkin karyawan (palsu) ke dalam database seperti yang kami miliki dalam produksi (Catatan: kita akan memerlukan ukuran penyimpanan yang sama di bawah database pengujian seperti dalam produksi).

Kami akan menggunakan Bash untuk mengisi database pengujian (dengan asumsi kami memiliki 500.000 karyawan dalam produksi):

$ untuk j di 1 ... 500000; do echo "masukkan ke karyawan (first_name, last_name, lahir_year, lahir_month, lahir_dayofmonth) nilai ('pengguna $ j', 'tes', 1900,01,01);"; selesai | psql -d foobardb 

Sekarang kami memiliki 500002 karyawan:

foobardb =# pilih Count (*) dari karyawan; Count -------- 500002 (1 baris) 

Mari Jalankan Kueri Jelaskan Lagi:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari karyawan di mana lahir_month = 3 dan lahir_dayofmonth = 20; Rencana Kueri ------------------------------------------------- -------------------------------------------------- -------- SEQ scan pada karyawan (biaya = 0.00 ... 11667.63 baris = 1 lebar = 22) (waktu aktual = 0.012… 150.998 baris = 1 loop = 1) Filter: ((lahir_month = 3 :: numerik) dan (lahir_dayofmonth = 20 :: numeric)) Baris dihapus dengan filter: 500001 Total runtime: 151.059 ms 

Kami masih memiliki satu pertandingan, tetapi permintaannya secara signifikan lebih lambat. Kita harus melihat simpul pertama perencana: SEQ SCAN yang merupakan singkatan dari Sequential Scan - database membaca keseluruhan
meja, sementara kita hanya membutuhkan satu catatan, seperti a grep akan masuk pesta. Faktanya, itu sebenarnya bisa lebih lambat dari grep. Jika kami mengekspor tabel ke file CSV yang dipanggil /tmp/exp500k.CSV:

 foobardb =# salin karyawan ke '/tmp/exp500k.CSV 'Pembatas', 'header CSV; Salin 500002 

Dan grep informasi yang kami butuhkan (kami mencari hari ke -20 bulan ke -3, dua nilai terakhir dalam file CSV di setiap
garis):

$ time grep ", 3,20" /tmp /exp500k.CSV 1, Emily, James, 1983,3,20 NYATA 0M0.067S Pengguna 0M0.018S SYS 0M0.010S 

Ini, di samping caching, dianggap lebih lambat dan lebih lambat saat meja tumbuh.

Solusinya adalah penyebab pengindeksan. Tidak ada karyawan yang dapat memiliki lebih dari satu tanggal lahir, yang terdiri dari satu orang tahun lahir, bulan lahir Dan Birth_dayofmonth - Jadi ketiga bidang ini memberikan nilai unik untuk pengguna tertentu. Dan pengguna diidentifikasi olehnya emp_id (Mungkin ada lebih dari satu karyawan di perusahaan dengan nama yang sama). Jika kami mendeklarasikan kendala pada keempat bidang ini, indeks implisit akan dibuat juga:

foobardb =# alter Table karyawan Tambahkan kendala Birth_uniq unik (emp_id, lahir_year, lahir_month, lahir_dayofmonth); PEMBERITAHUAN: ALTER TABLE / TAMBAHAN UNIK AKAN MEMBUAT INDEKS IMPLIST "BILLY_UTAQ" untuk tabel "karyawan" 

Jadi kami mendapat indeks untuk empat bidang, mari kita lihat bagaimana kueri kami berjalan:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari karyawan di mana lahir_month = 3 dan lahir_dayofmonth = 20; Rencana Kueri ------------------------------------------------- -------------------------------------------------- ---------- SEQ scan pada karyawan (biaya = 0.00 ... 11667.19 baris = 1 lebar = 22) (waktu aktual = 103.131… 151.084 Baris = 1 Loops = 1) Filter: ((Lahir_Month = 3 :: numerik) dan (lahir_dayofmonth = 20 :: numeric)) Baris dihapus dengan filter: 500001 Total runtime: 151.103 ms (4 baris) 

Itu identik dengan yang terakhir, dan kita bisa melihat rencananya sama, indeks tidak digunakan. Mari kita buat indeks lain dengan kendala unik emp_id, bulan lahir Dan Birth_dayofmonth Hanya (setelah semua, kami tidak meminta tahun lahir di hbapp):

foobardb =# ubah karyawan tabel menambahkan kendala lahir_uniq_m_dom unik (emp_id, lahir_month, lahir_dayofmonth); PEMBERITAHUAN: ubah tabel / tambahkan unik akan membuat indeks implisit "lahir_uniq_m_dom" untuk tabel "karyawan" 

Mari kita lihat hasil penyetelan kita:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari karyawan di mana lahir_month = 3 dan lahir_dayofmonth = 20; Rencana Kueri ------------------------------------------------- -------------------------------------------------- --------- SEQ scan pada karyawan (biaya = 0.00 ... 11667.19 baris = 1 lebar = 22) (waktu aktual = 97.187… 139.858 baris = 1 loops = 1) Filter: ((lahir_month = 3 :: numerik) dan (lahir_dayofmonth = 20 :: numeric)) baris dihapus dengan filter: 500001 Total runtime: 139.879 ms (4 baris) 

Tidak ada. Perbedaan di atas berasal dari penggunaan cache, tetapi rencananya sama. Ayo melangkah lebih jauh. Selanjutnya kami akan membuat indeks lain emp_id Dan bulan lahir:

foobardb =# ubah karyawan tabel tambahkan kendala bilah_uniq_m unik (emp_id, lahir_month); PEMBERITAHUAN: ubah tabel / tambahkan unik akan membuat indeks implisit "lahir_uniq_m" untuk tabel "karyawan" 

Dan jalankan pertanyaan lagi:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari karyawan di mana lahir_month = 3 dan lahir_dayofmonth = 20; Rencana Kueri ------------------------------------------------- -------------------------------------------------- ---------------------.00 ... 11464.19 baris = 1 lebar = 22) (waktu aktual = 0.089… 95.605 baris = 1 loop = 1) indeks cond: (lahir_month = 3 :: numeric) filter: (lahir_dayofmonth = 20 :: numerik) Total runtime: 95.630 ms (4 baris) 

Kesuksesan! Kueri 40% lebih cepat, dan kita dapat melihat bahwa rencana berubah: database tidak lagi memindai seluruh tabel, tetapi menggunakan indeks pada bulan lahir Dan emp_id. Kami menciptakan semua campuran dari empat bidang, hanya satu yang tersisa. Layak untuk dicoba:

foobardb =# ubah karyawan tabel menambahkan kendala lahir_uniq_dom unik (emp_id, lahir_dayofmonth); PEMBERITAHUAN: ubah tabel / tambahkan unik akan membuat indeks implisit "lahir_uniq_dom" untuk tabel "karyawan" 

Indeks terakhir dibuat di bidang emp_id Dan Birth_dayofmonth. Dan hasilnya adalah:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari karyawan di mana lahir_month = 3 dan lahir_dayofmonth = 20; Rencana Kueri ------------------------------------------------- -------------------------------------------------- ------------------------------ Indeks Pemindaian Menggunakan Birth_Uniq_Dom pada Karyawan (Biaya = 0.00 ... 11464.19 baris = 1 lebar = 22) (waktu aktual = 0.025… 72.394 baris = 1 loop = 1) indeks cond: (lahir_dayofmonth = 20 :: numeric) filter: (lahir_month = 3 :: numerik) Total runtime: 72.421 ms (4 baris) 

Sekarang kueri kami sekitar 49% lebih cepat, menggunakan indeks terakhir (dan hanya yang terakhir) dibuat. Tabel kami dan indeks terkait terlihat sebagai berikut:

foobardb =# \ d+ tabel karyawan "publik.Karyawan "Kolom | Ketik | Pengubah | Penyimpanan | Target Statistik | Deskripsi ------------------+---------+------- ----------------------------------------------+--- -------+--------------+------------- Emp_id | numerik | tidak null default nextval ('karyawan_seq' :: RegClass) | Main | | First_name | Teks | Tidak Null | diperpanjang | | last_name | Teks | tidak null | diperpanjang | | lahir_year | numerik | tidak null | main | | lahir_month | numerik | tidak null | main | | lahir_dayofmonth | numerik | bukan null | main | | indeks: "karyawan_pkey" kunci utama, btree (emp_id) "lahir_uniq" kendala unik, btree (emp_id, lahir_year, lahir_month, lahir_dayofonth) "lahir_uniq_dom" Kendala unik, btree (emp_id, birth_daydayofthitth) "muthype_ muthyp" muteqt "muteq. Batasan, btree (emp_id, lahir_month) "lahir_uniq_m_dom" Kendala unik, btree (emp_id, lahir_month, lahir_dayofmonth) memiliki oid: tidak 

Kami tidak membutuhkan indeks perantara yang dibuat, rencana tersebut dengan jelas menyatakan tidak akan menggunakannya, jadi kami menjatuhkannya:

foobardb =# ubah karyawan tabel drop kendala bilah belah_uniq; Ubah table foobardb =# ubah karyawan tabel drop kendala lahir_uniq_m; Ubah table foobardb =# ubah karyawan tabel drop kendala lahir_uniq_m_dom; Ubah tabel 

Pada akhirnya, tabel kami hanya memperoleh satu indeks tambahan, yang merupakan biaya rendah untuk kecepatan ganda HBAPP:

foobardb =# \ d+ tabel karyawan "publik.Karyawan "Kolom | Ketik | Pengubah | Penyimpanan | Target Statistik | Deskripsi ------------------+---------+------- ----------------------------------------------+--- -------+--------------+------------- Emp_id | numerik | tidak null default nextval ('karyawan_seq' :: RegClass) | Main | | First_name | Teks | Tidak Null | diperpanjang | | last_name | Teks | tidak null | diperpanjang | | lahir_year | numerik | tidak null | main | | lahir_month | numerik | tidak null | main | | lahir_dayofmonth | numerik | Not Null | Main | | Indeks: "karyawan_pkey" kunci utama, btree (emp_id) "lahir_uniq_dom" kendala unik, btree (emp_id, lahir_dayofmonth) memiliki oid: tidak 

Dan kami dapat memperkenalkan tuning kami pada produksi dengan menambahkan indeks yang telah kami lihat paling berguna:

ubah karyawan Table Tambahkan kendala Birth_uniq_dom Unik (emp_id, lahir_dayofmonth);

Kesimpulan

Tak perlu dikatakan bahwa ini hanya contoh boneka. Tidak mungkin Anda akan menyimpan tanggal lahir karyawan Anda di tiga bidang terpisah sementara Anda dapat menggunakan bidang jenis tanggal, memungkinkan operasi terkait tanggal dengan cara yang jauh lebih mudah daripada membandingkan nilai bulan dan hari sebagai bilangan bulat. Perhatikan juga bahwa beberapa di atas menjelaskan pertanyaan tidak sesuai dengan pengujian berlebihan. Dalam skenario dunia nyata Anda perlu menguji dampak objek basis data baru pada aplikasi lain yang menggunakan database, serta komponen sistem Anda yang berinteraksi dengan HBAPP.

Misalnya, dalam hal ini, jika kita dapat memproses tabel untuk penerima dalam 50% dari waktu respons asli, kita hampir dapat menghasilkan 200% dari email di ujung aplikasi (katakanlah, HBAPP berjalan dalam urutan untuk Semua anak perusahaan 500 perusahaan Nice Company), yang dapat menghasilkan beban puncak di tempat lain - mungkin server surat akan menerima banyak email "selamat ulang tahun" untuk disampaikan tepat sebelum mereka harus mengirimkan laporan harian ke manajemen, yang mengakibatkan penundaan pengiriman. Juga agak jauh dari kenyataan bahwa seseorang yang menyetel database akan membuat indeks dengan uji coba dan kesalahan buta - atau setidaknya, mari kita berharap ini terjadi di perusahaan yang mempekerjakan banyak orang.

Namun perhatikan, bahwa kami memperoleh peningkatan kinerja 50% pada kueri hanya menggunakan Postgresql bawaan menjelaskan fitur untuk mengidentifikasi indeks tunggal yang bisa berguna dalam situasi yang diberikan. Kami juga menunjukkan bahwa setiap basis data relasional tidak lebih baik dari pencarian teks yang jelas jika kami tidak menggunakannya seperti yang dimaksudkan untuk digunakan.

Tutorial Linux Terkait:

• Hal -hal yang harus diinstal pada ubuntu 20.04
• Ubuntu 20.04 Instalasi PostgreSQL
• Ubuntu 22.04 Instalasi PostgreSQL
• Pengantar Otomatisasi Linux, Alat dan Teknik
• Optimalisasi Kinerja Linux: Alat dan Teknik
• Hal -hal yang harus dilakukan setelah menginstal ubuntu 20.04 FOSSA FOSSA Linux
• Unduh Linux
• File Konfigurasi Linux: 30 Teratas Paling Penting
• Cara bertahan data ke postgresql di java
• Hal -hal yang harus diinstal pada Ubuntu 22.04