Überwachen der Leistung mit dem Abfragespeicher

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

Der Abfragespeicher ist ein Feature in Azure Database for PostgreSQL – Flexibler Server, das es Ihnen ermöglicht, die Abfrageleistung im Zeitverlauf nachzuverfolgen. Der Abfragespeicher vereinfacht die Behandlung von Leistungsproblemen, da Sie die am längsten ausgeführten und ressourcenintensivsten Abfragen schnell ermitteln können. Der Abfragespeicher erfasst automatisch einen Verlauf der Abfragen und Laufzeitstatistiken und bewahrt diese auf, damit Sie sie überprüfen können. Es teilt die Daten nach Zeit auf, so dass Sie zeitliche Nutzungsmuster sehen können. Die Daten für alle Benutzer, Datenbanken und Abfragen werden in einer Datenbank namens azure_sys in der Instanz von Azure Database for PostgreSQL – Flexibler Server gespeichert.

Aktivieren des Abfragespeichers

Der Abfragespeicher kann ohne zusätzliche Gebühren genutzt werden. Er ist ein optionales Feature und daher auf einem Server nicht standardmäßig aktiviert. Der Abfragespeicher kann global für alle Datenbanken auf einem bestimmten Server aktiviert oder deaktiviert werden. Ein Aktivieren oder Deaktivieren für einzelne Datenbanken ist nicht möglich.

Aktivieren des Abfragespeichers im Azure-Portal

1. Melden Sie sich beim Azure-Portal an, und wählen Sie Ihre Azure Database for PostgreSQL Flexible Server-Instanz aus.
2. Wählen Sie im Bereich Einstellungen im Menü die Option Serverparameter aus.
3. Suchen Sie nach dem Parameter pg_qs.query_capture_mode.
4. Legen Sie den Wert auf top oder all fest, je nachdem, ob Sie Abfragen der obersten Ebene oder auch geschachtelte Abfragen (innerhalb einer Funktion oder Prozedur ausgeführte Abfragen) nachverfolgen möchten, und wählen Sie Speichern aus. Es kann bis zu 20 Minuten dauern, bis der erste Datenbatch in der azure_sys-Datenbank gespeichert ist.

Aktivieren der Stichprobenentnahme für Wartezeiten für den Abfragespeicher

1. Suchen Sie nach dem Parameter pgms_wait_sampling.query_capture_mode.
2. Legen Sie für den Wert all fest und Speichern.

Informationen im Abfragespeicher

Der Abfragespeicher besteht aus zwei Speichern:

1. Ein Speicher für Laufzeitstatistiken zum Aufbewahren der Informationen aus den Abfragespeicherstatistiken.
2. Ein Speicher für Wartestatistiken zum Aufbewahren der Informationen aus den Wartestatistiken.

Häufige Szenarien für die Verwendung des Abfragespeichers sind u. a.:

• Bestimmen der Häufigkeit, mit der eine Abfrage in einem bestimmten Zeitfenster ausgeführt wurde
• Vergleichen der durchschnittlichen Ausführungszeit einer Abfrage über mehrere Zeitfenster hinweg, um große Abweichungen zu erkennen.
• Ermitteln der am längsten ausgeführten Abfragen in den vergangenen Stunden
• Ermitteln der Top-N-Abfragen, die auf Ressourcen warten
• Verstehen der Wartezeiten für eine bestimmte Abfrage.

Um die Speicherverwendung zu minimieren, werden die Laufzeit-Ausführungsstatistiken im Speicher für Laufzeitstatistiken für ein festes, konfigurierbares Zeitfenster zusammengefasst. Die Informationen in diesen Speichern können mithilfe von Ansichten abgefragt werden.

Zugreifen auf Abfragespeicherinformationen

Abfragespeicherdaten werden in der azure_sys-Datenbank in Ihrer Instanz von Azure Database for PostgreSQL – Flexibler Server gespeichert. Die folgende Abfrage gibt Informationen zu Abfragen zurück, die im Abfragespeicher aufgezeichnet wurden:

SELECT * FROM  query_store.qs_view;

Diese Abfrage gibt Informationen zu Wartestatistiken zurück:

SELECT * FROM  query_store.pgms_wait_sampling_view;

Suchen von wartenden Abfragen

Warteereignistypen kombinieren verschiedene Warteereignisse nach Ähnlichkeit in Buckets. Der Abfragespeicher enthält den Warteereignistyp, den Namen des spezifischen Warteereignisses und die jeweilige Abfrage. Die Möglichkeit zum Korrelieren dieser Warteinformationen mit den Abfragelaufzeitstatistiken bedeutet, dass Sie einen ausführlicheren Überblick darüber erhalten, welche Faktoren sich auf die Abfrageleistung auswirken.

Im Folgenden finden Sie einige Beispiele, wie Sie mithilfe der Wartestatistiken im Abfragespeicher weitere Erkenntnisse zur Ihrer Workload erhalten können:

Konfigurationsoptionen

Wenn der Abfragespeicher aktiviert ist, werden Daten in Aggregationsfenstern gespeichert, deren Länge durch den Serverparameter pg_qs.interval_length_minutes bestimmt wird (standardmäßig 15 Minuten). Pro Fenster werden bis zu 500 unterschiedliche Abfragen gespeichert. Attribute zur Unterscheidung von Abfragen sind „user_id“ (Bezeichner des Benutzenden, der die Abfrage ausführt), „db_id“ (Bezeichner der Datenbank, in deren Kontext die Abfrage ausgeführt wird) und „query_id“ (ein ganzzahliger Wert zur eindeutigen Identifizierung der ausgeführten Abfrage). Wenn die Anzahl unterschiedlicher Abfragen während des konfigurierten Intervalls 500 erreicht, werden 5 % der aufgezeichneten Abfragen freigegeben, um Platz für weitere Abfragen zu schaffen. Als Erstes werden dabei die Abfragen freigegeben, die am wenigsten oft ausgeführt wurden.

Die folgenden Optionen stehen für die Konfiguration der Abfragespeicher-Parameter zur Verfügung:

(*) Statischer Serverparameter, der einen Serverneustart erfordert, damit eine Änderung des Werts wirksam wird

Die folgenden Optionen gelten speziell für Wartestatistiken:

Verwenden Sie dasAzure-Portal, um für einen Parameter einen anderen Wert abzurufen oder festzulegen.

Ansichten und Funktionen

Sie können die vom Abfragespeicher aufgezeichneten Informationen mithilfe einiger Ansichten und Funktionen, die im query_store-Schema der azure_sys-Datenbank verfügbar sind, abfragen oder löschen. In der öffentlichen PostgreSQL-Rolle kann jeder diese Ansichten verwenden, um die Daten im Abfragespeicher anzuzeigen. Diese Ansichten sind nur in der azure_sys-Datenbank verfügbar.

Abfragen werden normalisiert, indem ihre Struktur analysiert und alles ignoriert wird, das nicht semantisch signifikant ist, z. B. Literale, Konstanten, Aliase oder Unterschiede bei der Groß-/Kleinschreibung.

Wenn zwei Abfragen semantisch identisch sind, auch wenn sie unterschiedliche Aliase für die gleichen Spalten und Tabellen verwenden, werden sie mit demselben query_id-Wert identifiziert. Wenn sich zwei Abfragen nur in den darin verwendeten Literalwerten unterscheiden, werden sie auch mit demselben query_id-Wert identifiziert. Bei Abfragen, die mit demselben query_id-Wert identifiziert wurden, entspricht der sql_query_text-Wert der Abfrage, die seit dem Beginn der Aufzeichnungsaktivität im Abfragespeicher zuerst ausgeführt wurde, oder seit dem letzten Verwerfen der gespeicherten Daten, da die Funktion query_store.qs_reset ausgeführt wurde.

Funktionsweise der Abfragenormalisierung

Im Folgenden sind einige Beispiele aufgeführt, um zu veranschaulichen, wie diese Normalisierung funktioniert:

Nehmen Sie an, dass Sie eine Tabelle mit der folgenden Anweisung erstellen:

create table tableOne (columnOne int, columnTwo int);

Sie aktivieren die Datensammlung im Abfragespeicher, und ein oder mehrere Benutzer*innen führen die folgenden Abfragen in genau dieser Reihenfolge aus:

select * from tableOne;
select columnOne, columnTwo from tableOne;
select columnOne as c1, columnTwo as c2 from tableOne as t1;
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one";

Alle vorherigen Abfragen verwenden denselben query_id-Wert. Der Text, den der Abfragespeicher speichert, ist der der ersten Abfrage, die nach dem Aktivieren der Datensammlung ausgeführt wird. Daher würde dieser select * from tableOne; lauten.

Sobald die folgenden Abfragen normalisiert sind, stimmen sie nicht mit den vorherigen Abfragen überein, da die WHERE-Klausel sie semantisch unterschiedlich macht:

select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
select * from tableOne where columnOne = -3 and columnTwo = -3;
select columnOne, columnTwo from tableOne where columnOne = '5' and columnTwo = '5';
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = 7 and columnTwo = 7;

Alle Abfragen in diesem letzten Abfrageset verwenden jedoch denselben query_id-Wert, und der Text, der zum Identifizieren dieser Abfragen verwendet wird, ist der der ersten Abfrage im Batch select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;.

Unten sehen Sie einige Abfragen, deren query_id-Werte nicht mit denen im vorherigen Batch übereinstimmen, und den Grund, warum dies der Fall ist:

Query (Abfrage):

select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;

Grund für Nichtübereinstimmung: Die Liste der Spalten bezieht sich auf die gleichen beiden Spalten (columnOne und columnTwo), aber die Reihenfolge, in der auf sie verwiesen wird, ist umgekehrt – columnOne, ColumnTwo im vorherigen Batch und ColumnTwo, columnOne in dieser Abfrage.

Query (Abfrage):

select * from tableOne where columnTwo = 25 and columnOne = 25;

Grund für Nichtübereinstimmung: Die Reihenfolge, in der auf die in der WHERE-Klausel ausgewerteten Ausdrücke verwiesen wird, wird von columnOne = ? and ColumnTwo = ? im vorherigen Batch zu ColumnTwo = ? and columnOne = ? in dieser Abfrage umgekehrt.

Query (Abfrage):

select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;

Grund für Nichtübereinstimmung: Der erste Ausdruck in der Spaltenliste ist nicht mehr columnOne, aber die abs-Funktion wird über columnOne (abs(columnOne)) ausgewertet, was nicht semantisch gleichwertig ist.

Query (Abfrage):

select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;

Grund für Nichtübereinstimmung: Der erste Ausdruck in der WHERE-Klausel wertet die Gleichheit von columnOne nicht mehr mit einem Literal aus, sondern mit dem Ergebnis der ceiling-Funktion, das über ein Literal ausgewertet wird, was nicht semantisch gleichwertig ist.

Ansichten

query_store.qs_view

Diese Ansicht gibt alle Daten zurück, die in den unterstützenden Tabellen des Abfragespeichers gespeichert wurden. Daten, die für das derzeit aktive Zeitfenster noch im Arbeitsspeicher aufgezeichnet werden, sind erst sichtbar, wenn das Zeitfenster abgelaufen ist und die flüchtigen Daten im Arbeitsspeicher gesammelt und in Tabellen auf dem Datenträger gespeichert werden. Diese Ansicht gibt eine andere Zeile für jede unterschiedliche Datenbank (db_id), jede*n Benutzer*in (user_id) und jede Abfrage (query_id) zurück.

query_store.query_texts_view

Diese Ansicht gibt alle Abfragetextdaten im Abfragespeicher zurück. Für jede unterschiedliche query_sql_text-Wert gibt es eine Zeile.

query_store.pgms_wait_sampling_view

Diese Ansicht gibt Warteereignisdaten im Abfragespeicher zurück. Diese Ansicht gibt eine andere Zeile für jede unterschiedliche Datenbank (db_id), jede*n Benutzer*in (user_id), jede Abfrage (query_id) und jedes Ereignis (event) zurück.

query_store.query_plans_view

Diese Ansicht gibt den Abfrageplan zurück, der zum Ausführen einer Abfrage verwendet wurde. Es gibt eine Zeile für jede einzelne Datenbank-ID und Abfrage-ID. Der Abfragespeicher zeichnet nur Abfragepläne für Abfragen ohne Hilfsprogramme auf.

Funktionen

query_store.qs_reset

Diese Funktion verwirft alle Statistiken, die bisher vom Abfragespeicher gesammelt wurden. Sie verwirft die Statistiken für abgeschlossene Zeitfenster, die bereits in Tabellen auf dem Datenträger gespeichert wurden. Sie verwirft auch die Statistiken für das aktuelle Zeitfenster, die nur im Arbeitsspeicher vorhanden sind. Nur Mitglieder der Rolle „Serveradministrator“ (azure_pg_admin) können diese Funktion ausführen.

query_store.staging_data_reset

Diese Funktion verwirft alle Statistiken, die vom Abfragespeicher im Arbeitsspeicher gesammelt wurden (d. h. die Daten im Arbeitsspeicher, die noch nicht in Tabellen auf dem Datenträger geleert wurden, die die Persistenz von gesammelten Daten für den Abfragespeicher unterstützen). Nur Mitglieder der Rolle „Serveradministrator“ (azure_pg_admin) können diese Funktion ausführen.

Schreibgeschützter Modus

Wenn sich eine Instanz von Azure Database for PostgreSQL – Flexibler Server im schreibgeschützten Modus befindet, z. B. weil der Parameter default_transaction_read_only auf on festgelegt ist oder der schreibgeschützte Modus automatisch aktiviert wurde, da die Speicherkapazität erschöpft ist, erfasst der Abfragespeicher keine Daten.

Durch das Aktivieren des Abfragespeichers auf einem Server mit Lesereplikaten wird der Abfragespeicher nicht automatisch für die Lesereplikate aktiviert. Auch wenn Sie ihn für eines der Lesereplikate aktivieren, zeichnet der Abfragespeicher die Abfragen auf Lesereplikaten nicht auf, da diese im schreibgeschützten Modus ausgeführt werden. Sie müssen sie zunächst zu primären Replikaten höherstufen.

Teilen Sie Ihre Vorschläge und Fehler mit dem Azure Database for PostgreSQL-Produktteam.

Zugehöriger Inhalt

• Verwendungsszenarien für den Abfragespeicher in Azure Database for PostgreSQL – Flexibler Server
• Bewährte Methoden für den Abfragespeicher in Azure Database for PostgreSQL – Flexibler Server
• Query Performance Insight in Azure Database for PostgreSQL – Flexibler Server