Ich möchte Sie daran erinnern, dass wir mit Problemen im Zusammenhang mit der Isolation begonnen haben , einen Exkurs über das Organisieren von Daten auf niedriger Ebene gemacht haben , ausführlich über Zeilenversionen gesprochen haben und wie Snapshots aus Versionen erhalten werden.

Dann haben wir uns die In-Page-Reinigung (und die HOT-Updates) und die regelmäßige Reinigung angesehen , aber heute haben wir uns die automatische Reinigung angesehen.

Automatische Reinigung (Autovakuum)

Wir haben bereits gesagt, dass die normale Reinigung unter normalen Bedingungen (wenn niemand lange den Transaktionshorizont hält) mit seiner Arbeit fertig werden muss. Die Frage ist, wie oft man es nennt.

Wenn Sie einen Wickeltisch zu selten reinigen, wird er größer als Sie möchten. Darüber hinaus kann es für die nächste Reinigung mehrere Durchgänge durch die Indizes dauern, wenn sich zu viele Änderungen angesammelt haben.

Wenn Sie die Tabelle zu oft räumen, wird der Server anstelle nützlicher Arbeit ständig gewartet - auch nicht gut.

Beachten Sie, dass das Starten einer regelmäßigen geplanten Reinigung das Problem nicht löst, da sich die Last im Laufe der Zeit ändern kann. Wenn die Tabelle aktiver aktualisiert wurde, sollte sie häufiger gereinigt werden.

Die automatische Reinigung ist genau der Mechanismus, mit dem Sie abhängig von der Aktivität der Änderungen in den Tabellen mit der Reinigung beginnen können.

Wenn die automatische Reinigung aktiviert ist ( Autovakuum- Konfigurationsparameter), ist der Autovakuum-Starterprozess immer in dem System vorhanden, das arbeiten soll, und Autovakuum-Arbeitsprozesse sind an der tatsächlichen Reinigung beteiligt, von denen mehrere Instanzen parallel arbeiten können.

Der Autovacuum Launcher-Prozess erstellt eine Liste von Datenbanken, in denen Aktivitäten vorhanden sind. Die Aktivität wird durch Statistiken bestimmt. Damit sie erfasst werden kann, muss der Parameter track_counts festgelegt werden. Schalten Sie niemals autovacuum und track_counts aus , sonst funktioniert die automatische Reinigung nicht.

In autovacuum_naptime startet der Autovacuum Launcher-Prozess (unter Verwendung des Postmaster-Prozesses) einen Workflow für jede Datenbank in der Liste. Mit anderen Worten, wenn die Datenbank eine Aktivität enthält, werden Workflows mit einem Intervall von autovacuum_naptime aufgerufen . Wenn dazu mehrere aktive Datenbanken (N Teile) vorhanden sind, werden die Arbeitsprozesse N-mal häufiger gestartet als autovacuum_naptime . Gleichzeitig wird die Gesamtzahl der gleichzeitig arbeitenden Workflows durch den Parameter autovacuum_max_workers begrenzt.

Nach dem Start stellt der Workflow eine Verbindung zu der von ihm angegebenen Datenbank her und erstellt zunächst die Liste:

• alle Tabellen, materialisierten Ansichten und Toasttabellen, die gelöscht werden müssen,
• Alle Tabellen und materialisierten Darstellungen, die analysiert werden müssen (Toasttabellen werden nicht analysiert, da immer über den Index auf sie zugegriffen wird).

Ferner reinigt und / oder analysiert der Workflow die ausgewählten Objekte und endet, wenn die Reinigung abgeschlossen ist.

Wenn der Prozess nicht alle beabsichtigten Arbeiten für autovacuum_naptime abgeschlossen hat , sendet der Autovacuum Launcher-Prozess einen weiteren Workflow an dieselbe Datenbank und sie arbeiten zusammen. "Zusammen" bedeutet einfach, dass der zweite Prozess seine Tabellenliste erstellt und dieser folgt. Somit werden verschiedene Tabellen parallel verarbeitet, aber auf der Ebene einer Tabelle gibt es keine Parallelität. Wenn einer der Arbeitsprozesse bereits an der Tabelle arbeitet, überspringt der andere diese und fährt fort.

Es wurde lange Zeit über die Notwendigkeit einer parallelen Verarbeitung diskutiert, aber der Patch wurde noch nicht übernommen.

Schauen wir uns nun genauer an, was "gereinigt werden muss" und "analysiert werden muss".

Welche Tische müssen gereinigt werden?

Es wird angenommen, dass eine Reinigung erforderlich ist, wenn die Anzahl der "toten", dh irrelevanten Versionen von Zeichenfolgen einen festgelegten Schwellenwert überschreitet. Die Anzahl der toten Versionen wird vom Statistiksammler ständig erfasst und in der Tabelle pg_stat_all_tables gespeichert. Der Schwellenwert wird durch zwei Parameter festgelegt:

• autovacuum_vacuum_threshold definiert den absoluten Wert (in Stücken),
• autovacuum_vacuum_scale_factor bestimmt den Anteil der Zeilen in einer Tabelle.

Die endgültige Formel lautet: Reinigung ist erforderlich, wenn pg_stat_all_tables.n_dead_tup> = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltupes.

Die Standardeinstellungen setzen autovacuum_vacuum_threshold = 50 und
autovacuum_vacuum_scale_factor = 0.2. Der Hauptparameter hier ist natürlich autovacuum_vacuum_scale_factor - es ist wichtig für große Tabellen ( dh mögliche Probleme sind damit verbunden). Der Wert von 20% scheint stark überschätzt zu sein, höchstwahrscheinlich muss er deutlich reduziert werden.

Die optimalen Parameterwerte können für verschiedene Tabellen je nach Größe und Art der Änderungen variieren. Es ist sinnvoll, insgesamt angemessene Werte festzulegen und - falls erforderlich - die Parameter auf der Ebene einiger Tabellen mithilfe der Speicherparameter speziell zu konfigurieren:

• autovacuum_vacuum_threshold und toast.autovacuum_vacuum_threshold ,
• autovacuum_vacuum_scale_factor und toast.autovacuum_vacuum_scale_factor .

Um nicht verwirrt zu werden, sollte dies nur für eine kleine Anzahl von Tabellen durchgeführt werden, die sich unter anderem durch das Volumen oder die Intensität der Änderungen auszeichnen, und nur dann, wenn die global festgelegten Werte nicht geeignet sind.

Darüber hinaus kann die automatische Reinigung auf Tabellenebene deaktiviert werden (obwohl es schwierig ist, sich einen Grund vorzustellen, warum dies erforderlich wäre):

• autovacuum_enabled und toast.autovacuum_enabled .

Zum Beispiel haben wir das letzte Mal eine Vac-Tabelle mit deaktivierter automatischer Reinigung erstellt, um zu Demonstrationszwecken die manuelle Reinigung zu verwalten. Der Speicherparameter kann wie folgt geändert werden:

=> ALTER TABLE vac SET (autovacuum_enabled = off); 

Um all das zu formalisieren, erstellen wir eine Ansicht, die zeigt, welche Tabellen derzeit bereinigt werden müssen. Es wird eine Funktion verwendet, die den aktuellen Wert des Parameters zurückgibt, da dieser auf Tabellenebene überschrieben werden kann:

 => CREATE FUNCTION get_value(param text, reloptions text[], relkind "char") RETURNS float AS $$ SELECT coalesce( --    ,    (SELECT option_value FROM pg_options_to_table(reloptions) WHERE option_name = CASE --  toast-    WHEN relkind = 't' THEN 'toast.' ELSE '' END || param ), --      current_setting(param) )::float; $$ LANGUAGE sql; 

Und hier ist die Ansicht:

 => CREATE VIEW need_vacuum AS SELECT st.schemaname || '.' || st.relname tablename, st.n_dead_tup dead_tup, get_value('autovacuum_vacuum_threshold', c.reloptions, c.relkind) + get_value('autovacuum_vacuum_scale_factor', c.reloptions, c.relkind) * c.reltuples max_dead_tup, st.last_autovacuum FROM pg_stat_all_tables st, pg_class c WHERE c.oid = st.relid AND c.relkind IN ('r','m','t'); 

Welche Tabellen müssen analysiert werden?

Bei der automatischen Analyse ist die Situation ungefähr dieselbe. Es wird angenommen, dass eine Analyse für diejenigen Tabellen erforderlich ist, für die die Anzahl der geänderten (seit der letzten Analyse) Version der Zeilen den durch zwei ähnliche Parameter angegebenen Schwellenwert überschreitet: pg_stat_all_tables.n_mod_since_analyze> = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * pg_class.reltupes.

Die Standardeinstellungen für die automatische Analyse unterscheiden sich geringfügig: autovacuum_analyze_threshold = 50 und autovacuum_analyze_scale_factor = 0.1. Sie können auch auf der Ebene der Speicherparameter für einzelne Tabellen definiert werden:

• autovacuum_analyze_threshold ,
• autovacuum_analyze_scale_factor

Da Toasttabellen nicht analysiert werden, gibt es für sie keine entsprechenden Parameter.

Erstellen wir eine Ansicht für die Analyse:

 => CREATE VIEW need_analyze AS SELECT st.schemaname || '.' || st.relname tablename, st.n_mod_since_analyze mod_tup, get_value('autovacuum_analyze_threshold', c.reloptions, c.relkind) + get_value('autovacuum_analyze_scale_factor', c.reloptions, c.relkind) * c.reltuples max_mod_tup, st.last_autoanalyze FROM pg_stat_all_tables st, pg_class c WHERE c.oid = st.relid AND c.relkind IN ('r','m'); 

Beispiel

Für Experimente setzen wir folgende Parameterwerte:

 => ALTER SYSTEM SET autovacuum_naptime = '1s'; --     => ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.03; -- 3% => ALTER SYSTEM SET autovacuum_vacuum_threshold = 0; => ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02; -- 2% => ALTER SYSTEM SET autovacuum_analyze_threshold = 0; 

 => SELECT pg_reload_conf(); 

  pg_reload_conf ---------------- t (1 row) 

Erstellen Sie nun eine Tabelle ähnlich der, die wir zuletzt verwendet haben, und fügen Sie tausend Zeilen ein. Die automatische Reinigung ist auf Tabellenebene deaktiviert und wird von uns selbst aktiviert. Wenn dies nicht erfolgt, sind die Beispiele nicht reproduzierbar, da die automatische Reinigung möglicherweise zur falschen Zeit funktioniert.

 => CREATE TABLE autovac( id serial, s char(100) ) WITH (autovacuum_enabled = off); => INSERT INTO autovac SELECT g.id,'A' FROM generate_series(1,1000) g(id); 

In unserer Bereinigungsansicht wird Folgendes angezeigt:

 => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 0 | 0 | (1 row) 

Es gibt zwei Punkte, auf die Sie achten sollten. Erstens ist max_dead_tup = 0, obwohl 3% von 1000 Zeilen 30 Zeilen sind. Tatsache ist, dass wir noch keine Statistiken in der Tabelle haben, da INSERT selbst diese nicht aktualisiert. Bis unsere Tabelle analysiert ist, bleiben die Nullen erhalten, da pg_class.reltuples = 0. Schauen wir uns jedoch die zweite Ansicht zur Analyse an:

 => SELECT * FROM need_analyze WHERE tablename = 'public.autovac'; 

  tablename | mod_tup | max_mod_tup | last_autoanalyze ----------------+---------+-------------+------------------ public.autovac | 1000 | 0 | (1 row) 

Da die Tabelle 1000 Zeilen geändert (hinzugefügt) hat und dies mehr als Null ist, sollte die automatische Analyse funktionieren. Überprüfen Sie dies:

 => ALTER TABLE autovac SET (autovacuum_enabled = on); 

Nach einer kurzen Pause sehen wir, dass die Tabelle analysiert wird und anstelle von Nullen in max_mod_tup sehen wir die richtigen 20 Zeilen:

 => SELECT * FROM need_analyze WHERE tablename = 'public.autovac'; 

  tablename | mod_tup | max_mod_tup | last_autoanalyze ----------------+---------+-------------+------------------------------- public.autovac | 0 | 20 | 2019-05-21 11:59:48.465987+03 (1 row) 

 => SELECT reltuples, relpages FROM pg_class WHERE relname = 'autovac'; 

  reltuples | relpages -----------+---------- 1000 | 17 (1 row) 

Kehren wir zur automatischen Reinigung zurück:

 => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 0 | 30 | (1 row) 

Wie wir sehen, wurde Max_dead_tup bereits behoben. Der zweite Punkt, auf den Sie achten sollten, ist dead_tup = 0. Statistiken zeigen, dass die Tabelle keine toten Versionen von Zeilen enthält ... und das ist wahr. In unserem Tisch gibt es noch nichts zu reinigen. Daher wird eine Tabelle, die nur im Nur-Anhängen-Modus verwendet wird, nicht gelöscht, und daher wird die Sichtbarkeitskarte nicht dafür aktualisiert. Dies macht es unmöglich, ausschließlich Index-Scannen (Nur-Index-Scan) zu verwenden.

(Das nächste Mal werden wir sehen, dass die Reinigung früher oder später zum Nur-Anhängen-Tisch kommt, aber dies wird sehr selten vorkommen.)

Praktische Schlussfolgerung: Wenn es wichtig ist, nur das Index-Scannen zu verwenden, müssen Sie möglicherweise eine manuelle Reinigung anfordern.

Schalten Sie nun die automatische Reinigung wieder aus und aktualisieren Sie die Zeile 31 - eine mehr als der Schwellenwert.

 => ALTER TABLE autovac SET (autovacuum_enabled = off); => UPDATE autovac SET s = 'B' WHERE id <= 31; => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 31 | 30 | (1 row) 

Jetzt ist die Bedingung zum Auslösen der automatischen Reinigung erfüllt. Aktivieren Sie die automatische Reinigung und nach einer kurzen Pause sehen wir, dass die Tabelle verarbeitet wurde:

 => ALTER TABLE autovac SET (autovacuum_enabled = on); => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+------------------------------- public.autovac | 0 | 30 | 2019-05-21 11:59:52.554571+03 (1 row) 

Lastregelung

Die Bereinigung blockiert keine anderen Prozesse, da sie seitenweise arbeitet, das System jedoch belastet und die Leistung spürbar beeinträchtigt.

Regelung zur regelmäßigen Reinigung

Um die Intensität der Reinigung und damit deren Auswirkung auf das System steuern zu können, wechselt der Prozess zwischen Arbeit und Erwartung. Die Reinigung führt ungefähr vakuum_Kosten_Limit konventionelle Arbeitseinheiten aus und schläft dann bei Vakuum_Kostenverzögerung ms ein.

Die Standardeinstellungen setzen vakuum_Kostenlimit = 200, Vakuum_Kostenverzögerung = 0. Die letzte Null bedeutet tatsächlich, dass die (normale) Reinigung nicht einschläft, sodass der spezifische Wert von Vakuum_Kostenlimit keine Rolle spielt. Dies geschieht aus dem Grund, dass der Administrator, wenn er VACUUM manuell starten musste, die Bereinigung wahrscheinlich so schnell wie möglich durchführen möchte.

Wenn Sie dennoch die Ruhezeit festlegen , besteht der in vakuum_Kostenlimit angegebene Arbeitsaufwand aus den Kosten für die Arbeit mit Seiten im Puffercache . Jeder Seitenzugriff wird wie folgt bewertet:

• Wenn die Seite im Puffercache gefunden wurde, ist vacac_cost_page_hit = 1;
• wenn nicht gefunden, dann vacuum_cost_page_miss = 10;
• Wenn Sie es nicht finden konnten und die verschmutzte Seite aus dem Puffer schieben mussten, ist vacac_cost_page_dirty = 20.

Das heißt, mit den Einstellungen für vakuum_Kostenlimit können standardmäßig 200 Seiten aus dem Cache oder 20 Seiten von der Festplatte oder 10 Seiten mit Extrusion in einer Sitzung verarbeitet werden. Es ist klar, dass dies eher willkürliche Zahlen sind, aber es macht keinen Sinn, sie genauer auszuwählen.

Regelung zur automatischen Reinigung

Die Lastregelung während der automatischen Reinigung funktioniert genauso wie bei der regulären Reinigung. Damit manuelle Reinigung und automatische Reinigung mit unterschiedlichen Intensitäten arbeiten können, haben Autoprozesse ihre eigenen Parameter: autovacuum_vacuum_cost_limit und autovacuum_vacuum_cost_delay . Wenn diese Parameter den Wert -1 annehmen, wird der Wert von vakuum_Kostenlimit und / oder Vakuumkostenverzögerung verwendet .

Standardmäßig ist autovacuum_vacuum_cost_limit = -1 ( dh der Wert vakuum_cost_limit = 200 wird verwendet) und autovacuum_vacuum_cost_delay = 20 ms. Bei modernen Geräten mit diesen Nummern funktioniert die automatische Reinigung sehr, sehr langsam.

In Version 12 wird der Wert von autovacuum_vacuum_cost_delay auf 2 ms reduziert, was als geeignetere erste Annäherung angesehen werden kann.

Darüber hinaus ist zu beachten, dass die durch diese Parameter festgelegte Grenze allen Arbeitsprozessen gemeinsam ist. Mit anderen Worten, wenn sich die Anzahl der gleichzeitigen Arbeitsprozesse ändert, bleibt die Gesamtlast konstant. Wenn die Aufgabe darin besteht, die Leistung der automatischen Reinigung zu erhöhen, lohnt es sich daher, beim Hinzufügen von Workflows das autovacuum_vacuum_cost_limit zu erhöhen.

Speichernutzung und Überwachung

Beim letzten Mal haben wir uns angesehen, wie beim Bereinigen der RAM-Speicher der Größe wartung_work_mem verwendet wird , um die Versionskennungen der zu bereinigenden Zeilen zu speichern.

Die automatische Reinigung funktioniert genauso. Es kann jedoch viele gleichzeitige Prozesse geben, wenn Sie autovacuum_max_workers auf einen großen Wert setzen. Darüber hinaus wird der gesamte Speicher sofort und vollständig und nicht notwendigerweise zugewiesen. Daher können Sie für den Workflow für die automatische Reinigung mithilfe des Parameters autovacuum_work_mem Ihre eigene Einschränkung festlegen . Standardmäßig ist dieser Parameter -1, d. H. Er wird nicht verwendet.

Wie bereits erwähnt, kann die Reinigung mit einem Minimum an Speicher ausgeführt werden. Wenn jedoch Indizes für die Tabelle erstellt werden, kann ein kleiner Wert für "tenance_work_mem "zu wiederholten Indexprüfungen führen. Gleiches gilt für die automatische Reinigung. Idealerweise sollten Sie einen Mindestwert für autovacuum_work_mem auswählen, bei dem keine wiederholten Scans auftreten.

Wir haben gesehen, dass Sie zur Überwachung der Reinigung den Parameter VERBOSE (der jedoch nicht für die automatische Reinigung angegeben werden kann) oder die Ansicht pg_stat_progress_vacuum verwenden können (es werden jedoch nur die aktuellen Informationen angezeigt). Daher ist die Hauptmethode zur Überwachung der automatischen Bereinigung der Parameter log_autovacuum_min_duration , mit dem Informationen im Nachrichtenprotokoll des Servers angezeigt werden. Standardmäßig ist es ausgeschaltet (auf -1 eingestellt). Es gibt einen Grund, diesen Parameter zu aktivieren (bei einem Wert von 0 werden Informationen zu allen Starts der automatischen Reinigung angezeigt) und die Zahlen zu beachten.

So sieht die Ausgabe aus:

 => ALTER SYSTEM SET log_autovacuum_min_duration = 0; => SELECT pg_reload_conf(); 

  pg_reload_conf ---------------- t (1 row) 

 => UPDATE autovac SET s = 'C' WHERE id <= 31; 

 student$ tail -n 7 /var/log/postgresql/postgresql-11-main.log 

 2019-05-21 11:59:55.675 MSK [9737] LOG: automatic vacuum of table "test.public.autovac": index scans: 0 pages: 0 removed, 18 remain, 0 skipped due to pins, 0 skipped frozen tuples: 31 removed, 1000 remain, 0 are dead but not yet removable, oldest xmin: 4040 buffer usage: 78 hits, 0 misses, 0 dirtied avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 2019-05-21 11:59:55.676 MSK [9737] LOG: automatic analyze of table "test.public.autovac" system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 

Alle notwendigen Informationen finden Sie hier.

Denken Sie daran, dass Sie häufig nicht den Speicher vergrößern, sondern den Schwellenwert für die Bereinigung verringern sollten, damit weniger Daten gleichzeitig verarbeitet werden.

Es kann auch sinnvoll sein, die Länge der Liste der zu bereinigenden Tabellen mithilfe der obigen Ansichten zu überwachen. Eine Vergrößerung der Liste zeigt an, dass die automatische Reinigung keine Zeit hat, ihre Arbeit zu erledigen, und dass die Einstellungen geändert werden müssen.

Fortsetzung folgt .