Penalaan Prestasi PostgreSQL untuk Pelaksanaan Pertanyaan Lebih Cepat

Objektif

Objektif kami adalah untuk membuat pelaksanaan pertanyaan dummy berjalan lebih cepat pada pangkalan data PostgreSQL hanya menggunakan alat yang dibina dalam
dalam pangkalan data.

Sistem operasi dan versi perisian

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

Keperluan

Pangkalan Pelayan PostgreSQL Pasang dan berjalan. Akses ke alat baris arahan PSQL dan pemilikan pangkalan data contoh.

Konvensyen

• # - Memerlukan arahan Linux yang diberikan untuk dilaksanakan dengan keistimewaan akar sama ada secara langsung sebagai pengguna root atau dengan menggunakan sudo perintah
• $ - Memandangkan perintah Linux dilaksanakan sebagai pengguna yang tidak berkadar biasa

Pengenalan

PostgreSQL adalah pangkalan data sumber terbuka yang boleh dipercayai yang terdapat di repositori pengedaran moden. Kemudahan penggunaan, keupayaan untuk menggunakan sambungan dan kestabilan yang memberikan semua penambahan kepada popularitinya.
Semasa menyediakan fungsi asas, seperti menjawab pertanyaan SQL, menyimpan data yang dimasukkan secara konsisten, mengendalikan urus niaga, dll. Penyelesaian pangkalan data yang paling matang menyediakan alat dan pengetahuan mengenai cara
Tune pangkalan data, mengenal pasti kemungkinan kesesakan, dan dapat menyelesaikan masalah prestasi yang terikat apabila sistem yang dikuasakan oleh penyelesaian yang diberikan tumbuh.

PostgreSQL tidak terkecuali, dan dalam hal ini
panduan kami akan menggunakan alat yang dibina menjelaskan untuk membuat pertanyaan perlahan-lahan lengkap dengan lebih cepat. Ia jauh dari pangkalan data dunia sebenar, tetapi seseorang boleh mengambil petunjuk mengenai penggunaan alat yang dibina. Kami akan menggunakan versi pelayan PostgreSQL 9.2 pada topi merah linux 7.5, tetapi alat yang ditunjukkan dalam panduan ini terdapat dalam pangkalan data yang lebih lama dan versi sistem operasi.

Masalah yang perlu diselesaikan

Pertimbangkan jadual mudah ini (nama lajur adalah jelas):

foobardb =# \ d+ meja pekerja "awam.Pekerja "Lajur | Jenis | Modifiers | Penyimpanan | Sasaran Statistik | Keterangan ------------------+---------+------- ----------------------------------------------+--- -------+--------------+------------- emp_id | Numerik | Tidak Null Default NextVal ('Pekerja_Seq' :: regclass) | main | | first_name | teks | tidak null | extended | | last_name | text | not null | extended | | lahir_year | angka | not null | main | | lahir_month | angka | tidak null | main | | lahir_dayofmonth | angka | Tidak Null | Utama | | Indeks: Kunci Utama "Pekerja_PKEY", BTREE (EMP_ID) mempunyai OID: Tidak 

Dengan rekod seperti:

foobardb =# pilih * dari pekerja had 2; emp_id | First_name | last_name | BIRTH_YEAR | BIRTH_MONTH | BIRTH_DAYOFMONTH --------+------------+-----------+------------+-- -----------+------------------ 1 | Emily | James | 1983 | 3 | 20 2 | John | Smith | 1990 | 8 | 12 

Dalam contoh ini kami adalah syarikat yang bagus, dan mengerahkan aplikasi yang dipanggil HBApp yang menghantar e -mel "selamat ulang tahun" kepada pekerja pada hari lahirnya. Permohonan menanyakan pangkalan data setiap pagi untuk mencari penerima untuk hari itu (sebelum waktu kerja, kami tidak mahu membunuh pangkalan data HR kami daripada kebaikan).
Aplikasi ini menjalankan pertanyaan berikut untuk mencari penerima:

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

Semua berfungsi dengan baik, pengguna mendapat surat mereka. Banyak aplikasi lain menggunakan pangkalan data, dan jadual pekerja dalam, seperti perakaunan dan bi. Syarikat yang bagus tumbuh, dan sebagainya menanam jadual pekerja. Pada waktunya aplikasi berjalan terlalu lama, dan pelaksanaan bertindih dengan permulaan waktu kerja yang mengakibatkan masa tindak balas pangkalan data perlahan dalam aplikasi kritikal misi. Kita mesti melakukan sesuatu untuk membuat pertanyaan ini berjalan lebih cepat, atau aplikasi itu tidak akan ditugaskan, dan dengan itu akan ada kurang niceness dalam syarikat yang baik.

Untuk contoh ini, kami tidak akan menggunakan alat lanjutan untuk menyelesaikan masalah ini, hanya satu yang disediakan oleh pemasangan asas. Mari lihat bagaimana perancang pangkalan data melaksanakan pertanyaan dengan menjelaskan.

Kami tidak menguji dalam pengeluaran; Kami membuat pangkalan data untuk menguji, membuat jadual, dan memasukkan dua pekerja ke dalamnya yang disebutkan di atas. Kami menggunakan nilai yang sama untuk pertanyaan sepanjang tutorial ini,
Oleh itu, hanya satu rekod, hanya satu rekod yang sepadan dengan pertanyaan: Emily James. Kemudian kami menjalankan pertanyaan dengan sebelumnya Terangkan analisis Untuk melihat bagaimana ia dilaksanakan dengan data yang minimum dalam jadual:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari pekerja di mana lahir_month = 3 dan kelahiran_dayofmonth = 20; Pelan pertanyaan ------------------------------------------------ -------------------------------------------------- --- imbasan SEQ pada pekerja (kos = 0.00 ... 15.40 baris = 1 lebar = 96) (masa sebenar = 0.023 ... 0.025 baris = 1 gelung = 1) Penapis: ((BIRTH_MONTH = 3 :: Numerik) dan (BIRTH_DAYOFMONTH = 20 :: NUMERIC)) baris yang dikeluarkan oleh penapis: 1 Jumlah runtime: 0.076 ms (4 baris) 

Itu pantas. Mungkin secepat yang berlaku ketika syarikat pertama kali mengerahkan HBAPP. Mari meniru keadaan pengeluaran semasa Foobardb Dengan memuatkan pekerja yang banyak (palsu) ke dalam pangkalan data seperti yang kita ada dalam pengeluaran (nota: kita memerlukan saiz simpanan yang sama di bawah pangkalan data ujian seperti dalam pengeluaran).

Kami hanya akan menggunakan bash untuk mengisi pangkalan data ujian (dengan andaian kami mempunyai 500.000 pekerja dalam pengeluaran):

$ untuk j dalam 1 ... 500000; Adakah echo "masukkan ke dalam pekerja (first_name, last_name, Birth_year, Birth_month, Birth_dayofmonth) nilai ('user $ j', 'test', 1900,01,01);"; Selesai | psql -d foobardb 

Sekarang kita mempunyai 500002 pekerja:

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

Mari jalankan pertanyaan jelaskan lagi:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari pekerja di mana lahir_month = 3 dan kelahiran_dayofmonth = 20; Pelan pertanyaan ------------------------------------------------ -------------------------------------------------- -------- SEQ imbasan pada pekerja (kos = 0.00 ... 11667.63 baris = 1 lebar = 22) (masa sebenar = 0.012 ... 150.998 baris = 1 gelung = 1) Penapis: ((BIRTH_MONTH = 3 :: Numerik) dan (BIRTH_DAYOFMONTH = 20 :: NUMERIC)) Baris dikeluarkan oleh penapis: 500001 Jumlah runtime: 151.059 ms 

Kami masih mempunyai satu perlawanan, tetapi pertanyaannya lebih perlahan. Kita harus melihat nod pertama perancang: Imbasan seq yang bermaksud imbasan berurutan - pangkalan data membaca keseluruhannya
jadual, sementara kita hanya memerlukan satu rekod, seperti grep akan masuk bash. Malah, ia sebenarnya boleh lebih perlahan daripada grep. Sekiranya kita mengeksport jadual ke fail CSV yang dipanggil /tmp/exp500k.CSV:

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

Dan grep maklumat yang kami perlukan (kami mencari hari ke -20 bulan ke -3, dua nilai terakhir dalam fail CSV dalam setiap
garis):

$ time grep ", 3,20" /tmp /exp500k.CSV 1, Emily, James, 1983,3,20 Real 0m0.067S USER 0M0.018S SYS 0M0.010s 

Ini, caching diketepikan, dianggap lebih perlahan dan lebih perlahan apabila jadual tumbuh.

Penyelesaiannya adalah penyebab pengindeksan. Tidak ada pekerja yang boleh mempunyai lebih dari satu tarikh lahir, yang terdiri daripada satu tahun lahir, BIRTH_MONTH dan BIRTH_DAYOFMONTH - Oleh itu, ketiga -tiga bidang ini memberikan nilai yang unik untuk pengguna tersebut. Dan pengguna dikenal pasti olehnya emp_id (boleh ada lebih daripada satu pekerja di syarikat dengan nama yang sama). Jika kita mengisytiharkan kekangan pada empat bidang ini, indeks tersirat akan dibuat juga:

foobardb =# Alter TABLE Pekerja menambah kekangan BIRTH_UNIQ Unik (emp_id, kelahiran_year, kelahiran_month, kelahiran_dayofmonth); NOTIS: Alter Table / Tambah Unik akan mencipta indeks tersirat "BIRTE_UNIQ" untuk Jadual "Pekerja" 

Oleh itu, kami mendapat indeks untuk empat bidang, mari kita lihat bagaimana pertanyaan kami berjalan:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari pekerja di mana lahir_month = 3 dan kelahiran_dayofmonth = 20; Pelan pertanyaan ------------------------------------------------ -------------------------------------------------- ---------- SEQ imbasan pada pekerja (kos = 0.00 ... 11667.19 baris = 1 lebar = 22) (masa sebenar = 103.131 ... 151.084 baris = 1 gelung = 1) Penapis: ((BIRTH_MONTH = 3 :: Numerik) dan (BIRTH_DAYOFMONTH = 20 :: Numeric)) Baris dikeluarkan oleh penapis: 500001 Jumlah runtime: 151.103 ms (4 baris) 

Itu sama dengan yang terakhir, dan kita dapat melihat rancangannya adalah sama, indeks tidak digunakan. Mari buat indeks lain dengan kekangan yang unik emp_id, BIRTH_MONTH dan BIRTH_DAYOFMONTH Hanya (selepas semua, kami tidak meminta pertanyaan tahun lahir di HBAPP):

foobardb =# Alter Table Pekerja menambah kekangan BIRTH_UNIQ_M_DOM unik (emp_id, kelahiran_month, kelahiran_dayofmonth); NOTIS: Alter Table / Tambah Unik akan membuat indeks tersirat "BIRD_UNIQ_MOMD" untuk Jadual "Pekerja" 

Mari lihat hasil penalaan kami:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari pekerja di mana lahir_month = 3 dan kelahiran_dayofmonth = 20; Pelan pertanyaan ------------------------------------------------ -------------------------------------------------- --------- imbasan SEQ pada pekerja (kos = 0.00 ... 11667.19 baris = 1 lebar = 22) (masa sebenar = 97.187 ... 139.858 baris = 1 gelung = 1) Penapis: ((BIRTH_MONTH = 3 :: Numerik) dan (BIRTH_DAYOFMONTH = 20 :: Numeric)) baris dikeluarkan oleh penapis: 500001 Jumlah runtime: 139.879 ms (4 baris) 

Tidak ada. Perbezaan di atas datang dari penggunaan cache, tetapi rancangannya adalah sama. Mari pergi lebih jauh. Seterusnya kita akan membuat indeks lain di emp_id dan BIRTH_MONTH:

foObardB =# Alter Table Pekerja menambah kekangan BIRD_UNIQ_M Unik (emp_id, BIDNYAK_MONTH); NOTIS: Alter Table / Tambah Unik akan mencipta indeks tersirat "BIRTE_UNIQ_M" untuk Jadual "Pekerja" 

Dan jalankan pertanyaan lagi:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari pekerja di mana lahir_month = 3 dan kelahiran_dayofmonth = 20; Pelan pertanyaan ------------------------------------------------ -------------------------------------------------- ---------------------------- Indeks imbasan menggunakan BIRTH_UNIQ_M pada pekerja (kos = 0.00 ... 11464.19 baris = 1 lebar = 22) (masa sebenar = 0.089 ... 95.605 baris = 1 gelung = 1) Indeks Cond: (Birth_month = 3 :: Numerik) Penapis: (Birth_dayofmonth = 20 :: Numerik) Jumlah Runtime: 95.630 ms (4 baris) 

Kejayaan! Pertanyaannya adalah 40% lebih cepat, dan kita dapat melihat bahawa rancangan itu berubah: pangkalan data tidak mengimbas keseluruhan jadual, tetapi menggunakan indeks pada BIRTH_MONTH dan emp_id. Kami mencipta semua campuran empat bidang, hanya satu yang kekal. Patut dicuba:

foobardb =# Alter Table Pekerja menambah kekangan BIRD_UNIQ_DOM Unik (emp_id, BIRTH_DAYOFMONTH); NOTIS: Alter Table / Tambah Unik akan mencipta indeks tersirat "BIRTE_UNIQ_DOM" untuk Jadual "Pekerja" 

Indeks terakhir dibuat di medan emp_id dan BIRTH_DAYOFMONTH. Dan hasilnya ialah:

foobardb =# Jelaskan analisis pilih emp_id, first_name, last_name dari pekerja di mana lahir_month = 3 dan kelahiran_dayofmonth = 20; Pelan pertanyaan ------------------------------------------------ -------------------------------------------------- ------------------------------ Indeks imbasan menggunakan BIRTH_UNIQ_DOM pada pekerja (kos = 0.00 ... 11464.19 baris = 1 lebar = 22) (masa sebenar = 0.025 ... 72.394 baris = 1 gelung = 1) Indeks Cond: (Birth_dayOfmonth = 20 :: Numerik) Penapis: (BIRTH_MONTH = 3 :: Numerik) Jumlah Runtime: 72.421 ms (4 baris) 

Sekarang pertanyaan kami adalah kira -kira 49% lebih cepat, menggunakan indeks terakhir (dan hanya terakhir) yang dibuat. Jadual kami dan indeks yang berkaitan kelihatan seperti berikut:

foobardb =# \ d+ meja pekerja "awam.Pekerja "Lajur | Jenis | Modifiers | Penyimpanan | Sasaran Statistik | Keterangan ------------------+---------+------- ----------------------------------------------+--- -------+--------------+------------- emp_id | Numerik | Tidak Null Default NextVal ('Pekerja_Seq' :: regclass) | main | | first_name | teks | tidak null | extended | | last_name | text | not null | extended | | lahir_year | angka | not null | main | | lahir_month | angka | tidak null | main | | lahir_dayofmonth | angka | tidak null | utama | | indeks: "Pekerja_pkey" kunci utama, btree (emp_id) "kelahiran_uniq" kekangan unik, btree (emp_id, kelahiran_year, kelahiran_month, kelahiran_dayofmonth) Kekangan, BTREE (EMP_ID, BIRTH_MONTH) "BIDNYAK 

Kami tidak memerlukan indeks perantaraan yang dibuat, pelan itu menyatakan dengan jelas ia tidak akan menggunakannya, jadi kami menjatuhkannya:

foobardb =# alter meja pekerja menjatuhkan kekangan Birth_uniq; Alter jadual foobardb =# alter meja pekerja drop kekangan Birth_uniq_m; Alter jadual foobardb =# alter meja pekerja drop kekangan Birth_uniq_m_dom; Alter Jadual 

Pada akhirnya, jadual kami hanya mendapat satu indeks tambahan, yang merupakan kos rendah untuk kelajuan dua kali ganda HBAPP:

foobardb =# \ d+ meja pekerja "awam.Pekerja "Lajur | Jenis | Modifiers | Penyimpanan | Sasaran Statistik | Keterangan ------------------+---------+------- ----------------------------------------------+--- -------+--------------+------------- emp_id | Numerik | Tidak Null Default NextVal ('Pekerja_Seq' :: regclass) | main | | first_name | teks | tidak null | extended | | last_name | text | not null | extended | | lahir_year | angka | not null | main | | lahir_month | angka | tidak null | main | | lahir_dayofmonth | angka | Tidak NULL | Utama | 

Dan kami dapat memperkenalkan penalaan kami kepada pengeluaran dengan menambahkan indeks yang kami lihat paling berguna:

Alter Table Pekerja menambah kekangan BIRD_UNIQ_DOM unik (emp_id, kelahiran_dayofmonth);

Kesimpulan

Tidak perlu dikatakan bahawa ini hanya contoh dummy. Tidak mungkin anda akan menyimpan tarikh lahir pekerja anda dalam tiga bidang berasingan semasa anda boleh menggunakan medan Jenis Tarikh, membolehkan operasi berkaitan tarikh dengan cara yang lebih mudah daripada membandingkan nilai bulan dan hari sebagai bilangan bulat. Perhatikan juga bahawa beberapa di atas menjelaskan pertanyaan tidak sesuai sebagai ujian yang berlebihan. Dalam senario dunia sebenar, anda perlu menguji kesan objek pangkalan data baru pada aplikasi lain yang menggunakan pangkalan data, serta komponen sistem anda yang berinteraksi dengan HBAPP.

Sebagai contoh, dalam kes ini, jika kita boleh memproses jadual untuk penerima dalam 50% masa tindak balas asal, kita boleh menghasilkan 200% daripada e -mel di hujung aplikasi yang lain (katakan, HBAPP berjalan secara urutan untuk Semua 500 syarikat anak syarikat Nice Company), yang mungkin mengakibatkan beban puncak di tempat lain - mungkin pelayan mel akan menerima banyak e -mel "selamat ulang tahun" untuk menyampaikan sebelum mereka menghantar laporan harian kepada pengurusan, mengakibatkan kelewatan penghantaran. Ia juga agak jauh dari realiti bahawa seseorang yang menyesuaikan pangkalan data akan membuat indeks dengan percubaan dan kesilapan buta - atau sekurang -kurangnya, mari kita berharap ini begitu dalam syarikat yang menggunakan banyak orang.

Perhatikan bagaimanapun, bahawa kami mendapat rangsangan prestasi 50% pada pertanyaan hanya menggunakan yang dibina dalam PostgreSQL menjelaskan ciri untuk mengenal pasti indeks tunggal yang boleh berguna dalam situasi yang diberikan. Kami juga menunjukkan bahawa mana -mana pangkalan data relasi tidak lebih baik daripada carian teks yang jelas jika kita tidak menggunakannya seperti yang dimaksudkan untuk digunakan.

Tutorial Linux Berkaitan:

• Perkara yang hendak dipasang di Ubuntu 20.04
• Ubuntu 20.04 Pemasangan PostgreSQL
• Ubuntu 22.04 Pemasangan PostgreSQL
• Pengenalan kepada Automasi, Alat dan Teknik Linux
• Pengoptimuman Prestasi Linux: Alat dan Teknik
• Perkara yang perlu dilakukan setelah memasang ubuntu 20.04 Focal Fossa Linux
• Muat turun linux
• Fail Konfigurasi Linux: 30 teratas yang paling penting
• Cara Mempertahankan Data ke PostgreSQL di Java
• Perkara yang perlu dipasang di Ubuntu 22.04