Zelfafstemming

Autonome afstemming is een functie in uw flexibele Azure Database for PostgreSQL-serverexemplaar die query's analyseert die zijn getraceerd vanuit uw workload en aanbevelingen geeft om de prestaties van die query's te verbeteren.

Het is een ingebouwde functie van uw Azure Database for PostgreSQL flexibele serverinstantie, die gebruikmaakt van de functionaliteit van het queryarchief. Autonome tuning analyseert de workload die wordt bijgehouden door de querystore en produceert index- of tabelaanbevelingen om de prestaties van de geanalyseerde workload te verbeteren. Het kan aanbevelingen opleveren voor het maken van nieuwe indexen, het elimineren van dubbele of ongebruikte indexen, het analyseren van tabellen zonder statistieken of verouderde statistieken, of het vacuüm trekken van opgeblazen tabellen.

Algemene beschrijving van het autonome afstemmingsalgoritmen

Wanneer de parameter index_tuning.mode is ingesteld op report, worden afstemsessies automatisch gestart met de frequentie die in parameter index_tuning.analysis_interval is ingesteld, uitgedrukt in minuten.

In de eerste fase zoekt de afstemmingssessie naar de lijst met databases waarin wordt gekeken of de aanbevelingen die het kan produceren, van invloed kunnen zijn op de algehele prestaties van het systeem. Hiervoor worden alle query's verzameld die zijn vastgelegd door Query Store en waarvan de uitvoeringen zijn vastgelegd binnen het zoekinterval waarop deze afstemsessie zich richt. De opzoekperiode loopt momenteel index_tuning.analysis_interval minuten terug in de tijd, gerekend vanaf het begin van de afstemsessie.

Voor alle door de gebruiker geïnitieerde query's met uitvoeringen die zijn vastgelegd in het queryarchief en waarvan de runtimestatistieken niet opnieuw worden ingesteld, rangschikt het systeem deze op basis van de geaggregeerde totale uitvoeringstijd. Het richt zich op de meest prominente query's, op basis van hun duur.

De volgende query's worden uitgesloten van die lijst:

  • Door het systeem geïnitieerde query's. (dat wil zeggen, query's die door de rol azuresu worden uitgevoerd)
  • Query's die worden uitgevoerd in de context van een systeemdatabase (azure_sys, template0, template1en azure_maintenance).

Het algoritme doorloopt de doeldatabases en zoekt naar mogelijke indexen die de prestaties van geanalyseerde workloads kunnen verbeteren. Zoekt ook naar indexen die kunnen worden geëlimineerd omdat ze duplicaten zijn of niet worden gebruikt voor een configureerbare periode. Identificeert ook tabellen die geen huidige statistieken of opbloede tabellen hebben.

AANBEVELINGEN VOOR CREATE INDEX

Voor elke database die is geïdentificeerd als kandidaat om te analyseren, worden alle SELECT-, UPDATE-, INSERT- en DELETE-query's uitgevoerd tijdens het opzoekinterval en in de context van die specifieke database opgenomen.

De resulterende verzameling query’s wordt gerangschikt op basis van hun geaggregeerde totale uitvoeringstijd, en de top-index_tuning.max_queries_per_database wordt geanalyseerd op mogelijke indexaanbevelingen.

Mogelijke aanbevelingen zijn gericht op het verbeteren van de prestaties van deze typen query's:

  • Query's met filters (dat wil gezegd, query's met predicaten in de WHERE-component),
  • Query's waarin meerdere relaties worden gecombineerd, ongeacht of ze de syntaxis volgen waarbij joins met de JOIN-clausule worden uitgedrukt of waarbij de joinpredicaten in de WHERE-clausule worden uitgedrukt.
  • Query’s die filters en joinpredicaten combineren.
  • Query's met groepering (query's met een GROUP BY-component).
  • Query's waarbij filters en groepering worden gecombineerd.
  • Query's met sortering (query's met een ORDER BY-component).
  • Query’s die filters en sorteren combineren.

Opmerking

Het enige type indexen dat het systeem momenteel aanbeveelt, is B-Tree.

Als een query verwijst naar één kolom van een tabel en die tabel geen statistieken heeft, worden er geen indexaanaanvelingen geproduceerd om de uitvoering ervan te verbeteren. Er wordt echter een aanbeveling gegenereerd om de tabel te analyseren.

index_tuning.max_indexes_per_table geeft het aantal indexen op dat kan worden aanbevolen, met uitzondering van indexen die al in de tabel aanwezig kunnen zijn voor elke tabel waarnaar wordt verwezen door een willekeurig aantal query's tijdens een afstemmingssessie.

index_tuning.max_index_count specificeert het aantal indexaanbevelingen dat wordt gegenereerd voor alle tabellen in elke database die tijdens een afstemsessie wordt geanalyseerd.

Om een indexaanbeveling te laten genereren, moet de afstemmingsengine schatten dat deze ten minste één query in de geanalyseerde workload verbetert met een factor die met index_tuning.min_improvement_factor is opgegeven.

Op dezelfde manier worden alle indexaanbeveling gecontroleerd om ervoor te zorgen dat ze geen regressie introduceren voor één query in die workload van een factor die is opgegeven met index_tuning.max_regression_factor.

Opmerking

index_tuning.min_improvement_factor en index_tuning.max_regression_factor beide verwijzen naar de kosten van queryplannen, niet naar hun duur of de resources die ze tijdens de uitvoering gebruiken.

Alle parameters die in de voorgaande alinea's worden genoemd, evenals hun standaardwaarden en geldige bereiken, worden beschreven in configuratieopties.

Het script dat samen met de aanbeveling voor het maken van een index wordt geproduceerd, volgt dit patroon:

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

Het bevat de component CONCURRENTLY. Ga naar de officiële documentatie van PostgreSQL voor CREATE INDEX voor meer informatie over de effecten van deze component.

Autonoom afstemmen genereert automatisch de namen van de aanbevolen indexen, die doorgaans bestaan uit de namen van de verschillende sleutelkolommen gescheiden door '_' (onderstrepingstekens) en met een constant achtervoegsel '_idx'. Als de totale lengte van de naam de Limieten van PostgreSQL overschrijdt of als deze conflicteert met bestaande relaties, is de naam iets anders. Het kan worden afgekapt en er kan een getal worden toegevoegd aan het einde van de naam.

De impact van een aanbeveling voor CREATE INDEX berekenen

De impact van het maken van een indexaanbeveling wordt gemeten op IndexSize (megabytes) en QueryCostImprovement (percentage).

IndexSize is één waarde die de geschatte grootte van de index aangeeft, rekening houdend met de huidige kardinaliteit van de tabel en de grootte van de kolommen waarnaar wordt verwezen door de aanbevolen index.

QueryCostImprovement bestaat uit een matrix met waarden, waarbij elk element de verbetering van de kosten van het plan vertegenwoordigt voor elke query waarvan de kosten van het plan naar schatting worden verbeterd als deze index bestaat. Elk element toont de ID van de query (queried) en het percentage waarmee de kosten van het plan zouden afnemen als de aanbeveling zou worden geïmplementeerd (dimensional).

AANBEVELINGEN VOOR DROP INDEX en REINDEX

Voor elke database die is geïdentificeerd als kandidaat, moet deze een nieuwe sessie starten en nadat de fase CREATE INDEX-aanbevelingen is voltooid, wordt aanbevolen bestaande indexen te verwijderen of opnieuw te indexeren op basis van de volgende criteria:

  • Neerzetten als het wordt beschouwd als duplicaat van anderen.
  • Neerzetten als deze niet wordt gebruikt voor een configureerbare hoeveelheid tijd.
  • Indexen die als ongeldig zijn gemarkeerd, opnieuw indexeren.

Dubbele indexen verwijderen

Aanbevelingen voor het verwijderen van dubbele indexen: bepaal eerst welke indexen dubbele waarden hebben.

Duplicaten worden gerangschikt op basis van verschillende functies die kunnen worden toegeschreven aan de index en op basis van hun geschatte grootten.

Ten slotte beveelt het aan om alle duplicaten met een lagere rangschikking dan de referentieleider te verwijderen en beschrijft het waarom elk duplicaat zo is gerangschikt.

Twee indexen moeten als duplicaat worden beschouwd:

  • U kunt deze maken via dezelfde tabel.
  • Wees een index van hetzelfde type.
  • Zorg dat hun sleutelkolommen overeenkomen en, bij indexsleutels met meerdere kolommen, dat ook de volgorde waarin ernaar wordt verwezen overeenkomt.
  • Komt overeen met de expressiestructuur van het predicaat. Alleen van toepassing op gedeeltelijke indexen.
  • Vergelijk de expressieboom van alle niet-eenvoudige kolomverwijzingen. Alleen van toepassing op indexen die zijn gemaakt op expressies.
  • Komt overeen met de sortering van elke kolom waarnaar in de sleutel wordt verwezen.

Ongebruikte indexen verwijderen

Aanbevelingen voor het verwijderen van ongebruikte indexen identificeren de indexen die:

  • Worden ten minste index_tuning.unused_min_period dagen niet gebruikt.
  • Geef een minimumaantal index_tuning.unused_dml_per_table (dagelijks gemiddelde) DML's weer in de tabel waarin de index wordt gemaakt.
  • Een minimumaantal index_tuning.unused_reads_per_table leesbewerkingen (dagelijks gemiddelde) weergeven in de tabel waarin de index wordt gemaakt.

Ongeldige indexen opnieuw indexeren

Aanbevelingen voor het opnieuw indexeren van bestaande indexen identificeren de indexen die als ongeldig zijn gemarkeerd. Raadpleeg de officiële documentatie van REINDEX in PostgreSQL voor meer informatie over waarom en wanneer indexen als ongeldig zijn gemarkeerd.

De impact van een DROP INDEX-aanbeveling berekenen

De impact van een aanbeveling voor een dalingsindex wordt gemeten op twee dimensies: Benefit (percentage) en IndexSize (megabytes).

Het voordeel is één waarde die voorlopig kan worden genegeerd.

IndexSize is één waarde die de geschatte grootte van de index aangeeft, rekening houdend met de huidige kardinaliteit van de tabel en de grootte van de kolommen waarnaar wordt verwezen door de aanbevolen index.

Aanbevelingen voor tabellen

Voor elke database die is geïdentificeerd als kandidaat om te analyseren, wordt er een sessie gestart die is gericht op het produceren van aanbevelingen op tabelniveau. Deze aanbevelingen nodigen u uit om ANALYZE of VACUUM uit te voeren op de tabellen die worden benaderd door de geïnspecteerde query's, waarbij de afstemmingsengine van mening is dat het uitvoeren van deze opdrachten de prestatie van uw werklast kan verbeteren.

Aanbevelingen voor ANALYZE-table

Aanbevelingen voor het analyseren van een tabel identificeren de tabellen die:

  • Er wordt in een query verwezen naar een tabel, en een of meer kolommen van die tabel worden gebruikt in een van de predicaten (WHERE, JOIN, ORDER BY, GROUP BY), en voldoen daarnaast aan een van de volgende twee voorwaarden:
    • Zijn nog nooit geanalyseerd.
    • Zijn op een bepaald moment geanalyseerd, maar er ontbreken nu statistieken (meestal omdat de server is vastgelopen voordat de statistieken op schijf werden bewaard).

Aanbevelingen voor VACUUM-tabellen

Aanbevelingen voor het optimaliseren van een tabel identificeren de tabellen die opgeblazen zijn. Deze aanbevelingen worden alleen geproduceerd wanneer autovacuum_enabled niet is ingesteld op off op serverniveau wanneer de workload wordt geanalyseerd.

Configureren van autonome optimalisatie

Autonoom afstemmen kan worden ingeschakeld, uitgeschakeld en geconfigureerd via een set parameters waarmee het gedrag wordt bepaald.

Wanneer autonome afstemming is ingeschakeld, wordt deze actief met een frequentie die is geconfigureerd in de parameter index_tuning.analysis_interval (standaard 720 minuten of 12 uur) en begint deze de workload te analyseren die in die periode is vastgelegd door Query Store.

Als u de waarde voor index_tuning.analysis_intervalwijzigt, wordt deze alleen waargenomen nadat de volgende geplande uitvoering is voltooid. Als u bijvoorbeeld autonoom afstemmen op één dag om 10:00 uur inschakelt, omdat de standaardwaarde index_tuning.analysis_interval 720 minuten is, wordt de eerste uitvoering gepland om diezelfde dag om 10:00 uur. Wijzigingen die u aanbrengt in de waarde van index_tuning.analysis_interval tussen 10:00 uur 's morgens en 22:00 uur 's avonds, hebben geen effect op het oorspronkelijke schema. Alleen wanneer de geplande uitvoering is voltooid, wordt de huidige waarde gelezen die is ingesteld voor index_tuning.analysis_interval en wordt de volgende uitvoering volgens die waarde gepland.

De volgende opties zijn beschikbaar voor het configureren van autonome afstemmingsparameters:

Parameter Beschrijving standaard Range Units
index_tuning.analysis_interval Hiermee stelt u de frequentie in waarop elke indexoptimalisatiesessie wordt geactiveerd wanneer index_tuning.mode is ingesteld op REPORT. 720 60 - 10080 minutes
index_tuning.max_columns_per_index Maximum aantal kolommen dat deel kan uitmaken van de indexsleutel voor elke aanbevolen index. 2 1 - 10
index_tuning.max_index_count Maximum aantal indexen dat wordt aanbevolen voor elke database tijdens één optimalisatiesessie. 10 1 - 25
index_tuning.max_indexes_per_table Maximum aantal indexen dat voor elke tabel kan worden aanbevolen. 10 1 - 25
index_tuning.max_queries_per_database Aantal traagste query's per database waarvoor indexen kunnen worden aanbevolen. 25 5 - 100
index_tuning.max_regression_factor Acceptabele regressie die is geïntroduceerd door een aanbevolen index op een van de query's die tijdens één optimalisatiesessie worden geanalyseerd. 0.1 0.05 - 0.2 procent
index_tuning.max_total_size_factor Maximale totale grootte, in percentage van de totale schijfruimte, die alle aanbevolen indexen voor een bepaalde database kunnen gebruiken. 0.1 0 - 1 procent
index_tuning.min_improvement_factor Kostenverbetering die een aanbevolen index moet bieden aan ten minste één van de query's die tijdens één optimalisatiesessie worden geanalyseerd. 0.2 0 - 20 procent
index_tuning.mode Configureert indexoptimalisatie als uitgeschakeld (OFF) of ingeschakeld om alleen aanbevelingen te verzenden. Vereist dat de query store is ingeschakeld door pg_qs.query_capture_mode in te stellen op TOP of ALL. OFF OFF, REPORT
index_tuning.unused_dml_per_table Minimaal aantal dagelijkse gemiddelde DML-bewerkingen die van invloed zijn op de tabel, zodat hun ongebruikte indexen worden overwogen om te worden verwijderd. 1000 0 - 9999999
index_tuning.unused_min_period Het minimum aantal dagen dat de index niet is gebruikt, op basis van systeemstatistieken, wordt daarom overwogen om te worden verwijderd. 35 30 - 70
index_tuning.unused_reads_per_table Minimaal aantal dagelijkse gemiddelde leesbewerkingen die van invloed zijn op de tabel, zodat hun ongebruikte indexen worden overwogen om te worden verwijderd. 1000 0 - 9999999

Als u de CLI-opdrachten az postgres flexible-server autonomous-tuning show-settings gebruikt en az postgres flexible-server autonomous-tuning set-settings een van de autonome afstemmingsinstellingen wilt weergeven of wijzigen, worden de waarden die als argumenten voor de --name parameter worden geaccepteerd, weergegeven in de kolom Parameter van de vorige tabel, maar zonder het voorvoegsel index_tuning..

Informatie geproduceerd door autonome afstemming

Gebruik van autonome afstemmingsaanbevelingen beschrijft in detail hoe u de aanbevelingen kunt verkrijgen en toepassen die worden geproduceerd door autonome afstemming.

Beperkingen en ondersteuning

Hieronder vindt u de lijst met beperkingen en ondersteuningsmogelijkheden voor autonome afstemming.

Automatisch verwijderen van aanbevelingen

Aanbevelingen worden automatisch 35 dagen na de laatste keer dat ze worden geproduceerd, verwijderd. Dit automatische verwijderingsmechanisme werkt alleen als autonome afstemming is ingeschakeld.

Afhankelijkheid van hypopg-extensie

Voor autonome afstemming om CREATE INDEX-aanbevelingen te produceren, wordt de hypopg-extensie gebruikt.

Als de extensie al bestaat wanneer een afstemmingssessie begint, wordt deze gebruikt in het schema waarin deze is gemaakt. En wanneer de afstemmingssessie is voltooid, wordt de extensie niet verwijderd. Een uitzondering op deze regel is als de extensie is gemaakt in het pg_catalog schema. Als dat het geval is, stopt autonome afstemming de extensie.

Als de extensie in de eerste plaats niet bestond of als deze is verwijderd omdat deze is gemaakt in het pg_catalog schema, creëert autonome afstemming deze onder een schema genaamd ms_temp_recommendations709253 en, wanneer de afstemmingssessie succesvol is voltooid, wordt de extensie verwijderd en het schema weggehaald.

Gebruikers die lid zijn van de azure_pg_admin rol, kunnen de hypopg-extensie op elk gewenst moment verwijderen, zelfs wanneer deze wordt gemaakt door de autonome afstemmingsfunctie. Als u het echter laat vallen terwijl een autonome afstemmingssessie wordt uitgevoerd, kan dit ertoe leiden dat die sessie mislukt en geen aanbevelingen oplevert.

Ondersteunde rekenlagen en SKU's

Autonoom afstemmen wordt ondersteund voor alle momenteel beschikbare lagen: Burstable, Algemeen gebruik en Geoptimaliseerd voor geheugen, en op elke momenteel ondersteunde reken-SKU met ten minste 4 vCores.

Ondersteunde versies van PostgreSQL

Autonoom afstemmen wordt ondersteund op primaire versies12 of hoger van azure Database for PostgreSQL flexibele serverexemplaren.

Gebruik van search_path

Autonome afstemming verbruikt de waarde die is opgeslagen in kolom search_path van query_store.qs_view, zodat wanneer elke query wordt geanalyseerd, dezelfde waarde van search_path die bij de oorspronkelijke uitvoering van de query was ingesteld, wordt gebruikt om mogelijke aanbevelingen te analyseren.

Geparameteriseerde query's

Geparameteriseerde query’s die zijn gemaakt met PREPARE of met behulp van het extended query protocol, worden geparseerd en geanalyseerd om indexaanbevelingen voor deze query’s te genereren.

Voor de analyse van geparameteriseerde query's vereist zelfstandige afstemming dat pg_qs.parameters_capture_mode is ingesteld op capture_first_sample wanneer de query store de uitvoering van de query vastlegt. Het vereist ook dat de parameters correct worden vastgelegd door het queryarchief wanneer de query wordt uitgevoerd. Met andere woorden, voor de query die wordt geanalyseerd, moet query_store.qs_view de kolom parameters_capture_status hebben ingesteld op succeeded.

Alleen-lezenmodus en leesreplica’s

Omdat autonome afstemming afhankelijk is van de gegevens die het queryarchief lokaal bewaart in de azure_sys database, die niet wordt ondersteund in leesreplica's of wanneer een exemplaar zich in de modus Alleen-lezen bevindt, wordt dit niet ondersteund op leesreplica's of op exemplaren die zich in de modus Alleen-lezen bevinden.

Eventuele aanbevelingen voor een leesreplica zijn geproduceerd op de primaire replica na het exclusief analyseren van de workload die op de primaire replica wordt uitgevoerd.

Omlaag schalen van rekenkracht

Als autonoom afstemmen is ingeschakeld op een server en u de rekenkracht van die server omlaag schaalt naar minder dan het minimale aantal vereiste vCores, blijft de functie ingeschakeld. Omdat de functie niet wordt ondersteund op servers met minder dan 4 vCores, wordt deze niet uitgevoerd om de werklast te analyseren en aanbevelingen te produceren, zelfs als index_tuning.mode is ingesteld op ON toen de rekenkracht werd verminderd. Hoewel de server niet voldoet aan de minimale vereisten, zijn alle index_tuning.* parameters niet toegankelijk. Wanneer u de server weer omhoog schaalt naar een berekening die voldoet aan de minimale vereisten, index_tuning.mode wordt geconfigureerd met de waarde die is ingesteld voordat u de server omlaag schaalde naar een berekening die niet aan de vereisten voldoet.

Hoge beschikbaarheid en leesreplica’s

Als u hoge beschikbaarheid of leesreplica's op uw server hebt geconfigureerd, moet u zich bewust zijn van de gevolgen van schrijvingsintensieve workloads op de primaire server bij het implementeren van de aanbevolen indexen. Wees vooral voorzichtig bij het maken van indexen waarvan de grootte naar schatting groot is.

Redenen waarom autonome afstemming geen indexaanbevelingen voor bepaalde query's kan produceren

Hieronder volgt een lijst met querytypen waarvoor autonome afstemming geen AANBEVELINGEN voor CREATE INDEX genereert. Queries die:

  • Er treedt een fout op wanneer de autonome afstemmingsengine de EXPLAIN-uitvoer probeert te verkrijgen tijdens de analysefase.
  • Naslagtabellen met geen statistieken over de inhoud in de pg_statistic systeemcatalogus. Voer ANALYZE uit op deze tabellen, zodat de afstemmingsengine in de toekomst rekening kan houden met deze query's.
  • Zorg ervoor dat de querytekst wordt afgekapt in Query Store. Dit is het geval wanneer de lengte van querytekst de waarde overschrijdt die is geconfigureerd in pg_qs.max_query_text_length.
  • Verwijzingsobjecten die zijn verwijderd of hernoemd voordat de analyse plaatsvindt. Deze query's kunnen nog steeds syntactisch geldig zijn, maar niet semantisch geldig.
  • Toegang tot tijdelijke tabellen of indexen voor tijdelijke tabellen.
  • Toegang tot weergaven of gerealiseerde weergaven.
  • Gepartitioneerde tabellen openen.
  • Worden geïdentificeerd als hulpprogramma-instructies. Instructies of opdrachten voor hulpprogramma's zijn in principe een instructie die niet als SELECT, INSERT, UPDATE, DELETE of MERGE wordt beschouwd en bepaalde opdrachten die een van deze opdrachten bevatten.
  • Behoren niet tot de bovenste aantal index_tuning.max_query's_per_database die het langzaamst zijn voor de geanalyseerde database en periode.
  • Werden uitgevoerd in de context van één specifieke database, toen geen van deze query's werd geïdentificeerd als de hoogste langzaamste op serverniveau.

Verwante inhoud

  • Last updated on