Aracılığıyla paylaş

Dizin optimizasyonu

ŞUNLAR IÇIN GEÇERLIDIR: PostgreSQL için Azure Veritabanı - Esnek Sunucu

Dizin ayarlama, PostgreSQL için Azure Veritabanı esnek sunucunuzda izlenen sorguları analiz ederek ve dizin önerileri sağlayarak iş yükünüzün performansını otomatik olarak geliştiren bir özelliktir.

PostgreSQL için Azure Veritabanı esnek sunucunuzda yerleşik olan ve sorgu deposu işlevselliğini kullanarak performansı izleyen bir özelliktir. Dizin ayarlama, sorgu deposu tarafından izlenen iş yükünü analiz eder ve analiz edilen iş yükünün performansını artırmak veya yinelenen veya kullanılmayan dizinleri bırakmak için dizin önerileri üretir.

Dizin ayarlama algoritmasının genel açıklaması

index_tuning.mode Sunucu parametresi olarak reportyapılandırıldığında ayarlama oturumları, sunucu parametresinde index_tuning.analysis_intervalyapılandırılan sıklıkta otomatik olarak başlatılır ve dakika cinsinden ifade edilir.

İlk aşamada ayarlama oturumu, üretebileceği önerilerin sistemin genel performansını önemli ölçüde etkileyebileceğini düşündüğü veritabanlarının listesini arar. Bunu yapmak için, yürütmeleri bu ayarlama oturumunun odaklandığı arama aralığı içinde yakalanan sorgu deposu tarafından kaydedilen tüm sorguları toplar. Arama aralığı şu anda ayarlama oturumunun başlangıç zamanından itibaren son index_tuning.analysis_interval dakikalara yayılıyor.

Sorgu deposunda kaydedilen ve çalışma zamanı istatistikleri sıfırlanmayan, kullanıcı tarafından başlatılan tüm sorgular için sistem bunları toplam yürütme süresine göre sıralar. Sürelerine göre dikkatini en öne çıkan sorgulara odaklar.

Aşağıdaki sorgular bu listenin dışında tutulur:

  • Sistem tarafından başlatılan sorgular. (yani rol tarafından azuresu yürütülen sorgular)
  • Herhangi bir sistem veritabanı (azure_sys, template0, template1ve azure_maintenance) bağlamında yürütülen sorgular.

Algoritma hedef veritabanları üzerinde yinelenir ve analiz edilen iş yüklerinin performansını geliştirebilecek olası dizinleri arar. Ayrıca, yinelenen olarak tanımlandıkları veya yapılandırılabilir bir süre için kullanılmadıkları için kaldırılabilen dizinleri arar.

CREATE INDEX önerileri

Dizin önerileri üretmek için analiz etmeye aday olarak tanımlanan her veritabanı için, arama aralığı boyunca ve bu veritabanı bağlamında yürütülen tüm SELECT, UPDATE, INSERT ve DELETE sorguları dikkate alınır.

Sonuçta elde edilen sorgu kümesi toplam yürütme süresine göre sıralanır ve olası dizin önerileri için en üstte index_tuning.max_queries_per_database analiz edilir.

Olası öneriler, bu tür sorguların performansını iyileştirmeyi hedefler:

  • Filtre içeren sorgular (where yan tümcesinde koşul içeren sorgular),
  • Birden çok ilişkiyi birleştiren sorgular, birleşimlerin JOIN yan tümcesiyle ifade edildiği söz dizimini izlemeleri veya birleştirme koşullarının WHERE yan tümcesinde ifade edilip edilmediği.
  • Filtreleri ve birleştirme koşullarını birleştiren sorgular.
  • Gruplandırma içeren sorgular (GROUP BY yan tümcesine sahip sorgular).
  • Filtreleri ve gruplandırmaları birleştiren sorgular.
  • Sıralama içeren sorgular (ORDER BY yan tümcesine sahip sorgular).
  • Filtreleri ve sıralamayı birleştiren sorgular.

Not

Sistemin şu anda önerdiği tek dizin türü B-Tree türündeki dizinlerdir.

Sorgu tablonun bir sütununa başvuruda bulunuyorsa ve bu tabloda istatistik yoksa, sorgunun tamamını atlar ve yürütmesini geliştirmek için dizin önerileri üretmez.

İstatistikleri toplamak için gereken analiz, ANALYZE komutu kullanılarak el ile veya otomatik vakum daemon'ı tarafından otomatik olarak tetiklenebilir.

index_tuning.max_indexes_per_table ayarlama oturumu sırasında herhangi bir sayıda sorgu tarafından başvurulmuş tek bir tablo için tabloda zaten var olabilecek dizinler hariç, önerilebilen dizin sayısını belirtir.

index_tuning.max_index_count ayarlama oturumu sırasında analiz edilen herhangi bir veritabanının tüm tabloları için oluşturulan dizin önerilerinin sayısını belirtir.

Bir dizin önerisinin yayılabilmesi için, ayarlama altyapısının ile index_tuning.min_improvement_factorbelirtilen bir faktör tarafından analiz edilen iş yükündeki en az bir sorguyu geliştirdiğini tahmin etmesi gerekir.

Benzer şekilde, tüm dizin önerileri, ile index_tuning.max_regression_factorbelirtilen bir faktörün iş yükündeki tek bir sorguda regresyona neden olmadığından emin olmak için denetlenir.

Not

index_tuning.min_improvement_factor ve index_tuning.max_regression_factor her ikisi de sorgu planlarının süresine veya yürütme sırasında kullandıkları kaynaklara değil maliyetine başvurur.

Önceki paragraflarda belirtilen tüm parametreler, varsayılan değerleri ve geçerli aralıkları yapılandırma seçeneklerinde açıklanmıştır.

Dizin oluşturma önerisiyle birlikte oluşturulan betik şu deseni izler:

create index concurrently {indexName} on {schema}.{table}({column_name}[, ...])

yan tümcesini concurrentlyiçerir. Bu yan tümcenin etkileri hakkında daha fazla bilgi için CREATE INDEX için PostgreSQL resmi belgelerini ziyaret edin.

Dizin ayarlama, önerilen dizinlerin adlarını otomatik olarak oluşturur. Bu adlar genellikle "_" (alt çizgi) ile ayrılmış farklı anahtar sütunlarının adlarından ve sabit bir "_idx" soneki ile oluşur. Adın toplam uzunluğu PostgreSQL sınırlarını aşarsa veya mevcut ilişkilerle çakıştırılırsa ad biraz farklıdır. Kesilebilir ve adın sonuna bir sayı eklenebilir.

CREATE INDEX önerisinin etkisini hesaplama

Dizin önerisi oluşturmanın etkisi IndexSize (megabayt) ve QueryCostImprovement (yüzde) üzerinde ölçülür.

IndexSize, tablonun geçerli kardinalitesini ve önerilen dizin tarafından başvuruda bulunan sütunların boyutunu dikkate alarak dizinin tahmini boyutunu temsil eden tek bir değerdir.

QueryCostImprovement, bir dizi değerden oluşur ve burada her öğe, bu dizin mevcut olduğunda planının maliyetinin artacağı tahmin edilen her sorgu için planın maliyetindeki iyileştirmeyi temsil eder. Her öğe, sorgunun tanımlayıcısını (sorgulanmış) ve öneri uygulandığında planın maliyetinin artırıldığı yüzdeyi (boyutlu) gösterir.

DROP INDEX ve REINDEX önerileri

Dizin ayarlama işlevinin belirlendiği her veritabanı için yeni bir oturum başlatması gerekir ve CREATE INDEX önerileri aşaması tamamlandıktan sonra, aşağıdaki ölçütlere göre mevcut dizinlerin bırakılması veya yeniden dizinlenmesi önerilir:

  • Başkalarının yineleneni olarak kabul edilirse bırakın.
  • Yapılandırılabilir bir süre kullanılmadıysa bırakın.
  • Geçersiz olarak işaretlenmiş dizinleri yeniden dizinle.

Yinelenen dizinleri bırakma

Yinelenen dizinleri bırakma önerileri: İlk olarak, hangi dizinlerin yinelenenleri olduğunu belirleyin.

Yinelemeler, dizine atfedilebilen farklı işlevlere ve bunların tahmini boyutlarına göre sıralanır.

Son olarak, başvuru liderinden daha düşük bir derecelendirmeye sahip tüm yinelemeleri bırakmanızı önerir ve her yinelemenin neden olduğu gibi sıralandığını açıklar.

İki dizinin yinelenen olarak kabul edilmesi için şunları yapmalıdır:

  • Aynı tablo üzerinde oluşturulmalıdır.
  • Tam olarak aynı türde bir dizin olmalıdır.
  • Anahtar sütunlarını eşleştirin ve çok sütunlu dizin anahtarları için başvurulan sırayla eşleşin.
  • Koşulunun ifade ağacını eşleştirin. Yalnızca kısmi dizinler için geçerlidir.
  • Tüm nonsimple sütun başvurularının ifade ağacını eşleştirin. Yalnızca ifadelerde oluşturulan dizinler için geçerlidir.
  • Anahtarda başvuruda bulunan her sütunun harmanlamasını eşleştirin.

Kullanılmayan dizinleri bırakma

Kullanılmayan dizinleri bırakma önerileri şu dizinleri tanımlar:

  • En az index_tuning.unused_min_period gün boyunca kullanılmaz.
  • Dizinin oluşturulduğu tabloda en düşük (günlük ortalama) DLL sayısını index_tuning.unused_dml_per_table gösterin.
  • Dizinin oluşturulduğu tabloda en az (günlük ortalama) okuma sayısını index_tuning.unused_reads_per_table gösterin.

Geçersiz dizinleri yeniden dizine alma

Mevcut dizinleri yeniden dizine alma önerileri, geçersiz olarak işaretlenen dizinleri tanımlar. Dizinlerin neden ve ne zaman geçersiz olarak işaretleneceği hakkında daha fazla bilgi edinmek için PostgreSQL'de REINDEX resmi belgelerine bakın.

DROP INDEX önerisinin etkisini hesaplama

Bırakma dizini önerisinin etkisi iki boyut üzerinde ölçülür: Avantaj (yüzde) ve IndexSize (megabayt).

Avantaj, şimdilik yoksayılabilir tek bir değerdir.

IndexSize, tablonun geçerli kardinalitesini ve önerilen dizin tarafından başvuruda bulunan sütunların boyutunu dikkate alarak dizinin tahmini boyutunu temsil eden tek bir değerdir.

Dizin ayarlamayı yapılandırma

Dizin ayarlama etkinleştirilebilir, devre dışı bırakılabilir ve davranışını denetleyebilen bir dizi parametre aracılığıyla yapılandırılabilir.

Dizin ayarlama etkinleştirildiğinde, sunucu parametresinde index_tuning.analysis_interval yapılandırılmış bir sıklık ile (varsayılan olarak 720 dakika veya 12 saat) uyanır ve bu süre boyunca sorgu deposu tarafından kaydedilen iş yükünü analiz etmeye başlar.

değerini index_tuning.analysis_intervaldeğiştirirseniz değerinin yalnızca bir sonraki zamanlanmış yürütme tamamlandıktan sonra gözlemlendiğine dikkat edin. Bu nedenle, örneğin, dizin ayarlamayı bir gün saat 10:00'da etkinleştirirseniz, varsayılan değeri index_tuning.analysis_interval 720 dakika olduğundan, ilk yürütme aynı gün saat 22:00'de başlayacak şekilde zamanlanır. Saat 10:00 ile 22:00 arasında değerinde index_tuning.analysis_interval yaptığınız değişiklikler bu ilk zamanlamayı etkilemez. Yalnızca zamanlanmış çalıştırma tamamlandığında, için ayarlanan index_tuning.analysis_interval geçerli değeri okur ve bu değere göre bir sonraki yürütmeyi zamanlar.

Dizin ayarlama parametrelerini yapılandırmak için aşağıdaki seçenekler kullanılabilir:

Parametre Açıklama Varsayılan Aralık Birimler
index_tuning.analysis_interval index_tuning.mode olarak ayarlandığında her dizin iyileştirme oturumunun tetiklendiği sıklığı ayarlar REPORT. 720 60 - 10080 dakika
index_tuning.max_columns_per_index Önerilen dizinler için dizin anahtarının parçası olabilecek en fazla sütun sayısı. 2 1 - 10
index_tuning.max_index_count Bir iyileştirme oturumu sırasında her veritabanı için önerilen en fazla dizin sayısı. 10 1 - 25
index_tuning.max_indexes_per_table Her tablo için önerilebilen en fazla dizin sayısı. 10 1 - 25
index_tuning.max_queries_per_database Dizinlerin önerilebileceği veritabanı başına en yavaş sorgu sayısı. 25 5 - 100
index_tuning.max_regression_factor Bir iyileştirme oturumu sırasında analiz edilen sorgulardan herhangi birinde önerilen dizin tarafından sunulan kabul edilebilir regresyon. 0.1 0.05 - 0.2 Yüzde
index_tuning.max_total_size_factor Belirli bir veritabanı için önerilen tüm dizinlerin kullanabileceği toplam disk alanının yüzdesi cinsinden maksimum toplam boyut. 0.1 0 - 1 Yüzde
index_tuning.min_improvement_factor Önerilen dizinin bir iyileştirme oturumu sırasında analiz edilen sorgulardan en az birine sağlaması gereken maliyet iyileştirmesi. 0.2 0 - 20 Yüzde
index_tuning.mode Dizin iyileştirmeyi devre dışı (OFF) veya yalnızca öneri yaymak için etkin olarak yapılandırılır. veya pg_qs.query_capture_modeolarak ayarlanarak TOP sorgu deposunın etkinleştirilmesini ALL gerektirir. OFF OFF, REPORT
index_tuning.unused_dml_per_table Tabloyu etkileyen günlük ortalama DML işlemlerinin en az sayısıdır, bu nedenle kullanılmayan dizinleri bırakma olarak kabul edilir. 1000 0 - 9999999
index_tuning.unused_min_period Sistem istatistiklerine göre dizinin kullanılmadığı en az gün sayısı, dolayısıyla düşüş olarak kabul edilir. 35 30 - 70
index_tuning.unused_reads_per_table Kullanılmayan dizinlerinin düşüş olarak kabul edilmesi için tabloyu etkileyen günlük ortalama okuma işlemlerinin en az sayısı. 1000 0 - 9999999

CLI komutlarını az postgres flexible-server index-tuning show-settings ve az postgres flexible-server index-tuning set-settings kullanarak dizin ayarlama ayarlarından herhangi birini görüntüler veya değiştirirseniz, --name parametresi için kabul edilen değerler, önceki tablonun Parametre sütununda gösterilenlerdir, ancak index_tuning. öneki eklenmeden kullanılır.

Dizin ayarlama tarafından üretilen bilgiler

Dizin ayarlama tarafından oluşturulan önerileri okuma, yorumlama ve kullanma, dizin ayarlama tarafından oluşturulan önerilerin nasıl alınıp kullanılacağını ayrıntılı olarak açıklar.

Sınırlamalar ve desteklenebilirlik

Aşağıda dizin ayarlamaya yönelik sınırlamaların ve desteklenebilirlik kapsamının listesi yer alır.

Önerilerin otomatik olarak silinmesi

Öneriler, son üretildikten 35 gün sonra otomatik olarak silinir. Bu otomatik silme mekanizmasının çalışması için dizin ayarlamanın etkinleştirilmesi gerekir.

Hypopg uzantısına bağımlılık

DIZIN ayarlamanın CREATE INDEX önerileri üretmesi için hypopg uzantısını kullanır.

Bir ayarlama oturumu başladığında uzantı zaten varsa, oluşturulduğu şemada kullanılır. Ayarlama oturumu tamamlandığında uzantı bırakılmaz. Uzantının şemada pg_catalog oluşturulması buna bir özel durumdur. Bu durumda, dizin ayarlama uzantıyı bırakır.

Uzantı ilk etapta mevcut değilse veya şemada pg_catalog oluşturulduğu için bıraktıysak dizin ayarlama, uzantıyı adlı ms_temp_recommendations709253 bir şema altında oluşturur ve ayarlama oturumu başarıyla tamamlandığında uzantıyı bırakır ve şemayı kaldırır.

Rolün azure_pg_admin üyesi olan kullanıcılar, dizin ayarlama özelliği tarafından oluşturulduğunda bile hipopg uzantısını herhangi bir zamanda bırakabilir. Ancak dizin ayarlama oturumu çalışırken bu ayarı bırakmak, bu oturumun başarısız olmasına neden olabilir ve herhangi bir öneri üretmez.

Desteklenen işlem katmanları ve SKU'lar

Dizin ayarlama şu anda kullanılabilir olan tüm katmanlarda desteklenir: SeriLeştirilebilir, Genel Amaçlı ve Bellek için İyileştirilmiş ve en az 4 sanal çekirdek içeren şu anda desteklenen herhangi bir işlem SKU'su .

PostgreSQL'in desteklenen sürümleri

Dizin ayarlama, PostgreSQL için Azure Veritabanı Esnek Sunucu'nun 12. veya daha büyük sürümlerindedesteklenir.

search_path kullanımı

Dizin ayarlama, query_store.qs_view sütununda search_pathkalıcı olan değeri tüketir, böylece her sorgu çözümlendiğinde, sorgu ilk olarak yürütülürken ayarlanan değerin aynı değerisearch_path, olası önerileri analiz etmek için ayarlandığı değerdir.

Parametreli sorgular

PREPARE ile oluşturulan veya genişletilmiş sorgu protokolü kullanılarak oluşturulan parametreli sorgular ayrıştırılır ve bunlar üzerinde dizin önerileri üretmek için analiz edilir.

Parametreli sorguların analizi için dizin ayarlama, sorgu deposu sorgunun yürütülmesini yakaladığında pg_qs.parameters_capture_modecapture_first_sample. Ayrıca, sorgu yürütülürken parametrelerin sorgu deposu tarafından doğru bir şekilde yakalanmasını gerektirir. Başka bir deyişle, analiz edilen sorgu için query_store.qs_view öğesinin sütunu parameters_capture_status olarak succeededayarlanmalıdır.

Salt okunur mod ve okuma amaçlı çoğaltmalar

Dizin ayarlama, okuma amaçlı çoğaltmalarda desteklenmeyen veya bir örnek salt okunur moddayken sorgu deposuna bağlı olduğundan, bunu okuma amaçlı çoğaltmalarda veya salt okunur modda olan örneklerde desteklemeyiz.

Okuma amaçlı çoğaltmada görülen öneriler birincil çoğaltmada yalnızca birincil çoğaltmada yürütülen iş yükü analiz edildikten sonra üretildi.

İşlem ölçeğini azaltma

Bir sunucuda dizin ayarlama etkinleştirildiyse ve bu sunucunun işlem ölçeğini gerekli sanal çekirdek sayısı en az olacak şekilde azaltıyorsanız özellik etkin kalır. Özellik 4'ten az sanal çekirdek içeren sunucularda desteklenmediğinden, iş yükünü analiz etmek ve öneriler üretmek için çalışmaz. İşlemin ölçeği azaltıldığında, index_tuning.modeON olarak ayarlansa bile çalışmaz. Sunucu en düşük gereksinimleri karşılamasa da, tüm index_tuning.* sunucu parametrelerine erişilemez. Sunucunuzun ölçeğini en düşük gereksinimleri karşılayan bir işlem olarak her artırdığınızda, index_tuning.mode ölçeğini gereksinimleri karşılamayan bir işlem olarak azaltmadan önce ayarlanan değerle yapılandırılır.

Yüksek kullanılabilirlik ve okuma amaçlı çoğaltmalar

Sunucunuzda yapılandırılmış yüksek kullanılabilirlik veya okuma amaçlı çoğaltmalarınız varsa, önerilen dizinleri uygularken birincil sunucuda yazma yoğunluklu iş yükleri üretmenin etkilerini unutmayın. Boyutu büyük olduğu tahmin edilen dizinler oluştururken özellikle dikkatli olun.

İlgili içerik