Sdílet prostřednictvím

Ladění indexu

Ladění indexu je funkce v instanci flexibilního serveru Azure Database for PostgreSQL, která automaticky zlepšuje výkon vaší úlohy analýzou sledovaných dotazů a poskytováním doporučení indexu.

Jedná se o integrovanou nabídku v instanci flexibilního serveru Azure Database for PostgreSQL, která vychází z výkonu monitorování pomocí funkcí úložiště dotazů . Ladění indexů analyzuje úlohy sledované úložištěm dotazů a vytváří doporučení indexů ke zlepšení výkonu analyzované úlohy nebo k vyřazení duplicitních nebo nepoužívaných indexů.

Obecný popis algoritmu ladění indexu

index_tuning.mode Pokud je parametr serveru nakonfigurovaný na report, relace ladění se automaticky spustí s frekvencí nakonfigurovanou v parametru index_tuning.analysis_intervalserveru vyjádřené v minutách.

V první fázi relace ladění vyhledá seznam databází, ve kterých se domnívá, že jakákoli doporučení, která by mohla přinést, může významně ovlivnit celkový výkon systému. Za tímto účelem shromažďuje všechny dotazy zaznamenané úložištěm dotazů, jejichž spuštění byla zachycena v intervalu vyhledávání, na které se tato relace ladění zaměřuje. Interval vyhledávání se aktuálně nachází v posledních index_tuning.analysis_interval minutách od počátečního času relace ladění.

Pro všechny dotazy iniciované uživatelem se spouštěními zaznamenanými v úložišti dotazů a jejichž statistiky modulu runtime nejsou resetovány, systém je řadí podle agregované celkové doby provádění. Zaměřuje se na nejdůležitější dotazy na základě jejich doby trvání.

Z tohoto seznamu jsou vyloučeny následující dotazy:

  • Dotazy iniciované systémem (to znamená, že dotazy spouštěné rolí azuresu )
  • Dotazy spouštěné v kontextu jakékoli systémové databáze (azure_sys, template0, template1a azure_maintenance).

Algoritmus iteruje cílové databáze a hledá možné indexy, které by mohly zlepšit výkon analyzovaných úloh. Vyhledá také indexy, které je možné odstranit, protože jsou identifikované jako duplicity nebo se nepoužívají v konfigurovatelném časovém období.

Doporučení k VYTVOŘENÍ INDEXu

Pro každou databázi, která byla identifikována jako kandidát na analýzu pro vytváření doporučení indexu, všechny dotazy SELECT, UPDATE, INSERT a DELETE spuštěné během vyhledávacího intervalu a v kontextu konkrétní databáze jsou zahrnuté.

Výsledná sada dotazů je seřazená na základě agregované celkové doby provádění a na nejvyšší úrovni index_tuning.max_queries_per_database se analyzují možná doporučení indexu.

Potenciální doporučení mají za cíl zlepšit výkon těchto typů dotazů:

  • Dotazy s filtry (to znamená dotazy s predikáty v klauzuli WHERE),
  • Dotazy, které spojují více relací, ať už následují syntaxi, ve které jsou spojení vyjádřena klauzulí JOIN, nebo zda jsou predikáty spojení vyjádřeny v klauzuli WHERE.
  • Dotazy kombinující filtry a predikáty spojení
  • Dotazy se seskupováním (dotazy s klauzulí GROUP BY).
  • Dotazy kombinující filtry a seskupování
  • Dotazy s řazením (dotazy s klauzulí ORDER BY).
  • Dotazy kombinující filtry a řazení

Poznámka:

Jediným typem indexů, které systém aktuálně doporučuje, jsou ty typu B-Tree.

Pokud dotaz odkazuje na jeden sloupec tabulky a tato tabulka nemá žádné statistiky, přeskočí celý dotaz a nevygeneruje žádná doporučení indexu ke zlepšení jeho provádění.

Analýzu potřebnou ke shromažďování statistik je možné aktivovat ručně pomocí příkazu ANALYZE nebo automaticky démonem automatického úklidu.

index_tuning.max_indexes_per_table určuje počet indexů, které lze doporučit, s výjimkou všech indexů, které již mohou existovat v tabulce pro libovolnou tabulku odkazovanou libovolným počtem dotazů během relace ladění.

index_tuning.max_index_count určuje počet doporučení indexů vytvořených pro všechny tabulky jakékoli databáze analyzované během relace ladění.

Aby bylo možné vygenerovat doporučení indexu, musí modul ladění odhadnout, že zlepšuje alespoň jeden dotaz v analyzované úloze pomocí faktoru zadaného parametrem index_tuning.min_improvement_factor.

Stejně tak jsou všechna doporučení indexu kontrolována, aby se zajistilo, že nezavádějí regresi u žádného jednoho dotazu v dané úloze faktoru zadaného pomocí index_tuning.max_regression_factor.

Poznámka:

index_tuning.min_improvement_factor a index_tuning.max_regression_factor oba odkazují na náklady na plány dotazů, ne na jejich dobu trvání ani na prostředky, které spotřebovávají během provádění.

Všechny parametry uvedené v předchozích odstavcích, jejich výchozí hodnoty a platné rozsahy jsou popsány v možnostech konfigurace.

Skript vytvořený společně s doporučením k vytvoření indexu se řídí tímto vzorem:

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

Obsahuje klauzuli concurrently. Další informace o vlivu této klauzule najdete v oficiální dokumentaci k postgreSQL pro CREATE INDEX.

Ladění indexu automaticky vygeneruje názvy doporučených indexů, které se obvykle skládají z názvů různých klíčových sloupců oddělených podtržítkem "_" (podtržítka) a s konstantní příponou "_idx". Pokud celková délka názvu překročí limity PostgreSQL nebo pokud koliduje s existujícími relacemi, název se mírně liší. Může se zkrátit a na konec názvu může být připojeno číslo.

Výpočet dopadu doporučení CREATE INDEX

Dopad vytváření doporučení indexu se měří na indexYize (megabajty) a QueryCostImprovement (procento).

IndexSize je jedna hodnota, která představuje odhadovanou velikost indexu vzhledem k aktuální kardinalitě tabulky a velikosti sloupců odkazovaných doporučeným indexem.

QueryCostImprovement se skládá z pole hodnot, kde každý prvek představuje zlepšení nákladů plánu pro každý dotaz, jehož náklady plánu se odhadují, aby se zlepšily, pokud tento index existoval. Každý prvek zobrazuje identifikátor dotazu (dotazovaný) a procento, podle kterého by náklady na plán byly vylepšeny, pokud by se doporučení implementovalo (dimenzionální).

DOPORUČENÍ DROP INDEX a REINDEX

Pro každou databázi, pro kterou se určí funkce ladění indexu, by měla zahájit novou relaci a po dokončení fáze doporučení CREATE INDEX doporučuje vyřazení nebo přeindexování existujících indexů na základě následujících kritérií:

  • Pokud se považuje za duplikát ostatních, odstraňte je.
  • Pokud se nepoužívá pro konfigurovatelnou dobu, zahoďte ji.
  • Přeindexovat indexy, které jsou označené jako neplatné.

Odstranění duplicitních indexů

Doporučení pro vyřazení duplicitních indexů: Nejprve určete, které indexy mají duplicity.

Duplicitní položky jsou seřazené na základě různých funkcí, které lze přiřadit indexu a na základě jejich odhadovaných velikostí.

Nakonec doporučuje vyřadit všechny duplicity s nižším hodnocením, než je jeho referenční vedoucí pozice, a popisuje, proč byly jednotlivé duplicity seřazené tak, jak byly.

Aby byly dva indexy považovány za duplicitní, musí:

  • Vytvoří se přes stejnou tabulku.
  • Být index stejného typu.
  • Porovná klíčové sloupce a u klíčů indexu s více sloupci odpovídá pořadí, ve kterém jsou odkazovány.
  • Porovná strom výrazu jeho predikátu. Platí pouze pro částečné indexy.
  • Porovná strom výrazu všech odkazů na sloupce bezsimple. Platí pouze pro indexy vytvořené ve výrazech.
  • Porovná kolaci jednotlivých sloupců odkazovaných v klíči.

Vyřazení nepoužívaných indexů

Doporučení pro vyřazení nepoužívaných indexů identifikují tyto indexy, které:

  • Nepoužívá se nejméně index_tuning.unused_min_period dny.
  • Zobrazí minimální (denní průměr) počtu index_tuning.unused_dml_per_table dynamické správy v tabulce, ve které se index vytvoří.
  • Zobrazí minimální (denní průměr) počet index_tuning.unused_reads_per_table čtení v tabulce, ve které se index vytvoří.

Přeindexovat neplatné indexy

Doporučení pro přeindexování existujících indexů identifikují indexy, které jsou označené jako neplatné. Další informace o tom, proč a kdy jsou indexy označené jako neplatné, najdete v oficiální dokumentaci k REINDEX v PostgreSQL.

Výpočet dopadu doporučení DROP INDEX

Dopad doporučení indexu poklesu se měří na dvě dimenze: Výhoda (procento) a IndexSize (megabajty).

Výhoda je jedna hodnota, kterou je možné prozatím ignorovat.

IndexSize je jedna hodnota, která představuje odhadovanou velikost indexu vzhledem k aktuální kardinalitě tabulky a velikosti sloupců odkazovaných doporučeným indexem.

Konfigurace ladění indexu

Ladění indexu je možné povolit, zakázat a nakonfigurovat prostřednictvím sady parametrů, které řídí jeho chování.

Při povoleném ladění indexu se aktivuje frekvence nakonfigurovaná v parametru index_tuning.analysis_interval serveru (výchozí hodnota je 720 minut nebo 12 hodin) a začne analyzovat úlohu zaznamenanou úložištěm dotazů během tohoto období.

Všimněte si, že pokud změníte hodnotu pro index_tuning.analysis_interval, bude pozorována pouze po dokončení dalšího naplánovaného spuštění. Pokud například povolíte ladění indexu jeden den v 10:00,00, protože výchozí hodnota je index_tuning.analysis_interval 720 minut, první spuštění je naplánováno tak, aby se ve stejný den spustilo v 10:00. Všechny změny provedené v hodnotě index_tuning.analysis_interval od 10:00 do 10:00 nebudou mít vliv na tento počáteční plán. Pouze když se naplánované spuštění dokončí, přečte aktuální hodnotu nastavenou pro index_tuning.analysis_interval a naplánuje další spuštění podle této hodnoty.

Pro konfiguraci parametrů ladění indexu jsou k dispozici následující možnosti:

parameter Popis výchozí Rozsah Jednotky
index_tuning.analysis_interval Nastaví frekvenci, s jakou se každá relace optimalizace indexu aktivuje při nastavení index_tuning.mode .REPORT 720 60 - 10080 minuty
index_tuning.max_columns_per_index Maximální počet sloupců, které mohou být součástí klíče indexu pro libovolný doporučený index. 2 1 - 10
index_tuning.max_index_count Maximální počet indexů doporučených pro každou databázi během jedné relace optimalizace. 10 1 - 25
index_tuning.max_indexes_per_table Maximální počet indexů, které se dají doporučit pro každou tabulku. 10 1 - 25
index_tuning.max_queries_per_database Počet nejpomalejšíchdotazůch 25 5 - 100
index_tuning.max_regression_factor Přijatelná regrese zavedená doporučeným indexem u kteréhokoli z dotazů analyzovaných během jedné relace optimalizace. 0.1 0.05 - 0.2 procento
index_tuning.max_total_size_factor Maximální celková velikost v procentech celkového místa na disku, které můžou používat všechny doporučené indexy pro libovolnou databázi. 0.1 0 - 1 procento
index_tuning.min_improvement_factor Zlepšení nákladů, které doporučený index musí poskytnout alespoň jednomu z dotazů analyzovaných během jedné relace optimalizace. 0.2 0 - 20 procento
index_tuning.mode Nakonfiguruje optimalizaci indexu jako zakázanou (OFF) nebo povolí pouze generování doporučení. Vyžaduje, aby úložiště dotazů bylo povoleno nastavením pg_qs.query_capture_mode nebo TOPALL. OFF OFF, REPORT
index_tuning.unused_dml_per_table Minimální počet denních průměrných operací DML ovlivňujících tabulku, takže jejich nepoužívané indexy se považují za vyřazení. 1000 0 - 9999999
index_tuning.unused_min_period Minimální počet dnů, po které index nebyl použit na základě systémových statistik, takže se považuje za vyřazení. 35 30 - 70
index_tuning.unused_reads_per_table Minimální počet denních průměrných operací čtení, které ovlivňují tabulku, aby se jejich nepoužívané indexy považovaly za vyřazení. 1000 0 - 9999999

Pokud používáte příkazy az postgres flexible-server index-tuning show-settings rozhraní příkazového řádku a az postgres flexible-server index-tuning set-settings k zobrazení nebo úpravě nastavení ladění indexu, hodnoty přijaté jako argumenty parametru --name jsou hodnoty zobrazené ve sloupci Parametr předchozí tabulky, ale bez zahrnutí předpony index_tuning..

Informace vytvořené laděním indexu

Jak číst, interpretovat a používat doporučení vytvořená laděním indexů podrobně popisuje, jak získat a používat doporučení vytvořená laděním indexu.

Omezení a možnosti podpory

Následuje seznam omezení a rozsahu možností podpory pro ladění indexu.

Automatické odstranění doporučení

Doporučení se automaticky odstraní 35 dnů po jejich posledním vytvoření. Aby tento mechanismus automatického odstranění fungoval, musí být povolené ladění indexu.

Závislost na rozšíření hypopg

Pro ladění indexu pro vytváření doporučení CREATE INDEX používá rozšíření hypopg .

Pokud rozšíření již existuje při zahájení relace ladění, použije se ve schématu, ve kterém byla vytvořena. A když se relace ladění dokončí, rozšíření se nezahodí. Výjimkou je, pokud se rozšíření vytvořilo ve schématu pg_catalog . Pokud tomu tak je, ladění indexu rozšíření zahodí.

Pokud rozšíření nebylo na prvním místě nebo jsme ho odstranili, protože bylo vytvořeno ve pg_catalog schématu, ladění indexu ho vytvoří pod volanou ms_temp_recommendations709253 schématem a po úspěšném dokončení relace ladění rozšíření zahodí a odebere schéma.

Uživatelé, kteří jsou členy azure_pg_admin role, můžou rozšíření hypopg v libovolném okamžiku vyřadit, i když ho vytvořila funkce ladění indexu. Když je však spuštěná relace ladění indexu, může to způsobit selhání relace a nevytvářet žádná doporučení.

Podporované úrovně výpočetních prostředků a skladové položky

Ladění indexů se podporuje na všech aktuálně dostupných úrovních: burstable, Pro obecné účely a Optimalizováno pro paměť a na všech aktuálně podporovaných výpočetních SKU s alespoň 4 virtuálními jádry.

Podporované verze PostgreSQL

Ladění indexů se podporuje u hlavních verzí12 nebo vyšších instancí flexibilních serverů Azure Database for PostgreSQL.

Použití search_path

Ladění indexu spotřebovává hodnotu uloženou ve sloupci search_pathquery_store.qs_view, aby při analýze každého dotazu byla nastavena stejná hodnota search_path , která byla nastavena při spuštění dotazu, je ta, na kterou je nastavená k analýze možných doporučení.

Parametrizované dotazy

Parametrizované dotazy vytvořené pomocí protokolu PREPARE nebo pomocí rozšířeného protokolu dotazu se analyzují a analyzují, aby se na nich vytvořila doporučení indexu.

Pro analýzu parametrizovaných dotazů vyžaduje ladění indexu, aby pg_qs.parameters_capture_mode byla nastavena, capture_first_sample když úložiště dotazů zachycuje provádění dotazu. Vyžaduje také správné zachycení parametrů úložištěm dotazů při spuštění dotazu. Jinými slovy, pro analyzovaný dotaz musí mít query_store.qs_view nastavený sloupec parameters_capture_status na succeededhodnotu .

Režim jen pro čtení a repliky pro čtení

Vzhledem k tomu, že ladění indexu závisí na úložišti dotazů, které není podporováno v replikách pro čtení nebo když je instance v režimu jen pro čtení, nepodporujeme ho u replik pro čtení ani na instancích, které jsou v režimu jen pro čtení.

Všechna doporučení zobrazená u repliky pro čtení byla vytvořena na primární replice po analýze výhradně úlohy spuštěné na primární replice.

Vertikální snížení kapacity výpočetních prostředků

Pokud je na serveru povolené ladění indexu a vertikálně snížíte kapacitu výpočetních prostředků tohoto serveru na méně než minimální počet požadovaných virtuálních jader, zůstane tato funkce povolená. Vzhledem k tomu, že tato funkce není podporovaná na serverech s méně než 4 virtuálními jádry, nespustí se k analýze úlohy a vytváření doporučení, i když index_tuning.mode byla nastavena na ON při snížení výpočetní kapacity. I když server nesplňuje minimální požadavky, všechny index_tuning.* parametry serveru jsou nepřístupné. Pokaždé, když škálujete server zpět na výpočetní prostředky, které splňují minimální požadavky, je nakonfigurovaná libovolná hodnota, kterou jste nastavili předtím index_tuning.mode , než jste vertikálně nastavili kapacitu na výpočetní výkon, který nesplňuje požadavky.

Vysoká dostupnost a repliky pro čtení

Pokud máte na serveru nakonfigurované vysoké dostupnosti nebo repliky pro čtení, při implementaci doporučených indexů mějte na paměti důsledky související s vytvářením úloh náročných na zápis na primárním serveru. Při vytváření indexů, jejichž velikost se odhaduje na velký, buďte obzvláště opatrní.

Důvody, proč ladění indexů nemusí vést k vytváření doporučení pro vytváření indexů pro určité dotazy

Následuje seznam typů dotazů, pro které ladění indexu nevygeneruje doporučení CREATE INDEX. Ty, které:

  • Při pokusu o získání výstupu EXPLAIN během fáze analýzy dojde k chybě.
  • Referenční tabulky, které nemají statistiky o jejich obsahu v katalogu systému pg_statistic Spusťte funkci ANALYZE u těchto tabulek, aby modul ladění mohl vzít v úvahu tyto dotazy v budoucnu.
  • Zkrátí se text dotazu v úložišti dotazů. To je případ, kdy délka textu dotazu překračuje hodnotu nakonfigurovanou v pg_qs.max_query_text_length.
  • Odkazování na objekty, které byly vyřazeny nebo přejmenovány před analýzou. Tyto dotazy mohou být stále syntakticky platné, ale ne séanticky platné.
  • Přístup k dočasným tabulkám nebo indexům dočasných tabulek
  • Přístup k zobrazením nebo materializovaným zobrazením
  • Přístup k děleným tabulkám
  • Jsou identifikovány jako příkazy nástroje. Příkazy nástroje nebo příkazy nástroje jsou v podstatě všechny příkazy, které nejsou považovány za SELECT, INSERT, UPDATE, DELETE nebo MERGE a některé příkazy obsahující jeden z těchto příkazů.
  • Nejsou mezi nejpomalejšími index_tuning.max_queries_per_database analyzovanými databázemi a obdobími.
  • Byly spuštěny v kontextu jedné konkrétní databáze, když se žádný z těchto dotazů na úrovni serveru neidentibroval jako nejpomalejší.

Související obsah

  • Last updated on