Automatisk optimering i Azure Database for PostgreSQL – Flexibel server

Autonom justering är en funktion i Azure Database for PostgreSQL flexibel server som analyserar frågor som registrerats från din arbetsbelastning och ger rekommendationer för att förbättra prestandan för dessa frågor.

Det är en inbyggd funktion i Azure Database for PostgreSQL – flexibel server som bygger vidare på funktionaliteten i frågearkivet. Autonom justering analyserar arbetsbelastningen som spåras av frågearkivet och genererar index- eller tabellrekommendationer för att förbättra prestanda för den analyserade arbetsbelastningen. Den kan ge rekommendationer för att skapa nya index, eliminera duplicerade eller oanvända index, analysera tabeller som inte har någon statistik eller inaktuell statistik eller dammsuga uppsvällda tabeller.

• Identifiera vilka index som är bra att skapa eftersom de avsevärt kan förbättra de frågor som analyseras under en autonom justeringssession.
• Identifiera index som är exakta dubbletter och som kan elimineras.
• Identifiera index som inte används under en konfigurerbar period som kan vara kandidater att eliminera.
• Identifiera index som markerats som ogiltiga och som ska indexeras om för att göra dem till giltiga.
• Identifiera tabeller som saknar aktuell statistik som ska analyseras.
• Identifiera tabeller som är uppsvällda och som ska dammsugas.

Allmän beskrivning av den autonoma justeringsalgoritmen

När du konfigurerar parametern index_tuning.mode till reportstartar systemet automatiskt justeringssessioner med den frekvens som du konfigurerar i parametern index_tuning.analysis_interval , uttryckt i minuter.

I den första fasen söker justeringssessionen efter listan över databaser där rekommendationer kan påverka systemets övergripande prestanda avsevärt. För att göra det samlar den in alla frågor som registrerats av Query Store och vars körningar fångades upp inom det sökintervall som den här justeringssessionen fokuserar på. Uppslagsintervallet sträcker sig för närvarande till de senaste index_tuning.analysis_interval minuterna, från starttiden för justeringssessionen.

För alla användarinitierade frågor med körningar som registrerats i frågearkivet och vars körningsstatistik inte har återställts rangerar systemet dem baserat på deras sammanlagda körningstid. Den fokuserar sin uppmärksamhet på de mest framträdande frågorna, baserat på deras varaktighet.

Följande frågor undantas från listan:

• Systeminitierade förfrågningar. (det vill säga frågor som körs av rollen azuresu)
• Frågor som körs i kontexten för alla systemdatabaser (azure_sys, , template0template1och azure_maintenance).

Algoritmen itererar över måldatabaserna och söker efter möjliga index som kan förbättra prestandan för analyserade arbetsbelastningar. Den söker också efter index som du kan eliminera eftersom de är dubbletter eller inte används under en konfigurerbar tidsperiod. Den identifierar även tabeller som saknar aktuell statistik eller uppsvällda tabeller.

Rekommendationer för CREATE INDEX

För varje databas som identifieras som en kandidat att analysera tar processen hänsyn till alla SELECT-, UPDATE-, INSERT- och DELETE-frågor som körs under uppslagsintervallet och i kontexten för den specifika databasen.

Processen rangordnar den resulterande uppsättningen frågor baserat på deras aggregerade totala körningstid och analyserar toppen index_tuning.max_queries_per_database för möjliga indexrekommendationer.

Potentiella rekommendationer syftar till att förbättra prestandan för dessa typer av frågor:

• Frågor med filter (d.s. frågor med predikat i WHERE-satsen).
• Frågor som ansluter till flera relationer, oavsett om de följer syntaxen där kopplingar uttrycks med JOIN-satsen eller om kopplingspredikaten uttrycks i WHERE-satsen.
• Frågor som kombinerar filter och kopplingspredikat.
• Frågor med gruppering (frågor med en GROUP BY-sats).
• Frågor som kombinerar filter och gruppering.
• Frågor med sortering (frågor med en ORDER BY-sats).
• Frågor som kombinerar filter och sortering.

Om en fråga refererar till en kolumn i en tabell och tabellen inte har någon statistik, ger processen inga indexrekommendationer för att förbättra dess körning. Det genererar dock en rekommendation för att analysera tabellen.

index_tuning.max_indexes_per_table anger antalet index som kan rekommenderas, exklusive index som kanske redan finns i tabellen för en enskild tabell som refereras till av valfritt antal frågor under en justeringssession.

index_tuning.max_index_count anger antalet indexrekommendationer som skapats för alla tabeller i en databas som analyserats under en justeringssession.

För att en indexrekommendering ska genereras måste justeringsmotorn uppskatta att den förbättrar minst en fråga i den analyserade arbetsbelastningen med en faktor som anges med index_tuning.min_improvement_factor.

På samma sätt kontrollerar processen alla indexrekommendationer för att säkerställa att de inte introducerar regression på en enskild fråga i den arbetsbelastningen för en faktor som anges med index_tuning.max_regression_factor.

Alla parametrar som nämns i föregående stycken, deras standardvärden och giltiga intervall beskrivs i konfigurationsalternativ.

Skriptet som skapas tillsammans med rekommendationen att skapa ett index följer det här mönstret:

CREATE INDEX CONCURRENTLY {indexName} ON {schema}.{table}({column_name}[, ...])

Den innehåller klausulen CONCURRENTLY. Mer information om effekterna av den här satsen finns i PostgreSQL officiell dokumentation för CREATE INDEX.

Autonom justering genererar automatiskt namnen på de rekommenderade indexen, som vanligtvis består av namnen på de olika nyckelkolumnerna avgränsade med "_" (understreck) och med ett konstant "_idx" suffix. Om namnets totala längd överskrider PostgreSQL-gränserna eller om det krockar med befintliga relationer är namnet något annorlunda. Det kan trunkeras och ett tal kan läggas till i slutet av namnet.

Beräkna effekten av en CREATE INDEX-rekommendation

Effekten av att skapa en indexrekommendation mäts på IndexSize (megabyte) och QueryCostImprovement (procent).

IndexSize är ett enda värde som representerar indexets uppskattade storlek, med tanke på tabellens aktuella kardinalitet och storleken på de kolumner som refereras till av det rekommenderade indexet.

QueryCostImprovement består av en matris med värden, där varje element representerar förbättringen av planens kostnad för varje fråga vars plankostnad beräknas förbättras om indexet finns. Varje element visar frågans identifierare (frågad) och den procentandel med vilken planens kostnad skulle förbättras om rekommendationen implementerades (dimensionell).

DROP INDEX- och REINDEX-rekommendationer

För varje databas som identifieras som kandidat initierar processen en ny session. När fasen CREATE INDEX-rekommendationer har slutförts rekommenderar vi att du släpper eller indexerar om befintliga index baserat på följande kriterier:

• Ignorera om den anses vara en dubblett av andra poster.
• Släpp om den inte används under en konfigurerbar tidsperiod.
• Indexera om index som har markerats som ogiltiga.

Ta bort duplicerade index

Rekommendationer för att ta bort duplicerade index börjar med att identifiera vilka index som har dubbletter.

Dubbletter rangordnas baserat på olika funktioner som du kan tillskriva indexet och baserat på deras uppskattade storlekar.

Processen rekommenderar slutligen att du släpper alla dubbletter med en lägre rangordning än dess referensledare och beskriver varför varje dubblett rangordnades som den var.

För att två index ska betraktas som dubbletter måste de:

• Skapas över samma tabell.
• Vara ett index av exakt samma typ.
• Se till att deras nyckelkolumner matchar och, för indexnycklar med flera kolumner, att de refereras i rätt ordning.
• Matcha predikatets uttrycksträd. Det här villkoret gäller endast för partiella index.
• Matcha uttrycksträdet för alla icke-enkla kolumnreferenser. Det här villkoret gäller endast för index som skapats i uttryck.
• Matcha sorteringen för varje kolumn som refereras till i nyckeln.

Ta bort oanvända index

Rekommendationer för att ta bort oanvända index identifierar de index som:

• Används inte på minst index_tuning.unused_min_period dagar.
• Visa ett minsta (dagligt genomsnitt) antal index_tuning.unused_dml_per_table DML:er i tabellen där indexet skapas.
• Visa ett minsta (dagligt genomsnitt) antal index_tuning.unused_reads_per_table läsningar i tabellen där indexet skapas.

Indexera om ogiltiga index

Rekommendationer för omindexering av befintliga index identifierar de index som har markerats som ogiltiga. Mer information om varför och när index markeras som ogiltiga finns i den officiella dokumentationen om REINDEX i PostgreSQL.

Beräkna effekten av en DROP INDEX-rekommendation

Effekten av en rekommendation om att ta bort ett index mäts utifrån två dimensioner: Nytta (procent) och IndexSize (megabyte).

Förmånen är ett enda värde som du kan ignorera för tillfället.

IndexSize är ett enda värde som representerar indexets uppskattade storlek, med tanke på tabellens aktuella kardinalitet och storleken på de kolumner som refereras till av det rekommenderade indexet.

Tabellrekommendationer

För varje databas som identifieras som en kandidat att analysera initierar processen en session som syftar till att skapa rekommendationer på tabellnivå. De här rekommendationerna inbjuder dig att köra ANALYZE eller VACUUM på tabellerna som frågorna inspekterade åtkomsten till. Justeringsmotorn anser att körning av dessa kommandon kan förbättra arbetsbelastningens prestanda.

ANALYSERA tabellrekommendationer

Rekommendationer för att analysera en tabell identifierar de tabeller som:

• Refereras i en fråga och har en kolumn i tabellen som används i något av dess predikat (WHERE, , JOINORDER BY, GROUP BY), och uppfyller även något av följande två villkor:
  • Analyseras aldrig.
  • Analyserades någon gång, men saknar nu statistik (vanligtvis eftersom servern kraschade innan statistiken bevarades till disk).

VACUUM-tabellrekommendationer

Rekommendationer för att dammsuga en tabell identifierar de tabeller som är uppsvällda. Processen genererar endast dessa rekommendationer när autovacuum_enabled inte anges till off på servernivå när arbetsbelastningen analyseras.

Konfigurera autonom justering

Du kan aktivera, inaktivera och konfigurera autonom justering via en uppsättning parametrar som styr dess beteende.

När du aktiverar autonom justering aktiveras den med en frekvens som konfigurerats i parametern index_tuning.analysis_interval (som standard är 720 minuter eller 12 timmar) och börjar analysera arbetsbelastningen som registrerats av frågearkivet under den perioden.

Om du ändrar värdet för index_tuning.analysis_intervalbörjar det nya värdet gälla först när nästa schemalagda körning har slutförts. Om du till exempel aktiverar autonom justering en dag kl. 10:00, eftersom standardvärdet för index_tuning.analysis_interval är 720 minuter, är den första körningen schemalagd att starta kl. 22:00 samma dag. Eventuella ändringar som du gör i index_tuning.analysis_interval värdet mellan 10:00 och 22:00 påverkar inte det ursprungliga schemat. Endast när den schemalagda körningen har slutförts läser den det aktuella värdet som angetts för index_tuning.analysis_interval och schemalägger nästa körning enligt det värdet.

Använd följande alternativ för att konfigurera autonoma justeringsparametrar:

Om du använder CLI-kommandona az postgres flexible-server autonomous-tuning show-settings och az postgres flexible-server autonomous-tuning set-settings för att visa eller ändra någon av de autonoma justeringsinställningarna är de värden som accepteras som argument för parametern --name de som visas i kolumnen Parameter i föregående tabell, men utan att inkludera prefixet index_tuning..

Information som produceras av autonom justering

Använd rekommendationer för autonom justering beskriver i detalj hur du hämtar och använder rekommendationerna från autonom justering.

Begränsningar och supportmöjligheter

I följande lista beskrivs begränsningarna och supportomfånget för autonom justering.

Automatisk borttagning av rekommendationer

Systemet tar automatiskt bort rekommendationer 35 dagar efter den senaste gången de skapades. För att den här automatiska borttagningsmekanismen ska fungera måste du aktivera autonom justering.

Beroende av hypopg-tillägg

För att skapa CREATE INDEX rekommendationer använder automatisk justering hypopg-tillägget.

Om tillägget finns när en justeringssession börjar använder processen det i schemat där det skapades. När justeringssessionen är klar släpper processen inte tillägget. Ett undantag till den här regeln är om tillägget skapades i pg_catalog schemat. Om så är fallet, avaktiverar autonom konfigurering tillägget.

Om tillägget inte fanns i första hand eller om processen tar bort det eftersom det skapades i pg_catalog schemat, skapar autonom justering det under ett schema med namnet ms_temp_recommendations709253. När justeringssessionen har slutförts tar processen bort tillägget och tar bort schemat.

Användare som har rollen azure_pg_admin kan när som helst ta bort hypopg-tillägget, även när funktionen för autonom justering skapade det. Att släppa den medan en autonom justeringssession körs kan dock leda till att sessionen misslyckas och inte ger några rekommendationer.

Beräkningsnivåer och SKU:er som stöds

Azure Database for PostgreSQL flexibel server stöder autonom justering på alla tillgängliga nivåer: Burstable, Generell användning och Minnesoptimerad. Den stöder även autonom justering på alla beräknings-SKU:er som stöds för närvarande med minst 4 virtuella kärnor.

Versioner av PostgreSQL som stöds

Azure Database for PostgreSQL Flexibel server stöder autonom optimering för huvudversioner12 eller senare.

Användning av search_path

Autonom justering använder värdet i search_path kolumnen query_store.qs_view. När den analyserar varje fråga använder den samma search_path värde som angavs när frågan ursprungligen kördes för att analysera möjliga rekommendationer.

Parametriserade frågor

Parametriserade frågor som skapats med PREPARE eller med hjälp av det utökade frågeprotokollet parsas och analyseras för att skapa indexrekommendationer.

För analys av parametriserade frågor kräver autonom justering att pg_qs.parameters_capture_mode anges till capture_first_sample när frågearkivet registrerar körningen av frågan. Det kräver också att Query Store korrekt registrerar parametrarna när frågan körs. För den fråga som analyseras parameters_capture_status måste kolumnen i query_store.qs_view med andra ord anges till succeeded.

Skrivskyddat läge och läsrepliker

Eftersom automatisk justering förlitar sig på de data som frågearkivet bevarar lokalt i azure_sys databasen, och läsrepliker eller när en server är i skrivskyddat läge inte stöds, stöds funktionen inte på läsrepliker eller på servrar som är i skrivskyddat läge.

Alla rekommendationer som du ser på en läsreplik genererades på den primära repliken efter att ha analyserat enbart den arbetsbelastning som kördes på den primära repliken.

Nedskalning av beräkningsresurser

Om du aktiverar autonom justering på en server och sedan skalar ned serverns beräkning till mindre än det minsta antalet nödvändiga virtuella kärnor förblir funktionen aktiverad. Eftersom funktionen inte stöds på servrar med mindre än 4 virtuella kärnor körs den inte för att analysera arbetsbelastningen och skapa rekommendationer, även om index_tuning.mode den har angetts till ON när du skalade ned beräkningen. Även om servern inte uppfyller minimikraven är alla index_tuning.* parametrar otillgängliga. När du skalar servern tillbaka till en beräkning som uppfyller minimikraven index_tuning.mode konfigureras den med det värde som angavs innan du skalade ned den till en beräkning som inte uppfyllde kraven.

Hög tillgänglighet och läsrepliker

Om du konfigurerar hög tillgänglighet eller läsrepliker på servern bör du vara medveten om konsekvenserna av att skapa skrivintensiva arbetsbelastningar på den primära servern när du implementerar de rekommenderade indexen. Var särskilt försiktig när du skapar index vars storlek uppskattas vara stor.

Orsaker till varför autonom justering kanske inte skapar indexrekommendationer för vissa frågor

Autonom justering genererar CREATE INDEX inte rekommendationer för följande typer av frågor:

• Frågor som stöter på ett fel när den autonoma justeringsmotorn försöker hämta sina EXPLAIN-utdata under analysfasen.
• Frågor som refererar till tabeller utan statistik om deras innehåll i systemkatalogen pg_statistic . Kör ANALYZE på dessa tabeller så att justeringsmotorn kan överväga dessa frågor i framtiden.
• Frågor med avkortad frågetext i Query Store. Den här trunkeringen sker när frågetextens längd överskrider det värde som konfigurerats i pg_qs.max_query_text_length.
• Frågor som refererar till objekt som du tappade eller bytte namn på innan analysen sker. Dessa frågor kan fortfarande vara syntaktiskt giltiga, men de är inte semantiskt giltiga.
• Frågor som har åtkomst till tillfälliga tabeller eller index i temporära tabeller.
• Frågor som har åtkomst till vyer eller materialiserade vyer.
• Frågor som har åtkomst till partitionerade tabeller.
• Frågor som har identifierats som nyttopåståenden. Hjälpinstruktioner eller hjälpkommandon är i princip alla instruktioner som inte betraktas som SELECT, INSERT, UPDATE, DELETE eller MERGE, samt vissa kommandon som innehåller någon av dessa instruktioner.
• Frågor som inte är bland de index_tuning.max_queries_per_database långsammaste för databasen och perioden som analyseras.
• Frågor som körs i kontexten för en specifik databas, när ingen av dessa frågor identifieras som den långsammaste på servernivå.

Relaterat innehåll

• Frågearkiv.
• Konfigurera självstyrd justering.
• Använd rekommendationer för autonom justering.