Bagikan melalui

Penyetelan otonom

Penyetelan otonom adalah fitur di instans server fleksibel Azure Database for PostgreSQL Anda yang menganalisis kueri yang dilacak dari beban kerja Anda dan memberikan rekomendasi untuk meningkatkan performa kueri tersebut.

Ini adalah fitur bawaan di instans server fleksibel Azure Database for PostgreSQL Anda, yang didasarkan pada fungsionalitas penyimpanan kueri. Penyetelan otonom menganalisis beban kerja yang dilacak oleh penyimpanan kueri, dan menghasilkan rekomendasi indeks atau tabel untuk meningkatkan performa beban kerja yang dianalisis. Ini dapat menghasilkan rekomendasi untuk membuat indeks baru, menghilangkan indeks duplikat atau tidak digunakan, menganalisis tabel yang tidak memiliki statistik atau statistik yang kedaluwarsa, atau melakukan vakum pada tabel yang membengkak.

Deskripsi umum algoritma penyetelan otonom

Ketika parameter server dikonfigurasi index_tuning.mode ke report, sesi penyetelan secara otomatis dimulai dengan frekuensi yang dikonfigurasi dalam parameter index_tuning.analysis_intervalserver , dinyatakan dalam hitungan menit.

Pada fase pertama, sesi penyetelan mencari daftar database di mana ia mempertimbangkan bahwa rekomendasi apa pun yang mungkin dihasilkannya dapat secara signifikan memengaruhi performa keseluruhan sistem. Untuk melakukannya, ini mengumpulkan semua kueri yang direkam oleh penyimpanan kueri yang eksekusinya diambil dalam interval pencarian yang difokuskan sesi penyetelan ini. Interval pencarian saat ini mencakup hingga menit-menit terakhir index_tuning.analysis_interval , dari waktu mulai sesi penyetelan.

Untuk semua kueri yang dimulai pengguna dengan eksekusi yang direkam di penyimpanan kueri dan yang statistik runtime-nya tidak diatur ulang, sistem memberi peringkat berdasarkan total waktu eksekusi agregat mereka. Ini memfokuskan perhatiannya pada kueri yang paling menonjol, berdasarkan durasinya.

Kueri berikut dikecualikan dari daftar tersebut:

  • Kueri yang dimulai sistem. (yaitu, kueri yang dijalankan oleh azuresu peran)
  • Kueri dijalankan dalam konteks database sistem apa pun (azure_sys, , template0template1, dan azure_maintenance).

Algoritma berulang di atas database target, mencari kemungkinan indeks yang dapat meningkatkan performa beban kerja yang dianalisis. Juga mencari indeks yang dapat dihilangkan karena duplikat atau tidak digunakan untuk jangka waktu yang dapat dikonfigurasi. Juga mengidentifikasi tabel yang tidak memiliki statistik terbaru atau tabel yang terlalu besar.

REKOMENDASI CREATE INDEX

Untuk setiap database yang diidentifikasi sebagai kandidat untuk dianalisis, semua kueri SELECT, UPDATE, INSERT, dan DELETE dijalankan selama interval pencarian dan dalam konteks database tertentu tersebut diperhitungkan.

Kumpulan kueri yang dihasilkan diberi peringkat berdasarkan total waktu eksekusi agregatnya, dan bagian atas index_tuning.max_queries_per_database dianalisis untuk kemungkinan rekomendasi indeks.

Rekomendasi potensial bertujuan untuk meningkatkan performa jenis kueri ini:

  • Kueri dengan filter (yaitu, kueri dengan predikat dalam klausa WHERE),
  • Kueri yang bergabung dengan beberapa hubungan, apakah mereka mengikuti sintaks di mana gabungan dinyatakan dengan klausa JOIN atau apakah predikat gabungan dinyatakan dalam klausa WHERE.
  • Kueri menggabungkan filter dan menggabungkan predikat.
  • Kueri dengan pengelompokan (kueri dengan klausa GROUP BY).
  • Kueri menggabungkan filter dan pengelompokan.
  • Kueri dengan pengurutan (kueri dengan klausa ORDER BY).
  • Kueri menggabungkan filter dan pengurutan.

Nota

Satu-satunya jenis indeks yang direkomendasikan sistem saat ini adalah B-Tree.

Jika kueri mereferensikan satu kolom tabel dan tabel tersebut tidak memiliki statistik, tidak menghasilkan rekomendasi indeks apa pun untuk meningkatkan eksekusinya. Namun, ini menghasilkan rekomendasi untuk menganalisis tabel.

index_tuning.max_indexes_per_table menentukan jumlah indeks yang dapat direkomendasikan, tidak termasuk indeks apa pun yang mungkin sudah ada pada tabel untuk tabel tunggal apa pun yang direferensikan oleh sejumlah kueri selama sesi penyetelan.

index_tuning.max_index_count menentukan jumlah rekomendasi indeks yang dihasilkan untuk semua tabel database apa pun yang dianalisis selama sesi penyetelan.

Agar rekomendasi indeks dipancarkan, mesin penyetelan harus memperkirakan bahwa ia meningkatkan setidaknya satu kueri dalam beban kerja yang dianalisis oleh faktor yang ditentukan dengan index_tuning.min_improvement_factor.

Demikian juga, semua rekomendasi indeks diperiksa untuk memastikan bahwa mereka tidak memperkenalkan regresi pada kueri tunggal dalam beban kerja faktor yang ditentukan dengan index_tuning.max_regression_factor.

Nota

index_tuning.min_improvement_factor dan index_tuning.max_regression_factor keduanya mengacu pada biaya rencana kueri, bukan ke durasinya atau sumber daya yang mereka konsumsi selama eksekusi.

Semua parameter yang disebutkan dalam paragraf sebelumnya, nilai default dan rentang yang valid dijelaskan dalam opsi konfigurasi.

Skrip yang diproduksi bersama dengan rekomendasi untuk membuat indeks, mengikuti pola ini:

CREATE INDEX CONCURRENTLY {indexName} ON {schema}.{table}({column_name}[, ...])

Ini termasuk klausul CONCURRENTLY. Untuk informasi lebih lanjut tentang efek klausul ini, kunjungi dokumentasi resmi PostgreSQL untuk CREATE INDEX.

Penyetelan otonom secara otomatis menghasilkan nama indeks yang direkomendasikan, yang biasanya terdiri dari nama kolom kunci yang berbeda yang dipisahkan oleh "_" (garis bawah) dan dengan akhiran "_idx" konstan. Jika panjang total nama melebihi batas PostgreSQL atau jika berbenturan dengan hubungan yang ada, namanya sedikit berbeda. Ini bisa dipotong, dan angka dapat ditambahkan ke akhir nama.

Menghitung dampak rekomendasi CREATE INDEX

Dampak pembuatan rekomendasi indeks diukur pada IndexSize (megabyte) dan QueryCostImprovement (persentase).

IndexSize adalah nilai tunggal yang mewakili perkiraan ukuran indeks, mengingat kardinalitas tabel saat ini dan ukuran kolom yang direferensikan oleh indeks yang direkomendasikan.

QueryCostImprovement terdiri dari array nilai, di mana setiap elemen mewakili peningkatan biaya paket untuk setiap kueri yang biaya paketnya diperkirakan meningkat jika indeks ini ada. Setiap elemen menunjukkan pengidentifikasi kueri (dikueri) dan persentase di mana biaya paket akan meningkat jika rekomendasi diimplementasikan (dimensi).

Rekomendasi DROP INDEX dan REINDEX

Untuk setiap database yang diidentifikasi sebagai kandidat, database harus memulai sesi baru, dan setelah fase rekomendasi CREATE INDEX selesai, disarankan untuk menghapus atau mengindeks ulang indeks yang ada, berdasarkan kriteria berikut:

  • Hilangkan jika dianggap duplikat orang lain.
  • Hilangkan jika tidak digunakan untuk jumlah waktu yang dapat dikonfigurasi.
  • Indeks ulang indeks yang ditandai sebagai tidak valid.

Menghilangkan indeks duplikat

Rekomendasi untuk menghilangkan indeks duplikat: Pertama, identifikasi indeks mana yang memiliki duplikat.

Duplikat diberi peringkat berdasarkan berbagai fungsi yang dapat dikaitkan dengan indeks dan berdasarkan perkiraan ukurannya.

Akhirnya, merekomendasikan untuk menjatuhkan semua duplikat dengan peringkat yang lebih rendah daripada pemimpin referensinya dan menjelaskan mengapa setiap duplikat diberi peringkat seperti itu.

Agar dua indeks dianggap duplikat, mereka harus:

  • Dibuat di atas tabel yang sama.
  • Jadilah indeks dengan jenis yang sama persis.
  • Cocokkan kolom kuncinya dan, untuk kunci indeks multikolom, cocok dengan urutan referensinya.
  • Cocokkan pohon ekspresi predikatnya. Hanya berlaku untuk indeks parsial.
  • Cocokkan pohon ekspresi dari semua referensi kolom nonsimple. Hanya berlaku untuk indeks yang dibuat pada ekspresi.
  • Cocokkan kolatasi setiap kolom yang dirujuk dalam kunci.

Menghilangkan indeks yang tidak digunakan

Rekomendasi untuk menghilangkan indeks yang tidak digunakan mengidentifikasi indeks tersebut yang:

  • Tidak digunakan setidaknya index_tuning.unused_min_period selama beberapa hari.
  • Tampilkan jumlah MINIMUM (rata-rata harian index_tuning.unused_dml_per_table ) DML pada tabel tempat indeks dibuat.
  • Perlihatkan jumlah pembacaan minimum (rata-rata harian index_tuning.unused_reads_per_table ) pada tabel tempat indeks dibuat.

Indeks tidak valid reindeks

Rekomendasi untuk indeks yang ada mengindeks ulang mengidentifikasi indeks tersebut yang ditandai sebagai tidak valid. Untuk mempelajari selengkapnya tentang mengapa dan kapan indeks ditandai sebagai tidak valid, lihat dokumentasi resmi REINDEX di PostgreSQL.

Menghitung dampak rekomendasi DROP INDEX

Dampak rekomendasi indeks penurunan diukur pada dua dimensi: Manfaat (persentase) dan IndexSize (megabyte).

Manfaatnya adalah nilai tunggal yang dapat diabaikan untuk saat ini.

IndexSize adalah nilai tunggal yang mewakili perkiraan ukuran indeks, mengingat kardinalitas tabel saat ini dan ukuran kolom yang direferensikan oleh indeks yang direkomendasikan.

Rekomendasi untuk tabel

Untuk setiap database yang diidentifikasi sebagai kandidat untuk dianalisis, database memulai sesi yang bertujuan untuk menghasilkan rekomendasi tingkat tabel. Rekomendasi tersebut menyarankan Anda untuk menjalankan ANALYZE atau VACUUM pada tabel yang diakses oleh kueri yang diperiksa, di mana mesin penyetelan mempertimbangkan bahwa menjalankan perintah tersebut dapat meningkatkan performa beban kerja Anda.

ANALYZE rekomendasi tabel

Rekomendasi untuk menganalisis tabel meliputi identifikasi tabel yang:

  • Dirujuk dalam kueri, dan memiliki beberapa kolom tabel yang digunakan dalam salah satu predikatnya (WHERE, JOIN, ORDER BY, GROUP BY), dan juga memenuhi salah satu dari dua kondisi berikut:
    • Belum pernah dianalisis.
    • Dianalisis pada beberapa titik, tetapi sekarang tidak memiliki statistik (biasanya karena server mengalami crash sebelum statistik dipertahankan ke disk).

Rekomendasi tabel VACUUM

Rekomendasi untuk membersihkan tabel mengidentifikasi tabel yang berlebihan. Rekomendasi ini hanya diproduksi ketika autovacuum_enabled tidak diatur ke off pada tingkat server saat beban kerja dianalisis.

Mengonfigurasi penyetelan otonom

Penyetelan otonom dapat diaktifkan, dinonaktifkan, dan dikonfigurasi melalui serangkaian parameter yang mengontrol perilakunya.

Ketika penyetelan otonom diaktifkan, penyetelan ini dijalankan dengan frekuensi yang dikonfigurasi dalam index_tuning.analysis_interval parameter server (secara default 720 menit atau 12 jam) dan mulai menganalisis beban kerja yang direkam oleh query store selama periode tersebut.

Perhatikan bahwa jika Anda mengubah nilai untuk index_tuning.analysis_interval, nilai tersebut hanya diamati setelah eksekusi terjadwal berikutnya selesai. Jadi, misalnya, jika Anda mengaktifkan penyetelan otonom satu hari pada pukul 10:00, karena nilai default untuk index_tuning.analysis_interval adalah 720 menit, eksekusi pertama dijadwalkan dimulai pada pukul 22:00 pada hari yang sama. Setiap perubahan yang Anda buat pada nilai index_tuning.analysis_interval antara pukul 10:00 pagi dan 22:00 tidak berpengaruh pada jadwal awal tersebut. Hanya ketika eksekusi terjadwal selesai, eksekusi akan membaca nilai saat ini yang ditetapkan untuk index_tuning.analysis_interval dan akan menjadwalkan eksekusi berikutnya sesuai dengan nilai tersebut.

Opsi berikut tersedia untuk mengonfigurasi parameter penyetelan otonom:

Parameter Deskripsi Default Rentang Unit
index_tuning.analysis_interval Mengatur frekuensi di mana setiap sesi pengoptimalan indeks dipicu saat index_tuning.mode diatur ke REPORT. 720 60 - 10080 minutes
index_tuning.max_columns_per_index Jumlah maksimum kolom yang dapat menjadi bagian dari kunci indeks untuk indeks yang direkomendasikan. 2 1 - 10
index_tuning.max_index_count Indeks maksimum yang direkomendasikan untuk setiap database selama satu sesi pengoptimalan. 10 1 - 25
index_tuning.max_indexes_per_table Jumlah maksimum indeks yang dapat direkomendasikan untuk setiap tabel. 10 1 - 25
index_tuning.max_queries_per_database Jumlah kueri terlambat per database yang indeksnya dapat direkomendasikan. 25 5 - 100
index_tuning.max_regression_factor Regresi yang dapat diterima diperkenalkan oleh indeks yang direkomendasikan pada salah satu kueri yang dianalisis selama satu sesi pengoptimalan. 0.1 0.05 - 0.2 Persentase
index_tuning.max_total_size_factor Ukuran total maksimum, dalam persentase total ruang disk, yang dapat digunakan oleh semua indeks yang direkomendasikan untuk database tertentu. 0.1 0 - 1 Persentase
index_tuning.min_improvement_factor Peningkatan biaya yang harus diberikan indeks yang direkomendasikan untuk setidaknya salah satu kueri yang dianalisis selama satu sesi pengoptimalan. 0.2 0 - 20 Persentase
index_tuning.mode Mengonfigurasi pengoptimalan indeks sebagai dinonaktifkan (OFF) atau diaktifkan hanya untuk mengeluarkan rekomendasi. Mengharuskan penyimpanan kueri diaktifkan dengan mengatur pg_qs.query_capture_mode ke TOP atau ALL. OFF OFF, REPORT
index_tuning.unused_dml_per_table Jumlah minimum operasi DML rata-rata harian yang memengaruhi tabel, sehingga indeks yang tidak digunakan dipertimbangkan untuk dihilangkan. 1000 0 - 9999999
index_tuning.unused_min_period Jumlah hari minimum indeks belum digunakan, berdasarkan statistik sistem, sehingga dianggap untuk menjatuhkan. 35 30 - 70
index_tuning.unused_reads_per_table Jumlah minimum operasi baca rata-rata harian yang memengaruhi tabel sehingga indeks yang tidak digunakan dipertimbangkan untuk menjatuhkan. 1000 0 - 9999999

Jika Anda menggunakan perintah az postgres flexible-server autonomous-tuning show-settings CLI dan az postgres flexible-server autonomous-tuning set-settings untuk menampilkan atau memodifikasi salah satu pengaturan penyetelan otonom, nilai yang diterima sebagai argumen untuk --name parameter adalah yang ditampilkan di kolom Parameter tabel sebelumnya, tetapi tanpa menyertakan awalan index_tuning..

Informasi yang dihasilkan oleh penyetelan otomatis

Gunakan rekomendasi penyetelan otonom menjelaskan secara rinci cara mendapatkan dan menggunakan rekomendasi yang dihasilkan oleh penyetelan otonom.

Batasan dan dukungan

Berikut ini adalah daftar batasan dan cakupan dukungan untuk penyetelan otonom.

Penghapusan otomatis rekomendasi

Rekomendasi dihapus secara otomatis 35 hari setelah terakhir kali diproduksi. Agar mekanisme penghapusan otomatis ini berfungsi, penyetelan otonom harus diaktifkan.

Dependensi pada ekstensi hipopg

Untuk penyetelan otonom untuk menghasilkan rekomendasi CREATE INDEX, ia menggunakan ekstensi hipopg .

Jika ekstensi sudah ada ketika sesi penyetelan dimulai, ekstensi tersebut digunakan pada skema tempatnya dibuat. Dan ketika sesi penyetelan selesai, ekstensi tidak dihilangkan. Pengecualian untuk aturan ini adalah jika ekstensi dibuat dalam pg_catalog skema. Jika demikian, penyetelan otonom akan menghilangkan ekstensi.

Jika ekstensi tidak ada dari awal atau kami menghapuskannya karena dibuat dalam skema pg_catalog, penyetelan otomatis membuatnya di bawah skema yang disebut ms_temp_recommendations709253 dan, ketika sesi penyetelan berhasil diselesaikan, menghapus ekstensi dan skema tersebut.

Pengguna yang merupakan anggota peran azure_pg_admin dapat menghapus ekstensi hypopg kapan saja, bahkan ketika sudah dibuat oleh fitur penyetelan otomatis. Namun, menghilangkannya saat sesi penyetelan otonom berjalan dapat menyebabkan sesi tersebut gagal dan tidak menghasilkan rekomendasi apa pun.

Tingkat komputasi dan SKU yang didukung

Penyetelan otonom didukung pada semua tingkatan yang tersedia saat ini: Burstable, General Purpose, dan Memory Optimized, serta pada SKU komputasi yang saat ini didukung dengan minimal 4 vCores.

Versi PostgreSQL yang didukung

Penyetelan otonom didukung pada server fleksibel Azure Database for PostgreSQL versi utama12 atau yang lebih besar.

Penggunaan search_path

Penyetelan otonom menggunakan nilai yang disimpan di kolom search_path dari query_store.qs_view, sehingga ketika setiap kueri dianalisis, nilai search_path yang sama, yang diatur saat kueri tersebut pertama kali dijalankan, digunakan untuk menganalisis rekomendasi yang mungkin.

Kueri dengan parameter

Kueri berparameter yang dibuat dengan PREPARE atau menggunakan protokol kueri yang diperluas diurai dan dianalisis untuk menghasilkan rekomendasi indeks pada kueri tersebut.

Untuk analisis kueri berparameter, penyetelan otonom mengharuskan pg_qs.parameters_capture_mode diatur ke capture_first_sample saat penyimpanan kueri menangkap eksekusi kueri. Ini juga mengharuskan parameter ditangkap dengan benar oleh penyimpanan kueri saat kueri dijalankan. Dengan kata lain, untuk kueri yang dianalisis, query_store.qs_view harus mengatur kolomnya parameters_capture_status ke succeeded.

Mode baca-saja dan replika baca

Karena penyetelan otonom bergantung pada data yang disimpan kueri secara lokal ke azure_sys database, yang tidak didukung dalam replika baca atau ketika instans dalam mode baca-saja, kami tidak mendukungnya pada replika baca atau pada instans yang berada dalam mode baca-saja.

Setiap rekomendasi yang terlihat pada replika baca diproduksi pada replika utama setelah menganalisis secara eksklusif beban kerja yang dijalankan pada replika utama.

Menurunkan skala komputasi

Jika penyetelan otonom diaktifkan di server, dan Anda menurunkan skala komputasi server tersebut menjadi kurang dari jumlah minimum vCore yang diperlukan, fitur tetap diaktifkan. Karena fitur ini tidak didukung pada server dengan kurang dari 4 vCore, fitur ini tidak berjalan untuk menganalisis beban kerja dan menghasilkan rekomendasi, bahkan jika index_tuning.mode diatur ke ON saat komputasi diturunkan. Meskipun server tidak memenuhi persyaratan minimum, semua index_tuning.* parameter server tidak dapat diakses. Setiap kali Anda menskalakan server Anda kembali ke komputasi yang memenuhi persyaratan minimum, index_tuning.mode dikonfigurasi dengan nilai apa pun yang ditetapkan sebelum Anda menurunkan skalanya ke komputasi yang tidak memenuhi persyaratan.

Ketersediaan tinggi dan replika baca

Jika Anda memiliki ketersediaan tinggi atau replika baca yang dikonfigurasi di server Anda, ketahui implikasi yang terkait dengan memproduksi beban kerja intensif tulis di server utama saat menerapkan indeks yang direkomendasikan. Berhati-hatilah saat membuat indeks yang ukurannya diperkirakan besar.

Alasan mengapa penyetelan otonom mungkin tidak memberikan rekomendasi pembuatan indeks untuk kueri tertentu

Berikut ini adalah daftar jenis kueri yang penyetelan otonomnya tidak menghasilkan rekomendasi CREATE INDEX. Permintaan yang:

  • Mengalami kesalahan ketika mesin penyetelan otonom mencoba mendapatkan output EXPLAIN-nya selama fase analisis.
  • Tabel referensi yang tidak memiliki statistik tentang kontennya di katalog sistem pg_statistic. Jalankan ANALYZE pada tabel tersebut sehingga mesin penyetelan dapat mempertimbangkan kueri ini di masa mendatang.
  • Membuat teks kueri terpotong di penyimpanan kueri. Ini adalah kasus ketika panjang teks kueri melebihi nilai yang dikonfigurasi dalam pg_qs.max_query_text_length.
  • Objek referensi yang dihilangkan atau diganti namanya sebelum analisis terjadi. Kueri ini masih bisa valid secara sintetis, tetapi tidak valid secara semantik.
  • Akses tabel atau indeks sementara pada tabel sementara.
  • Mengakses tampilan atau tampilan materialisasi.
  • Mengakses tabel yang dipartisi.
  • Diidentifikasi sebagai pernyataan utilitas. Pernyataan utilitas atau perintah utilitas adalah, pada dasarnya, pernyataan apa pun yang tidak dianggap SELECT, INSERT, UPDATE, DELETE, atau MERGE, dan perintah tertentu yang berisi salah satu dari ini.
  • Bukan termasuk yang paling lambat di antara index_tuning.max_queries_per_database teratas, untuk database dan periode yang dianalisis.
  • Dijalankan dalam konteks satu database tertentu, ketika tidak ada kueri tersebut yang diidentifikasi sebagai yang paling lambat teratas di tingkat server.

Konten terkait

  • Last updated on