Zur Erinnerung, wir begannen mit Problemen im Zusammenhang mit der Isolation , machten einen Exkurs über die Datenstruktur auf niedriger Ebene , diskutierten Zeilenversionen im Detail und beobachteten, wie Datenschnappschüsse aus Zeilenversionen erhalten werden.

Dann untersuchten wir In-Page-Vakuum (und HOT-Updates) und Vakuum . Jetzt schauen wir uns das Autovakuum an.

Autovakuum

Wir haben bereits erwähnt, dass VACUUM normalerweise (dh wenn lange Zeit nichts den Transaktionshorizont festhält) seine Arbeit erledigt. Das Problem ist, wie oft man es anruft.

Wenn wir einen Wickeltisch zu selten staubsaugen, wächst seine Größe mehr als gewünscht. Außerdem kann ein nächster Vakuumvorgang mehrere Indexdurchläufe erfordern, wenn zu viele Änderungen vorgenommen wurden.

Wenn wir den Tisch zu oft staubsaugen, führt der Server ständig Wartungsarbeiten durch, anstatt nützliche Arbeiten auszuführen - und das ist auch nicht gut.

Beachten Sie, dass das planmäßige Starten von VACUUM das Problem in keiner Weise behebt, da sich die Arbeitslast mit der Zeit ändern kann. Wenn sich der Tisch stärker verändert, muss er häufiger gesaugt werden.

Autovakuum ist genau die Technik, die es uns ermöglicht, das Staubsaugen abhängig von der Intensität der Tischwechsel zu starten.

Wenn Autovacuum aktiviert ist (der Autovacuum- Konfigurationsparametersatz), wird der Autovacuum-Launcher- Daemon-Prozess gestartet, der die Arbeit plant. Das Staubsaugen selbst erfolgt durch Autovakuum-Worker- Prozesse, von denen mehrere Instanzen parallel ausgeführt werden können.

Der Autovacuum Launcher- Prozess erstellt eine Liste von Datenbanken, in denen Aktivitäten stattfinden. Die Aktivität wird aus Statistiken ermittelt. Um sie zu erfassen, muss der Parameter track_counts festgelegt werden. Schalten Sie niemals autovacuum und track_counts aus , sonst funktioniert die autovacuum-Funktion nicht.

Einmal alle autovacuum_naptime Sekunden startet der Autovacuum-Launcher (unter Verwendung des Postmaster- Prozesses) einen Worker-Prozess für jede Datenbank in der Liste. Mit anderen Worten, wenn eine Datenbank Aktivitäten enthält, werden Worker-Prozesse in einem Intervall von autovacuum_naptime Sekunden an diese Datenbank gesendet. Zu diesem Zweck werden Arbeitsprozesse N-mal so oft wie alle autovacuum_naptime Sekunden gestartet, wenn einige (N) aktive Datenbanken verfügbar sind. Die Gesamtzahl der gleichzeitig ausgeführten Worker-Prozesse ist jedoch durch den Parameter autovacuum_max_workers begrenzt.

Beim Start stellt ein Worker-Prozess eine Verbindung zu der ihm zugewiesenen Datenbank her und erstellt zunächst eine Liste mit folgenden Elementen:

• Alle Tabellen, materialisierten Ansichten und TOAST-Tabellen, die gesaugt werden müssen.
• Alle zu analysierenden Tabellen und materialisierten Ansichten (TOAST-Tabellen werden nicht analysiert, da sie immer mit Indexzugriff erreicht werden).

Dann saugt und / oder analysiert der Arbeiter die Objekte auf der Liste nacheinander und wird abgeschlossen, wenn das Saugen beendet ist.

Wenn der Prozess nicht alle in autovacuum_naptime Sekunden geplanten Arbeiten ausführen konnte, sendet der Autovacuum-Launcher- Prozess einen weiteren Worker-Prozess an diese Datenbank und sie arbeiten zusammen. "Zusammen" bedeutet nur, dass der zweite Prozess eine eigene Liste erstellt und diese durcharbeitet. Es werden also nur verschiedene Tabellen parallel verarbeitet, es gibt jedoch keine Parallelität auf der Ebene einer Tabelle. Wenn einer der Arbeitsprozesse bereits eine Tabelle verarbeitet, wird diese von einem anderen Prozess übersprungen und fortgesetzt.

Lassen Sie uns nun näher erläutern, was unter "erfordert Staubsaugen" und "erfordert Analyse" zu verstehen ist.

Kürzlich wurde ein Patch implementiert, mit dem das Vakuum Indizes parallel zu Hintergrund-Workern verarbeiten kann.

Welche Tische müssen gesaugt werden?

Das Staubsaugen wird als erforderlich angesehen, wenn die Anzahl der toten (dh veralteten) Tupel den angegebenen Schwellenwert überschreitet. Der Statistikkollektor verfolgt permanent die Anzahl der toten Tupel, die in der Tabelle pg_stat_all_tables gespeichert sind. Und zwei Parameter geben den Schwellenwert an:

• autovacuum_vacuum_threshold definiert einen absoluten Wert (die Anzahl der Tupel).
• autovacuum_vacuum_scale_factor definiert den Anteil der Zeilen in der Tabelle.

Fazit: Staubsaugen ist erforderlich, wenn pg_stat_all_tables.n_dead_tup > = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltupes .

Mit den Standardeinstellungen sind autovacuum_vacuum_threshold = 50 und autovacuum_vacuum_scale_factor = 0.2. autovacuum_vacuum_scale_factor ist hier sicherlich der wichtigste - dieser Parameter ist entscheidend für große Tabellen (und mit diesen sind mögliche Probleme verbunden). Der Wert von 20% erscheint zu hoch und muss höchstwahrscheinlich stark reduziert werden.

Die optimalen Werte der Parameter können für verschiedene Tabellen variieren und hängen von den Tabellengrößen und den Besonderheiten der Änderungen ab. Es ist sinnvoll, allgemein geeignete Werte festzulegen und bei Bedarf die Parameter auf der Ebene bestimmter Tabellen mit Hilfe von Speicherparametern zu optimieren:

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

Um Verwirrung zu vermeiden, ist dies nur für wenige Tabellen sinnvoll, die sich im Übrigen durch Umfang und Intensität der Änderungen auszeichnen, und nur dann, wenn die global festgelegten Werte nicht ordnungsgemäß funktionieren.

Außerdem können Sie den automatischen Unterdruck auf Tabellenebene ausschalten (obwohl wir uns kaum einen Grund vorstellen können, warum dies notwendig sein könnte):

• autovacuum_enabled und toast.autovacuum_enabled .

Zum Beispiel, als wir das letzte Mal den Vakuumtisch mit ausgeschaltetem Autovakuum erstellt haben, um das Staubsaugen für Demozwecke manuell zu steuern. 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 zu diesem Zeitpunkt gesaugt werden müssen. Dabei wird die Funktion verwendet, die den aktuellen Wert des Parameters zurückgibt und berücksichtigt, dass der Wert auf Tabellenebene neu definiert werden kann:

 => CREATE FUNCTION get_value(param text, reloptions text[], relkind "char") RETURNS float AS $$ SELECT coalesce( -- if the storage parameter is set, we take its value (SELECT option_value FROM pg_options_to_table(reloptions) WHERE option_name = CASE -- for TOAST tables, the parameter name differs WHEN relkind = 't' THEN 'toast.' ELSE '' END || param ), -- otherwise, we take the value of the configuration parameter current_setting(param) )::float; $$ LANGUAGE sql; 

Und das 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?

Ähnlich ist die Situation bei der automatischen Analyse. Es wird davon ausgegangen, dass für diese Tabellen eine Analyse erforderlich ist, deren Anzahl aktualisierter Tupel (seit der letzten Analyse) 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 der automatischen Analyse sind etwas anders: autovacuum_analyze_threshold = 50 und autovacuum_analyze_scale_factor = 0.1. Sie können auch auf der Ebene der Speicherparameter separater Tabellen definiert werden:

• autovacuum_analyze_threshold
• autovacuum_analyze_scale_factor

Da TOAST-Tabellen nicht analysiert werden, verfügen sie nicht über solche Parameter.

Erstellen wir auch eine Ansicht zur 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

Stellen wir die folgenden Parameterwerte für Experimente ein:

 => ALTER SYSTEM SET autovacuum_naptime = '1s'; -- to aviod waiting long => 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) 

Jetzt erstellen wir eine Tabelle, die der zuletzt verwendeten ähnelt, und fügen eintausend Zeilen ein. Autovakuum ist auf Tischebene ausgeschaltet, und wir werden es selbst einschalten. Ohne dies sind die Beispiele nicht reproduzierbar, da das automatische Absaugen zu einem schlechten Zeitpunkt ausgelöst werden kann.

 => 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); 

Das zeigt unsere Sichtweise zum Staubsaugen:

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

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

Hierbei sind zwei Dinge zu beachten. Erstens ist max_dead_tup = 0, obwohl 3% von 1000 Zeilen 30 Zeilen ergeben. Die Sache ist, dass wir noch keine Statistiken in der Tabelle haben, da INSERT sie nicht selbst aktualisiert. Bis die Tabelle analysiert wird, bleiben Nullen, da pg_class.reltuples = 0 ist. 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 in der Tabelle 1000 Zeilen geändert (hinzugefügt) wurden, die größer als Null sind, muss eine automatische Analyse ausgelöst werden. Lassen Sie uns das überprüfen:

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

Nach einer kurzen Pause können wir sehen, dass die Tabelle analysiert wurde und dass in max_dead_tup anstelle von Nullen die richtigen 20 Zeilen angezeigt werden:

 => 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 zum automatischen Staubsaugen 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 können, wurde max_dead_tup bereits behoben. Eine andere Sache, auf die man achten muss, ist dead_tup = 0. Die Statistik zeigt, dass die Tabelle keine toten Tupel hat ... und das ist wahr. In der Tabelle gibt es noch nichts zu saugen. Alle Tabellen, die ausschließlich im Nur-Anhängen-Modus verwendet werden, werden nicht gesaugt und daher wird die Sichtbarkeitskarte nicht aktualisiert. Dies macht jedoch die Verwendung des Nur-Index-Scans unmöglich.

(Das nächste Mal werden wir sehen, dass das Staubsaugen früher oder später eine Nur-Anhängen-Tabelle erreichen wird, aber dies wird zu selten passieren.)

Eine Lektion gelernt: Wenn nur der Index-Scan kritisch ist, muss möglicherweise ein Vakuumprozess manuell aufgerufen werden.

Schalten Sie nun das automatische Vakuum wieder aus und aktualisieren Sie 31 Zeilen. Dies ist eine Zeile höher 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 der Vakuumauslösung erfüllt. Lassen Sie uns das automatische Vakuum einschalten und nach einer kurzen Pause werden wir sehen, 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) 

Lastdrosselung

VACUUM blockiert keine anderen Prozesse, da es Seite für Seite arbeitet, aber es belastet das System zusätzlich und kann die Leistung erheblich beeinträchtigen.

Vakuum drosseln

Um die Vakuumintensität und damit deren Auswirkung auf das System steuern zu können, wird abwechselnd gearbeitet und gewartet. Der Prozess erledigt etwa das Vakuum_Kosten_Begrenzen herkömmlicher Arbeitseinheiten und schläft dann für Vakuum_Kosten_Verzögerungs- ms.

Die Standardeinstellungen sind vacuum_cost_limit = 200 und vacuum_cost_delay = 0. Die letzte Null bedeutet tatsächlich, dass VACUUM nicht schläft, sodass ein bestimmter Wert von vacuum_cost_limit überhaupt keine Rolle spielt. Der Grund dafür ist, dass ein Administrator, der VACUUM manuell starten musste, wahrscheinlich wünscht, dass das Staubsaugen so schnell wie möglich erfolgt.

Wenn wir jedoch die Ruhezeit festlegen , setzt sich der in vacuum_cost_limit angegebene Arbeitsaufwand aus den Kosten für die Arbeit mit Seiten im Puffercache zusammen. Jeder Seitenzugriff wird wie folgt geschätzt:

• Wenn die Seite im Puffercache gefunden wird, ist vacuum_cost_page_hit = 1.
• Wenn es nicht gefunden wurde, ist vacuum_cost_page_miss = 10.
• Wenn es nicht gefunden wurde und eine schmutzige Seite zusätzlich aus dem Puffer-Cache entfernt werden musste, ist vacuum_cost_page_dirty = 20.

Das heißt, mit den Standardeinstellungen von vacuum_cost_limit können 200 Cacheseiten oder 20 Plattenseiten oder 10 Seiten mit Räumung auf einmal verarbeitet werden. Es ist klar, dass diese Zahlen ziemlich vorläufig sind, aber es macht keinen Sinn, genauere Zahlen auszuwählen.

Drosselung zum automatischen Absaugen

Bei Vakuumprozessen funktioniert die Lastdrosselung wie bei VACUUM. Damit Autovakuumprozesse und manuell gestartetes VACUUM mit unterschiedlicher Intensität arbeiten können, hat Autovakuum seine eigenen Parameter: autovacuum_vacuum_cost_limit und autovacuum_vacuum_cost_delay . Wenn diese Parameter den Wert -1 haben, wird der Wert von vacuum_cost_limit und / oder vacuum_cost_delay verwendet.

Standardmäßig ist autovacuum_vacuum_cost_limit = -1 ( d. H. Der Wert von vacuum_cost_limit = 200 wird verwendet) und autovacuum_vacuum_cost_delay = 20 ms. Auf moderner Hardware ist das automatische Vakuum sehr langsam.

In Version 12 wird der Wert von autovacuum_vacuum_cost_delay auf 2 ms reduziert, was für eine geeignetere erste Annäherung verwendet werden kann.

Außerdem sollten wir beachten, dass das durch diese Einstellungen festgelegte Limit für alle Arbeitsprozesse gilt. Mit anderen Worten, wenn die Anzahl der gleichzeitigen Arbeitsprozesse geändert wird, bleibt die Gesamtlast unverändert. Um die Autovakuum-Leistung beim Hinzufügen von Arbeitsprozessen zu erhöhen, ist es sinnvoll, auch autovacuum_vacuum_cost_limit zu erhöhen.

Speichernutzung und Überwachung

Das letzte Mal haben wir beobachtet, wie VACUUM RAM der Größe maintenance_work_mem zum Speichern von zu saugenden Nachrichten verwendet hat.

Autovacuum macht genau dasselbe. Es können jedoch viele Worker-Prozesse gleichzeitig ausgeführt werden, wenn autovacuum_max_workers auf einen hohen Wert gesetzt ist. Darüber hinaus wird der gesamte Speicher auf einmal und nicht nach Bedarf zugewiesen. Daher kann für einen Arbeitsprozess seine eigene Begrenzung mithilfe des Parameters autovacuum_work_mem festgelegt werden. Der Standardwert dieses Parameters ist -1, dh er wird nicht verwendet.

Wie bereits erwähnt, kann VACUUM auch mit einer minimalen Speichergröße arbeiten. Wenn jedoch Indizes für die Tabelle erstellt werden, kann ein kleiner Wert von maintenance_work_mem zu wiederholten Indexprüfungen führen. Gleiches gilt für Autovakuum. Im Idealfall sollte autovacuum_work_mem einen Mindestwert haben, damit keine wiederholten Scans auftreten.

Wir haben gesehen, dass zum Überwachen von VACUUM die Option VERBOSE verwendet werden kann (die für Autovakuum nicht angegeben werden kann) oder die Ansicht pg_stat_progress_vacuum (die jedoch nur die aktuellen Informationen anzeigt). Daher besteht das Hauptmittel zur Überwachung des automatischen Absaugens in der Verwendung des Parameters log_autovacuum_min_duration , der die Informationen an das Servermeldungsprotokoll ausgibt. Es ist standardmäßig deaktiviert (auf -1 gesetzt). Es ist sinnvoll, diesen Parameter einzuschalten (mit dem Wert 0 werden Informationen über alle automatischen Vakuumläufe ausgegeben) und die Zahlen zu beobachten.

So sehen die Ausgabeinformationen 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.

Um Sie daran zu erinnern, ist es oft sinnvoll, die Schwelle für Vakuum-Triggerung zu senken, um weniger Daten gleichzeitig zu verarbeiten, anstatt die Speichergröße zu erhöhen.

Es kann auch sinnvoll sein, die obigen Ansichten zu verwenden, um die Länge der Liste der Tabellen zu überwachen, für die ein Staubsaugen erforderlich ist. Eine Erhöhung der Listenlänge zeigt an, dass die automatischen Vakuumprozesse nicht genügend Zeit haben, um ihre Arbeit zu erledigen, und dass die Einstellungen geändert werden müssen.

Fortsetzung folgt.