Übersicht über die Abfrageplanverwaltung in Aurora PostgreSQL

Die Aurora-PostgreSQL-Abfrageplanverwaltung wurde entwickelt, um die Planstabilität unabhängig von Änderungen an der Datenbank zu gewährleisten, die eine Regression des Abfrageplans verursachen könnten. Eine Regression des Abfrageplans tritt auf, wenn der Optimierer nach System- oder Datenbankänderungen einen suboptimalen Plan für eine bestimmte SQL-Anweisung auswählt. Änderungen von Statistiken, Einschränkungen, Umgebungseinstellungen, Abfrageparameter-Bindungen und Upgrades der PostgreSQL-Datenbank-Engine können eine Planregression verursachen.

Mit der Abfrageplanverwaltung in Aurora PostgreSQL steuern Sie, wie und wann sich Abfrageausführungspläne ändern. Zu den Vorteilen der Abfrageplanverwaltung in Aurora PostgreSQL gehören die nachfolgend aufgeführten.

• Verbesserung der Planstabilität, indem der Optimierer zur Auswahl aus einer kleinen Anzahl bekannter, bewährter Pläne gezwungen wird.

• Zentrale Optimierung von Plänen, gefolgt von einer globalen Verteilung der besten Pläne.

• Ermittlung von nicht verwendeten Indizes und Bewertung der Auswirkungen einer Indexerstellung oder -löschung.

• Automatische Erkennung eines vom Optimierer ermittelten Minimalkostenplans.

• Test neuer Optimierer-Funktionen mit geringerem Risiko, da Sie nur die Planänderungen genehmigen können, die zu Performance-Steigerungen führen.

Sie können die von der Abfrageplanverwaltung bereitgestellten Tools proaktiv verwenden, um den besten Plan für bestimmte Abfragen festzulegen. Sie können die Abfrageplanverwaltung auch verwenden, um auf sich ändernde Umstände zu reagieren und Planregressionen zu vermeiden. Weitere Informationen finden Sie unter Bewährte Methoden für die Aurora-PostgreSQL-Abfrageplanverwaltung.

Unterstützte SQL-Anweisungen

Die Abfrageplanverwaltung unterstützt die folgenden Typen von SQL-Anweisungen.

• SELECT-, INSERT-, UPDATE- oder DELETE-Anweisungen, unabhängig von ihrer Komplexität.

• Vorbereitete Anweisungen. Weitere Informationen finden Sie unter PREPARE in der PostgreSQL-Dokumentation.

• Dynamische Anweisungen, einschließlich solcher, die im Sofortmodus ausgeführt werden. Weitere Informationen finden Sie unter Dynamisches SQL und EXECUTE IMMEDIATE in der PostgreSQL-Dokumentation.

• Eingebettete SQL-Befehle und -Anweisungen. Weitere Informationen finden Sie unter Eingebettete SQL-Befehle in der PostgreSQL-Dokumentation.

• Anweisungen innerhalb benannter Funktionen. Weitere Informationen finden Sie im Abschnitt CREATE FUNCTION der PostgreSQL-Dokumentation.

• Anweisungen, die temporäre Tabellen enthalten.

• Anweisungen innerhalb von Prozeduren und DO-Blöcken.

Sie können die Abfrageplanverwaltung mit EXPLAIN im manuellen Modus verwenden, um einen Plan zu erfassen, ohne ihn tatsächlich auszuführen. Weitere Informationen finden Sie unter Analysieren des vom Optimierer ausgewählten Plans. Weitere Informationen zu den Modi der Abfrageplanverwaltung (manuell, automatisch) finden Sie unter Erfassung von Aurora PostgreSQL-Ausführungsplänen.

Die Abfrageplanverwaltung in Aurora PostgreSQL unterstützt sämtliche PostgreSQL-Sprachfunktionen, darunter partitionierte Tabellen, Vererbung, Sicherheit auf Zeilenebene und rekursive allgemeine Tabellenausdrücke (CTEs). Weitere Informationen zu diesen PostgreSQL-Sprachfunktionen finden Sie unter Table Partitioning (Tabellenpartitionierung), Row Security Policies (Zeilensicherheitsrichtlinien) und WITH Queries (Common Table Expressions) (WITH-Abfragen (allgemeine Tabellenausdrücke)) und anderen Themen in der PostgreSQL-Dokumentation.

Informationen zu den verschiedenen Versionen der Abfrageplanverwaltung von Aurora PostgreSQL finden Sie unter Versionen der apg_plan_mgmt-Erweiterung in den Versionshinweisen für Aurora PostgreSQL.

Einschränkungen der Abfrageplanverwaltung

Die aktuelle Version der Abfrageplanverwaltung von Aurora PostgreSQL unterliegt folgenden Einschränkungen.

• Pläne werden nicht für Anweisungen erfasst, die auf Systembeziehungen verweisen – Anweisungen, die auf Systembeziehungen verweisen, wie z. B. pg_class, werden nicht erfasst. Dies ist beabsichtigt, um zu verhindern, dass eine große Anzahl von systemgenerierten Plänen, die intern verwendet werden, erfasst werden. Dies gilt auch für Systemtabellen innerhalb von Ansichten.

• Für Ihren DB-Cluster von Aurora PostgreSQL ist möglicherweise eine größere DB-Instance-Klasse erforderlich – Je nach Workload erfordert die Abfrageplanverwaltung möglicherweise eine DB-Instance-Klasse mit mehr als 2 vCPUs. Die Anzahl von max_worker_processes ist durch die Größe der DB-Instance-Klasse begrenzt. Die Anzahl von max_worker_processes, die von einer 2-vCPU-DB-Instance-Klasse (z. B. db.t3.medium) bereitgestellt wird, ist möglicherweise für eine bestimmte Workload nicht ausreichend. Wir empfehlen Ihnen, bei Verwendung der Abfrageplanverwaltung eine DB-Instance-Klasse mit mehr als 2 vCPUs für Ihren DB-Cluster von Aurora PostgreSQL auszuwählen.

  Wenn die DB-Instance-Klasse Ihre Workload nicht unterstützen kann, gibt die Abfrageplanverwaltung eine Fehlermeldung wie die folgende aus.

  WARNING: could not register plan insert background process
  HINT: You may need to increase max_worker_processes.

  In diesem Fall sollten Sie Ihren DB-Cluster von Aurora PostgreSQL auf eine DB-Instance-Klassengröße mit mehr Speicher hochskalieren. Weitere Informationen finden Sie unter Unterstützte DB-Engines für DB-Instance-Klassen.

• Pläne, die bereits in Sitzungen gespeichert sind, sind nicht betroffen. – Die Abfrageplanverwaltung bietet eine Möglichkeit, Abfragepläne zu beeinflussen, ohne den Anwendungscode ändern zu müssen. Wenn jedoch ein generischer Plan bereits in einer vorhandenen Sitzung gespeichert ist und Sie den Abfrageplan ändern möchten, müssen Sie zunächst plan_cache_mode in der DB-Cluster-Parametergruppe auf force_custom_plan setzen.

• queryid in apg_plan_mgmt.dba_plans und pg_stat_statements kann in folgenden Fällen abweichen:

  • Objekte werden nach dem Speichern in apg_plan_mgmt.dba_plans gelöscht und neu erstellt.

  • Die Tabelle apg_plan_mgmt.plans wird aus einem anderen Cluster importiert.

Informationen zu den verschiedenen Versionen der Abfrageplanverwaltung von Aurora PostgreSQL finden Sie unter Versionen der apg_plan_mgmt-Erweiterung in den Versionshinweisen für Aurora PostgreSQL.

Terminologie der Abfrageplanverwaltung

Die folgenden Begriffe werden in diesem Thema verwendet.

verwaltete Anweisung

Eine SQL-Anweisung, die vom Optimierer im Rahmen der Abfrageplanverwaltung erfasst wurde. Eine verwaltete Anweisung verfügt über einen oder mehrere Abfrageausführungspläne, die in der apg_plan_mgmt.dba_plans-Ansicht gespeichert sind.

Plan-Baseline

Die Menge der genehmigten Pläne für eine bestimmte verwaltete Anweisung. Das heißt, alle Pläne für die verwaltete Anweisung, für die in der status-Spalte der dba_plan-Ansicht „Approved“ angegeben ist.

Planverlauf

Die Menge der erfassten Pläne für eine bestimmte verwaltete Anweisung. Der Planverlauf enthält alle Pläne, die für die Anweisung erfasst wurden, unabhängig vom Status.

Abfrageplanregression

Tritt auf, wenn der Optimierer einen weniger optimalen Plan als vor einer bestimmten Änderung an der Datenbankumgebung auswählt, z. B. eine neue PostgreSQL-Version oder Änderungen an Statistiken.

Die Abfrageplanverwaltung in Aurora-PostgreSQL-Versionen

Die Abfrageplanverwaltung wird von allen derzeit verfügbaren Aurora-PostgreSQL-Versionen unterstützt. Weitere Informationen finden Sie unter Aktualisierungen von Amazon Aurora PostgreSQL im Abschnitt Versionshinweise für Aurora PostgreSQL.

Die Funktion für die Abfrageplanverwaltung wird Ihrem DB-Cluster von Aurora PostgreSQL hinzugefügt, wenn Sie die apg_plan_mgmt-Erweiterung installieren. Verschiedene Versionen von Aurora PostgreSQL unterstützen unterschiedliche Versionen der apg_plan_mgmt-Erweiterung. Wir empfehlen Ihnen, die Erweiterung für die Abfrageplanverwaltung auf die neueste Version für Ihre Version von Aurora PostgreSQL zu aktualisieren.

Anmerkung

Versionshinweise für die Versionen jeder apg_plan_mgmt-Erweiterung finden Sie unter Aurora PostgreSQL apg_plan_mgmt extension versions (Versionen der apg_plan_mgmgmt-Erweiterung von Aurora PostgreSQL) in den Release Notes for Aurora PostgreSQL (Versionshinweisen für Aurora PostgreSQL).

Sie können die auf Ihrem Cluster ausgeführte Version identifizieren, indem Sie mit psql eine Verbindung mit einer Instance herstellen und den Metabefehl \dx verwenden, um Erweiterungen aufzulisten, wie nachfolgend gezeigt.

labdb=> \dx
                       List of installed extensions
     Name      | Version |    Schema     |                            Description
---------------+---------+---------------+-------------------------------------------------------------------
 apg_plan_mgmt | 1.0     | apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management
 plpgsql       | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

Die Ausgabe zeigt, dass dieser Cluster die Version 1.0 der Erweiterung verwendet. Für eine bestimmte Aurora-PostgreSQL-Version sind nur bestimmte apg_plan_mgmt-Versionen verfügbar. In einigen Fällen müssen Sie möglicherweise den DB-Cluster von Aurora PostgreSQL auf eine neue Nebenversion aktualisieren oder einen Patch anwenden, damit Sie ein Upgrade auf die neueste Version der Abfrageplanverwaltung durchführen können. Die in der Ausgabe angezeigte apg_plan_mgmt-Version 1.0 stammt von einem DB-Cluster von Aurora-PostgreSQL-Version 10.17, für den keine neuere apg_plan_mgmt-Version verfügbar ist. In diesem Fall sollte der DB-Cluster von Aurora PostgreSQL auf eine neuere Version von PostgreSQL aktualisiert werden.

Weitere Informationen über die Aktualisierung Ihres DB-Clusters von Aurora PostgreSQL auf eine neue PostgreSQL-Version finden Sie unter Amazon Aurora PostgreSQL-Aktualisierungen.

Informationen zum Upgrade der apg_plan_mgmt-Erweiterung finden Sie unter Aktualisieren der Abfrageplanverwaltung in Aurora PostgreSQL.

Aktivieren der Abfrageplanverwaltung in Aurora PostgreSQL

Das Einrichten der Abfrageplanverwaltung für Ihren DB-Cluster von Aurora PostgreSQL umfasst die Installation einer Erweiterung und das Ändern mehrerer DB-Cluster-Parametereinstellungen. Sie benötigen rds_superuser-Berechtigungen, um die apg_plan_mgmt-Erweiterung zu installieren und die Funktion für den DB-Cluster von Aurora PostgreSQL zu aktivieren.

Durch die Installation der Erweiterung wird eine neue Rolle, apg_plan_mgmt, erstellt. Diese Rolle ermöglicht Datenbankbenutzern, Abfragepläne anzuzeigen, zu verwalten und zu pflegen. Als Administrator mit rds_superuser-Berechtigungen müssen Sie die apg_plan_mgmt-Rolle Datenbankbenutzern nach Bedarf zuweisen.

Nur Benutzer mit der rds_superuser-Rolle können den folgenden Vorgang ausführen. rds_superuser ist erforderlich, um die Erweiterung apg_plan_mgmt und die zugehörige Rolle apg_plan_mgmt zu erstellen. Benutzer benötigen die Rolle apg_plan_mgmt zur Verwaltung der Erweiterung apg_plan_mgmt.

So aktivieren Sie die Abfrageplanverwaltung für Ihren DB-Cluster von Aurora PostgreSQL

Mit den folgenden Schritten wird die Abfrageplanverwaltung für alle SQL-Anweisungen aktiviert, die an den DB-Cluster von Aurora PostgreSQL übermittelt werden. Dies wird als automatischer Modus bezeichnet. Weitere Informationen zum Unterschied zwischen den Modi finden Sie unter Erfassung von Aurora PostgreSQL-Ausführungsplänen.

1. Öffnen Sie die Amazon-RDS-Konsole unter https://console.aws.amazon.com/rds/.

2. Erstellen Sie eine benutzerdefinierte DB-Cluster-Parametergruppe für Ihren DB-Cluster von Aurora PostgreSQL. Sie müssen bestimmte Parameter ändern, um die Abfrageplanverwaltung zu aktivieren und ihr Verhalten festzulegen. Weitere Informationen finden Sie unter Erstellen einer DB-Parametergruppe.

3. Öffnen Sie die benutzerdefinierte DB-Cluster-Parametergruppe und legen Sie den rds.enable_plan_management-Parameter auf 1 fest, wie in der folgenden Abbildung gezeigt.

   

   Weitere Informationen finden Sie unter Ändern von Parametern in einer DB-Cluster-Parametergruppe.

4. Erstellen Sie eine benutzerdefinierte DB-Parametergruppe, mit der Sie Abfrageplanparameter auf Instance-Ebene festlegen können. Weitere Informationen finden Sie unter Erstellen einer DB-Cluster-Parametergruppe.

5. Ändern Sie die Writer-Instance des DB-Clusters von Aurora PostgreSQL, um die benutzerdefinierte DB-Parametergruppe zu verwenden. Weitere Informationen finden Sie unter Ändern einer DB-Instance in einem DB-Cluster.

6. Ändern Sie den DB-Cluster von Aurora PostgreSQL, um die benutzerdefinierte DB-Parametergruppe zu verwenden. Weitere Informationen finden Sie unter Ändern des DB-Clusters über die Konsole, die CLI und die API.

7. Starten Sie Ihre DB-Instance neu, um die Einstellungen der benutzerdefinierten Parametergruppe zu aktivieren.

8. Stellen Sie eine Verbindung mit dem DB-Instance-Endpunkt Ihres DB-Clusters von Aurora PostgreSQL mit psql oder pgAdmin her. Im folgenden Beispiel wird das postgres-Standardkonto für die rds_superuser-Rolle verwendet.

   psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db

9. Erstellen Sie die apg_plan_mgmt-Erweiterung für Ihre DB-Instance, wie nachfolgend gezeigt.

   labdb=> CREATE EXTENSION apg_plan_mgmt;
   CREATE EXTENSION

   Tipp

   Installieren Sie die apg_plan_mgmt-Erweiterung in der Vorlagendatenbank für Ihre Anwendung. Der Name der Standard-Vorlagendatenbank lautet template1. Weitere Informationen finden Sie unter Template Databases (Vorlagendatenbanken) in der PostgreSQL-Dokumentation.

10. Ändern Sie den Parameter apg_plan_mgmt.capture_plan_baselines in automatic. Diese Einstellung veranlasst den Optimierer, Pläne für jede SQL-Anweisung zu generieren, die entweder geplant oder mindestens zweimal ausgeführt wird.

    Anmerkung

    Die Abfrageplanverwaltung verfügt auch über einen manuellen Modus, den Sie für bestimmte SQL-Anweisungen verwenden können. Weitere Informationen hierzu finden Sie unter Erfassung von Aurora PostgreSQL-Ausführungsplänen.

11. Ändern Sie den Wert des apg_plan_mgmt.use_plan_baselines-Parameters in „on“. Dieser Parameter veranlasst den Optimierer, einen Plan für die Anweisung aus der Plan-Baseline auszuwählen. Weitere Informationen hierzu finden Sie unter Verwenden von Aurora PostgreSQL-Plänen.

    Anmerkung

    Sie können den Wert eines dieser dynamischen Parameter für die Sitzung ändern, ohne die Instance neu starten zu müssen.

Wenn die Einrichtung der Abfrageplanverwaltung abgeschlossen ist, müssen Sie die apg_plan_mgmt-Rolle allen Datenbankbenutzern zuweisen, die Abfragepläne anzeigen, verwalten oder pflegen müssen.

Aktualisieren der Abfrageplanverwaltung in Aurora PostgreSQL

Wir empfehlen Ihnen, die Erweiterung für die Abfrageplanverwaltung auf die neueste Version für Ihre Version von Aurora PostgreSQL zu aktualisieren.

1. Stellen Sie eine Verbindung mit der Writer-Instance Ihres DB-Clusters von Aurora PostgreSQL als Benutzer mit rds_superuser-Berechtigungen her. Wenn Sie beim Einrichten Ihrer Instance den Standardnamen beibehalten haben, stellen Sie eine Verbindung als postgres her. In diesem Beispiel wird die Verwendung von psql gezeigt, Sie können jedoch auch pgAdmin verwenden, wenn Sie möchten.

   psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

2. Führen Sie die folgende Abfrage aus, um die Erweiterung zu aktualisieren.

   ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';

3. Verwenden Sie die Funktion apg_plan_mgmt.validate_plans, um die Hashes aller Pläne zu aktualisieren. Der Optimierer validiert alle genehmigten, nicht genehmigten und abgelehnten Pläne, um sicherzustellen, dass es sich weiterhin um durchführbare Pläne für die neue Version der Erweiterung handelt.

   SELECT apg_plan_mgmt.validate_plans('update_plan_hash');

   Weitere Informationen zu dieser Funktion finden Sie unter Validieren von Plänen.

4. Verwenden Sie die Funktion apg_plan_mgmt.reload, um alle Pläne im gemeinsam genutzten Arbeitsspeicher mit den validierten Plänen aus der dba_plans-Ansicht zu aktualisieren.

   SELECT apg_plan_mgmt.reload();

Weitere Informationen zu allen Funktionen, die für die Abfrageplanverwaltung verfügbar sind, finden Sie unter Funktionsreferenz für die Aurora-PostgreSQL-Abfrageplanverwaltung.

Deaktivieren der Abfrageplanverwaltung in Aurora PostgreSQL

Sie können die Abfrageplanverwaltung jederzeit deaktivieren, indem Sie die apg_plan_mgmt.use_plan_baselines und apg_plan_mgmt.capture_plan_baselines deaktivieren.

labdb=> SET apg_plan_mgmt.use_plan_baselines = off;

labdb=> SET apg_plan_mgmt.capture_plan_baselines = off;