AQO - Optimasi Kueri Adaptif PostgreSQL

Saat menjalankan kueri, DBMS modern menggunakan model optimisasi biaya - berdasarkan pada koefisien yang disimpan dalam file konfigurasi dan statistik yang dikumpulkan, “biaya” untuk memperoleh dan volume rowset yang dihasilkan dihitung. Ketika kueri dijalankan lagi, biaya dan selektivitas dihitung ulang. Anda dapat menjalankan kueri dan melihat nilai sebenarnya dari parameter ini, namun, pengoptimal DBMS tidak menggunakan informasi ini dalam proses penjadwalan (standar).



Tetapi bagaimana jika pengoptimal menyimpan nilai riil dari biaya, selektivitas dan parameter lain yang diperlukan dari eksekusi query dan, ketika dieksekusi lagi, itu tidak hanya dipandu oleh statistik yang dikumpulkan standar, tetapi juga oleh mereka yang disimpan setelah eksekusi sebelumnya?



Ini disebut optimisasi permintaan adaptif, dan metode optimisasi ini menjanjikan. Beberapa DBMS sudah menggunakan teknologi tersebut.



Company Postgres Professional selama beberapa tahun bekerja pada ekstensi AQO untuk PostgreSQL, yang mengimplementasikan (dalam beberapa bentuk) optimasi adaptif. Pekerjaan masih berlangsung, tetapi sudah ada sesuatu untuk diuji.



Pertama, mari kita lihat lebih dekat pada area subjek optimasi kueri.

Mengapa seorang perencana dapat memilih rencana yang tidak optimal

Permintaan SQL dapat dilakukan dengan berbagai cara. Misalnya, ketika ada gabungan dua tabel, itu bisa dilakukan dalam beberapa cara berbeda - menggunakan loop bersarang, penggabungan, hashing. Semakin banyak tabel berpartisipasi dalam kueri, semakin banyak variasi dalam gabungan mereka. Tugas perencana adalah memilih rencana pelaksanaan permintaan dengan biaya terendah dari banyak variasi.



Seperti yang telah disebutkan, dalam pekerjaan mereka, penjadwal banyak DBMS menggunakan informasi statistik yang dikumpulkan baik secara otomatis atau manual. Perencana menghitung perkiraan biaya berdasarkan statistik ini.



Secara umum, penjadwal DBMS modern berfungsi dengan baik di sebagian besar situasi. Namun, dalam beberapa kasus, rencana yang dipilih mungkin sangat jauh dari optimal.



Sebagai contoh,kurangnya informasi statistik yang relevan mengarah pada fakta bahwa perencana berfokus pada (kemungkinan besar) data yang salah pada jumlah baris dalam tabel yang digabungkan. Terlalu rendahnya perkiraan (atau perkiraan yang berlebihan) dari kardinalitas mengarah pada pilihan metode yang tidak optimal untuk mengakses data dalam tabel.



Alasan penting lainnya mungkin adalah kurangnya indeks yang diperlukan . Dengan tidak adanya indeks, penjadwal memiliki pilihan terbatas metode akses data.



Penggunaan kondisi dependen (berkorelasi)juga dapat secara negatif mempengaruhi operasi DBMS. Penjadwal (secara default) menganggap bahwa semua kondisi dalam permintaan tidak tergantung satu sama lain, yaitu, nilai satu kondisi tidak memengaruhi yang lain dengan cara apa pun. Ini tidak selalu terjadi. Jika kondisi dependen digunakan (misalnya, kode pos dan kota), penjadwal juga akan menghitung biaya dan kardinalitas koneksi yang salah. Penggunaan fungsi dalam kondisi



juga dapat memengaruhi penjadwal . Fungsi untuk penjadwal adalah "kotak hitam", ia tidak tahu berapa banyak baris fungsi akan kembali, yang juga dapat menyebabkan biaya yang keliru dari rencana tersebut.

Cara untuk mempengaruhi penjadwal

Statistik aktual adalah kondisi yang sangat diperlukan untuk pekerjaan penjadwal yang memadai. Pertama-tama, pastikan bahwa sistem dikonfigurasikan untuk secara teratur mengumpulkan informasi statistik.

Ada beberapa cara untuk memperbaiki situasi di atas dan untuk membantu perencana memilih rencana eksekusi permintaan yang lebih optimal.



Tanpa indeks, perencana hanya memiliki satu cara untuk mendapatkan data - scan tabel sekuensial (dan ini tidak selalu buruk dan mahal). Dalam beberapa kasus, membuat indeks yang diperlukan dapat membantu mempercepat akses data - Anda tidak perlu memindai seluruh tabel. Tetapi penggunaan indeks (mencari yang diperlukan, pembuatan, pemeliharaan) bukanlah kesenangan gratis. Idealnya, mereka harus digunakan tepat di mana mereka dibutuhkan. Dan di mana tidak perlu - jangan gunakan.



Saat menggunakan kondisi gabungan yang berkorelasi dalam kueri, Anda dapat menghasilkan statistik yang diperluas- secara eksplisit "meminta" pengoptimal bahwa kondisi terkait satu sama lain. Untuk melakukan ini, administrator basis data (atau pengembang) perlu mengetahui datanya dengan baik dan memantau kondisi dependen dalam kueri, karena sulit untuk memperkirakan jumlah kombinasi kolom dependen terlebih dahulu. Statistik yang diperluas harus dibuat secara manual untuk setiap opsi tersebut.



Saat membuat fungsi, Anda dapat menentukan perkiraan biaya eksekusi dan / atau perkiraan jumlah garis yang dihasilkan oleh fungsi tersebut. Dalam versi 12 , menjadi mungkin untuk menggunakan fungsi bantu untuk meningkatkan perkiraan penjadwal tergantung pada argumen. Ini juga merupakan metode manual, yang tidak selalu memberikan hasil terbaik.



Jika semuanya gagal, Anda dapat menulis ulang kueri secara manual, misalnya, menggunakan representasi terwujud, Common Table Expressions (CTE). Atau untuk mengklarifikasi persyaratan bidang subjek dan, mungkin, secara radikal menulis ulang logika permintaan.



Dan ada satu lagi cara untuk "petunjuk" perencana - optimasi query adaptif ( a daptive q uery o ptimization). Gagasan di balik metode ini adalah bahwa setelah kueri dieksekusi, informasi statistik yang sebenarnya disimpan dan, ketika kueri yang diberikan (atau serupa) dieksekusi lagi, pengoptimal dapat mengandalkannya.



DBMS Postgres Pro Enterprise adalah ekstensi untuk optimasi kueri adaptif yang disebut AQO... Ekstensi ini diposting di github: github.com/postgrespro/aqo , Anda dapat mencobanya dengan vanilla PostgreSQL, lebih lanjut di bawah ini.

Bagaimana modul ini bekerja

Modul AQO menggunakan pembelajaran mesin dalam pekerjaannya. Anda dapat membaca lebih lanjut tentang prinsip operasi dalam artikel oleh Oleg Ivanov, Menggunakan Machine Learning untuk Meningkatkan Kinerja PostgreSQL, dan lebih detail dalam presentasi Adaptive Query Optimization (laporan di YouTube ).



Inti dari metode ini dijelaskan secara singkat di bawah ini:



Untuk memperkirakan biaya, perencana membutuhkan perkiraan kardinalitas, dan untuk ini, pada gilirannya, diperlukan perkiraan selektivitas kondisi.



Untuk kondisi sederhana (seperti "atribut = konstan" atau "atribut> konstan"), perencana memiliki model yang digunakan untuk memperkirakan selektivitas. Untuk melakukan ini, ia menggunakan informasi statistik: jumlah nilai atribut unik, histogram, dll.

Untuk kondisi yang terdiri dari elemen sederhana menggunakan penghubung logis, penjadwal menggunakan rumus yang mudah dihitung:

• sel (bukan A) = 1 - sel (A)
• sel (bukan A) = 1 - sel (A)
• sel (A dan B) = sel (A) * sel (B)
• sel (A atau B) = sel (bukan (bukan A dan bukan B)) = 1 - (1 - sel (A)) * (1 - sel (B))

Rumus ini mengasumsikan independensi (non-korelasi) dari kondisi A dan B, yang karenanya kami mendapatkan perkiraan yang salah dalam kasus ketika asumsi ini dilanggar.

AQO memperumit formula: rumus ini memperkenalkan koefisiennya sendiri untuk setiap kondisi sederhana. Menggunakan pembelajaran mesin (menggunakan regresi tetangga-terdekat), AQO memilih koefisien-koefisien ini sehingga selektivitas yang dihitung dengan rumus paling cocok dengan selektivitas nyata yang diamati AQO sebelumnya.



Untuk ini, modul ini menyimpan:

• , ;
• .

Dalam kerjanya, AQO membedakan antara kondisi hingga konstanta. Ini memungkinkan untuk mengurangi kompleksitas masalah yang sedang dipecahkan, dan di samping itu, dalam banyak kasus, informasi masih belum hilang: AQO tidak “melihat” nilai konstanta, tetapi “melihat” selektivitas kondisi.

Situasi di mana kerugian memang terjadi, ini adalah kondisi yang dievaluasi oleh sebuah konstanta terlepas dari nilai-nilai spesifik. Misalnya, untuk beberapa kondisi perencana tidak dapat membuat perkiraan yang masuk akal dan memilih konstanta default (misalnya, selektivitas kondisi "ekspresi1 = ekspresi2" selalu mengevaluasi menjadi 0,005, dan "ekspresi1> ekspresi2" sebagai 1/3).



Dengan demikian, AQO meningkatkan estimasi selektivitas kondisi kompleks (dan, sebagai konsekuensinya, estimasi biaya, yang dapat mengarah pada pemilihan rencana eksekusi yang lebih memadai).

Memasang modul

Untuk mencoba fungsionalitas modul pada vanilla PostgreSQL, Anda perlu menggunakan tambalan khusus dan kemudian membangun sistem dari sumber. Baca lebih lanjut di file README di github.



Jika Postgres Pro Enterprise digunakan, pemasangan modul AQO akan dilanjutkan dalam mode standar:



shared_preload_libraries = 'aqo'



Setelah itu, Anda dapat membuat ekstensi dalam database yang diperlukan.

Mempersiapkan database

Mari kita lihat contoh konkret tentang cara kerja modul AQO dalam database demo . Kami akan menggunakan database besar yang berisi informasi penerbangan untuk tahun ini, dari September 2016 hingga September 2017.



Pertama, buat ekstensi:

CREATE EXTENSION aqo;

Selanjutnya, matikan pemrosesan kueri paralel sehingga tampilan paket paralel tidak mengalihkan tugas utama:



max_parallel_workers_per_gather = 0;



Agar penjadwal PostgreSQL memiliki lebih banyak opsi untuk bergabung dengan tabel, buat dua indeks:

CREATE INDEX ON flights (scheduled_departure );
CREATE INDEX ON ticket_flights (fare_conditions );

Saat menganalisis hasil, kami akan fokus pada nilai BUFFERS sebagai jumlah halaman yang harus dibaca untuk melakukan pekerjaan itu. Mari kita juga melihat waktu eksekusi (tetapi waktu pada sistem yang dimuat dan pada laptop di rumah bisa sangat berbeda).



Mari tingkatkan cache buffer dan work_mem agar semua pekerjaan dilakukan dalam RAM:



shared_buffers = '256MB';

work_mem = '256MB';

Menggunakan modul AQO

Kami akan merumuskan permintaan: diperlukan untuk menerima penumpang yang terbang kelas bisnis dari tanggal tertentu, dan tiba terlambat dengan tidak lebih dari satu jam.

Mari kita jalankan query tanpa menggunakan AQO (selanjutnya, beberapa informasi yang tidak mempengaruhi pemahaman tentang operasi modul telah dihapus dari rencana):

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT t.ticket_no
  FROM flights f
   	JOIN ticket_flights tf ON f.flight_id = tf.flight_id
   	JOIN tickets t ON tf.ticket_no = t.ticket_no
 WHERE f.scheduled_departure > '2017-08-01'::timestamptz
   AND f.actual_arrival < f.scheduled_arrival + interval '1 hour'
   AND tf.fare_conditions = 'Business';

Dan mari kita lihat rencana yang dihasilkan: Dalam hal ini, perencana mempertimbangkan rencana optimal, di mana, pertama, menggunakan pemindaian bitmap ( ), kita mendapatkan satu set baris dari tabel penerbangan, yang kita miliki (sebuah simpul ) dengan satu set baris dari tabel ticket_flight yang diperoleh menggunakan indeks memindai ( ). Hasil yang dihasilkan akan digunakan sebagai rowset luar untuk join nested loop join (node ). Set dalam untuk gabungan ini diperoleh menggunakan pemindaian indeks eksklusif pada tabel tiket ( ). Operasi yang paling "besar" adalah mendapatkan rowset internal untuk Nested Loop - 106 205 buffer dibaca.



Nested Loop (rows=33210) (actual rows=31677)

  Buffers: shared hit=116830 read=1

  ->  Hash Join (rows=33210) (actual rows=31677)

        Hash Cond: (tf.flight_id = f.flight_id)

        ->  Index Scan ... on ticket_flights tf  

              Index Cond: fare_conditions = 'Business'

        ->  Hash

              ->  Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

                    Recheck Cond: scheduled_departure > '2017-08-01'

                    Filter: actual_arrival < scheduled_arrival + '01:00:00'::interval

                    ->  Bitmap Index Scan on ... [flights]

                          Index Cond: scheduled_departure > '2017-08-01'

                          Buffers: shared hit=44 read=1

  ->   Index Only Scan  ... on tickets t (rows=1 width=14) (actual rows=1 loops=31677)

        Index Cond: (ticket_no = tf.ticket_no)

        Buffers: shared hit=106205

 Planning Time: 9.326 ms

 Execution Time: 675.836 ms



Bitmap Heap Scan on flightsHash JoinIndex Scan ... on ticket_flightsNested LoopIndex Only Scan ... on tickets





Paket ini bisa disebut relatif baik, karena sejumlah kecil baris dalam set eksternal diproses oleh gabungan loop bersarang.



Sekarang mari kita melakukan percobaan dan melihat bagaimana rencana yang diusulkan akan (atau tidak akan) berubah tergantung pada perubahan tanggal dalam permintaan. Tanggal dipilih sedemikian rupa untuk secara berurutan meningkatkan kisaran baris tabel Penerbangan yang memenuhi kondisi, yang mengarah ke kesalahan perencana dalam menilai kardinalitas akses ke tabel ini. Dalam paket di atas, Anda dapat melihat bahwa dengan kencan pertama, pengoptimal hampir tidak keliru dalam kardinalitas ( ). Ganti tanggal berikut dalam permintaan:Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

• 2017-04-01
• 2017-01-01
• 2016-08-01

Dan mari kita lihat hasilnya:





Untuk meringkas, tanpa menggunakan modul AQO, perencana bekerja sebagai berikut:



Mari kita lihat hasilnya lagi:

Ketika AQO diaktifkan, scheduler dengan cepat menyadari bahwa Anda perlu beralih dari koneksi loop bersarang dan menggunakan indeks ke hash join dan scan sekuensial.

Meringkaskan

Menggunakan modul AQO untuk optimisasi permintaan adaptif memiliki kelebihan dan kekurangan.



Salah satu manfaat menggunakan modul adalah Anda tidak harus melacak kondisi dependen dalam kueri. Dalam beberapa kasus, kecepatan eksekusi permintaan dapat meningkat. Dan ada berbagai mode penggunaan modul. Misalnya, Anda dapat menggunakan AQO untuk hanya mengoptimalkan jenis kueri tertentu.



Di antara kekurangan modul, kita dapat membedakan biaya overhead tambahan untuk pelatihan dan menyimpan statistik dalam struktur modul. Dan informasi statistik yang dikumpulkan oleh modul tidak ditransfer ke replika.



Modul AQO bukan peluru perak terhadap semua masalah penjadwalan yang mungkin. Misalnya, dalam beberapa situasi, modul dapat menggantikan statistik lanjutan (jika Anda tidak membuatnya sendiri), atau tidak akan memperhatikan statistik yang tidak relevan. Tetapi modul tidak akan membuat indeks yang diperlukan dan, lebih lanjut, tidak akan menulis ulang teks kueri.



Oleh karena itu, Anda tidak boleh mengaktifkan modul untuk semua permintaan. Calon pengoptimalan yang ideal untuk AQO adalah pertanyaan di mana kesalahan perencana dalam menghitung kardinalitas node menghasilkan rencana yang buruk. Dan, untuk beberapa alasan, tidak mungkin mempengaruhi penyempurnaan estimasi ini.