Autonome Optimierung in Azure Database for PostgreSQL – Flexible Server

Die autonome Optimierung ist eine Funktion in Azure Database for PostgreSQL – Flexibler Server, die Abfragen analysiert, die aus Ihrer Workload aufgezeichnet wurden, und Empfehlungen gibt, um die Leistung dieser Abfragen zu verbessern.

Es ist eine integrierte Funktion in Azure Database for PostgreSQL Flexible Server, die auf der Abfragespeicher-Funktionalität aufbaut. Die autonome Optimierung analysiert die im Query Store erfasste Workload und erstellt Index- oder Tabellenempfehlungen, um die Leistung der analysierten Workload zu verbessern. Es kann Empfehlungen erstellen, um neue Indizes zu erstellen, doppelte oder nicht verwendete Indizes zu entfernen, Tabellen zu analysieren, die keine Statistiken oder veraltete Statistiken aufweisen, oder aufgeblähte Tabellen leeren.

• Ermitteln Sie, welche Indizes von Vorteil sind , um sie zu erstellen, da sie die während einer autonomen Optimierungssitzung analysierten Abfragen erheblich verbessern könnten.
• Identifizieren Sie Indizes, die genaue Duplikate sind und können eliminiert werden.
• Ermitteln Sie, welche Indizes innerhalb eines konfigurierbaren Zeitraums nicht verwendet wurden und ggf. gelöscht werden können.
• Identifizieren Sie als ungültig markierte Indizes , die neu indiziert werden sollen, um sie in gültige zu verwandeln.
• Identifizieren Sie Tabellen mit fehlenden aktuellen Statistiken , die analysiert werden sollen.
• Identifizieren Sie Tabellen, die aufgebläht sind , die abgesaugt werden sollen.

Allgemeine Beschreibung des autonomen Optimierungsalgorithmus

Wenn Sie den Parameter index_tuning.mode auf report setzen, startet das System automatisch Tuning-Sitzungen mit der Frequenz, die Sie im Parameter index_tuning.analysis_interval konfigurieren und die in Minuten angegeben wird.

In der ersten Phase sucht die Optimierungssitzung nach der Liste der Datenbanken, in denen Sich Empfehlungen erheblich auf die Gesamtleistung des Systems auswirken können. Hierzu werden alle vom Abfragespeicher erfassten Abfragen gesammelt, deren Ausführungen innerhalb des Suchintervalls erfasst wurden, auf das sich diese Optimierungssitzung konzentriert. Das Suchintervall umfasst derzeit die letzten index_tuning.analysis_interval Minuten (ab dem Startzeitpunkt der Optimierungssitzung).

Alle benutzerseitig initiierten Abfragen mit im Abfragespeicher erfassten Ausführungen und nicht zurückgesetzter Laufzeitstatistik werden vom System basierend auf der aggregierten Gesamtausführungszeit bewertet. Die Konzentration liegt auf den hervorstechendsten Abfragen, basierend auf ihrer Dauer.

Folgende Abfragen werden aus dieser Liste ausgeschlossen:

• Systemseitig initiierte Abfragen. (also Abfragen, die von der azuresu-Rolle ausgeführt wurden)
• Abfragen, die im Kontext einer beliebigen Systemdatenbank (azure_sys, template0, template1 und azure_maintenance) ausgeführt wurden.

Der Algorithmus durchläuft die Zieldatenbanken und sucht nach möglichen Indizes, die ggf. die Leistung der analysierten Workloads verbessern. Außerdem wird nach Indizes gesucht, die Sie beseitigen können, da sie Duplikate sind oder nicht für einen konfigurierbaren Zeitraum verwendet werden. Außerdem werden Tabellen identifiziert, die keine aktuellen Statistiken oder aufgeblähten Tabellen aufweisen.

CREATE INDEX-Empfehlungen

Für jede Datenbank, die als Kandidat identifiziert werden soll, berücksichtigt der Prozess alle SELECT-, UPDATE-, INSERT- und DELETE-Abfragen, die während des Nachschlageintervalls und im Kontext dieser spezifischen Datenbank ausgeführt werden.

Der Prozess bewertet den resultierenden Satz von Abfragen basierend auf der aggregierten Gesamtausführungszeit und analysiert den Oberen index_tuning.max_queries_per_database für mögliche Indexempfehlungen.

Mögliche Empfehlungen zielen darauf ab, die Leistung folgender Arten von Abfragen zu verbessern:

• Abfragen mit Filtern (d. a. Abfragen mit Prädikaten in der WHERE-Klausel).
• Abfragen, die mehrere Beziehungen miteinander verknüpfen – unabhängig davon, ob sie der Syntax folgen, in der Joins per JOIN-Klausel ausgedrückt werden, oder ob die Join-Prädikate in der WHERE-Klausel ausgedrückt werden
• Abfragen, die Filter und Join-Prädikate miteinander kombinieren
• Abfragen mit Gruppierung (Abfragen mit einer GROUP BY-Klausel)
• Abfragen, die Filter und Gruppierung miteinander kombinieren
• Abfragen mit Sortierung (Abfragen mit einer ORDER BY-Klausel)
• Abfragen, die Filter und Sortierung miteinander kombinieren

Wenn eine Abfrage auf eine Spalte einer Tabelle verweist und diese Tabelle keine Statistiken enthält, erzeugt der Prozess keine Indexempfehlungen, um die Ausführung zu verbessern. Es generiert jedoch eine Empfehlung, die Tabelle zu analysieren.

index_tuning.max_indexes_per_table gibt die Anzahl von Indizes an, die empfohlen werden können (mit Ausnahme von Indizes, die möglicherweise bereits für die Tabelle vorhanden sind) – für eine beliebige einzelne Tabelle, auf die während einer Optimierungssitzung durch eine beliebige Anzahl von Abfragen verwiesen wird.

index_tuning.max_index_count gibt die Anzahl von Indexempfehlungen an, die für alle Tabellen jeder Datenbank generiert werden, die während einer Optimierungssitzung analysiert wurde.

Damit eine Indexempfehlung ausgegeben wird, muss es für die Optimierungs-Engine wahrscheinlich sein, dass sich dadurch mindestens eine Abfrage in der analysierten Workload um einen mithilfe von index_tuning.min_improvement_factor angegebenen Faktor verbessert.

Ebenso überprüft der Prozess alle Indexempfehlungen, um sicherzustellen, dass sie bei keiner einzelnen Abfrage in diesem Workload eine Leistungsverschlechterung um den mit index_tuning.max_regression_factor angegebenen Faktor verursachen.

Alle in den vorherigen Absätzen erwähnten Parameter, deren Standardwerte und gültige Bereiche werden in den Konfigurationsoptionen beschrieben.

Das skript, das zusammen mit der Empfehlung zum Erstellen eines Indexes erstellt wurde, folgt diesem Muster:

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

Es beinhaltet die Klausel CONCURRENTLY. Weitere Informationen zu den Auswirkungen dieser Klausel finden Sie in der offiziellen Dokumentation von PostgreSQL für CREATE INDEX.

Die autonome Optimierung generiert automatisch die Namen der empfohlenen Indizes, die in der Regel aus den Namen der verschiedenen Schlüsselspalten bestehen, die durch "_" (Unterstriche) und mit einem Konstantensuffix "_idx" getrennt sind. Wenn die Gesamtlänge des Namens die Grenzwerte von PostgreSQL übersteigt oder wenn ein Konflikt mit bereits vorhandenen Beziehungen entsteht, weicht der Name geringfügig ab. Möglicherweise wird er abgeschnitten, oder es wird am Ende des Namens eine Zahl hinzugefügt.

Berechnen der Auswirkungen einer CREATE INDEX-Empfehlung

Die Auswirkungen der Erstellung einer Indexempfehlung werden anhand von „IndexSize“ (in Megabytes) und „QueryCostImprovement“ (in Prozent) bestimmt.

„IndexSize“ ist ein einzelner Wert, der die geschätzte Größe des Index darstellt, wobei die aktuelle Kardinalität der Tabelle und die Größe der Spalten berücksichtigt werden, auf die durch den empfohlenen Index verwiesen wird.

Bei „QueryCostImprovement“ handelt es sich um ein Array von Werten, in dem jedes Element die Verbesserung der Kosten des Plans für jede Abfrage darstellt, bei der davon ausgegangen wird, dass sich die Kosten ihres Plans verbessern, wenn dieser Index vorhanden wäre. Für jedes Element werden der Bezeichner der Abfrage (abgefragt) sowie der Prozentwert angezeigt, um den sich die Kosten des Plans verbessert würden, wenn die Empfehlung implementiert würde (dimensional).

DROP INDEX- und REINDEX-Empfehlungen

Für jede datenbank, die als Kandidat identifiziert wurde, initiiert der Prozess eine neue Sitzung. Nach Abschluss der CREATE INDEX-Empfehlungsphase empfiehlt es, vorhandene Indizes basierend auf den folgenden Kriterien abzulegen oder neu zu indizieren:

• Lösen Sie die Verknüpfung auf, wenn sie als Duplikat von anderen betrachtet wird.
• Lösen Sie die Verknüpfung auf, wenn sie nicht für eine konfigurierbare Zeitspanne verwendet wird.
• Neuindizieren Sie Indizes, die als ungültig markiert sind.

Löschen doppelter Indizes

Empfehlungen zum Ablegen doppelter Indizes beginnen mit der Identifizierung, welche Indizes Duplikate aufweisen.

Duplikate werden basierend auf verschiedenen Funktionen bewertet, die Sie dem Index zuordnen und auf deren geschätzten Größen basieren können.

Der Prozess empfiehlt schließlich das Ablegen aller Duplikate mit einer niedrigeren Rangfolge als der Referenzleiter und beschreibt, warum jedes Duplikat auf die Art und Weise bewertet wurde, wie es war.

Damit zwei Indizes als dupliziert betrachtet werden, müssen sie:

• Sie müssen für die gleiche Tabelle erstellt worden sein.
• Sie müssen exakt den gleichen Typ haben.
• Achten Sie darauf, dass ihre Schlüsselspalten übereinstimmen und bei mehrspaltigen Indexschlüsseln auch die Reihenfolge, in der auf sie verwiesen wird.
• Die Ausdrucksbaumstruktur ihres Prädikats muss übereinstimmen. Diese Bedingung gilt nur für Teilindizes.
• Die Ausdrucksbaumstruktur aller nicht einfachen Spaltenverweise muss übereinstimmen. Diese Bedingung gilt nur für Indizes, die für Ausdrücke erstellt wurden.
• Die Sortierung der einzelnen Spalten, auf die im Schlüssel verwiesen wird, muss übereinstimmen.

Löschen nicht verwendeter Indizes

Empfehlungen für das Ablegen nicht verwendeter Indizes identifizieren die Indizes, die:

• Sie wurden mindestens index_tuning.unused_min_period Tage lang nicht verwendet.
• Weisen Sie eine Mindestanzahl von index_tuning.unused_dml_per_table-DMLs (Tagesdurchschnitt) in der Tabelle auf, in welcher der Index erstellt wurde.
• Weisen Sie eine Mindestanzahl von index_tuning.unused_reads_per_table-Lesevorgängen (Tagesdurchschnitt) in der Tabelle auf, in welcher der Index erstellt wurde.

Ungültige Indizes neu indizieren

Empfehlungen zum Erneuten Indizieren vorhandener Indizes identifizieren die Indizes, die als ungültig gekennzeichnet sind. Weitere Informationen dazu, warum und wann Indizes als ungültig gekennzeichnet werden, finden Sie in der offiziellen PostgreSQL-Dokumentation unter REINDEX.

Berechnen der Auswirkungen einer DROP INDEX-Empfehlung

Die Auswirkung einer Empfehlung zum Löschen eines Index wird in zwei Dimensionen gemessen: „Benefit“ (in Prozent) und „IndexSize“ (in Megabytes).

Der Vorteil ist ein einzelner Wert, den Sie jetzt ignorieren können.

„IndexSize“ ist ein einzelner Wert, der die geschätzte Größe des Index darstellt, wobei die aktuelle Kardinalität der Tabelle und die Größe der Spalten berücksichtigt werden, auf die durch den empfohlenen Index verwiesen wird.

Tabellenempfehlungen

Für jede Datenbank, die als Kandidat zur Analyse identifiziert wurde, initiiert der Prozess eine Sitzung, die darauf abzielt, Empfehlungen auf Tabellenebene zu erstellen. Diese Empfehlungen empfehlen, ANALYZE oder VACUUM für die Tabellen auszuführen, auf die die überprüften Abfragen zugreifen. Das Optimierungsmodul ist der Ansicht, dass die Ausführung dieser Befehle die Leistung Ihrer Workload verbessern könnte.

Empfehlungen zur Tabellenanalyse

Empfehlungen für die Analyse einer Tabelle identifizieren diese Tabellen, die:

• Auf die in einer Abfrage verwiesen wird, bei denen eine Spalte dieser Tabelle in einem ihrer Prädikate (WHERE, JOIN, ORDER BY, GROUP BY) verwendet wird und die außerdem eine der beiden folgenden Bedingungen erfüllen:
  • Werden nie analysiert.
  • Wurden irgendwann analysiert, fehlen aber jetzt Statistiken (in der Regel weil der Server abgestürzt war, bevor die Statistiken auf dem Datenträger gespeichert wurden).

VACUUM-Tabellenempfehlungen

Empfehlungen für das Vakuumieren einer Tabelle identifizieren die Tabellen, die aufgebläht sind. Der Prozess erzeugt diese Empfehlungen nur, wenn autovacuum_enabled bei der Analyse der Workload nicht auf Serverebene auf off festgelegt ist.

Konfigurieren der autonomen Anpassung

Sie können die autonome Optimierung über eine Reihe von Parametern aktivieren, deaktivieren und konfigurieren, die ihr Verhalten steuern.

Wenn Sie die autonome Optimierung aktivieren, wird sie in dem im Parameter index_tuning.analysis_interval konfigurierten Intervall ausgeführt (standardmäßig alle 720 Minuten bzw. 12 Stunden) und beginnt mit der Analyse der Workload, die der Query Store in diesem Zeitraum aufgezeichnet hat.

Wenn Sie den Wert ändern index_tuning.analysis_interval, wird der neue Wert erst nach Abschluss der nächsten geplanten Ausführung wirksam. Wenn Sie beispielsweise die autonome Optimierung an einem Tag um 10:00 Uhr aktivieren, ist die erste Ausführung, da der Standardwert für index_tuning.analysis_interval 720 Minuten beträgt, für den Start am selben Tag um 22:00 Uhr geplant. Änderungen, die Sie zwischen 10:00 Uhr vormittags und 22:00 Uhr am Wert von index_tuning.analysis_interval vornehmen, wirken sich nicht auf diesen ursprünglichen Zeitplan aus. Erst wenn die geplante Ausführung abgeschlossen ist, liest sie den aktuell für index_tuning.analysis_interval festgelegten Wert und plant die nächste Ausführung gemäß diesem Wert.

Verwenden Sie die folgenden Optionen, um autonome Optimierungsparameter zu konfigurieren:

Wenn Sie die CLI-Befehle az postgres flexible-server autonomous-tuning show-settings und az postgres flexible-server autonomous-tuning set-settings verwenden, um eine der Einstellungen für die autonome Optimierung anzuzeigen oder zu ändern, sind die Werte, die als Argumente für den --name-Parameter akzeptiert werden, die, die in der Spalte „Parameter“ der vorherigen Tabelle angezeigt werden, jedoch ohne das Präfix .

Durch autonome Optimierung erzeugte Informationen

Verwenden Sie autonome Optimierungsempfehlungen , in denen ausführlich beschrieben wird, wie Sie die empfehlungen, die von autonomer Optimierung erzeugt werden, abrufen und verwenden.

Einschränkungen und Unterstützbarkeit

In der folgenden Liste werden die Einschränkungen und Unterstützungsmöglichkeiten für die autonome Optimierung beschrieben.

Automatische Löschung von Empfehlungen

Das System löscht Empfehlungen automatisch 35 Tage nach ihrer letzten Erstellung. Damit dieser automatische Löschmechanismus funktioniert, müssen Sie die autonome Optimierung aktivieren.

Abhängigkeit von der hypopg-Erweiterung

Um Empfehlungen zu erstellen CREATE INDEX , verwendet die autonome Optimierung die Hypopg-Erweiterung .

Wenn die Erweiterung vorhanden ist, wenn eine Optimierungssitzung beginnt, verwendet der Prozess sie im Schema, in dem sie erstellt wurde. Wenn die Optimierungssitzung abgeschlossen ist, wird die Erweiterung nicht gelöscht. Eine Ausnahme dieser Regel ist, wenn die Erweiterung im pg_catalog Schema erstellt wurde. Wenn dies der Fall ist, entfernt das automatische Tuning die Erweiterung.

Wenn die Erweiterung von vornherein nicht vorhanden war oder der Prozess sie verwirft, weil sie im Schema pg_catalog erstellt wurde, erstellt die autonome Optimierung sie unter dem Schema ms_temp_recommendations709253. Wenn die Tuning-Sitzung erfolgreich beendet wird, löscht der Prozess die Erweiterung und entfernt das Schema.

Benutzer, die Mitglieder der Rolle azure_pg_admin sind, können die hypopg-Erweiterung jederzeit entfernen, auch wenn sie von der autonomen Optimierungsfunktion erstellt wurde. Das Abbrechen, während eine autonome Optimierungssitzung ausgeführt wird, kann jedoch dazu führen, dass diese Sitzung fehlschlägt und keine Empfehlungen liefert.

Unterstützte Computeebenen und SKUs

Azure Database for PostgreSQL flexible Server unterstützt die autonome Optimierung auf allen derzeit verfügbaren Ebenen: Burstable, General Purpose und Memory Optimized. Es unterstützt auch die autonome Optimierung für alle derzeit unterstützten Compute-SKU mit mindestens 4 vCores.

Unterstützte Versionen von PostgreSQL

Azure Database for PostgreSQL flexibler Server unterstützt die automatische Optimierung für Hauptversionen12 oder höher.

Verwendung von search_path

Die autonome Optimierung verwendet den Wert in der search_path Spalte query_store.qs_view. Bei der Analyse jeder Abfrage wird derselbe search_path Wert verwendet, der beim ursprünglichen Ausführen der Abfrage festgelegt wurde, um mögliche Empfehlungen zu analysieren.

Parametrisierte Abfragen

Parametrisierte Abfragen, die mit PREPARE oder mithilfe des erweiterten Abfrageprotokolls erstellt wurden, werden geparst und analysiert, um Indexempfehlungen zu erzeugen.

Für die Analyse von parametrisierten Abfragen erfordert das autonome Tuning, dass pg_qs.parameters_capture_mode auf capture_first_sample festgelegt wird, wenn der Query Store die Ausführung der Abfrage erfasst. Außerdem erfordert der Abfragespeicher, dass die Parameter beim Ausführen der Abfrage ordnungsgemäß erfasst werden. Anders ausgedrückt: Für die zu analysierende Abfrage muss die parameters_capture_status Spalte in query_store.qs_view auf festgelegt succeededwerden.

Schreibgeschützter Modus und Lesereplikate

Da die autonome Optimierung auf den Daten basiert, die der Abfragespeicher lokal in der azure_sys Datenbank beibehält, und Lesereplikate oder Server im schreibgeschützten Modus nicht unterstützt werden, wird die Funktion auf Lesereplikaten oder auf Servern im schreibgeschützten Modus nicht unterstützt.

Alle Empfehlungen, die Sie auf einem Lesereplikat sehen, wurden auf dem primären Replikat erstellt, nachdem ausschließlich die auf dem primären Replikat ausgeführte Workload analysiert wurde.

Compute herunterskalieren

Wenn Sie die autonome Optimierung auf einem Server aktivieren und dann die Berechnung dieses Servers auf weniger als die Mindestanzahl der erforderlichen vCores skalieren, bleibt das Feature aktiviert. Da die Funktion auf Servern mit weniger als 4 vCores nicht unterstützt wird, wird sie nicht ausgeführt, um die Arbeitsauslastung zu analysieren und Empfehlungen zu erstellen, selbst wenn index_tuning.mode beim Herunterskalieren der Rechenleistung auf ON festgelegt wurde. Der Server erfüllt zwar nicht die Mindestanforderungen, aber auf alle index_tuning.* Parameter kann nicht zugegriffen werden. Wenn Sie Ihren Server wieder auf einen Compute herunterskalieren, der die Mindestanforderungen erfüllt, wird index_tuning.mode mit dem Wert konfiguriert, den Sie vor dem Herunterskalieren auf einen Compute festgelegt haben, der die Anforderungen nicht erfüllt hat.

Hochverfügbarkeit und Lesereplikate

Wenn Sie Hohe Verfügbarkeit konfigurieren oder Replikate auf Ihrem Server lesen, beachten Sie die Auswirkungen, die mit der Erstellung von schreibintensiven Workloads auf dem primären Server verbunden sind, wenn Sie die empfohlenen Indizes implementieren. Seien Sie insbesondere vorsichtig, wenn Sie Indizes erstellen, die wahrscheinlich groß sind.

Gründe, warum die autonome Optimierung möglicherweise keine Empfehlungen zum Erstellen von Indizes für bestimmte Abfragen erzeugt

Die autonome Optimierung generiert CREATE INDEX keine Empfehlungen für die folgenden Arten von Abfragen:

• Abfragen, die auf einen Fehler stoßen, wenn das autonome Tuningmodul versucht, seine EXPLAIN-Ausgabe während der Analysephase abzurufen.
• Abfragen, die ohne Statistiken zu ihren Inhalten im pg_statistic Systemkatalog auf Tabellen verweisen. Führen Sie ANALYZE für diese Tabellen aus, damit das Optimierungsmodul diese Abfragen in Zukunft in Betracht ziehen kann.
• Abfragen mit gekürztem Abfragetext im Abfragespeicher. Diese Abkürzung erfolgt, wenn die Länge des Abfragetexts den in pg_qs.max_query_text_length konfigurierten Wert überschreitet.
• Abfragen, die auf Objekte verweisen, die Sie abgelegt oder umbenannt haben, bevor die Analyse auftritt. Diese Abfragen können weiterhin syntaktisch gültig sein, aber sie sind nicht semantisch gültig.
• Abfragen, die auf temporäre Tabellen oder Indizes für temporäre Tabellen zugreifen.
• Abfragen, die auf Ansichten oder materialisierte Ansichten zugreifen.
• Abfragen, die auf partitionierte Tabellen zugreifen.
• Abfragen, die als Hilfsprogrammanweisungen identifiziert wurden. Hilfsanweisungen oder Hilfsbefehle sind im Wesentlichen alle Anweisungen, die nicht als SELECT, INSERT, UPDATE, DELETE oder MERGE gelten, sowie bestimmte Befehle, die eine dieser Anweisungen enthalten.
• Abfragen, die nicht zu den index_tuning.max_queries_per_database langsamsten gehören, für die analysierte Datenbank und den analysierten Zeitraum.
• Abfragen, die im Kontext einer bestimmten Datenbank ausgeführt werden, wenn keine dieser Abfragen als die langsamste auf Serverebene identifiziert wird.

Verwandte Inhalte

• Abfragespeicher.
• Konfigurieren Sie das autonome Tuning.
• Verwenden Sie autonome Optimierungsempfehlungen.