Delen via

Indexafstemming in Azure Database for PostgreSQL - Flexibele server

  • Artikel

VAN TOEPASSING OP: Azure Database for PostgreSQL - Flexibele server

Indexafstemming is een functie in flexibele Azure Database for PostgreSQL-server waarmee de prestaties van uw workload automatisch worden verbeterd door de bijgehouden query's te analyseren en indexaan aanbevelingen te geven.

Het is een ingebouwde aanbieding in azure Database for PostgreSQL flexibele server, die voortbouwt op de prestaties van de monitor met querystore-functionaliteit . Indexafstemming analyseert de workload die wordt bijgehouden door het queryarchief en produceert indexaankopen om de prestaties van de geanalyseerde workload te verbeteren of dubbele of ongebruikte indexen te verwijderen.

Algemene beschrijving van het algoritme voor indexafstemming

Wanneer de index_tuning.mode serverparameter is geconfigureerd report, worden afstemmingssessies automatisch gestart met de frequentie die is geconfigureerd in de serverparameter index_tuning.analysis_interval, 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 het queryarchief waarvan de uitvoeringen zijn vastgelegd binnen het opzoekinterval waarop deze afstemmingssessie zich richt. Het opzoekinterval duurt momenteel tot de afgelopen index_tuning.analysis_interval minuten, vanaf de begintijd van de afstemmingssessie.

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 gezegd, query's uitgevoerd op azuresu rol)
  • 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 kunnen worden geëlimineerd omdat ze worden geïdentificeerd als duplicaten of niet worden gebruikt voor een configureerbare periode.

AANBEVELINGEN VOOR CREATE INDEX

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

Notitie

Indexafstemming analyseert niet alleen SELECT-instructies, maar ook DML-instructies (UPDATE, INSERT en DELETE).

De resulterende set query's wordt gerangschikt op basis van de geaggregeerde totale uitvoeringstijd en de top index_tuning.max_queries_per_database wordt geanalyseerd op mogelijke indexaan aanbevelingen.

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 die meerdere relaties samenvoegen, of ze nu de syntaxis volgen waarin joins worden uitgedrukt met JOIN-component of of de joinpredicaten worden uitgedrukt in de WHERE-component.
  • Query's combineren filters en joinpredicaten.
  • 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 combineren filters en sorteren.

Notitie

Het enige type indexen dat het systeem momenteel aanbeveelt, zijn die van het type B-Tree.

Als een query verwijst naar één kolom van een tabel en die tabel geen statistieken heeft, wordt de hele query overgeslagen en worden er geen indexaanaanvelingen geproduceerd om de uitvoering te verbeteren.

Analyse die nodig is om statistieken te verzamelen, kan handmatig worden geactiveerd met behulp van de opdracht ANALYZE of automatisch door de autovacuum-daemon.

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 geeft het aantal indexaankopen op dat wordt geproduceerd voor alle tabellen van elke database die tijdens een afstemmingssessie wordt geanalyseerd.

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

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.

Notitie

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 vorige alinea's worden genoemd, worden hun standaardwaarden en geldige bereiken 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.

Indexafstemming 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 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 (opgevraagd) en het percentage waarmee de kosten van het plan zouden worden verbeterd als de aanbeveling werd geïmplementeerd (dimensional).

AANBEVELINGEN VOOR DROP INDEX en REINDEX

Voor elke database waarvoor de functionaliteit voor indexafstemming wordt bepaald, moet er een nieuwe sessie worden gestart. 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: 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 wordt aanbevolen om alle duplicaten met een lagere classificatie te verwijderen dan de verwijzingsleider en beschrijft waarom elke duplicaat op de manier is gerangschikt zoals het was.

Twee indexen moeten als duplicaat worden beschouwd:

  • U kunt deze maken via dezelfde tabel.
  • Wees een index van hetzelfde type.
  • Komen overeen met de sleutelkolommen en komen voor indexsleutels met meerdere kolommen overeen met de volgorde waarnaar wordt verwezen.
  • Komt overeen met de expressiestructuur van het predicaat. Alleen van toepassing op gedeeltelijke indexen.
  • Komt overeen met de expressiestructuur van alle niet-simple 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.

Indexafstemming configureren

Indexafstemming kan worden ingeschakeld, uitgeschakeld en geconfigureerd via een set parameters die het gedrag ervan bepalen, zoals hoe vaak een afstemmingssessie kan worden uitgevoerd.

Bekijk alle details over de juiste configuratie van de functie voor indexafstemming voor het inschakelen, uitschakelen en configureren van indexafstemming.

Informatie geproduceerd door indexafstemming

Het lezen, interpreteren en gebruiken van aanbevelingen die door indexafstemming worden geproduceerd, beschrijft in detail hoe u de aanbevelingen kunt verkrijgen en gebruiken die worden geproduceerd door indexafstemming.

Beperkingen en ondersteuning

Hieronder vindt u de lijst met beperkingen en ondersteuningsbereik voor het afstemmen van indexen.

Ondersteunde rekenlagen en SKU's

Indexafstemming 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

Indexafstemming wordt ondersteund in primaire versies 12 of hoger van Azure Database for PostgreSQL Flexible Server.

Gebruik van search_path

Indexafstemming verbruikt de waarde die behouden blijft in de kolom search_path van query_store.qs_view, zodat wanneer elke query wordt geanalyseerd, dezelfde waarde is search_path ingesteld toen de query oorspronkelijk werd uitgevoerd, de waarde is waarop de query oorspronkelijk is ingesteld om mogelijke aanbevelingen te analyseren.

Geparameteriseerde query's

Geparameteriseerde query's die zijn gemaakt met PREPARE of met behulp van het uitgebreide queryprotocol , worden geparseerd en geanalyseerd om er indexaanaankopen op te maken.

Voor de analyse van geparameteriseerde query's is voor indexafstemming vereist dat pg_qs.parameters_capture_mode is ingesteld capture_first_sample op wanneer het queryarchief 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.

Modus Alleen-lezen en leesreplica's

Omdat het afstemmen van indexen afhankelijk is van het queryarchief, dat 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 nadat ze uitsluitend de workload hebben geanalyseerd die op de primaire replica is uitgevoerd.

Omlaag schalen van rekenkracht

Als indexafstemming 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 workload te analyseren en aanbevelingen te produceren, zelfs niet als deze is ingesteld ON op het moment dat index_tuning.mode de berekening omlaag is geschaald. Hoewel de server niet voldoet aan de minimale vereisten, zijn alle index_tuning.* serverparameters niet toegankelijk. Wanneer u uw server weer schaalt naar een berekening die voldoet aan de minimale vereisten, index_tuning.mode wordt geconfigureerd met de waarde die is ingesteld voordat u deze omlaag schaalde naar een berekening die niet aan de vereisten voldoet.

Hoge beschikbaarheid en leesreplica's

Als u hoge beschikbaarheid of leesreplica's hebt geconfigureerd op uw server, moet u rekening houden met de gevolgen voor het produceren van schrijfintensieve werkbelastingen 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.

Gerelateerde inhoud