Autovacuum-Optimierung in Azure-Datenbank für PostgreSQL

Dieser Artikel bietet eine Übersicht über die Autovacuum-Funktion für die Azure-Datenbank für PostgreSQL sowie Problemlösungsanleitungen, die zur Überwachung der Datenbankaufblähung und Autovacuum-Blocker verfügbar sind. Darüber hinaus enthält sie Informationen darüber, wie weit die Datenbank von einer Notfall- oder Krisensituation 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 in den Dateien der Datenbank frei, indem tote Tupel entfernt und dieser Speicherplatz von PostgreSQL als wiederverwendbar markiert wird. Die physische Größe der Datenbankdateien auf dem Datenträger wird nicht unbedingt reduziert. Verwenden Sie Vorgänge, die die Tabelle neu schreiben (z. B. VACUUM FULL oder pg_repack), die zusätzliche Überlegungen wie exklusive Sperren oder Wartungsfenster aufweisen, um Platz zum Betriebssystem zurückzugeben.
• ANALYZE – Sammelt Tabellen- und Indexstatistiken, die der PostgreSQL-Abfrageplaner verwendet, um effiziente Ausführungspläne auszuwählen.

Um sicherzustellen, dass autovacuum ordnungsgemäß funktioniert, legen Sie den Parameter autovacuum server auf ON. 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, um nach toten Tupeln zu suchen. Wenn keine unbenutzten Tupel gefunden werden, verwirft Autovacuum die Seite. Wenn Autovacuum tote Tupel findet, entfernt es sie. Die Kosten basieren auf den folgenden Parametern:

Die Menge der Arbeit, die autovacuum verrichtet, hängt von zwei Parametern ab.

In allen derzeit unterstützten Versionen von PostgreSQL ist der Standardwert für autovacuum_vacuum_cost_limit 200 (tatsächlich auf -1 festgelegt, was den Wert des regulären vacuum_cost_limitWerts entspricht, der standardmäßig 200 ist).

Der Standardwert für autovacuum_vacuum_cost_delay ist 2 Millisekunden in PostgreSQL-Versionen 12 und höher (in Version 11 waren es 20 Millisekunden).

Puffernutzungsgrenzwert (PostgreSQL 16+)

Ab PostgreSQL Version 16 können Sie den Parameter verwenden, um die vacuum_buffer_usage_limit Speichernutzung bei VAKUUM-, ANALYSE- und Autovacuum-Vorgängen zu steuern.

Dieser Parameter verhindert, dass VAKUUM und Autovacuum zu viele nützliche Seiten aus freigegebenen Puffern entfernen, wodurch die Gesamtleistung der Datenbank bei Wartungsvorgängen verbessert werden kann. Der Standardwert wird in der Regel basierend auf shared_buffers festgelegt und kann so konfiguriert werden, dass die Vakuumleistung mit den Anforderungen regulärer Datenbankvorgänge ausgeglichen wird.

Maximale Schwelle für autovacuum (PostgreSQL 18+)

Ab PostgreSQL Version 18 können Sie den autovacuum_vacuum_max_threshold Parameter verwenden, um eine Obergrenze für die Anzahl der Tupelaktualisierungen festzulegen oder zu löschen, die autovacuum auslösen.

Dieser Parameter ist besonders nützlich für große Tabellen, bei denen die standardmäßige skalierungsfaktorbasierte Auslösung dazu führen kann, dass Autovacuum mit der Ausführung zu lange wartet.

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

Azure Database for PostgreSQL stellt die folgenden Metriken für die Überwachung von autovacuum bereit.

Autovacuum-Metriken können verwendet werden, um die Autovacuum-Leistung für Azure Database for PostgreSQL Flexible Server zu überwachen und zu optimieren. Jede Metrik wird mit einem Intervall von 30 Minuten ausgegeben und verfügt über eine Aufbewahrungsdauer von bis zu 93 Tagen. Sie können Warnungen für bestimmte Metriken erstellen und Metrikdaten mithilfe der DatabaseName-Dimension aufteilen und filtern.

So aktivieren Sie Autovacuum-Metriken

• Autovacuum-Metriken sind standardmäßig deaktiviert.
• Legen Sie zum Aktivieren dieser Metriken den Serverparameter metrics.autovacuum_diagnostics auf ON fest.
• Dieser Parameter ist dynamisch und erfordert keinen Neustart der Instanz.

Liste der Autovacuum-Metriken

Überlegungen zur Verwendung der Autovacuum-Metriken

• Für Autovacuum-Metriken, die die DatabaseName-Dimension verwenden, gilt ein Limit von 30 Datenbanken.
• Bei der Burstable-SKU beträgt das Limit 10 Datenbanken für Metriken, die die Dimension DatabaseName verwenden.
• Das Limit für die DatabaseName-Dimension wird auf die OID-Spalte angewendet, die die Reihenfolge der Erstellung für die Datenbank widerspiegelt.

Weitere Informationen finden Sie unter Autovacuum Metrics.

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;

Anhand der folgenden Spalten können Sie ermitteln, ob Autovacuum die Tabellenaktivität aufnimmt:

Auslösen von Autovacuum

Eine Autovacuum-Aktion (ANALYSIEREN oder VAKUUM) löst aus, wenn die Anzahl der toten Tupel eine bestimmte Zahl überschreitet. Diese Zahl hängt von zwei Faktoren ab: der Gesamtanzahl der Zeilen in einer Tabelle sowie eines festen Schwellenwerts. ANALYZE wird standardmäßig ausgelöst, wenn 10 % der Tabelle plus 50 Zeilenänderungen auftreten, während VACUUM ausgelöst wird, wenn 20 % der Tabelle plus 50 Zeilenänderungen eintreten. Da die VAKUUM-Schwelle doppelt so hoch ist wie die ANALYZE-Schwelle, löst ANALYZE früher aus als VAKUUM.

Bei PostgreSQL-Versionen 13 und höher löst ANALYZE standardmäßig aus, wenn 20% der Tabelle plus 1.000 Zeileneinfügungen auftreten.

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 (Für PostgreSQL-Versionen 13 und höher)
• Autovacuum = autovacuum_vacuum_scale_factor * Tuples + autovacuum_vacuum_threshold

Wenn Sie beispielsweise eine Tabelle mit 100 Zeilen haben, werden die folgenden Formeln angezeigt, wenn die Analyse- und Vakuumaktionen ausgelöst werden:

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

ANALYSIEREN-Trigger , nachdem 60 Zeilen in einer Tabelle geändert wurden, und VAKUUM-Trigger , wenn 70 Zeilen in einer Tabelle geändert werden.

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

ANALYZE wird ausgeführt, nachdem 1.020 Zeilen in eine Tabelle eingefügt wurden.

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 der häufig auftretenden Probleme mit dem Autovacuum-Prozess.

Nicht-Schritthalten mit einem beschäftigten Server

Der Autovacuum-Prozess schätzt die Kosten für jeden E/A-Vorgang, sammelt eine Summe für jeden ausgeführten Vorgang und hält an, sobald die Obergrenze der Kosten erreicht ist. Der Prozess verwendet zwei Serverparameter: autovacuum_vacuum_cost_delay und autovacuum_vacuum_cost_limit.

Standardmäßig ist autovacuum_vacuum_cost_limit auf -1 gesetzt, was bedeutet, dass das autovacuum Kostenlimit denselben Wert wie der vacuum_cost_limit Parameter verwendet. Der Standardwert für vacuum_cost_limit ist 200. vacuum_cost_limit stellt die Kosten eines manuellen Vakuums dar.

Wenn Sie autovacuum_vacuum_cost_limit auf -1 festlegen, verwendet autovacuum den Parameter vacuum_cost_limit. Wenn Sie einen Wert festlegen autovacuum_vacuum_cost_limit , der größer als -1 ist, verwendet autovacuum den autovacuum_vacuum_cost_limit Parameter.

Wenn autovacuum nicht mehr mithalten kann, sollten Sie die folgenden Parameter ändern:

Autovacuum wird ständig ausgeführt

Wenn der Autovacuum-Dienst kontinuierlich ausgeführt wird, kann er sich auf die CPU- und I/O-Auslastung auf dem Server auswirken. Dies sind einige mögliche Ursachen:

maintenance_work_mem

Der Autovacuum-Daemon verwendet autovacuum_work_mem, das standardmäßig auf -1 eingestellt ist. Diese Standardeinstellung bedeutet, dass autovacuum_work_mem derselbe Wert wie der maintenance_work_mem Parameter verwendet wird. In diesem Artikel wird davon ausgegangen, dass autovacuum_work_mem auf -1 gesetzt ist und der Autovacuum-Daemon maintenance_work_mem verwendet.

Wenn maintenance_work_mem niedrig ist, können Sie es auf einer Azure-Datenbank für flexible Serverinstanz von PostgreSQL auf bis zu 2 GB erhöhen. 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].

Wenn es mehr Datenbanken in einem Cluster gibt, erhöhen Sie autovacuum_naptime. Machen Sie gleichzeitig den Autovacuum-Prozess aggressiver, indem Sie die Parameter autovacuum_cost_limit erhöhen und die Parameter autovacuum_cost_delay verringern. Sie können auch von der Standardeinstellung 3 auf 4 oder 5 erhöhen autovacuum_max_workers .

Arbeitsspeicherfehler

Übermäßig aggressive maintenance_work_mem Werte können periodisch Speicherfehler im System verursachen. Verstehen Sie den verfügbaren RAM auf dem Server, bevor Sie den maintenance_work_mem Parameter ändern.

Autovacuum ist zu störend

Wenn autovacuum zu viele Ressourcen verbraucht, probieren Sie die folgenden Aktionen aus:

Autovacuum-Parameter

Wertet die Parameter autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limitund autovacuum_max_workers. 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 autovacuum_vacuum_cost_delay und reduzieren autovacuum_vacuum_cost_limit, wenn Sie diese Werte höher als den Standardwert von 200 festlegen.
• Verringern Sie die Anzahl von autovacuum_max_workers, wenn Sie diese auf einen Wert höher als den Standardwert von 3 festgelegt haben.

Zu viele Autovacuum-Worker

Das Erhöhen der Anzahl der Autovacuum-Worker erhöht nicht die Geschwindigkeit von Vacuum. Verwenden Sie keine hohe Anzahl von Autovacuum-Mitarbeitern.

Das Erhöhen der Anzahl der Autovacuum-Mitarbeiter führt zu mehr Arbeitsspeicherverbrauch. Je nach Wert maintenance_work_memkann dies zu Leistungsbeeinträchtigungen führen.

Jeder Autovacuum-Worker-Prozess erhält nur (1/autovacuum_max_workers) von insgesamt autovacuum_cost_limit, daher könnte eine hohe Anzahl von Workern dazu führen, dass jeder einzelne langsamer wird.

Wenn Sie die Anzahl der Mitarbeiter erhöhen, erhöhen Sie autovacuum_vacuum_cost_limit und/oder verringern Sie autovacuum_vacuum_cost_delay, um den Vakuumprozess schneller zu gestalten.

Wenn Sie 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 auf einen Transaktions-ID-Umbruchschutz trifft, wird eine Fehlermeldung wie folgende angezeigt:

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 nicht geleert wird oder wenn es viele unbenutzte Tupel gibt, die von Autovacuum nicht entfernt werden.

Mögliche Gründe für dieses Problem sind die folgenden Gründe:

Schwere Arbeitslast

Eine hohe Arbeitsauslastung verursacht in kurzer Zeit zu viele unbenutzte Tupel, sodass Autovacuum nur schwer mithalten kann. 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

Jede lang andauernde Transaktion im System verhindert, dass Autovacuum unbenutzte Tupel entfernt. 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 Autovacuum unbenutzte Tupel entfernt. 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 übernehmen oder zurückzusetzen.

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

Sie können autovacuum-Parameter für einzelne Tabellen festlegen. Diese Einstellungen sind besonders wichtig für kleine und große Tabellen. Bei einer kleinen Tabelle, die nur 100 Zeilen enthält, löst autovacuum beispielsweise den VAKUUM-Vorgang aus, wenn sich 70 Zeilen ändern (wie zuvor berechnet). Wenn Sie diese Tabelle häufig aktualisieren, werden möglicherweise Hunderte von Autovacuum-Vorgängen am Tag angezeigt. Diese Vorgänge verhindern, dass autovacuum andere Tabellen beibehalten, bei denen der Prozentsatz der Änderungen nicht so wichtig 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 Einstellungen für die automatische Leerstelle für jede Tabelle festlegen möchten, ändern Sie die Serverparameter wie in den folgenden Beispielen gezeigt:

    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);
    -- For PostgreSQL 16 and later:
    ALTER TABLE <table name> SET (vacuum_buffer_usage_limit = 'xx MB');

Workloads nur zum Einfügen

In PostgreSQL Version 13 und niedriger 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 dieses Verhaltens 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

PostgreSQL Versionen 13 und früher

Mithilfe der pg_cron Erweiterung können Sie einen Cron-Job einrichten, um eine regelmäßige Vakuumanalyse auf der Tabelle zu planen. Die Häufigkeit des Cronauftrags hängt von der Arbeitslast ab.

Anleitungen finden Sie in speziellen Überlegungen zur Verwendung von pg_cron in der Azure-Datenbank für PostgreSQL.

PostgreSQL 13 und höhere Versionen

Autovacuum wird auf Tabellen mit einem Workload nur zum EInfügen ausgeführt. Zwei Serverparameter, autovacuum_vacuum_insert_threshold und autovacuum_vacuum_insert_scale_factor, helfen dabei zu steuern, wann Autovacuum bei Insert-only-Tabellen ausgelöst werden kann.

Leitfäden zur Problembehandlung

Der flexible Azure Database for PostgreSQL-Server bietet Anleitungen zur Problembehandlung im Portal, die Ihnen dabei helfen, Aufblähungen auf Datenbank- oder einzelner Schemaebene zu überwachen und potenzielle Blocker für den Autovacuum-Prozess zu identifizieren.

Es stehen zwei Anleitungen zur Problembehandlung zur Verfügung:

• Autovacuum monitoring – Verwenden Sie diesen Leitfaden, um Bloat auf Datenbank- oder einzelner Schemaebene zu überwachen.
• Autovacuum-Blockierungen und Umbrüche: Dieser Leitfaden hilft Ihnen dabei, potenzielle Autovacuum-Blockierungen zu identifizieren, und er liefert Informationen dazu, wie weit die Datenbanken auf dem Server von einem Umbruch oder einer Notfallsituation entfernt sind.

Die Problembehandlungsleitfäden enthalten Empfehlungen zum Beheben potenzieller Probleme. Informationen zum Einrichten und Verwenden der Anleitungen zur Problembehandlung finden Sie in den Anleitungen zur Problembehandlung.

Beenden des Autovacuum-Prozesses: pg_signal_autovacuum_worker-Rolle

Autovacuum ist ein wichtiger Hintergrundprozess, da es bei einer effizienten Speicher- und Leistungswartung in der Datenbank hilft. Im normalen Autovacuum-Prozess wird er nach deadlock_timeout abgebrochen. Wenn ein Benutzer eine DDL-Anweisung für eine Tabelle ausführt, muss der Benutzer möglicherweise bis zum deadlock_timeout Intervall warten. Autovacuum lässt das Ausführen von Lese- oder Schreibvorgängen in der Tabelle, die von verschiedenen Verbindungsanforderungen angefordert wird, nicht zu, wodurch die Latenz in der Transaktion hinzugefügt wird.

Wir haben eine neue Rolle pg_signal_autovacuum_worker von PostgreSQL eingeführt, die es Nichtsuperbenutzermitgliedern ermöglicht, eine laufende Autovacuum-Aufgabe zu beenden. Die neue Rolle hilft Benutzern, sicheren und kontrollierten Zugriff auf den Autovacuum-Prozess zu erhalten. Nicht-Superuser können den Autovacuum-Prozess abbrechen, sobald sie die pg_signal_autovacuum_worker Rolle mithilfe des pg_terminate_backend Befehls erhalten haben. Die Rolle pg_signal_autovacuum_worker ist in Azure Database for PostgreSQL in Den Versionen 15 und höher von PostgreSQL verfügbar.

Empfohlener Ansatz für wiederkehrende Autovacuum-Worker

In seltenen Szenarien, wie dem Anti-Umbruch-Autovacuum, werden Worker möglicherweise unmittelbar nach der Beendigung neu gestartet, weil sie von entscheidender Bedeutung dafür sind, zu verhindern, dass die Transaktions-IDs aufgebraucht werden. Führen Sie die folgenden Schritte aus, um wiederholte Konflikte zu minimieren:

• Ordnen Sie den DDL-Vorgang vor der Beendigung in die Warteschlange ein:

  • Session 1: Vorbereiten und ausführen der DDL-Anweisung.

  • Sitzung 2: Beenden des Autovacuum-Prozesses.

    Von Bedeutung

    Diese beiden Schritte müssen back-to-back ausgeführt werden. Wenn die DDL-Anweisung zu lange blockiert bleibt, kann sie Sperren halten und andere DML-Vorgänge auf dem Server blockieren.

• Beenden Sie autovacuum, und führen Sie DDL aus: Wenn die DDL sofort ausgeführt werden muss:

  • Beenden Sie den Autovacuum-Prozess mithilfe von pg_terminate_backend().
  • Führen Sie die DDL-Anweisung direkt nach der Beendigung aus.

Schritte zur Vermeidung wiederholter Konflikte:

1. Rolle für Benutzer gewähren

   GRANT pg_signal_autovacuum_worker TO app_user;
   

   1. Identifizieren der Autovacuum-Prozess-ID

   SELECT pid, query FROM pg_stat_activity WHERE query LIKE '%autovacuum%' and pid!=pg_backend_pid();
   

2. Autovacuum beenden

   SELECT pg_terminate_backend(<pid>);
   

3. DDL-Anweisung sofort ausführen

   ALTER TABLE my_table ADD COLUMN new_col TEXT;
   

Azure Advisor-Empfehlungen

Azure Advisor-Empfehlungen identifizieren proaktiv, ob ein Server ein hohes Bloat-Verhältnis aufweist oder ob sich der Server einem Transaktions-Überlauf-Szenario nähert. Sie können auch Azure Advisor-Warnungen für die Empfehlungen erstellen.

Die Empfehlungen lauten wie folgt:

• Hohes Bloat-Verhältnis: Ein hohes Bloat-Verhältnis kann sich auf verschiedene Arten auf die Serverleistung auswirken. 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.

• Transaktionsumbruch: Dieses Szenario ist eines der schwerwiegendsten Probleme, die auf einem Server auftreten können. Sobald sich der Server in diesem Zustand befindet, kann er keine weiteren Transaktionen akzeptieren, wodurch der Server nur-lesbar wird. Daher wird eine Empfehlung ausgelöst, wenn der Server den Schwellenwert von 1 Milliarden Transaktionen überschreitet.

Verwandte Inhalte

• Vollständiges Vakuum unter Verwendung von pg_repack in der Azure-Datenbank für PostgreSQL
• Problembehandlung bei hoher CPU-Auslastung in Azure-Datenbank für PostgreSQL
• Problembehandlung bei hoher Speicherauslastung in Azure Database for PostgreSQL
• Problembehandlung bei hoher IOPS-Auslastung in Azure-Datenbank für PostgreSQL
• Problembehandlung und Identifizierung langsam ausgeführter Abfragen in Azure Database for PostgreSQL
• Serverparameter in Azure-Datenbank für PostgreSQL