Wenn ich eine neue Postgres-Instanz einrichte, gibt es ein paar Dinge, die ich tue, um sicherzustellen, dass ich herausfinden kann, was schief gelaufen ist, wenn etwas schief geht. Viele Leute denken, dass Sie zusätzliche Software einrichten müssen, um die Leistung von Postgres zu überwachen, aber Sie können mit einigen der integrierten Tools ziemlich weit kommen. Dieser Beitrag behandelt die folgenden Tools, die alle von Postgres bereitgestellt werden:

pg_stat_statements

Die pg_stat_statements Erweiterung bietet eine aggregierte Ansicht darüber, wie lange Ihre Abfragen dauern. Sie können pg_stat_statements wie folgt aktivieren :

Ausführen von sudo apt-get install postgres-contrib-${YOUR POSTGRES VERSION}. Dies installiert das postgres contrib-Paket, das zusätzliche Funktionen bietet, die Teil des offiziellen Postgres-Projekts sind, es ist nur nicht mit der Standard-Postgres-Installation gebündelt.
Fügen Sie pg_stat_statements zu den shared_preload_libraries in Ihren Postgres hinzu. conf-Datei, wie folgt: shared_preload_libraries = 'pg_stat_statements'.
Starten Sie Postgres neu und führen Sie CREATE EXTENSION pg_stat_statements aus.

Nachdem Sie pg_stat_statements aktiviert haben, sehen Sie eine Ansicht namens pg_stat_statements. Die pg_stat_statements Ansicht enthält eine Menge Informationen darüber, welche Abfragen die meiste Zeit in Anspruch nehmen:

Bei der Optimierung einer Datenbank suche ich zunächst, welche Abfragen optimiert werden sollen. Ich suche speziell nach zwei verschiedenen Arten von Abfragen. Ich schaue mir zuerst die Abfragen an, die die meiste CPU-Zeit beanspruchen, und schaue mir dann die Abfragen an, die im Durchschnitt am längsten dauern. Sie können die Abfragen abrufen, die die meiste CPU-Zeit in Anspruch nehmen, indem Sie Folgendes ausführen:

SELECT total_time, query FROM
pg_stat_statements
ORDER BY total_time
DESC LIMIT 10;

Hans-Jürgen Schönig von Cybertec benutzt dazu diese Query:

SELECT substring(query, 1, 50) AS short_query,

round(total_time::numeric, 2) AS total_time,

calls,

round(mean_time::numeric, 2) AS mean,

round((100 * total_time /

sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu

FROM pg_stat_statements

ORDER BY total_time DESC

LIMIT 20;

Die spalte total_time ist die Gesamtzeit, die für die Ausführung der Abfrage aufgewendet wurde. Sie können es sich als durchschnittliche Abfragezeit * Anzahl der Male vorstellen, in denen die Abfrage ausgeführt wurde.

Sie können die Abfragen abrufen, die im Durchschnitt am längsten dauern, indem Sie Folgendes ausführen:

SELECT mean_time, query FROM
pg_stat_statements
ORDER BY mean_time
DESC LIMIT 10;

Sobald ich die Abfragen habe, die die meiste Zeit in Anspruch nehmen, verwende ich das langsame Abfrageprotokoll, um ausführbare Instanzen der Abfrage zu finden.

Relevante Links:

Postgres Docs on pg_stat_statements
Citus Blog Post on pg_stat_statements

Das Protokoll für langsame Abfragen

Das Problem mit pg_stat_statements ist, dass es Ihnen normalisierte Versionen der Abfrage gibt. Wenn Sie beispielsweise die Abfrage SELECT * FROM users WHERE id = 1 ausführen, zeigt pg_stat_statements SELECT * FROM users WHERE id = ? . Obwohl es gut ist, Abfragen aus einer aggregierten Perspektive zu betrachten, wenn Sie die Abfrage optimieren möchten, benötigen Sie eine tatsächliche Instance der Abfrage mit allen ausgefüllten Parametern. Hier kommt das langsame Abfrageprotokoll ins Spiel. Wenn Sie das Langsam abfrageprotokoll aktivieren, protokolliert Postgres den Volltext jeder Abfrage, die einen Bestimmten Schwellenwert überschreitet. Normalerweise stelle ich es gerne auf 100ms ein.

Sie können das Protokoll für langsame Abfragen aktivieren, indem Sie den Parameter log_min_duration_statement in postgres.conf auf den gewünschten Schwellenwert festlegen:

log_min_duration_statement = 100

Nachdem Sie die Konfiguration aktualisiert und SELECT pg_reload_conf() ausgeführt haben, um die Konfiguration neu zu laden, beginnt Postgres mit der Protokollierung aller Abfragen, die den Schwellenwert überschreiten.

Nachdem ich eine Abfrage von pg_stat_statements abgerufen habe, suche ich das Langsam abfrageprotokoll nach einer bestimmten Instanz der Abfrage, die ich ausführen kann. Ich werde dann EXPLAIN ANALYZE verwenden , um zu verstehen, warum die Abfrage so lange dauert.

Relevante Links

Postgres Docs on pg_stat_statements
Citus Blog Post on pg_stat_statements

statement_timeout

Obwohl nicht für die Leistungsüberwachung an sich, ist statement_timeout eine Einstellung, die Sie unabhängig davon festlegen sollten. Wenn eine Abfrage den statement_timeout schwellwert überschreitet wird Postgres sie abbrechen. Das Festlegen von statement_timeout ist der einfachste Weg, um zu verhindern, dass außer Kontrolle geratene Abfragen Ihre DB überlasten. Obwohl die Postgres-Dokumente davon abraten, stelle ich gerne ein globales Timeout von 15s ein:

statement_timeout = 15000

Wenn es dann Queries gibt, von denen ich weiß, dass sie länger dauern werden, kann ich das Timeout nur für diese Abfragen erhöhen (wenn Sie eine Postgres-Bibliothek verwenden und ein Timeout festlegen, wird wahrscheinlich statement_timeout unter der Haube verwendet). Wenn ich eine Abfrage von psql ausführe und das Timeout treffe, ist es ziemlich einfach, das Timeout zu deaktivieren, indem ich Folgendes ausführe:

set statement_timeout = 0;

Indem ich eine globale statement_timeout setze und sie dann bei Bedarf heraufbeziehe, stelle ich sicher, dass keine Abfrage länger als 15 Sekunden dauert, ohne dass ich dieser Abfrage explizit die Berechtigung gegeben habe, länger zu dauern.

Ein Leitfaden für Beginners zur Leistungsüberwachung nach Postgres

pg_stat_statements

Relevante Links:

Das Protokoll für langsame Abfragen

Relevante Links

statement_timeout