Autovacuum-Optimierung in Azure Database for PostgreSQL – Flexibler Server

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

Dieser Artikel enthält eine Übersicht über das Autovacuum-Feature für Azure Database for PostgreSQL – Flexibler Server und die Problembehandlung der Funktion, die zur Überwachung der Datenbankaufblähung zur Verfügung steht, Autovacuum-Blocker. Er enthält auch Informationen darüber, wie weit die Datenbank von einer Notfallsituation oder einem Wraparound entfernt ist.

Was ist Autovacuum

Autovacuum ist ein PostgreSQL-Hintergrundprozess, der automatisch unbenutzte Tupel bereinigt und Statistiken aktualisiert. Es hilft, die Leistung der Datenbank aufrechtzuerhalten, indem es automatisch zwei wichtige Wartungsaufgaben ausführt:

• VACUUM – Gibt Speicherplatz frei, indem unbenutzte Tupel entfernt werden.
• ANALYZE – Sammelt Statistiken, die dem PostgreSQL Optimizer helfen, die besten Ausführungspfade für Abfragen zu wählen.

Um sicherzustellen, dass Autovacuum ordnungsgemäß funktioniert, sollte der Serverparameter Autovakuum immer auf ON festgelegt sein. Wenn diese Funktion aktiviert ist, entscheidet PostgreSQL automatisch, wann VACUUM oder ANALYZE für eine Tabelle ausgeführt werden soll, um sicherzustellen, dass die Datenbank effizient und optimiert bleibt.

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:

Der Umfang der Arbeit, die Autovacuum verrichtet, hängt von zwei Parametern ab:

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:

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. Für PG-Versionen >=13; ANALYZE standardmäßig ausgelöst wird, wenn 20 % der Tabelle plus 1000 Zeilen eingefügt werden.

Die genauen Formeln für jede Aktion sind:

• Autoanalyze = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold or autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold (For PG versions >= 13)
• Autovacuum = autovacuum_vacuum_scale_factor * Tuples + autovacuum_vacuum_threshold

Wenn beispielsweise eine Tabelle mit 100 Zeilen vorhanden ist. Die folgende Gleichung liefert dann die Informationen darüber, wann Analyze und Vacuum ausgelöst werden:

Für Updates/Löschungen: Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

Analyze wird ausgelöst, nachdem 60 Zeilen in einer Tabelle geändert wurden, und Vacuum wird ausgelöst, wenn 70 Zeilen in einer Tabelle geändert werden.

Für Einfügungen: Autoanalyze = 0.2 * 100 + 1000 = 1020

Analyze wird ausgelöst nach dem Einfügen von 1.020 Zeilen in eine Tabelle

Hier ist die Beschreibung der in der Formel verwendeten Parameter:

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;

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 auf -1 festgelegt ist, verwendet Autovacuum den Parameter vacuum_cost_limit, aber wenn autovacuum_vacuum_cost_limit selbst auf größer als -1 eingestellt ist, dann wird der Parameter autovacuum_vacuum_cost_limit verwendet.

Wenn Autovacuum nicht Schritt hält, werden möglicherweise die folgenden Parameter geändert:

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. Nachfolgend sind einige mögliche Gründe aufgeführt:

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 Datenbanken].

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 mehr Ressourcen verbraucht, können folgende Aktionen 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 folgende Aktionen in Betracht ziehen:

• Erhöhen Sie autovacuum_vacuum_cost_delay und verringern Sie autovacuum_vacuum_cost_limit, wenn sie höher als der Standardwert 200 festgelegt sind.
• Verringern Sie die Anzahl von 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 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 den Parameter jedoch auf Tabellenebene autovacuum_vacuum_cost_delay oder autovacuum_vacuum_cost_limit in den Parametern festlegen, werden die Worker, die auf diesen Tabellen laufen, bei dem Ausgleichsalgorithmus nicht berücksichtigt [autovacuum_cost_limit/autovacuum_max_workers].

Autovacuum-Transaktions-ID (TXID)-Rundumschutz

Wenn eine Datenbank im Transaktions-ID-Wraparound 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.

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 können. Die Gründe für dieses Problem 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 unbenutzte Tupel 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 die Blocker 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, kann es zu Hunderten von Vorgängen pro Tag kommen, die verhindern, dass Autovacuum andere Tabellen pflegt, bei denen der Prozentsatz der Änderungen nicht so hoch 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 <=13 wird Autovacuum nicht für Tabellen mit Nur-Einfügen-Workloads ausgeführt, da es keine unbenutzten 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.
• Hinweisbits sind nicht festgelegt.

Lösungen

Postgres-Versionen <= 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. Der zweite Leitfaden zur Problembehandlung ist Autovacuum-Blocker und -Wraparound, der Ihnen hilft, mögliche Autovacuum-Blocker zu identifizieren. Er gibt auch Auskunft darüber, wie weit die Datenbanken auf dem Server von einem Wraparound oder einer Notfallsituation entfernt sind. Die Problembehandlungsleitfäden enthalten Empfehlungen zum Beheben potenzieller Probleme. Wie Sie die Problembehandlung einrichten, um sie zu verwenden, folgen Sie den Anweisungen zur Einrichtung der Problembehandlung.

Azure Advisor-Empfehlungen

Mit den Empfehlungen von Azure Advisor können Sie proaktiv feststellen, ob ein Server eine hohe Bloat Ratio aufweist oder sich dem Transaktions-Wraparound-Szenario nähert. Sie können auch Warnungen für die Empfehlungen einrichten, indem Sie die Funktion Azure Advisor-Warnungen für neue Empfehlungen über das Azure-Portal erstellen verwenden.

Die Empfehlungen lauten wie folgt:

• High Bloat Ratio: Ein hohes Bloat-Verhältnis kann die Serverleistung auf verschiedene Arten beeinflussen. Ein signifikantes Problem besteht darin, dass der PostgreSQL-Moduloptimierer Schwierigkeiten haben könnte, den besten Ausführungsplan auszuwählen, was zu einer beeinträchtigten Abfrageleistung führt. Daher wird eine Empfehlung ausgelöst, wenn der Bloat-Prozentsatz auf einem Server einen bestimmten Schwellenwert erreicht, um solche Leistungsprobleme zu vermeiden.

• Transaktions-Wraparound: Dieses Szenario ist eines der schwerwiegendsten Probleme, die auf einem Server auftreten können. Sobald sich Ihr Server in diesem Zustand befindet, kann es sein, dass er keine weiteren Transaktionen mehr annimmt, so dass der Server schreibgeschützt wird. Daher wird eine Empfehlung ausgelöst, wenn wir sehen, dass der Server die Schwelle von 1 Milliarde Transaktionen überschritten hat.

Zugehöriger Inhalt

• Hohe CPU-Auslastung
• Hohe Speicherauslastung
• Serverparameter