Automatische afstemming in Azure Database voor PostgreSQL – flexibele server

Autonoom afstemmen is een functie in Azure Database for PostgreSQL flexibele server die query's analyseert die zijn vastgelegd op basis van uw workload en aanbevelingen biedt om de prestaties van deze query's te verbeteren.

Dit is een ingebouwde functie in Azure Database for PostgreSQL flexible server die is gebaseerd op de functionaliteit van query store. Automatisch afstemmen analyseert de workload die wordt bijgehouden door Query Store en genereert 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.

• Bepaal welke indexen nuttig zijn om te maken, omdat ze de query's die tijdens een autonome afstemmingssessie worden geanalyseerd aanzienlijk kunnen verbeteren.
• Identificeer indexen die exacte duplicaten zijn en kunnen worden geëlimineerd.
• Identificeer indexen die niet worden gebruikt in een configureerbare periode die kandidaten kunnen zijn om te elimineren.
• Identificeer indexen die als ongeldig zijn gemarkeerd en die opnieuw moeten worden geïndexeerd om ze om te zetten in geldige indexen.
• Identificeer tabellen die geen actuele statistieken bevatten die moeten worden geanalyseerd.
• Identificeer tabellen die opgeblazen zijn en die gevacumeerd moeten worden.

Algemene beschrijving van het autonome afstemmingsalgoritmen

Wanneer u de parameter index_tuning.mode instelt op report, start het systeem automatisch afstemsessies met de frequentie die u instelt in de parameter index_tuning.analysis_interval, uitgedrukt in minuten.

In de eerste fase zoekt de afstemmingssessie naar de lijst met databases waar aanbevelingen 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. Er wordt ook gezocht naar indexen die u kunt elimineren omdat ze duplicaten zijn of niet worden gebruikt voor een configureerbare periode. Het signaleert ook tabellen zonder actuele statistieken of te grote tabellen.

AANBEVELINGEN VOOR CREATE INDEX

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

Het proces rangschikt de resulterende verzameling query’s op basis van de geaggregeerde totale uitvoeringstijd en analyseert de bovenste index_tuning.max_queries_per_database op mogelijke indexaanbevelingen.

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

• Query's met filters (dat wil zeggen query's met predicaten in de WHERE-clausule).
• 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.

Als een query verwijst naar één kolom van een tabel en die tabel geen statistieken heeft, levert het proces geen indexaanaanvelingen op 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 controleert het proces alle indexaanbeveling om ervoor te zorgen dat er geen regressie wordt geïntroduceerd op één query in die workload van een factor die is opgegeven met index_tuning.max_regression_factor.

Alle parameters die in de voorgaande alinea’s worden genoemd, hun standaardwaarden en geldige waardenbereiken, worden beschreven in de configuratieopties.

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

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

Het bevat de component CONCURRENTLY. Zie 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 als kandidaat is geïdentificeerd, start het proces een nieuwe sessie. Nadat de fase CREATE INDEX-aanbevelingen is voltooid, wordt u aangeraden 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 beginnen met het identificeren van welke indexen dubbele waarden hebben.

Duplicaten worden gerangschikt op basis van verschillende functies die u aan de index kunt toewijzen en op basis van de geschatte grootten.

Het proces raadt ten slotte aan om alle duplicaten met een lagere classificatie te verwijderen dan de referentieleider en beschrijft waarom elk duplicaat op de manier is gerangschikt zoals het was.

Als u twee indexen als duplicaat wilt beschouwen, moeten ze:

• U kunt deze maken via dezelfde tabel.
• Wees een index van hetzelfde type.
• Laat de sleutelkolommen overeenkomen en laat bij indexsleutels met meerdere kolommen ook de volgorde overeenkomen waarin ernaar wordt verwezen.
• Komt overeen met de expressiestructuur van het predicaat. Deze voorwaarde is alleen van toepassing op gedeeltelijke indexen.
• Vergelijk de expressieboom van alle niet-eenvoudige kolomverwijzingen. Deze voorwaarde is 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. Zie 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 u voorlopig kunt negeren.

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, start het proces een sessie die is gericht op het produceren van aanbevelingen op tabelniveau. Met deze aanbevelingen wordt u gevraagd om ANALYZE of VACUUM uit te voeren op de tabellen waartoe de geïnspecteerde query’s toegang hebben. De optimalisatie-engine gaat ervan uit dat het uitvoeren van deze commando's de prestaties van uw workload kan verbeteren.

Aanbevelingen voor ANALYZE-table

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

• Waarnaar in een query wordt verwezen, en waarvan een kolom van die tabel wordt gebruikt in een van de predicaten (WHERE, JOIN, ORDER BY, GROUP BY), en die ook aan een van de volgende twee voorwaarden voldoen:
  • Worden 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. Het proces genereert deze aanbevelingen alleen wanneer autovacuum_enabled niet is ingesteld op off op serverniveau wanneer de workload wordt geanalyseerd.

Configureren van autonome optimalisatie

U kunt autonome afstemming inschakelen, uitschakelen en configureren via een set parameters waarmee het gedrag wordt bepaald.

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

Als u de waarde voor index_tuning.analysis_intervalwijzigt, wordt de nieuwe waarde pas van kracht 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 en 10:00 uur, zijn niet van invloed op die initiële planning. Alleen wanneer de geplande uitvoering is voltooid, leest deze de huidige waarde die is ingesteld voor index_tuning.analysis_interval en plant u de volgende uitvoering op basis van die waarde.

Gebruik de volgende opties om autonome afstemmingsparameters te configureren:

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

In de volgende lijst worden de beperkingen en het ondersteuningsbereik voor autonome afstemming beschreven.

Automatisch verwijderen van aanbevelingen

Het systeem verwijdert automatisch aanbevelingen 35 dagen na de laatste keer dat het systeem ze heeft geproduceerd. Om dit mechanisme voor automatische verwijdering te laten werken, moet u autonome optimalisatie inschakelen.

Afhankelijkheid van hypopg-extensie

Om CREATE INDEX aanbevelingen te genereren, maakt autonoom afstemmen gebruik van de extensie hypopg.

Als de extensie bestaat wanneer een afstemsessie begint, maakt het proces er gebruik van in het schema waarin deze is gemaakt. Wanneer de afstemsessie is afgerond, laat het proces de extensie niet los. 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 eerste instantie niet bestond of het proces deze verwijdert omdat deze is gemaakt in het schema pg_catalog, maakt autonome optimalisatie deze onder een schema met de naam ms_temp_recommendations709253. Wanneer de afstemmingssessie succesvol wordt voltooid, verwijdert het proces de extensie en het schema.

Gebruikers die lid zijn van de azure_pg_admin rol kunnen de hypopg-extensie op elk gewenst moment verwijderen, zelfs wanneer de autonome afstemmingsfunctie deze heeft gemaakt. 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

Azure Database for PostgreSQL flexibele server ondersteunt autonome afstemming op alle momenteel beschikbare lagen: Burstable, Algemeen gebruik en Geoptimaliseerd voor geheugen. Het biedt ook ondersteuning voor autonome afstemming op elke momenteel ondersteunde reken-SKU met ten minste 4 vCores.

Ondersteunde versies van PostgreSQL

Azure Database for PostgreSQL flexibele server ondersteunt automatische afstemming voor hoofdversies12 of hoger.

Gebruik van search_path

Autonoom afstemmen gebruikt de waarde in de kolom search_path van query_store.qs_view. Wanneer elke query wordt geanalyseerd, wordt dezelfde search_path waarde gebruikt die is ingesteld toen de query oorspronkelijk werd uitgevoerd om mogelijke aanbevelingen te analyseren.

Geparameteriseerde query's

Geparameteriseerde query’s die zijn opgesteld met PREPARE of met behulp van het uitgebreide queryprotocol, worden geparseerd en geanalyseerd om indexaanbevelingen 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 queryopslag de parameters correct vastlegt wanneer de query wordt uitgevoerd. Met andere woorden, voor de query die wordt geanalyseerd, moet de parameters_capture_status kolom in query_store.qs_view zijn ingesteld op succeeded.

Alleen-lezenmodus en leesreplica’s

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

Aanbevelingen die u op een leesreplica ziet, zijn gegenereerd op de primaire replica na uitsluitend de workload te hebben geanalyseerd die op de primaire replica is uitgevoerd.

Omlaag schalen van rekenkracht

Als u autonome afstemming op een server inschakelt en vervolgens de rekenkracht van die server omlaag schaalt tot 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 workload te analyseren en aanbevelingen te genereren, zelfs als index_tuning.mode was ingesteld op ON toen u de rekenkracht terugschakelde. 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 read replica's op uw server configureert, wees u dan bewust van de implicaties van schrijfintensieve workloads op de primaire server wanneer u de aanbevolen indexen implementeert. 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

Autonoom afstemmen genereert geen CREATE INDEX aanbevelingen voor de volgende typen query’s:

• Query's die een fout tegenkomen wanneer de autonome afstemmingsengine probeert hun EXPLAIN-uitvoer te verkrijgen tijdens de analysefase.
• Query's die verwijzen naar tabellen zonder statistieken over hun inhoud in de pg_statistic systeemcatalogus. Voer ANALYZE uit op deze tabellen, zodat de afstemmingsengine deze query's in de toekomst kan overwegen.
• Query's met afgekorte querytekst in queryopslag. Deze afkapping vindt plaats wanneer de lengte van querytekst de waarde overschrijdt die is geconfigureerd in pg_qs.max_query_text_length.
• Query's die verwijzen naar objecten die u hebt verwijderd of hernoemd voordat de analyse plaatsvindt. Deze query's kunnen nog steeds syntactisch geldig zijn, maar ze zijn niet semantisch geldig.
• Query's die toegang hebben tot tijdelijke tabellen of indexen voor tijdelijke tabellen.
• Query's die toegang hebben tot weergaven of gerealiseerde weergaven.
• Query's die toegang hebben tot gepartitioneerde tabellen.
• Query's geïdentificeerd als hulpprogramma-instructies. Hulpinstructies of hulpopdrachten zijn in wezen alle instructies die niet worden beschouwd als SELECT, INSERT, UPDATE, DELETE of MERGE, en bepaalde opdrachten die een van deze instructies bevatten.
• Query’s die niet tot de index_tuning.max_queries_per_database langzaamste behoren voor de geanalyseerde database en periode.
• Query's die worden uitgevoerd in de context van één specifieke database, wanneer geen van deze query's wordt geïdentificeerd als de hoogste langzaamste op serverniveau.

Verwante inhoud

• Query store.
• Autonome afstemming configureren.
• Gebruik aanbevelingen voor autonome configuratie.