Sdílet prostřednictvím

Autonomní ladění

Autonomní ladění je funkce ve vaší instanci flexibilního serveru Azure Database for PostgreSQL, která analyzuje dotazy trasované vaší úlohou a poskytuje doporučení ke zlepšení výkonu těchto dotazů.

Jedná se o integrovanou nabídku v instanci flexibilního serveru Azure Database for PostgreSQL, která vychází z funkcí úložiště dotazů . Autonomní ladění analyzuje pracovní zatížení sledované úložištěm dotazů a vytváří doporučení pro indexy nebo tabulky ke zlepšení výkonu analyzovaného pracovního zatížení. Může vytvářet doporučení pro vytváření nových indexů, odstranění duplicitních nebo nepoužívaných indexů, analýzu tabulek, které nemají žádné statistiky nebo mají zastaralé statistiky, nebo vakuování nafouknutých tabulek.

Obecný popis autonomního algoritmu ladění

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 vytvořit, 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 duplicitní nebo se nepoužívají pro konfigurovatelné časové období. Identifikuje také tabulky, které nemají aktuální statistiku nebo nafouklé tabulky.

Doporučení k VYTVOŘENÍ INDEXu

Pro každou databázi identifikovanou jako kandidáta k analýze se všechny dotazy SELECT, UPDATE, INSERT a DELETE spouštěly během vyhledávacího intervalu a v kontextu dané konkrétní databáze.

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, je B-Tree.

Pokud dotaz odkazuje na jeden sloupec tabulky a tato tabulka nemá žádné statistiky, nevygeneruje žádná doporučení indexu ke zlepšení jeho provádění. Vygeneruje ale doporučení k analýze tabulky.

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.

Autonomní ladění automaticky generuje 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 konstantou "_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, která je identifikována jako kandidát, by měla zahájit novou relaci a po dokončení fáze doporučení CREATE INDEX doporučí 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.

Doporučení pro tabulky

Pro každou databázi identifikovanou jako kandidáta k prozkoumání zahájí relaci s cílem vytvořit doporučení na úrovni tabulky. Tato doporučení vás zvou ke spuštění příkazů ANALYZE nebo VACUUM u tabulek, ke kterým přistupují prozkoumané dotazy, přičemž modul ladění zvažuje, že spuštění těchto příkazů by mohlo zlepšit výkon vaší úlohy.

Doporučení k tabulce ANALYZE

Doporučení k analýze tabulky identifikují tyto tabulky, které:

  • Odkazuje se v dotazu a obsahuje nějaký sloupec této tabulky, který se používá v jednom z jejích predikátů (WHERE, JOIN, ORDER BY, GROUP BY), a také splňují jednu z těchto dvou podmínek:
    • Nebyly nikdy analyzovány.
    • V určitém okamžiku byly analyzovány, ale teď chybí statistiky (obvykle kvůli tomu, že server se chybově ukončil před zachováním statistik na disk).

Doporučení k optimalizaci tabulky VACUUM

Doporučení pro čištění tabulky identifikují tabulky, které jsou naddimenzované. Tato doporučení se vytvářejí jenom v případech, kdy autovacuum_enabled není nastavená na off úrovni serveru při analýze úlohy.

Konfigurace autonomního ladění

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

Když je povolené autonomní ladění, probouzí se s frekvencí nakonfigurovanou v parametru serveru index_tuning.analysis_interval (výchozí hodnota je každých 720 minut nebo 12 hodin) a začne analyzovat zátěž zaznamenanou úložištěm dotazů v tomto časovém 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í. Takže pokud například povolíte autonomní ladění jeden den v 10:00, protože výchozí hodnota pro index_tuning.analysis_interval je 720 minut, je první spuštění naplánováno na 22:00 téhož dne. Všechny změny, které provedete v hodnotě index_tuning.analysis_interval od 10:00 do 22:00, nemají 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ů autonomního ladění jsou k dispozici následující možnosti:

Parameter Description výchozí Rozsah Units
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 minutes
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 autonomous-tuning show-settings rozhraní příkazového řádku a az postgres flexible-server autonomous-tuning set-settings k zobrazení nebo úpravě některého z nastavení autonomního ladění, 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é automatickým laděním

Použití autonomních doporučení pro ladění podrobně popisuje, jak získat a využívat doporučení vytvořená autonomním laděním.

Omezení a možnosti podpory

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

Automatické odstranění doporučení

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

Závislost na rozšíření hypopg

Pro autonomní ladění ke generování 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 bylo vytvořeno. A když se ladicí relace dokončí, rozšíření se nezruší. Výjimkou tohoto pravidla je, pokud se rozšíření vytvořilo ve schématu pg_catalog . Pokud je to ten případ, autonomní ladění rozšíření zahodí.

Pokud rozšíření neexistovalo od počátku nebo jsme ho odstranili, protože bylo vytvořeno ve pg_catalog schématu, autonomní ladění ho vytvoří pod schématem nazvaným ms_temp_recommendations709253 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 této role, můžou rozšíření hypopg v libovolném okamžiku vyřadit, i když ho vytvoří funkce autonomního ladění. Když je ale spuštěná autonomní relace ladění, 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

Autonomní ladění je podporováno na všech aktuálně dostupných úrovních: Burstable, General Purpose a Memory Optimized, a na všech aktuálně podporovaných výpočetních SKU s alespoň 4 vCores.

Podporované verze PostgreSQL

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

Použití search_path

Autonomní ladění využívá hodnotu uloženou ve sloupci search_pathquery_store.qs_view, aby při analýze každého dotazu byla použita stejná hodnota search_path, která byla nastavena při původním spuštění dotazu, je ta, která je použita 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 autonomní ladění, aby pg_qs.parameters_capture_mode byl nastaven na capture_first_sample , když úložiště dotazů zaznamenává 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 autonomní ladění spoléhá na data, která úložiště dotazů uchovává místně v azure_sys databázi, což není podporováno v replikách pro čtení nebo když je instance v režimu jen pro čtení, nepodporujeme je v replikách pro čtení ani v 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é autonomní ladění a vertikálně snížíte kapacitu výpočetních prostředků daného 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 na výpočetní výkon, který splňuje minimální požadavky, je nakonfigurovaný s libovolnou hodnotou, index_tuning.mode kterou jste nastavili, než jste ho škálovali 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č automatické ladění nemusí vytvářet doporučení na vytvoření indexu pro určité dotazy

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

  • Při pokusu autonomního ladicího stroje získat jeho výstup 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 tyto dotazy v budoucnu vzít v úvahu.
  • Zkrátí se text dotazu v úložišti dotazů. Jedná se o případ, kdy délka textu dotazu překročí 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 dotazy podle parametru index_tuning.max_queries_per_database pro analyzované období a databázi.
  • 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