Suchen und Beenden der Ausführung von Abfragen in PostgreSQL
Ihr PostgreSQL-Server greift zu, wobei einige ungezogene Abfragen zu viele Ressourcen verbrauchen oder andere blockieren. Keine Panik! Sie können diese problem-Abfragen stoppen und Ihr System stabilisieren.
In diesem Beitrag befassen wir uns mit dem Stoppen von Abfragen über SQL, Techniken zum Auffinden problematischer Abfragen und der gelegentlich nützlichen Möglichkeit, über Betriebssystemtools abzubrechen.
Stoppen von Abfragen über SQL in zwei Schritten
Hier ist der grundlegende Prozess zum Suchen und Beenden einer Abfrage. Beachten Sie, dass Sie eine Verbindung als Benutzer mit entsprechenden Berechtigungen herstellen müssen, z. B. über ein Administratorkonto.
1. Finden Sie die pid
PostgreSQL erstellt einen Prozess pro Verbindung und identifiziert jeden Prozess mit seiner Betriebssystem-Prozess-ID oder pid. Um eine Abfrage abzubrechen, müssen Sie die PID für die Verbindung kennen, auf der sie ausgeführt wird.
Eine Möglichkeit, dies herauszufinden, ist die pg_stat_activity Ansicht, die Informationen über die Live-Abfragen liefert. Versuchen Sie es beispielsweise mit dieser Abfrage:
SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY backend_start;
Der substr-Aufruf begrenzt das angezeigte SQL auf 100 Zeichen, um lange Abfragen zu vermeiden, die viel Platz auf dem Bildschirm beanspruchen. Der backend_type Filter vermeidet die Anzeige von Hintergrundserverprozessen, z. B. dem Autovacuum Launcher. Die Sortierung nach backend_start zeigt zuerst die am längsten laufenden Verbindungen, was oft problematische lange laufende Transaktionen zeigt.
Hier ist ein Beispiel für die Ausführung auf meinem Entwicklungsserver:
stagingi_inventev=# SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY backend_start;
pid | state | backend_start | q
—–+——–+——————————-+—————————————————————————————————–
73 | active | 2022-06-17 18:57:15.850803+00 | SELECT pid, state, backend_start, substr(query, 0, 100) q FROM pg_stat_activity WHERE backend_type
77 | idle | 2022-06-17 18:57:33.567574+00 | SELECT /* long-running query */ pg_sleep(10000);
(2 rows)
Eine andere Möglichkeit, PIDs zu finden, ist über das Betriebssystem mit Tools wie htop. Dies ist nützlich, wenn Sie Zugriff auf Ihren PostgreSQL-Server haben und Abfragen finden möchten, die die meisten Ressourcen verbrauchen.
Okay, Sie haben eine pid, jetzt lassen Sie uns diese Abfrage stoppen!
2. Beenden oder Abbrechen des Vorgangs
PostgreSQL verfügt über zwei Funktionen zum Beenden von Abfragen, wobei die folgenden Unterschiede erörtert werden.
Der "harte" ist pg_terminate_backend, den Sie wie folgt verwenden können:
SELECT pg_terminate_backend(pid);
Ich benutze standardmäßig dieses, die Argumentation wird unten erläutert.
Die Funktion "kinder" ist pg_cancel_backend, die Sie wie folgt verwenden können:
SELECT pg_cancel_backend(pid);
Wenn Sie eine der beiden verwenden, ersetzen Sie pid durch die pid, die Sie in Schritt 1 gefunden haben. So beenden Sie beispielsweise die lang andauernde Abfrage von oben:
SELECT pg_terminate_backend(77);
Und boom, es ist weg.
Es gibt zwei Unterschiede, die pg_terminate_backend "härter" machen.
Zuerst stoppt pg_terminate_backend den Prozess vollständig, wodurch die Verbindung geschlossen wird. Dadurch werden alle offenen Transaktionen auf der Verbindung zurückgesetzt, wodurch alle Sperren aufgehoben werden, die sie enthält.
Im Gegensatz dazu unterbricht pg_cancel_backend nur die laufende Abfrage und lässt die Verbindung offen. Die aktuelle Transaktion oder der Sicherungspunkt wird abgebrochen. Wenn also die Verbindung Sicherungspunkte verwendet, kann sie die umgebende Transaktion mit ausstehenden Datenänderungen und Sperren weiterhin offen halten.Zweitens gilt pg_terminate_backend sofort*, während pg_cancel_backend an bestimmten Punkten im Backend-Prozesslebenszyklus aufgeschoben werden kann. Manchmal können Sie pg_cancel_backend laufen und sehen, dass für eine Weile nichts passiert. Insbesondere tritt dies auf, wenn der Prozess Eingaben vom Client liest, z. B. eine eingehende Abfrage. Der Backend-Prozess verschiebt die Abwicklung des Abbruchs, bis alle Eingaben gelesen wurden, da sonst die Verbindung nicht offen und funktionsfähig gelassen werden könnte.
(*Aktuell, pg_terminate_backend kann auch nicht sofort angewendet werden, aber es ist viel weniger wahrscheinlich. Kleine Abschnitte des Codes verschieben auch die Verarbeitung. Theoretisch verbringen diese Abschnitte nicht viel Zeit mit der Ausführung, aber sagen Sie niemals nie. Wenn Sie daran interessiert sind, in die Quelle einzutauchen, beginnen Sie mit ProcessInterrupts in src / backend / tcp / postgres.c.)
Ich verwende standardmäßig pg_terminate_backend. Normalerweise, wenn ich eine Abfrage beenden muss, möchte ich den gesamten Anwendungsprozess stoppen, der sie gestartet hat, alle Datenänderungen rückgängig machen und alle Sperren aufheben. Bei Verwendung von pg_cancel_backend besteht das Risiko, dass der Fehlerbehandlungscode der Anwendung die Transaktion/den Savepoint zurücksetzt und ähnliche Abfragen ausführt. Und es kann weiterhin problematische Locks halten.
Erkennen von bösartigen ausgeführten Abfragen
Wenn Sie mehr als eine Abfrage stoppen möchten, kann es mühsam sein, pg_terminate_backend () einzeln auszuführen. Sie können SQL verwenden, um die fehlerhaften Abfragen zu finden und die terminate-Anweisungen zu generieren, um sie alle mit Leichtigkeit loszuwerden. Hier sind ein paar Beispiele.
Abfragen, die einen bestimmten Prozess blockieren
Wenn Sie eine ALTER TABLE ausführen und feststellen, dass sie blockiert ist und auf eine Tabellensperre wartet, können Sie die Verbindungen beenden, die locks auf dieser Tabelle enthalten. Dadurch kann ALTER TABLE fortgesetzt werden.
Beispielsweise habe ich kürzlich an einer Anwendung mit einigen lang andauernden Transaktionen gearbeitet, die eine Datenbankmigration blockiert haben. Diese lang andauernden Abfragen konnten sicher beendet werden, da der verantwortliche Bewerbungsprozess später erneut ausgeführt und etwaige Lücken geschlossen werden konnte.
Sie könnendie blockierte PID von ALTER TABLE mit einer Abfrage gegen pg_stat_activity wie folgt finden:
SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND query LIKE 'ALTER TABLE%'
ORDER BY backend_start;
Mit der blockierten PID können Sie diese Abfrage mit pg_blocking_pids verwenden, um SQL zu generieren, um die blockierenden Prozesse zu beenden:
SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE pid = ANY (pg_blocking_pids(blockedpid));
Um diese Abfrage auszuführen, ersetzen Sie blockedpid durch die PID des blockierten Prozesses. Kopieren Sie dann die Ausgabezeilen, fügen Sie sie ein und führen Sie sie aus.
Hier ist eine Beispielsitzung, die diese Abfragen verwendet, um die Blockierung einer ALTER TABLE aufzuheben:
mydb=# SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND query LIKE 'ALTER TABLE%'
ORDER BY backend_start;
pid | state | backend_start | q
—–+——–+——————————-+————————————————
613 | active | 2022-06-17 20:25:06.604326+00 | ALTER TABLE auth_user ADD COLUMN age int null;
(1 row)
mydb=# SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE pid = ANY (pg_blocking_pids(613));
?column?
———————————-
SELECT pg_terminate_backend(77);
(1 row)
mydb=# SELECT pg_terminate_backend(77);
pg_terminate_backend
———————-
t
(1 row)
mydb=# SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND query LIKE 'ALTER TABLE%'
ORDER BY backend_start;
pid | state | backend_start | q
—–+——-+——————————-+————————————————
613 | idle | 2022-06-17 20:25:06.604326+00 | ALTER TABLE auth_user ADD COLUMN age int null;
(1 row)
Cowabunga!
Abfragen für eine bestimmte Tabelle
Manchmal möchten Sie möglicherweise einfach alle Abfragen beenden, die für eine bestimmte Tabelle ausgeführt werden. Dies kann angemessen sein, um eine Überlastung durch einen bestimmten sich schlecht verhaltenden Bewerbungsprozess zu verhindern.
Diese Abfrage generiert SQL, um alle laufenden Abfragen zu beenden, die so aussehen, als würden Sie eine bestimmte-Tabelle namens auth_user verwenden:
SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE query LIKE '%auth_user%'
AND pid != pg_backend_pid();
Um diese Abfrage zu verwenden, ändern Sie den übereinstimmenden Tabellennamen in LIKE '% auth_user%', bevor Sie sie ausführen. Kopieren Sie dann die Ausgabezeilen, fügen Sie sie ein und führen Sie sie aus.
Das Abgleichen der Abfrage mit LIKE ist etwas stumpf, da es die Möglichkeit für falsche Positive hat, aber es ist einfach. Der Vergleich mit pg_backend_pid ist erforderlich, um eine Übereinstimmung mit der aktuellen Verbindung zu vermeiden.
Im Folgenden finden Sie ein Beispiel für die Verwendung dieser Abfrage zum Beenden aller Abfragen an eine Tabelle namens library_book:
mydb=# SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE query LIKE '%library_book%'
AND pid != pg_backend_pid();
?column?
——————————-
SELECT pg_terminate_backend(123);
SELECT pg_terminate_backend(124);
(1 row)
mydb=# SELECT pg_terminate_backend(123);
SELECT pg_terminate_backend(124);
pg_terminate_backend
——————-
t
(1 row)
pg_terminate_backend
——————-
t
(1 row)
Verbindungen öffnen sich länger als n Sekunden
Ein letztes Beispiel: Wie man Verbindungen zu denjenigen herausfiltert, die länger als N Sekunden geöffnet sind. Dies ist ein ziemlich stumpfer Hammer, aber Sie können es in Notfällen versuchen.
SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE backend_type = 'client backend'
AND pid != pg_backend_pid()
AND backend_start < NOW() – '10 seconds'::interval;
Passen Sie " 10 Sekunden" entsprechend an.
Zum Beispiel:
mydb=# SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE backend_type = 'client backend'
AND backend_start < now() – '10 seconds'::interval
AND pid != pg_backend_pid();
?column?
————————————
SELECT pg_terminate_backend(2675);
SELECT pg_terminate_backend(2676);
(2 rows)
mydb=# SELECT pg_terminate_backend(2675);
SELECT pg_terminate_backend(2676);
pg_terminate_backend
———————-
t
(1 row)
WARNING: PID 2676 is not a PostgreSQL server process
pg_terminate_backend
———————-
f
WARNUNG: PID 2676 ist kein PostgreSQL-Serverprozess
pg_terminate_backend
———————-
f
(1 Reihe)
Beachten Sie, dass in diesem Fall pid 2676 zwischen dem Generieren von SQL und dem Ausführen abgeschlossen zu haben scheint.
Stoppen von Abfragen über das Betriebssystem
Wenn Sie Zugriff auf Ihren PostgreSQL-Server haben, können Sie Abfragen auch stoppen, indem Sie die entsprechenden Betriebssystemprozesse signalisieren. Dies erfordert, dass Sie Shell-Zugriff auf Ihren PostgreSQL-Server haben, was heutzutage mit dem Aufkommen von verwalteten Database-Plattformen nicht mehr so üblich ist.
Sie können ein Tool wie htop verwenden , um die laufenden Backend-Prozesse zu überprüfen. Möglicherweise können Sie die problematischen basierend auf einer hohen CPU- oder Speicherauslastung finden.
Backend-Prozesse werden als untergeordnete Elemente des PostgreSQL-Hauptserverprozesses angezeigt. Seien Sie vorsichtig, dass Sie die richtigen PIDs auswählen, als ob Sie den Hauptprozess beenden würden, der gesamte PostgreSQL-Server wird heruntergefahren.
Unter Linux/macOS/Unixes
Unter Unix-Betriebssystemen können Sie eine laufende Abfrage beenden, indem Sie den Prozess SIGTERM (das Terminierungssignal) mit kill senden:
$ kill -SIGTERM pid
Ersetzen Sie pid durch den Backend-Prozess pid.
Und Sie können einen Prozess abbrechen, indem Sie SIGINT (das Interrupt-Signal) senden:
$ kill -SIGINT pid
Unter Windows
Windows hat keinen Kill-Befehl, daher stellt PostgreSQL das pg_ctl Tool zum Senden von Signalen an Prozesse bereit. Sie können es verwenden, um eine Abfrage zu beenden, indem Sie das Abschlusssignal wie folgt senden:
$ pg_ctl kill TERM pid
Ersetzen Sie pid durch die Ziel-Backend-pid.
Sie können es verwenden, um eine Abfrage abzubrechen, indem Sie das Interrupt-Signal wie folgt senden:
$ pg_ctl kill INT pid
… Tschüss Anfrage!
Beenden von Abfrageprozessen
Mit dem Betriebssystem können wir einen Prozess auch sofort stoppen, garantiert, indem wir ihn "töten". Daher kommt der Name des "Kill" -Dienstprogramms. (Und seine veraltete, gewalttätige Terminologie.) Das Doing so kann in seltenen Situationen nützlich sein, in denen ein Prozess nach dem Beenden weiterhin Ressourcen verbraucht, möglicherweise aufgrund eines Fehlers in PostgreSQL.
Diese Aktion ist jedoch sehr drastisch. Die sofortige Beendigung stoppt den Prozess sofort, ohne die Möglichkeit, eine Bereinigung durchzuführen. Das Design von PostgreSQL sollte Datenverlust (von festgeschriebenen Zeilen) verhindern, aber Sie können andere zufällige Daten wie Protokollnachrichten verpassen.
Um einen Prozess zu beenden, senden Sie ihm das KILL-Signal .
Über Unixe:
$ kill -KILL pid
Unter Windows:
$ pg_ctl kill KILL pid