Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.
Arbeiten mit der PostgreSQL-Selbstbereinigung in Amazon RDS for PostgreSQL
Wir empfehlen ausdrücklich die Selbstbereinigungsfunktion zu verwenden, um die Integrität Ihrer PostgreSQL-DB-Instance zu wahren. Die Selbstbereinigung automatisiert den Start der Befehle VACUUM und ANALYZE. Sie prüft auf Tabellen mit einer großen Zahl von eingefügten, aktualisierten oder gelöschten Tupeln. Nach dieser Prüfung wird Speicher durch Entfernen von überflüssigen Daten oder Tupeln aus der PostgreSQL-Datenbank freigegeben.
Standardmäßig ist die Selbstbereinigungsfunktion auf den DB-Instances von Amazon RDS for PostgreSQL aktiviert, die Sie mit einer der standardmäßigen PostgreSQL-DB-Parametergruppen erstellen. Dazu zählen default.postgres10, default.postgres11, usw. Alle standardmäßigen PostgreSQL-DB-Parametergruppen haben den Parameter rds.adaptive_autovacuum, der auf 1 festgelegt ist und die Funktion aktiviert. Andere Konfigurationsparameter, die mit der Selbstbereinigungsfunktion verknüpft sind, sind ebenfalls standardmäßig festgelegt. Da diese Standardwerte generisch sind, können Sie davon profitieren, einige der mit der Selbstbereinigungsfunktion verbundenen Parameter für Ihre spezifische Workload zu optimieren.
Im Folgenden finden Sie weitere Informationen zur Selbstbereinigung und wie Sie einige ihrer Parameter auf Ihrer DB-Instance von RDS for PostgreSQL optimieren können. Informationen auf hoher Ebene finden Sie unter Bewährte Methoden für die Arbeit mit PostgreSQL.
Themen
- Zuweisen von Arbeitsspeicher für die Selbstbereinigung
- Verringern der Wahrscheinlichkeit von Transaktions-ID-Wraparounds
- Ermittlung, ob die Tabellen in Ihrer Datenbank bereinigt werden müssen
- Ermittlung, für welche Tabellen derzeit eine Selbstbereinigung nötig ist
- Ermittlung, ob die Selbstbereinigung derzeit ausgeführt wird und wie lange sie dauert
- Ausführen einer manuellen Bereinigungseinfrierung
- Neuindizierung einer Tabelle während der Ausführung einer Selbstbereinigung
- Verwalten der automatischen Bereinigung mit großen Indizes
- Weitere Parameter, die sich auf die Selbstbereinigung auswirken
- Festlegen von Selbstbereinigungsparametern auf Tabellenebene
- Protokollieren von Selbstbereinigung- und Bereinigungsaktivitäten
Zuweisen von Arbeitsspeicher für die Selbstbereinigung
Einer der wichtigsten Parameter, der sich auf die Leistung der Selbstbereinigung auswirkt, ist der Parameter maintenance_work_memmaintenance_work_mem zu niedrig einstellen, muss die Tabelle während des Bereinigungsvorgangs möglicherweise mehrmals gescannt werden, um die Bereinigung auszuführen. Solche wiederholten Scans können negative Auswirkungen auf die Leistung haben.
Sie müssen bei der Berechnung des Werts für den Parameter maintenance_work_mem zwei Dinge berücksichtigen:
Die Standardeinheit für diesen Parameter ist Kilobyte (KB).
-
Der Parameter
maintenance_work_memfunktioniert in Verbindung mit dem Parameterautovacuum_max_workers. Wenn Sie viele kleine Tabellen haben, müssen Sie mehr autovacuum_max_workersund wenigermaintenance_work_memzuteilen. Wenn Sie große Tabellen haben (beispielsweise größer als 100 GB), sollten Sie mehr Arbeitsspeicher und weniger Worker-Prozesse zuteilen. Sie müssen genügend Arbeitsspeicher zuweisen, um den Vorgang für Ihre größte Tabelle erfolgreich ausführen zu können. Jederautovacuum_max_workers-Parameter kann den von Ihnen zugeteilten Arbeitsspeicher nutzen. Daher muss die Kombination von Worker-Prozessen und Arbeitsspeicher dem gesamten Arbeitsspeicher entsprechen, den Sie zuteilen möchten.
Allgemein ausgedrückt, müssen Sie für große Hosts den Parameter maintenance_work_mem auf einen Wert zwischen einem und zwei Gigabyte (zwischen 1.048.576 und 2.097.152 KB) festlegen. Im Fall extrem großer Hosts sollten Sie den Parameter auf einen Wert zwischen zwei und vier Gigabyte (zwischen 2.097.152 und 4.194.304 KB) festlegen. Der Wert, den Sie für diesen Parameter festlegen, hängt von der Workload ab. Amazon RDS hat den Standardwert für diesen Parameter auf Kilobyte aktualisiert. Er wird wie folgt berechnet.
GREATEST({DBInstanceClassMemory/63963136*1024},65536).
Verringern der Wahrscheinlichkeit von Transaktions-ID-Wraparounds
In einigen Fällen sind Parametergruppen-Einstellungen, die sich auf die Selbstbereinigung beziehen, möglicherweise nicht aggressiv genug, um Transaktions-ID-Wraparounds zu verhindern. Um dieses Problem zu beheben, stellt RDS for PostgreSQL eine Methode bereit, mit der die Selbstbereinigungsparameter automatisch angepasst werden. Adaptive Optimierung der Selbstbereinigungsparameter ist eine Funktion für RDS for PostgreSQL. In der PostgreSQL-Dokumentation finden Sie eine sehr detaillierte Beschreibung von Transaktions-ID-Wraparounds
Die adaptive Optimierung von Selbstbereinigungsparametern ist für Instances von RDS for PostgreSQL, bei denen der Parameter rds.adaptive_autovacuum auf ON gesetzt ist, automatisch aktiviert. Wir raten dringend dazu, diese Option aktiviert zu lassen. Um die adaptive Optimierung der Selbstbereinigungsparameter zu deaktivieren, stellen Sie den Parameter rds.adaptive_autovacuum jedoch auf 0 oder OFF ein.
Transaktions-ID-Wraparounds können selbst dann noch auftreten, wenn Amazon RDS die Selbstbereinigungsparameter optimiert. Wir empfehlen Ihnen, einen Amazon- CloudWatch Alarm für Transaktions-ID-Wraparounds zu implementieren. Weitere Informationen finden Sie im Beitrag Implement an Early Warning System for Transaction ID Wraparound in RDS for PostgreSQL
Wenn die adaptive Optimierung der Selbstbereinigungsparameter aktiviert ist, beginnt Amazon RDS mit der Anpassung der Selbstbereinigungsparameter, wenn die CloudWatch Metrik den Wert des autovacuum_freeze_max_age Parameters oder 500.000.000 MaximumUsedTransactionIDs erreicht, je nachdem, welcher Wert größer ist.
Amazon RDS fährt mit dem Anpassen der Parameter für die Selbstbereinigung fort, wenn eine Tabelle weiterhin zu Transaktions-ID-Wraparounds tendiert. Jede dieser Anpassungen stellt weitere Ressourcen für die Selbstbereinigung bereit, um Wraparounds zu vermeiden. Amazon RDS aktualisiert die folgenden Parameter, die sich auf die Selbstbereinigung beziehen:
RDS ändert diese Parameter nur, wenn der neue Wert die Selbstbereinigung aggressiver macht. Die Parameter werden im Arbeitsspeicher auf der DB-Instance geändert. Die Werte in der Parametergruppe werden nicht geändert. Um die aktuellen Arbeitsspeichereinstellungen anzuzeigen, verwenden Sie den PostgreSQL-SQL-Befehl SHOW
Wenn Amazon RDS einen dieser Selbstbereinigungsparameter ändert, wird ein Ereignis für die betroffene DB-Instance erzeugt. Dieses Ereignis ist auf der AWS Management Console und über die Amazon-RDS-API sichtbar. Nachdem die MaximumUsedTransactionIDs CloudWatch Metrik unter den Schwellenwert zurückkehrt, setzt Amazon RDS die sich auf die Selbstbereinigung beziehenden Parameter im Speicher auf die in der Parametergruppe angegebenen Werte zurück. Es generiert dann ein anderes Ereignis, das dieser Änderung entspricht.
Ermittlung, ob die Tabellen in Ihrer Datenbank bereinigt werden müssen
Sie können die folgende Abfrage verwenden, um die Anzahl der nicht bereinigten Transaktionen in einer Datenbank anzuzeigen. Die Spalte datfrozenxid einer pg_database-Zeile der Datenbank ist eine Untergrenze für die normalen Transaktions-IDs, die in dieser Datenbank erscheinen. Diese Spalte ist der Mindestwert der relfrozenxid-Werte pro Tabelle in der Datenbank.
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;
Beispielsweise könnten die Ergebnisse der Ausführung der oben gezeigten Abfrage wie folgt aussehen.
datname | age mydb | 1771757888 template0 | 1721757888 template1 | 1721757888 rdsadmin | 1694008527 postgres | 1693881061 (5 rows)
Wenn das Alter einer Datenbank 2 Milliarden Transaktions-IDs erreicht, treten Transaktions-ID (XID)-Wraparounds auf und die Datenbank wird als schreibgeschützt festgelegt. Sie können diese Abfrage verwenden, um eine Metrik zu erstellen und einige Male am Tag auszuführen. Standardmäßig ist die Selbstbereinigung so festgelegt, dass das Alter der Transaktionen 200 000 000 nicht überschreitet 200,000,000 (autovacuum_freeze_max_age
Eine Überwachungsstrategie kann beispielsweise wie folgt aussehen:
Stellen Sie den
autovacuum_freeze_max_age-Wert auf 200 Millionen Transaktionen ein.Wenn eine Tabelle 500 Millionen nicht bereinigter Transaktionen erreicht, wird ein Alarm mit niedrigem Schweregrad ausgelöst. Dies ist kein unangemessener Wert. Er könnte jedoch zu erkennen geben, dass die Selbstbereinigung nicht Schritt hält.
Wenn eine Tabelle 1 Milliarde nicht bereinigter Transaktionen aufweist, sollte dies als ein Alarm behandelt werden, für den Maßnahmen zu ergreifen sind. Im Allgemeinen sollte aus Leistungsgründen das Alter möglichst nahe zu
autovacuum_freeze_max_ageliegen. Wir empfehlen, dass Sie eine Untersuchung unter Beachtung der folgenden Empfehlungen durchführen.Wenn eine Tabelle 1,5 Milliarden nicht bereinigter Transaktionen erreicht, wird ein Alarm mit hohem Schweregrad ausgelöst. Abhängig davon, wie schnell Ihre Datenbank Transaktions-IDs verwendet, kann dieser Alarm zu erkennen geben, dass die Zeit für eine Selbstbereinigung des Systems abläuft. In diesem Fall empfehlen wir, eine unmittelbare Lösung in Betracht zu ziehen.
Wenn eine Tabelle diese Schwellenwerte konstant überschreitet, ändern Sie die Selbstbereinigungsparameter weiter. Standardmäßig ist die manuelle Verwendung von VACUUM (für den kostenbasierte Verzögerungen deaktiviert sind) aggressiver als die Standardselbstbereinigung. Der Befehl hat jedoch insgesamt auch größere Auswirkungen auf das System.
Wir empfehlen Folgendes:
Aktivieren Sie einen Überwachungsmechanismus, damit Sie über das Alter der ältesten Transaktionen informiert sind.
Informationen zum Erstellen eines Prozesses, der Sie über Transaktions-ID-Wraparounds benachrichtigt, finden Sie im AWS-Datenbank-Blogbeitrag Implementieren eines Frühwarnsystem für den Transaktions-ID-Wraparound in Amazon RDS for PostgreSQL
. Führen Sie für häufiger verwendete Tabellen zusätzlich zur Selbstbereinigung während Wartungsfenstern regelmäßig eine manuelle Bereinigungseinfrierung aus. Informationen zur Ausführung manueller Bereinigungseinfrierungen finden Sie unter Ausführen einer manuellen Bereinigungseinfrierung.
Ermittlung, für welche Tabellen derzeit eine Selbstbereinigung nötig ist
Häufig benötigen eine oder zwei Tabellen eine Bereinigung. Tabellen, deren relfrozenxid-Wert größer als die Anzahl von Transaktionen in autovacuum_freeze_max_age ist, sind stets Ziel der Selbstbereinigung. Wenn andernfalls die Anzahl der Tupeln, die seit dem letzten VACUUM-Befehl veraltet sind, den Bereinigungsschwellenwert überschreitet, wird die Tabelle bereinigt.
Der Selbstbereinigungsschwellenwert
Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples
wobei vacuum base thresholdautovacuum_vacuum_threshold, vacuum scale factor autovacuum_vacuum_scale_factorund number of tuples istpg_class.reltuples.
Führen Sie während der Herstellung der Verbindung mit Ihrer Datenbank die folgende Abfrage aus, um eine Liste der Tabellen anzuzeigen, für die Selbstbereinigungsfunktion eine Bereinigung als notwendig betrachtet.
WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold'), vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'), fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'), sto AS (select opt_oid, split_part(setting, '=', 1) as param, split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt) SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation, pg_size_pretty(pg_table_size(c.oid)) as table_size, age(relfrozenxid) as xid_age, coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age, (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM pg_class c join pg_namespace ns on ns.oid = c.relnamespace join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1) left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid WHERE c.relkind = 'r' and nspname <> 'pg_catalog' AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup) ORDER BY age(relfrozenxid) DESC LIMIT 50;
Ermittlung, ob die Selbstbereinigung derzeit ausgeführt wird und wie lange sie dauert
Wenn Sie eine Tabelle manuell bereinigen müssen, müssen Sie ermitteln, ob zurzeit eine Selbstbereinigung ausgeführt wird. Wenn dies der Fall ist, müssen Sie die Parameter anpassen, damit sie effizienter ausgeführt wird, oder die Selbstbereinigung temporär beenden, damit Sie den Befehl VACUUM manuell ausführen können.
Verwenden Sie die folgende Abfrage, um zu ermitteln, ob die Selbstbereinigung ausgeführt wird, wie lange diese bereits dauert und ob diese auf eine andere Sitzung wartet.
SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;
Nach dem Ausführen der Abfrage wird Ihnen eine Ausgabe ähnlich der folgenden angezeigt.
datname | usename | pid | state | wait_event | xact_runtime | query --------+----------+-------+--------+------------+-------------------------+-------------------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | active | | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | active | | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | active | | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | active | | 00:00:00 | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+ | | | | | | FROM pg_stat_activity + | | | | | | WHERE query like '%VACUUM%' + | | | | | | ORDER BY xact_start; +
Verschiedene Probleme können dazu führen, dass eine Selbstbereinigungssitzung über eine lange Zeit (mehrere Tage) ausgeführt wird. Das häufigste Problem besteht jedoch darin, dass der Wert Ihres Parameters maintenance_work_mem
Es wird empfohlen, dass Sie die folgende Formel verwenden, um den Wert des Parameters maintenance_work_mem festzulegen.
GREATEST({DBInstanceClassMemory/63963136*1024},65536)
Selbstbereinigungssitzungen, die nur kurze Zeit ausgeführt werden, können ebenfalls auf Probleme hinweisen:
Es kann bedeuten, dass es für Ihren Workload nicht genügend
autovacuum_max_workersgibt. In diesem Fall müssen Sie die Anzahl der Worker angeben.Es kann bedeuten, dass es eine Indexbeschädigung gibt (die Selbstbereinigung stürzt ab und startet bei derselben Relation neu, es gibt jedoch keine Fortschritte). Führen Sie in diesem Fall ein manuelles
vacuum freeze verboseaus, um die genaue Ursache zu sehen.table
Ausführen einer manuellen Bereinigungseinfrierung
Möglicherweise möchten Sie eine manuelle Bereinigung für eine Tabelle ausführen, für die bereits ein Bereinigungsvorgang ausgeführt wird. Dies ist nützlich, wenn Sie eine Tabelle mit einem Alter identifiziert haben, das 2 Milliarden Transaktionen (oder einen Wert oberhalb eines von Ihnen beobachteten Schwellenwerts) erreicht.
Die folgenden Schritte sind Richtlinien mit mehreren Variationen des Prozesses. Angenommen, Sie stellen während der Überprüfung fest, dass der Wert für den Parameter maintenance_work_memmaintenance_work_mem ändern. Sie müssen jedoch auch sofortige Maßnahmen einleiten und die betreffende Tabelle bereinigen. Das folgende Verfahren zeigt, was Sie in dieser Situation unternehmen.
So führen Sie manuell eine Bereinigungseinfrierung aus
Öffnen Sie zwei Sitzungen für die Datenbank, die Tabelle enthält, die Sie bereinigen möchten. Verwenden Sie für die zweite Sitzung „screen“ oder ein anderes Hilfsprogramm, das die Sitzung beibehält, wenn die Verbindung verloren geht.
Rufen Sie in der ersten Sitzung die Prozess-ID (PID) der Selbstbereinigungssitzung ab, die für die Tabelle ausgeführt wird.
Führen Sie die folgende Abfrage aus, um die PID der Selbstbereinigungssitzung abzurufen.
SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;Berechnen Sie in der zweiten Sitzung die Menge des Arbeitsspeichers, den Sie für diese Operation benötigen. In diesem Beispiel ermitteln Sie, dass für diese Operation bis zu 2 GB Arbeitsspeicher verwendet werden können. Daher wird für die aktuelle Sitzung
maintenance_work_memauf 2 GB festgelegt. SET maintenance_work_mem='2 GB';SETGeben Sie in der zweiten Sitzung einen
vacuum freeze verbose-Befehl für die Tabelle aus. Die Verbose-Einstellung ist nützlich, da Sie Aktivität sehen können, auch wenn in PostgreSQL hierfür zurzeit kein Fortschrittsbericht verfügbar ist.\timing onTiming is on.vacuum freeze verbose pgbench_branches;INFO: vacuuming "public.pgbench_branches" INFO: index "pgbench_branches_pkey" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pgbench_branches_test_index" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pgbench_branches": found 0 removable, 50 nonremovable row versions in 43 out of 43 pages DETAIL: 0 dead row versions cannot be removed yet. There were 9347 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Time: 2.765 ms-
Wenn die Selbstbereinigung die Bereinigungssitzung blockiert hat, wird Ihnen in der ersten Sitzung in
pg_stat_activityangezeigt, dass das Warten für Ihre Bereinigungssitzung "T" ist. In diesem Fall müssen Sie den Selbstbereinigungsvorgang wie folgt beenden.SELECT pg_terminate_backend('the_pid');An diesem Punkt beginnt Ihre Sitzung. Es ist wichtig, zu beachten, dass die Selbstbereinigung sofort neu gestartet wird, da diese Tabelle in der Liste der Aufgaben wahrscheinlich ganz oben steht.
-
Initiieren Sie Ihren
vacuum freeze verbose-Befehl in der zweiten Sitzung und beenden Sie dann den Selbstbereinigungsvorgang in der ersten Sitzung.
Neuindizierung einer Tabelle während der Ausführung einer Selbstbereinigung
Wenn ein Index beschädigt wurde, verarbeitet die Selbstbereinigung die Tabelle weiter und schlägt fehl. Wenn Sie in dieser Situation eine manuelle Bereinigung versuchen, werden Sie eine Fehlermeldung ähnlich der folgenden erhalten.
postgres=>vacuum freeze pgbench_branches;ERROR: index "pgbench_branches_test_index" contains unexpected zero page at block 30521 HINT: Please REINDEX it.
Wenn der Index beschädigt ist und eine Selbstbereinigung für die Tabelle versucht wird, konkurrieren Sie mit einer Selbstbereinigungssitzung, die bereits ausgeführt wird. Wenn Sie einen REINDEX
So führen Sie eine Neuindizierung für eine Tabelle aus, wenn eine Selbstbereinigung für die Tabelle ausgeführt wird
Öffnen Sie zwei Sitzungen für die Datenbank, die Tabelle enthält, die Sie bereinigen möchten. Verwenden Sie für die zweite Sitzung „screen“ oder ein anderes Hilfsprogramm, das die Sitzung beibehält, wenn die Verbindung verloren geht.
Rufen Sie in der ersten Sitzung die PID der Selbstbereinigungssitzung ab, die für die Tabelle ausgeführt wird.
Führen Sie die folgende Abfrage aus, um die PID der Selbstbereinigungssitzung abzurufen.
SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY xact_start;Führen Sie in der zweiten Sitzung den Neuindizierungsbefehl aus.
\timing onTiming is on.reindex index pgbench_branches_test_index;REINDEX Time: 9.966 msWenn die Selbstbereinigung den Prozess blockiert hat, wird Ihnen in der ersten Sitzung in
pg_stat_activityangezeigt, dass das Warten für Ihre Bereinigungssitzung "T" ist. In diesem Fall beenden Sie den Selbstbereinigungsprozess.SELECT pg_terminate_backend('the_pid');An diesem Punkt beginnt Ihre Sitzung. Es ist wichtig, zu beachten, dass die Selbstbereinigung sofort neu gestartet wird, da diese Tabelle in der Liste der Aufgaben wahrscheinlich ganz oben steht.
Initiieren Sie Ihren Befehl in der zweiten Sitzung und beenden Sie dann den Selbstbereinigungsvorgang in der ersten Sitzung.
Verwalten der automatischen Bereinigung mit großen Indizes
Im Rahmen ihrer Funktion führt die automatische Bereinigung mehrere Bereinigungsphasen
Überprüfen Sie für diesen Vorgang zunächst die Gesamtindexgröße. Stellen Sie dann fest, ob es möglicherweise unbenutzte Indizes gibt, die entfernt werden können, wie in den folgenden Beispielen dargestellt.
So überprüfen Sie die Größe der Tabelle und ihrer Indizes
postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts'));pg_size_pretty 6404 MB (1 row)
postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts'));pg_size_pretty 11 GB (1 row)
In diesem Beispiel ist die Größe der Indizes größer als die Tabelle. Dieser Unterschied kann zu Leistungsproblemen führen, da die Indizes überlastet oder ungenutzt sind, was sich sowohl auf die automatische Bereinigung als auch auf Insert-Operationen auswirkt.
So prüfen Sie, ob ungenutzte Indizes vorhanden sind
Mithilfe der Ansicht pg_stat_user_indexesidx_scan verwendet wird. Im folgenden Beispiel haben die ungenutzten Indizes den idx_scan-Wert 0.
postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+------------------+-----------------------+----------+--------------+--------------- 16433 | 16454 | public | pgbench_accounts | index_f | 6 | 6 | 0 16433 | 16450 | public | pgbench_accounts | index_b | 3 | 199999 | 0 16433 | 16447 | public | pgbench_accounts | pgbench_accounts_pkey | 0 | 0 | 0 16433 | 16452 | public | pgbench_accounts | index_d | 0 | 0 | 0 16433 | 16453 | public | pgbench_accounts | index_e | 0 | 0 | 0 16433 | 16451 | public | pgbench_accounts | index_c | 0 | 0 | 0 16433 | 16449 | public | pgbench_accounts | index_a | 0 | 0 | 0 (7 rows)
postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;schemaname | relname | indexrelname | idx_scan ------------+------------------+-----------------------+---------- public | pgbench_accounts | index_f | 6 public | pgbench_accounts | index_b | 3 public | pgbench_accounts | pgbench_accounts_pkey | 0 public | pgbench_accounts | index_d | 0 public | pgbench_accounts | index_e | 0 public | pgbench_accounts | index_c | 0 public | pgbench_accounts | index_a | 0 (7 rows)
Anmerkung
Diese Statistiken sind ab dem Zeitpunkt, an dem die Statistiken zurückgesetzt werden, inkrementell. Angenommen, Sie haben einen Index, der nur am Ende eines Geschäftsquartals oder nur für einen bestimmten Bericht verwendet wird. Es ist möglich, dass dieser Index seit dem Zurücksetzen der Statistiken nicht mehr verwendet wurde. Weitere Informationen finden Sie unter Statistikfunktionen
Um zu überprüfen, wann die Statistiken für eine Datenbank zuletzt zurückgesetzt wurden, verwenden Sie pg_stat_database
postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres';datname | stats_reset ----------+------------------------------- postgres | 2022-11-17 08:58:11.427224+00 (1 row)
Möglichst schnelles Bereinigen einer Tabelle
RDS für PostgreSQL 12 und höher
Wenn Sie zu viele Indizes in einer großen Tabelle haben, nähert sich Ihre DB-Instance möglicherweise dem Transaktions-ID-Wraparound (XID), also dem Zeitpunkt, an dem der XID-Zähler auf Null zurückgeht. Wenn diese Option nicht aktiviert ist, kann diese Situation zu Datenverlust führen. Sie können die Tabelle jedoch schnell bereinigen, ohne die Indizes zu bereinigen. In RDS für PostgreSQL 12 und höher können Sie VACUUM mit der Klausel INDEX_CLEANUP
postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts;INFO: vacuuming "public.pgbench_accounts" INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
Wenn eine automatische Bereinigungssitzung bereits läuft, müssen Sie sie beenden, um mit dem manuellen VACUUM-Vorgang zu beginnen. Informationen zur Ausführung manueller Bereinigungseinfrierungen finden Sie unter Ausführen einer manuellen Bereinigungseinfrierung.
Anmerkung
Wenn Sie die Indexbereinigung regelmäßig überspringen, kann dies zu einer Überlastung der des Indizes führen, was sich auf die gesamte Scanleistung auswirkt. Verwenden Sie das vorherige Verfahren am besten nur, um einen Transaktions-ID-Wraparound zu verhindern.
RDS für PostgreSQL 11 und niedriger
In RDS für PostgreSQL 11 und niedrigeren Versionen besteht die einzige Möglichkeit, den Bereinigungsvorgang schneller abzuschließen, darin, die Anzahl der Indizes in einer Tabelle zu reduzieren. Das Löschen eines Index kann sich auf Abfragepläne auswirken. Wir empfehlen, zuerst unbenutzte Indizes zu löschen und dann die Indizes löschen, wenn der XID-Wraparound kurz bevorsteht. Nach Abschluss des Bereinigungsvorgangs können Sie diese Indizes neu erstellen.
Weitere Parameter, die sich auf die Selbstbereinigung auswirken
Die folgende Abfrage zeigt die Werte einiger Parameter an, die sich direkt auf die Selbstbereinigung und ihr Verhalten auswirken. Die Selbstbereinigungsparameter
SELECT name, setting, unit, short_desc FROM pg_settings WHERE name IN ( 'autovacuum_max_workers', 'autovacuum_analyze_scale_factor', 'autovacuum_naptime', 'autovacuum_analyze_threshold', 'autovacuum_analyze_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_cost_delay', 'autovacuum_vacuum_cost_limit', 'vacuum_cost_limit', 'autovacuum_freeze_max_age', 'maintenance_work_mem', 'vacuum_freeze_min_age');
All diese Parameter wirken sich auf die Selbstbereinigung aus. Die wichtigsten unter ihnen sind jedoch:
Festlegen von Selbstbereinigungsparametern auf Tabellenebene
Sie können Speicherparameter
Die folgende Abfrage zeigt, für welche Tabellen zurzeit Optionen auf Tabellenebene festgelegt wurden.
SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT null;
Ein Beispiel, in dem dies nützlich sein kann, sind Tabellen, die sehr viel größer als Ihre restlichen Tabellen sind. Angenommen, es gibt eine Tabelle mit 300 GB und 30 weitere Tabellen mit weniger als 1 GB. In diesem Fall würde es sich anbieten, einige spezifische Parameter nur für die große Tabelle festzulegen, um nicht das Verhalten des gesamten Systems zu ändern.
ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0);
Hierdurch wird die kostenbasierte Selbstbereinigungsverzögerung für diese Tabelle auf Kosten einer größeren Ressourcennutzung in Ihrem System deaktiviert. Normalerweise pausiert die Selbstbereinigung für autovacuum_vacuum_cost_delay jedes Mal, wenn autovacuum_cost_limit erreicht wird. Weitere Details finden Sie in der PostgreSQL-Dokumentation zum Thema kostenbasierte Bereinigung
Protokollieren von Selbstbereinigung- und Bereinigungsaktivitäten
Informationen über Bereinigungsaktivitäten werden basierend auf der imrds.force_autovacuum_logging_level-Parameter angegebenen Ebene an das postgresql.log gesendet. Im Folgenden sind die für diesen Parameter zulässigen Werte und die PostgreSQL-Versionen aufgeführt, für die dieser Wert die Standardeinstellung ist:
disabled(PostgreSQL 10, PostgreSQL 9.6)debug5,debug4,debug3,debug2,debug1info(PostgreSQL 12, PostgreSQL 11)noticewarning(PostgreSQL 13 und höher)error, Protokoll,fatal,panic
Das rds.force_autovacuum_logging_level arbeitet mit dem log_autovacuum_min_duration-Parameter. Der Wert des log_autovacuum_min_duration-Parameters ist der Schwellenwert (in Millisekunden), über dem Selbstbereinigung-Aktionen protokolliert werden. Eine Einstellung von -1 protokolliert nichts, während eine Einstellung von 0 alle Aktionen protokolliert. Wie bei rds.force_autovacuum_logging_level, Standardwerte für log_autovacuum_min_duration sind versionsabhängig wie folgt:
10000 ms– PostgreSQL 14, PostgreSQL 13, PostgreSQL 12 und PostgreSQL 11(empty)– Kein Standardwert für PostgreSQL 10 und PostgreSQL 9.6
Wir empfehlen Ihnen, rds.force_autovacuum_logging_level auf WARNING einzustellen. Wir empfehlen auch, dass Sie log_autovacuum_min_duration auf einen Wert von 1000 bis 5000 einstellen. Eine Einstellung von 5000 Protokollaktivitäten, die länger als 5000 Millisekunden dauern. Jede andere Einstellung als –1 protokolliert auch Meldungen, wenn die Selbstbereinigungsaktion aufgrund einer widersprüchlichen Sperre oder gleichzeitig verworfener Beziehungen übersprungen wird. Weitere Informationen finden Sie unter Selbstbereinigung
Um Probleme zu beheben, können Sie die rds.force_autovacuum_logging_level-Parameter in eine der Debugebenen ändern, von debug1 bis zu debug5 für die ausführlichsten Informationen. Wir empfehlen, die Debug-Einstellungen für kurze Zeiträume und nur zur Fehlerbehebung zu verwenden. Weitere Informationen finden Sie unter Zeitpunkt des Protokollierens
Anmerkung
PostgreSQL ermöglicht es dem Konto rds_superuser, Autovakuum-Sitzungen in pg_stat_activity anzuzeigen. Sie können beispielsweise eine Selbstbereinigungssitzung identifizieren und beenden, die die Ausführung eines Befehls blockiert oder langsamer als ein manuell ausgegebener Bereinigungsbefehl ausgeführt wird.
