Sdílet prostřednictvím

Ladění indexů na flexibilním serveru Azure Database for PostgreSQL

  • Článek

PLATÍ PRO: Flexibilní server Azure Database for PostgreSQL

Ladění indexů je funkce 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 flexibilního serveru Azure Database for PostgreSQL, která vychází z výkonu monitorování s využitím 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í, jako je například četnost spuštění relace ladění.

Prozkoumejte všechny podrobnosti o správné konfiguraci funkce ladění indexu v tom, jak povolit, zakázat a nakonfigurovat ladění indexu.

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.

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ů je podporováno na flexibilním serveru Azure Database for PostgreSQL verze 12 nebo vyšší .

Použití search_path

Ladění indexu spotřebovává hodnotu uloženou ve sloupci search_path query_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 k vytváření doporučení, i když index_tuning.mode byla nastavená na ON dobu vertikálního snížení kapacity výpočetních prostředků. 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í.

Související obsah