Freigeben über

Indexoptimierung in Azure Database for PostgreSQL – Flexibler Server

  • Artikel

GILT FÜR: Azure Database for PostgreSQL – Flexibler Server

Die Indexoptimierung ist ein Feature in Azure Database for PostgreSQL – Flexibler Server zur automatischen Verbesserung der Leistung Ihrer Workload durch Analyse der nachverfolgten Abfragen und Bereitstellung von Indexempfehlungen.

Sie ist ein integriertes Angebot in Azure Database for PostgreSQL – Flexibler Server, das auf der Überwachung der Leistung mit dem Abfragespeicher basiert. Die Indexoptimierung analysiert die vom Abfragespeicher nachverfolgte Workload und generiert Indexempfehlungen, um die Leistung der analysierten Workload zu verbessern oder doppelte oder nicht verwendete Indizes zu löschen.

Allgemeine Beschreibung des Indexoptimierungsalgorithmus

Wenn der Serverparameter index_tuning.mode auf report festgelegt ist, werden Optimierungssitzungen automatisch mit der im Serverparameter index_tuning.analysis_interval konfigurierten Frequenz gestartet (ausgedrückt in Minuten).

In der ersten Phase sucht die Optimierungssitzung nach der Liste der Datenbanken, bei denen es wahrscheinlich ist, dass sich die generierten Empfehlungen erheblich auf die Gesamtleistung des Systems auswirken. 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 sucht er nach Indizes, die gelöscht werden können, weil es sich bei ihnen um Duplikate handelt oder sie für einen konfigurierbaren Zeitraum nicht verwendet wurden.

CREATE INDEX-Empfehlungen

Für jede Datenbank, die als Analysekandidat für die Generierung von Indexempfehlungen identifiziert wurde, werden alle SELECT-, UPDATE-, INSERT- und DELETE-Abfragen berücksichtigt, die während des Suchintervalls und im Kontext dieser bestimmten Datenbank ausgeführt wurden.

Hinweis

Bei der Indexoptimierung werden nicht nur SELECT-Anweisungen, sondern auch DML-Anweisungen (UPDATE, INSERT und DELETE) analysiert.

Die resultierende Gruppe von Abfragen wird basierend auf ihrer aggregierten Gesamtausführungszeit bewertet, und die wichtigsten index_tuning.max_queries_per_database werden auf mögliche Indexempfehlungen analysiert.

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

  • Abfragen mit Filtern (also 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

Hinweis

Das System empfiehlt derzeit ausschließlich Indizes vom Typ B-Struktur.

Wenn eine Abfrage auf eine Spalte einer Tabelle verweist und diese Tabelle keine Statistiken enthält, wird die gesamte Abfrage übersprungen und führt zu keinen Indexempfehlungen, welche die Ausführung zu verbessern.

Die Analyse, die zum Sammeln von Statistiken erforderlich ist, kann manuell mithilfe des ANALYZE-Befehls oder automatisch vom Autovacuum-Daemon ausgelöst werden.

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 werden alle Indexempfehlungen überprüft, um sicherzustellen, dass sie keine Regression für eine Abfrage in dieser Workload zur Folge haben, die einem mithilfe von index_tuning.max_regression_factor angegebenen Faktor entspricht.

Hinweis

index_tuning.min_improvement_factor und index_tuning.max_regression_factor beziehen sich auf die Kosten von Abfrageplänen, nicht auf ihre Dauer oder die Ressourcen, die sie bei der Ausführung nutzen.

Alle in den vorherigen Absätzen erwähnten Parameter sowie ihre Standardwerte und gültigen Bereiche sind in den Konfigurationsoptionen beschrieben.

Das Skript, das zusammen mit der Empfehlung zum Erstellen eines Index generiert wird, basiert auf folgendem 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 PostgreSQL-Dokumentation für CREATE INDEX.

Die Indexoptimierung generiert automatisch die Namen der empfohlenen Indizes. Diese setzen sich in der Regel aus den durch Unterstriche („_“) getrennten Namen der verschiedenen Schlüsselspalten und dem konstanten Suffix „_idx“ zusammen. 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, für welche die Indexoptimierungsfunktion festgelegt wird, sollte eine neue Sitzung initiiert werden, und nach Abschluss der CREATE INDEX-Empfehlungsphase wird die Löschung oder das erneute Indizieren von Indizes aufgrund der folgenden Kriterien empfohlen:

  • 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.
  • Indizieren Sie Indizes neu, die als ungültig markiert sind.

Löschen doppelter Indizes

Empfehlungen für die Löschung doppelter Indizes: Identifizieren Sie zuerst, für welche Indizes Duplikate vorhanden sind.

Für Duplikate wird basierend auf verschiedenen Funktionen, die dem Index zugeordnet werden können, sowie basierend auf ihrer geschätzten Größe eine Rangfolge erstellt.

Abschließend wird empfohlen, alle Duplikate zu löschen, die über einen niedrigeren Rang verfügen als das zugehörige Referenzelement mit dem höchsten Rang, und es wird jeweils beschrieben, wie der Rang des jeweiligen Duplikats zustande kam.

Damit zwei Indizes als Duplikate betrachtet werden, muss Folgendes erfüllt sein:

  • Sie müssen für die gleiche Tabelle erstellt worden sein.
  • Sie müssen exakt den gleichen Typ haben.
  • Die Schlüsselspalten müssen übereinstimmen. Bei mehrspaltigen Indexschlüsseln muss außerdem die Reihenfolge übereinstimmen, in der auf sie verwiesen wird.
  • Die Ausdrucksbaumstruktur ihres Prädikats muss übereinstimmen. Dies gilt nur für partielle Indizes.
  • Die Ausdrucksbaumstruktur aller nicht einfachen Spaltenverweise muss übereinstimmen. Dies 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

Im Zusammenhang mit dem Löschen nicht verwendeter Indizes empfiehlt es sich, Indizes zu identifizieren, auf die Folgendes zutrifft:

  • 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 für eine erneute Indizierung vorhandener Indizes - identifizieren Sie die Indizes, die als ungültig gekennzeichnet sind. Weitere Informationen dazu, warum und wann Indizes als ungültig gekennzeichnet sind, finden Sie in der offiziellen Dokumentation zu REINDEX in PostgreSQL.

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).

„Benefit“ ist ein einzelner Wert, der vorerst ignoriert werden kann.

„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.

Konfigurieren der Indexoptimierung

Die Indexoptimierung kann über eine Reihe von Parametern für die Verhaltenssteuerung aktiviert, deaktiviert und konfiguriert werden. So können Sie beispielsweise festlegen, wie oft eine Optimierungssitzung ausgeführt werden kann.

Ausführliche Informationen zur richtigen Konfiguration des Indexoptimierungsfeatures finden Sie in den Informationen zum Aktivieren, Deaktivieren und Konfigurieren der Indexoptimierung.

Durch die Indexoptimierung erzeugte Informationen

In den Informationen zum Lesen, Interpretieren und Verwenden von Empfehlungen, die durch die Indexoptimierung erzeugt werden wird ausführlich beschrieben, wie Sie die von der Indexoptimierung erzeugten Empfehlungen abrufen und verwenden können.

Einschränkungen und Unterstützbarkeit

Es folgt die Liste der Einschränkungen und Unterstützungsmöglichkeiten für die Indexoptimierung.

Unterstützte Computeebenen und SKUs

Die Indexoptimierung wird in allen derzeit verfügbaren Tarifen („Burstfähig“, „Universell“ und „Arbeitsspeicheroptimiert“) sowie in allen derzeit unterstützten Compute-SKUs mit mindestens vier vCores unterstützt.

Unterstützte Versionen von PostgreSQL

Die Indexoptimierung wird in Hauptversionen ab Version 12 von Azure Database for PostgreSQL – Flexible Server unterstützt.

Verwendung von search_path

Bei der Indexoptimierung wird der Wert in der Spalte search_path der Datei query_store.qs_view beibehalten, sodass beim Analysieren jeder Abfrage derselbe Wert von search_path, der beim ursprünglichen Ausführen der Abfrage festgelegt wurde, derjenige ist, der zum Analysieren möglicher Empfehlungen festgelegt wurde.

Parametrisierte Abfragen

Parametrisierte Abfragen, die mit PREPARE erstellt wurden oder das erweiterte Abfrageprotokoll verwenden, werden zerlegt und analysiert, um Indexempfehlungen auf ihnen zu erstellen.

Für die Analyse parametrisierter Abfragen erfordert die Indexoptimierung, dass pg_qs.parameters_capture_mode auf capture_first_sample festgelegt wird, wenn der Abfragespeicher die Ausführung der Abfrage erfasst. Außerdem müssen die Parameter beim Ausführen der Abfrage ordnungsgemäß vom Abfragespeicher erfasst werden. Anders gesagt, für die zu analysierende Abfrage query_store.qs_view muss die Spalte parameters_capture_status auf succeeded festgelegt sein.

Schreibgeschützter Modus und Lesereplikate

Da die Indexoptimierung auf Abfragespeichern basiert, was in Lesereplikaten oder wenn eine Instanz im schreibgeschützten Modus nicht unterstützt wird, wird sie nicht für Lesereplikate oder für Instanzen unterstützt, die sich im schreibgeschützten Modus befinden.

Alle Empfehlungen, die für ein Lesereplikat angezeigt werden, wurden im primären Replikat erstellt, nachdem sie ausschließlich die Workload analysiert haben, die für das primäre Replikat ausgeführt wurde.

Compute herunterskalieren

Wenn die Indexoptimierung auf einem Server aktiviert ist und Sie die Berechnung dieses Servers auf weniger als die Mindestanzahl der erforderlichen vCores verkleinern, bleibt das Feature aktiviert. Da das Feature auf Servern mit weniger als 4 vCores nicht unterstützt wird, wird es nicht ausgeführt, um die Workload zu analysieren und Empfehlungen zu erstellen, auch wenn index_tuning.mode auf ON festgelegt war, als der Compute nach unten skaliert wurde. Während der Server nicht die Mindestanforderungen erfüllt, kann auf alle index_tuning.*-Serverparameter nicht zugegriffen werden. Jedes Mal, wenn Sie den Server auf einen Compute skalieren, der die Mindestanforderungen erfüllt, wird index_tuning.mode mit dem Wert konfiguriert, der festgelegt wurde, bevor Sie ihn auf eine Berechnung skalieren, die nicht den Anforderungen entspricht.

Hochverfügbarkeit und Lesereplikate

Falls Ihr Server mit Hochverfügbarkeit oder Lesereplikaten konfiguriert ist, beachten Sie die Auswirkungen, die mit der Erstellung schreibintensiver Workloads auf dem primären Server verbunden sind, wenn empfohlene Indizes implementiert werden. Seien Sie insbesondere vorsichtig, wenn Sie Indizes erstellen, die wahrscheinlich groß sind.

Zugehöriger Inhalt