Autovacuum-Optimierung in Azure Database for PostgreSQL – Flexibler Server
GILT FÜR:
Azure Database for PostgreSQL – Flexible Server
Dieser Artikel enthält eine Übersicht über das Autovacuum-Feature für Azure Database for PostgreSQL – Flexibler Server sowie die Problembehandlungsleitfäden für Funktionen. Mit diesen Leitfäden können Sie die Datenbanküberfrachtung überwachen sowie Autovacuum-Blockierungen und Informationen dazu ermitteln, wie weit die Datenbank von einem Notfall oder Umbruch entfernt ist.
Was ist Autovacuum
Interne Datenkonsistenz in PostgreSQL basiert auf dem Multi-Version Concurrency Control (MVCC)-Mechanismus, der es dem Datenbankmodul ermöglicht, mehrere Versionen einer Zeile aufrechtzuerhalten, und eine größere Parallelität mit minimaler Blockierung zwischen den verschiedenen Prozessen bietet.
PostgreSQL-Datenbanken benötigen eine angemessene Wartung. Wenn beispielsweise eine Zeile gelöscht wird, wird sie nicht physisch entfernt. Stattdessen wird die Zeile als „tot“ markiert. Ähnlich wie bei Updates wird die Zeile als „tot“ gekennzeichnet und eine neue Version der Zeile wird eingefügt. Diese Vorgänge hinterlassen tote Datensätze, die als tote Tupel bezeichnet werden, auch wenn alle Transaktionen, bei denen diese Versionen sichtbar sein könnten, beendet sind. Solange sie nicht beseitigt werden, bleiben tote Tupel bestehen, verbrauchen Speicherplatz und blähen Tabellen und Indizes auf, was zu einer langsamen Abfrageleistung führen.
PostgreSQL verwendet einen Prozess namens Autovacuum, um tote Tupel automatisch zu bereinigen.
Interne Autovacuum-Elemente
Autovacuum liest Seiten, die nach toten Tupeln suchen, und wenn keine gefunden werden, wird die Seite automatisch verworfen. Wenn Autovacuum tote Tupel findet, entfernt es sie. Die Kosten basieren auf:
vacuum_cost_page_hit: Kosten für das Lesen einer Seite, die sich bereits in freigegebenen Puffern befindet und kein Lesen vom Datenträger erfordert. Standardmäßig ist der Wert auf „1“ festgelegt.vacuum_cost_page_miss: Kosten für das Abrufen einer Seite, die sich nicht in gemeinsam genutzten Puffern befindet. Der Standardwert ist 10.vacuum_cost_page_dirty: Kosten für das Schreiben auf jede Seite, wenn darauf ungültige Tupel gefunden werden. Der Standardwert ist 20.
Die Anzahl der Arbeit, die Autovacuum verrichtet, hängt von zwei Parametern ab:
autovacuum_vacuum_cost_limitist die Arbeitsmenge, die Autovacuum in einem Durchgang erledigen kann.autovacuum_vacuum_cost_delay: Anzahl von Millisekunden, die Autovacuum untätig ist, nachdem die durch den Parameterautovacuum_vacuum_cost_limitfestgelegte Kostenbeschränkung erreicht wurde.
In allen derzeit unterstützten Versionen von Postgres ist der Standardwert für autovacuum_vacuum_cost_limit 200 (tatsächlich ist sie auf -1 festgelegt, wodurch sie dem Wert der regulären vacuum_cost_limit entspricht, die standardmäßig 200 ist).
Was autovacuum_vacuum_cost_delay betrifft, so ist sie in Postgres Version 11 standardmäßig auf 20 Millisekunden eingestellt, während sie in Postgres Versionen 12 und höher standardmäßig auf 2 Millisekunden eingestellt ist.
Autovacuum wird jede Sekunde 50 Mal aktiviert (50*20 ms=1000 ms). Jedes Mal, wenn es aufwacht, liest Autovacuum 200 Seiten.
Dies bedeutet, dass Autovacuum in einer Sekunde Folgendes tun kann:
- ~80 MB/Sek. [ (200 Seiten/
vacuum_cost_page_hit) * 50 * 8 KB pro Seite] wenn alle Seiten mit toten Tupeln in freigegebenen Puffern gefunden werden. - ~8 MB/Sek. [ (200 Seiten/
vacuum_cost_page_miss) * 50 * 8 KB pro Seite] wenn alle Seiten mit toten Tupeln in freigegebenen Puffern gefunden werden. - ~4 MB/Sec [ (200 Seiten/
vacuum_cost_page_dirty) * 50 * 8 KB pro Seite] Autovacuum kann bis zu 4 MB/Sek. schreiben.
Überwachen von Autovacuum
Verwenden Sie die folgenden Abfragen, um Autovacuum zu überwachen:
select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;
In den folgenden Spalten wird ermittelt, ob Autovacuum die Tabellenaktivität aufnimmt:
- dead_pct: Prozentsatz der toten Tupel im Vergleich zu lebenden Tupeln.
- last_autovacuum: Das Datum der letzten Uhrzeit, zu der die Tabelle von Autovacuum bearbeitet wurde.
- last_autoanalyze: Das Datum der letzten automatischen Analyse der Tabelle.
Wann löst PostgreSQL Autovacuum aus
Eine Autovacuum-Aktion (entweder ANALYZE oder VACUUM) wird ausgelöst, wenn die Anzahl der toten Tupel eine bestimmte Zahl überschreitet, die von zwei Faktoren abhängt: der Gesamtanzahl der Zeilen in einer Tabelle sowie einem festen Schwellenwert. ANALYZE wird standardmäßig ausgelöst, wenn 10 % der Tabelle plus 50 Zeilen geändert werden, während VACUUM ausgelöst wird, wenn 20 % der Tabelle plus 50 Zeilen geändert werden. Da der VACUUM-Schwellenwert doppelt so hoch ist wie der ANALYZE-Schwellenwert, wird ANALYZE früher als VAKUUM ausgelöst.
Die genauen Formeln für jede Aktion sind:
- Autoanalyse: autovacuum_analyze_scale_factor * Tupel + autovacuum_analyze_threshold
- Autovacuum = autovacuum_vacuum_scale_factor * Tuples + autovacuum_vacuum_threshold
Beispielsweise wird die Analyse ausgelöst nach einer Änderung von 60 Zeilen in einer Tabelle mit 100 Zeilen, und Vacuum wird ausgelöst, wenn sich 70 Zeilen in der Tabelle geändert haben, mithilfe der folgenden Formeln:
Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70
Verwenden Sie die folgende Abfrage, um die Tabellen in einer Datenbank aufzuführen und die Tabellen zu identifizieren, die sich für den Autovacuum-Prozess qualifizieren:
SELECT *
,n_dead_tup > av_threshold AS av_needed
,CASE
WHEN reltuples > 0
THEN round(100.0 * n_dead_tup / (reltuples))
ELSE 0
END AS pct_dead
FROM (
SELECT N.nspname
,C.relname
,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
,pg_stat_get_live_tuples(C.oid) AS n_live_tup
,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
,C.reltuples AS reltuples
,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.relkind IN (
'r'
,'t'
)
AND N.nspname NOT IN (
'pg_catalog'
,'information_schema'
)
AND N.nspname !~ '^pg_toast'
) AS av
ORDER BY av_needed DESC ,n_dead_tup DESC;
Hinweis
Die Abfrage berücksichtigt nicht, dass Autovacuum mithilfe des DDL-Befehls „alter table“ auf Tabellenbasis konfiguriert werden kann.
Gängige Probleme mit Autovacuum
Überprüfen Sie die folgende Liste möglicher gängiger Probleme mit dem Autovacuum-Prozess.
Nicht mit einem geschäftigen Server schritthalten
Der Autovacuum-Prozess schätzt die Kosten jedes I/O-Vorgangs, akkumuliert eine Summe für jeden ausgeführten Vorgang und hält an, sobald die Obergrenze der Kosten erreicht ist. autovacuum_vacuum_cost_delay und autovacuum_vacuum_cost_limit sind die beiden Serverparameter, die im Prozess verwendet werden.
Standardmäßig ist autovacuum_vacuum_cost_limit auf –1 festgelegt, d. h. die Kostengrenze für Autovacuum ist derselbe Wert wie der Parameter vacuum_cost_limit, der standardmäßig auf 200 festgelegt ist. vacuum_cost_limit ist die Kosten für ein manuelles Vacuum.
Wenn autovacuum_vacuum_cost_limit die Option auf -1 eingestellt ist, verwendet Autovacuum den vacuum_cost_limit-Parameter, aber wenn autovacuum_vacuum_cost_limit selbst auf größer als -1 eingestellt ist, dann wird der autovacuum_vacuum_cost_limit-Parameter berücksichtigt.
Wenn Autovacuum nicht Schritt hält, werden möglicherweise die folgenden Parameter geändert:
| Parameter | Beschreibung |
|---|---|
autovacuum_vacuum_scale_factor |
Standard: 0.2, Bereich: 0.05 - 0.1. Der Skalierungsfaktor ist arbeitslastspezifisch und sollte abhängig von der Datenmenge in den Tabellen festgelegt werden. Untersuchen Sie vor dem Ändern des Werts die Arbeitsauslastung und einzelne Tabellenvolumes. |
autovacuum_vacuum_cost_limit |
Standardwert: 200. Die Kostengrenze wird unter Umständen erhöht. Die CPU- und E/A-Auslastung der Datenbank sollte vor und nach dem Vornehmen von Änderungen überwacht werden. |
autovacuum_vacuum_cost_delay |
Postgres Version 11 – Standard: 20 ms. Der Parameter wird möglicherweise auf 2-10 ms gesenkt.Postgres Versionen 12 und höher – Standard: 2 ms. |
Hinweis
Der autovacuum_vacuum_cost_limit-Wert wird proportional zwischen den ausgeführten Autovacuum-Workern verteilt, sodass die Summe der Grenzwerte für jeden Worker den Wert des autovacuum_vacuum_cost_limit-Parameters nicht überschreitet, wenn es mehrere gibt
Autovacuum wird ständig ausgeführt
Die fortlaufende Ausführung von Autovacuum kann sich auf die CPU- und E/A-Auslastung auf dem Server auswirken. Folgende Gründe sind möglich dafür:
maintenance_work_mem
Der Autovacuum-Daemon verwendet autovacuum_work_mem, was standardmäßig auf -1 eingestellt ist, was bedeutet, dass autovacuum_work_mem den gleichen Wert hätte wie der Parameter maintenance_work_mem. Dieses Dokument geht davon aus, dass er autovacuum_work_mem auf -1 festgelegt ist und maintenance_work_mem vom Autovacuum-Daemon verwendet wird.
Wenn maintenance_work_mem niedrig ist, kann der Wert auf Azure DB for PostgreSQL – Flexibler Server auf bis zu 2 GB erhöht werden. Eine allgemeine Faustregel besteht darin, für alle 1 GB RAM 50 MB an maintenance_work_mem zuzuweisen.
Große Anzahl von Datenbanken
Autovacuum versucht, alle autovacuum_naptime Sekunden einen Worker an jeder Datenbank starten zu lassen.
Wenn beispielsweise ein Server über 60 Datenbanken verfügt und autovacuum_naptime auf 60 Sekunden festgelegt ist, startet der Autovacuum-Worker jede Sekunde [autovacuum_naptime/Anzahl von DBs].
Es empfiehlt sich, autovacuum_naptime zu erhöhen, wenn mehr Datenbanken in einem Cluster vorhanden sind. Gleichzeitig kann der Autovacuum-Prozess aggressiver gemacht werden, indem autovacuum_cost_limit erhöht und die autovacuum_cost_delay-Parameter verringert und autovacuum_max_workers vom Standardwert 3, 4 oder 5 erhöht wird.
Arbeitsspeicherfehler
Übermäßig aggressive maintenance_work_mem-Werte könnten regelmäßig Fehler wegen unzureichendem Arbeitsspeicher im System verursachen. Es ist wichtig, den verfügbaren RAM auf dem Server zu verstehen, bevor eine Änderung an dem maintenance_work_mem-Parameter vorgenommen wird.
Autovacuum ist zu störend
Wenn Autovacuum viele Ressourcen verbraucht, können Folgende Schritte ausgeführt werden:
Autovacuum-Parameter
Schätzen Sie die Parameter autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit, autovacuum_max_workers ein. Die Autovacuum-Parameter nicht richtig festzulegen kann zu Szenarien führen, in denen Autovacuum zu störend wird.
Wenn Autovacuum zu störend ist, sollten Sie Folgendes in Betracht ziehen:
- Erhöhen Sie
autovacuum_vacuum_cost_delayund verringern Sieautovacuum_vacuum_cost_limit, wenn sie höher als der Standardwert 200 festgelegt sind. - Verringern Sie die Anzahl
autovacuum_max_workers, wenn ein höherer Wert als der Standardwert 3 festgelegt ist.
Zu viele Autovacuum-Worker
Das Erhöhen der Anzahl der Autovacuum-Worker erhöht nicht unbedingt die Geschwindigkeit von Vacuum. Es wird nicht empfohlen, eine hohe Anzahl von Autovacuum-Workern zu haben.
Das Erhöhen der Anzahl der Autovacuum-Worker führt zu mehr Arbeitsspeicherverbrauch, und je nach dem Wert von maintenance_work_mem könnte es eine Leistungsverschlechterung verursachen.
Jeder Autovacuum-Worker-Prozess erhält nur (1/maximale_Anzahl-Worker) von insgesamt autovacuum_cost_limit, daher könnte eine hohe Anzahl von Workern dazu führen, dass jeder einzelne langsamer wird.
Wenn die Anzahl der Arbeitnehmer erhöht wird, sollte autovacuum_vacuum_cost_limit auch erhöht werden und/oder autovacuum_vacuum_cost_delay sollte verringert werden, um den Vacuumprozess schneller zu machen.
Wenn wir jedoch die Tabellenebene autovacuum_vacuum_cost_delay oder autovacuum_vacuum_cost_limit Parameter geändert haben, werden die Worker, die auf diesen Tabellen ausgeführt werden, nicht im Ausgleichsalgorithmus berücksichtigt [Autovacuum_Kosten_Limit/Autovacuum_Maximalzahl_Worker].
Autovacuum-Transaktions-ID (TXID)-Rundumschutz
Wenn eine Datenbank im Transaktions-ID-Umbruchsch ausgeführt wird, kann eine Fehlermeldung wie folgt beobachtet werden:
Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.
Hinweis
Diese Fehlermeldung ist eine langfristige Aufsicht. Normalerweise müssen Sie nicht in den Einzelbenutzermodus wechseln. Stattdessen können Sie die erforderlichen VACUUM-Befehle ausführen und die Optimierung für VACUUM ausführen, damit es schneller läuft. Auch wenn Sie keine Datenbearbeitungssprache (DML) ausführen können, können Sie noch immer VACUUM ausführen.
Das Umbruchproblem tritt auf, wenn die Datenbank entweder nicht mit Vacuum behandelt wird oder wenn es zu viele tote Tupel gibt, die von Autovacuum nicht entfernt werden konnten. Die Gründe hierfür sind möglicherweise:
Schwere Arbeitslast
Die Arbeitslast könnte zu viele tote Tuples in einem kurzen Zeitraum verursachen, was es für Autovacuum schwierig macht, schrittzuhalten. Die toten Tupeln häufen sich nach einer Zeit im System an, was zu einer Verschlechterung der Abfrageleistung führt und zu einer Umbruchsituation führt. Ein Grund für diese Situation ist möglicherweise, dass Autovacuum-Parameter nicht richtig festgelegt sind und nicht mit einem ausgelasteten Server schrittgehalten werden kann.
Lang andauernde Transaktionen
Zeitintensive Transaktionen im System lassen nicht zu, dass tote Tuples entfernt werden, während Autovacuum ausgeführt wird. Sie sind ein Blocker für den Vakuumprozess. Durch das Entfernen der zeitintensiven Transaktionen werden tote Tupel für das Löschen frei, wenn Autovacuum ausgeführt wird.
Zeitintensive Transaktionen können mithilfe der folgenden Abfrage erkannt werden:
SELECT pid, age(backend_xid) AS age_in_xids,
now () - xact_start AS xact_age,
now () - query_start AS query_age,
state,
query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY 2 DESC
LIMIT 10;
Vorbereitete Anweisungen
Wenn es vorbereitete Anweisungen gibt, die nicht committet werden, verhindern sie, dass tote Tupel entfernt werden.
Die folgende Abfrage hilft bei der Suche nach nicht committeten vorbereiteten Anweisungen:
SELECT gid, prepared, owner, database, transaction
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;
Verwenden Sie COMMIT PREPARED oder ROLLBACK PREPARED, um diese Anweisungen zu committen oder zurückzunehmen.
Nicht verwendete Replikationsslots
Nicht verwendete Replikationsslots verhindern, dass Autovacuum tote Tupel geltend machen. Die folgende Abfrage hilft, nicht verwendete Replikationsslots zu identifizieren:
SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;
Verwenden Sie pg_drop_replication_slot() zum Löschen nicht verwendeter Replikationsslots.
Wenn die Datenbank in den Transaktions-ID-Umbruchschutz ausgeführt wird, überprüfen Sie nach allen Blockern wie zuvor erwähnt, und entfernen Sie diese manuell, um Autovacuum fortzusetzen und abzuschließen. Sie können auch die Geschwindigkeit von Autovacuum erhöhen, indem Sie autovacuum_cost_delay auf 0 festlegen und autovacuum_cost_limit auf einen Wert erhöhen, der größer als 200 ist. Änderungen an diesen Parametern werden jedoch nicht auf vorhandene Autovacuum-Worker angewendet. Starten Sie entweder die Datenbank neu oder beenden Sie vorhandene Worker manuell, um Parameteränderungen anzuwenden.
Tabellenspezifische Anforderungen
Autovacuum-Parameter können für einzelne Tabellen festgelegt werden. Es ist besonders wichtig für kleine und große Tabellen. Für eine kleine Tabelle, die nur 100 Zeilen enthält, löst Autovacuum z. B. den VACUUM-Vorgang aus, wenn 70 Zeilen geändert werden (wie zuvor berechnet). Wenn diese Tabelle häufig aktualisiert wird, werden möglicherweise Hunderte von Autovacuum-Vorgängen am Tag angezeigt. Dadurch wird verhindert, dass Autovacuum andere Tabellen pflegt, bei denen der Prozentsatz der Änderungen nicht so groß ist. Alternativ muss eine Tabelle mit einer Milliarde Zeilen 200 Millionen Zeilen ändern, um Autovacuum-Vorgänge auszulösen. Das Festlegen von Autovacuum-Parametern verhindert, dass solche Szenarien angemessen festgelegt werden.
Wenn Sie die Einstellung für Autovacuum pro Tabelle festlegen möchten, ändern Sie die Serverparameter wie in den folgenden Beispielen:
ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);
Nur-Einfügen-Workloads
In Versionen von PostgreSQL vor 13 wird Autovacuum nicht für Tabellen mit Nur-Einfügen-Workloads ausgeführt, da es keine Aktualisierungen oder Löschungen, keine toten Tupel und keinen freien Speicherplatz gibt, der beansprucht werden muss. Die automatische Analyse wird jedoch für Nur-Einfügen-Workloads ausgeführt, da neue Daten vorhanden sind. Die Nachteile davon sind:
- Die Sichtbarkeitszuordnung der Tabellen wird nicht aktualisiert, und somit beginnt die Abfrageleistung, insbesondere wenn Nur-Index-Scans vorhanden sind, im Laufe der Zeit zu leiden.
- Die Datenbank kann auf den Transaktions-ID-Umbruchschutz stoßen.
- Hinweis-Bits werden nicht festgelegt.
Lösungen
Postgres-Versionen vor 13
Mithilfe der Erweiterung pg_cron kann ein Cronauftrag eingerichtet werden, um eine regelmäßige Vacuumanalyse für die Tabelle zu planen. Die Häufigkeit des Cronauftrags hängt von der Arbeitslast ab.
Eine Schritt-für-Schritt-Anleitung für pg_cron finden Sie in Erweiterungen.
Spark 13 und höhere Versionen
Autovacuum wird auf Tabellen mit einem Nur-Einfügen-Workload ausgeführt. Zwei neue Serverparameter autovacuum_vacuum_insert_threshold und autovacuum_vacuum_insert_scale_factor helfen zu steuern, wann Autovacuum auf Nur-Einfügen-Tabellen ausgelöst werden kann.
Leitfäden zur Problembehandlung
Mithilfe der Problembehandlungsleitfäden für Funktionen, die im Portal von Azure Database for PostgreSQL – Flexibler Server vorhanden sind, ist es möglich, Überfrachtung auf Datenbankebene oder auf Ebene einzelner Schemas zu überwachen und gleichzeitig potenzielle Blockierungen für den Autovacuum-Prozess zu ermitteln. Zwei Problembehandlungsleitfäden sind verfügbar: Einer behandelt die Autovacuum-Überwachung, mit der die Überfrachtung auf Datenbankebene oder auf Ebene einzelner Schemas überwacht werden kann. Im zweiten Problembehandlungsleitfaden geht es um Autovacuum-Blockierungen und Umbrüche. Mithilfe dieses Leitfadens können Sie potenzielle Autovacuum-Blockierungen sowie Informationen dazu ermitteln, wie weit die Datenbanken auf dem Server von einem Umbruch oder einem Notfall entfernt sind. Die Problembehandlungsleitfäden enthalten Empfehlungen zum Beheben potenzieller Probleme. Informationen dazu, wie Sie die Problembehandlungsleitfäden einrichten, finden Sie unter Einrichten von Problembehandlungsleitfäden.
Zugehöriger Inhalt
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Tickets als Feedbackmechanismus für Inhalte auslaufen lassen und es durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter: https://aka.ms/ContentUserFeedback.
Einreichen und Feedback anzeigen für