Bagikan melalui

Penyetelan indeks

Penyetelan indeks adalah fitur di instans server fleksibel Azure Database for PostgreSQL Anda yang secara otomatis meningkatkan performa beban kerja Anda dengan menganalisis kueri yang dilacak dan memberikan rekomendasi indeks.

Ini adalah penawaran bawaan di instans server fleksibel Azure Database for PostgreSQL Anda, yang dibangun di atas performa Monitor dengan fungsionalitas penyimpanan kueri. Penyetelan indeks menganalisis beban kerja yang dilacak oleh penyimpanan kueri, dan menghasilkan rekomendasi indeks untuk meningkatkan performa beban kerja yang dianalisis atau untuk menghilangkan indeks duplikat atau tidak digunakan.

Deskripsi umum algoritma penyetelan indeks

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 berdampak signifikan pada 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. Ini juga mencari indeks yang dapat dihilangkan karena diidentifikasi sebagai duplikat atau tidak digunakan untuk jangka waktu yang dapat dikonfigurasi.

REKOMENDASI CREATE INDEX

Untuk setiap database yang diidentifikasi sebagai kandidat yang akan dianalisis untuk menghasilkan rekomendasi indeks, semua kueri SELECT, UPDATE, INSERT, dan DELETE yang 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.

Catatan

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

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

Analisis yang diperlukan untuk mengumpulkan statistik dapat dipicu secara manual menggunakan perintah ANALYZE atau secara otomatis oleh daemon autovacuum.

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.

Catatan

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 indeks 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" konstanta. 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 fungsionalitas penyetelan indeksnya ditentukan, itu harus memulai sesi baru, dan setelah fase rekomendasi CREATE INDEX selesai, merekomendasikan untuk menghilangkan 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 indeks indeks ulang 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.

Mengonfigurasi penyetelan indeks

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

Saat penyetelan indeks diaktifkan, ia bangun dengan frekuensi yang dikonfigurasi dalam index_tuning.analysis_interval parameter server (default hingga 720 menit atau 12 jam) dan mulai menganalisis beban kerja yang direkam oleh penyimpanan kueri 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 indeks 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 sampai 22:00 tidak akan memengaruhi 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 indeks:

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 Menit
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 index-tuning show-settings CLI dan az postgres flexible-server index-tuning set-settings untuk menampilkan atau mengubah salah satu pengaturan penyetelan indeks, 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 indeks

Cara membaca, menginterpretasikan, dan menggunakan rekomendasi yang dihasilkan oleh penyetelan indeks menjelaskan secara rinci cara mendapatkan dan menggunakan rekomendasi yang dihasilkan oleh penyetelan indeks.

Batasan dan dukungan

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

Penghapusan otomatis rekomendasi

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

Dependensi pada ekstensi hipopg

Agar penyetelan indeks 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 ini adalah jika ekstensi dibuat dalam pg_catalog skema. Jika demikian, penyetelan indeks akan menghilangkan ekstensi.

Jika ekstensi tidak ada di tempat pertama atau kami menjatuhkannya karena dibuat dalam pg_catalog skema, penyetelan indeks akan membuatnya di bawah skema yang disebut ms_temp_recommendations709253 dan, ketika sesi penyetelan berhasil diselesaikan, itu menghilangkan ekstensi dan menghapus skema.

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

Tingkat komputasi dan SKU yang didukung

Penyetelan indeks didukung pada semua tingkatan yang tersedia saat ini: Burstable, General Purpose, dan Memory Optimized, dan pada SKU komputasi yang saat ini didukung dengan setidaknya 4 vCore.

Versi PostgreSQL yang didukung

Penyetelan indeks didukung pada instans server fleksibel Azure Database for PostgreSQL versi utama12 atau yang lebih tinggi .

Penggunaan search_path

Penyetelan indeks menggunakan nilai yang bertahan di kolom search_pathquery_store.qs_view, sehingga ketika setiap kueri dianalisis, nilai search_path yang sama yang ditetapkan ketika kueri yang dijalankan awalnya adalah yang diatur untuk menganalisis kemungkinan rekomendasi.

Kueri berparameter

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 indeks mengharuskan pg_qs.parameters_capture_mode diatur ke capture_first_sample saat penyimpanan kueri mengambil 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 indeks bergantung pada penyimpanan kueri, 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 indeks 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 indeks mungkin tidak menghasilkan rekomendasi indeks buat untuk kueri tertentu

Berikut ini adalah daftar jenis kueri yang penyetelan indeksnya tidak akan menghasilkan rekomendasi CREATE INDEX. Yang:

  • Mengalami kesalahan saat mesin penyetelan indeks mencoba mendapatkan output EXPLAIN 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. Itulah 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.
  • Tidak termasuk index_tuning.max _queries_per_database teratas paling lambat, 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