Der Effekt des Aufblähens von Tabellen und Indizes (Bloat) ist weithin bekannt und tritt nicht nur in Postgres auf. Es gibt Möglichkeiten, „out of the box“ damit umzugehen, wie VACUUM FULL oder CLUSTER, aber sie sperren Tabellen während des Betriebs und können daher nicht immer verwendet werden.
Der Artikel enthält einige theoretische Informationen darüber, wie Aufblähungen auftreten, wie Sie damit umgehen können, über verzögerte Einschränkungen und die Probleme, die sie bei der Verwendung der pg_repack-Erweiterung mit sich bringen.
Dieser Artikel basiert auf auf der PgConf.Russia 2020.
Warum es Blähungen gibt
Postgres basiert auf einem Multiversionsmodell (). Das Wesentliche ist, dass jede Zeile in der Tabelle mehrere Versionen haben kann, während Transaktionen nicht mehr als eine dieser Versionen sehen, aber nicht unbedingt dieselbe. Dadurch können mehrere Transaktionen gleichzeitig ablaufen und sich kaum oder gar nicht gegenseitig beeinflussen.
Natürlich müssen alle diese Versionen beibehalten werden. Postgres arbeitet Seite für Seite mit dem Speicher, und eine Seite ist die minimale Datenmenge, die von der Festplatte gelesen oder geschrieben werden kann. Schauen wir uns ein kleines Beispiel an, um zu verstehen, wie das passiert.
Nehmen wir an, wir haben eine Tabelle, zu der wir mehrere Datensätze hinzugefügt haben. Die erste Seite der Datei, in der die Tabelle gespeichert ist, enthält neue Daten. Hierbei handelt es sich um Live-Versionen von Zeilen, die nach dem Commit für andere Transaktionen verfügbar sind (der Einfachheit halber gehen wir davon aus, dass die Isolationsstufe „Read Committed“ lautet).
Wir haben daraufhin einen der Einträge aktualisiert und damit die alte Version als veraltet markiert.
Schritt für Schritt, indem wir Zeilenversionen aktualisierten und löschten, landeten wir bei einer Seite, auf der etwa die Hälfte der Daten „Müll“ ist. Diese Daten sind für keine Transaktion sichtbar.
Postgres verfügt über einen Mechanismus , wodurch veraltete Versionen bereinigt und Platz für neue Daten geschaffen werden. Wenn es jedoch nicht aggressiv genug konfiguriert ist oder mit der Arbeit in anderen Tabellen beschäftigt ist, bleiben die „Mülldaten“ zurück und wir müssen zusätzliche Seiten für neue Daten verwenden.
In unserem Beispiel besteht die Tabelle zu einem bestimmten Zeitpunkt aus vier Seiten, enthält jedoch nur die Hälfte der Live-Daten. Dadurch werden wir beim Zugriff auf die Tabelle deutlich mehr Daten auslesen als nötig.
Selbst wenn VACUUM jetzt alle irrelevanten Zeilenversionen entfernt, wird sich die Situation nicht dramatisch verbessern. Wir werden auf Seiten oder sogar ganzen Seiten freien Platz für neue Zeilen haben, aber wir werden trotzdem mehr Daten lesen, als wir brauchen.
Übrigens, wenn eine völlig leere Seite (in unserem Beispiel die zweite) am Ende der Datei wäre, könnte VACUUM sie abschneiden. Aber jetzt ist sie mittendrin, also kann nichts mit ihr gemacht werden.
Wenn die Anzahl solcher leeren oder stark spärlichen Seiten groß wird, was als Aufblähen bezeichnet wird, beginnt dies Auswirkungen auf die Leistung zu haben.
Alles, was oben beschrieben wurde, ist die Mechanik des Auftretens von Blähungen in Tabellen. Bei Indizes geschieht dies auf ähnliche Weise.
Habe ich Blähungen?
Es gibt verschiedene Möglichkeiten, festzustellen, ob Sie einen Blähbauch haben. Die erste Idee besteht darin, die internen Statistiken von Postgres zu verwenden, die ungefähre Informationen über die Anzahl der Zeilen in Tabellen, die Anzahl der „lebenden“ Zeilen usw. enthalten. Im Internet gibt es viele Variationen vorgefertigter Skripte. Wir haben es als Grundlage genommen von PostgreSQL Experts, das Table Bloat zusammen mit Toast- und Bloat-Btree-Indizes auswerten kann. Unserer Erfahrung nach beträgt der Fehler 10–20 %.
Eine andere Möglichkeit besteht darin, die Erweiterung zu verwenden , wodurch Sie einen Blick in die Seiten werfen und sowohl den geschätzten als auch den genauen Wert von Bloat erhalten können. Im zweiten Fall müssen Sie jedoch die gesamte Tabelle scannen.
Eine leichte Aufblähung, bis zu 20 %, ist akzeptabel. Er kann als Analogon zum Füllfaktor für betrachtet werden и . Bei 50 % und mehr können Leistungsprobleme auftreten.
Möglichkeiten, mit Blähungen umzugehen
Es gibt mehrere sofort einsatzbereite Möglichkeiten, mit Blähungen in Postgres umzugehen, aber diese sind bei weitem nicht immer verfügbar und passen möglicherweise nicht für jeden.
Richten Sie AUTOVACUUM so ein, dass kein Aufblähen auftritt. Genauer gesagt, um es auf einem für Sie akzeptablen Niveau zu halten. Dies scheint ein „Kapitänsratschlag“ zu sein, doch in Wirklichkeit ist dies nicht immer einfach umzusetzen. Sie haben beispielsweise eine aktive Entwicklung mit einer regelmäßigen Änderung des Datenschemas oder es findet eine Art Datenmigration statt. Daher kann sich Ihr Lastprofil häufig ändern und ist für verschiedene Tische unterschiedlich. Das bedeutet, dass Sie immer einen Schritt voraus sein und AUTOVACUUM an das sich ändernde Profil jedes Tisches anpassen müssen. Aber es ist offensichtlich, dass dies nicht einfach ist.
Ein weiterer häufiger Grund dafür, dass AUTOVACUUM Tabellen nicht verarbeiten kann, ist das Vorhandensein lang laufender Transaktionen, die das Bereinigen von Daten verhindern, da diese für diese Transaktionen verfügbar sind. Auch hier liegt die Empfehlung auf der Hand: Beseitigen Sie „hängende“ Transaktionen und minimieren Sie die Zeit aktiver Transaktionen. Wenn die Belastung Ihrer Anwendung jedoch eine Mischung aus OLAP und OLTP ist, können Sie gleichzeitig viele häufige Aktualisierungen und kurze Abfragen sowie langfristige Vorgänge durchführen – beispielsweise das Erstellen eines Berichts. In einer solchen Situation sollten Sie darüber nachdenken, die Last auf verschiedene Untergründe zu verteilen, um jede einzelne Unterlage feinabstimmen zu können.
Ein weiteres Beispiel: Selbst wenn das Profil homogen ist, die Datenbank jedoch sehr stark ausgelastet ist, kann es sein, dass selbst das aggressivste AUTOVACUUM nicht damit zurechtkommt und es zu einer Aufblähung kommt. Die Skalierung (vertikal oder horizontal) ist die einzige Lösung.
Wie kommt man in eine Situation, in der man AUTOVACUUM konfiguriert hat, die Blähungen aber weiter zunehmen?
Team VAKUUM VOLL baut den Inhalt von Tabellen und Indizes neu auf und belässt darin nur aktuelle Daten. Um ein Aufblähen zu vermeiden, funktioniert es einwandfrei, aber während seiner Ausführung wird eine exklusive Sperre für die Tabelle (AccessExclusiveLock) erfasst, die keine Abfragen an diese Tabelle zulässt, nicht einmal Auswahlen. Wenn Sie es sich leisten können, Ihren Dienst oder einen Teil davon für einige Zeit anzuhalten (von mehreren zehn Minuten bis zu mehreren Stunden, abhängig von der Größe der Datenbank und Ihrer Hardware), dann ist diese Option die beste. Leider haben wir während der geplanten Wartung keine Zeit, VACUUM FULL auszuführen, daher ist diese Methode für uns nicht geeignet.
Team CLUSTER baut den Inhalt von Tabellen auf die gleiche Weise wie VACUUM FULL neu auf und ermöglicht Ihnen dabei die Angabe eines Index, nach dem die Daten physisch auf der Festplatte geordnet werden (die Reihenfolge ist jedoch für neue Zeilen in der Zukunft nicht garantiert). In bestimmten Situationen ist dies eine gute Optimierung für mehrere Abfragen – mit dem Lesen mehrerer Datensätze im Index. Der Nachteil des Befehls ist der gleiche wie der von VACUUM FULL – er sperrt die Tabelle während des Betriebs.
Team NEUINDEX Ähnlich wie die beiden vorherigen, erstellt jedoch einen bestimmten Index oder alle Indizes einer Tabelle neu. Sperren sind etwas schwächer: ShareLock für eine Tabelle (verhindert Änderungen, erlaubt aber Auswahlen) und AccessExclusiveLock für einen wiederhergestellten Index (blockiert Abfragen, die diesen Index verwenden). Allerdings wurde diese Option mit Postgres 12 eingeführt , wodurch Sie einen Index neu erstellen können, ohne das gleichzeitige Hinzufügen, Ändern oder Löschen von Datensätzen zu blockieren.
In früheren Versionen von Postgres können Sie mit REINDEX CONCURRENTLY ein ähnliches Ergebnis erzielen . Sie können damit einen Index ohne starke Sperre erstellen (ShareUpdateExclusiveLock, das parallele Abfragen nicht beeinträchtigt), dann den alten Index durch einen neuen ersetzen und den alten Index löschen. Dadurch können Sie das Aufblähen von Indizes vermeiden, ohne Ihre Anwendung zu beeinträchtigen. Es ist wichtig zu berücksichtigen, dass bei der Neuerstellung von Indizes das Festplattensubsystem zusätzlich belastet wird.
Wenn es also Möglichkeiten für Indizes gibt, Hot Bloat zu beseitigen, dann gibt es keine für Tabellen. Hier kommen externe Erweiterungen ins Spiel: (zuvor pg_reorg), , und andere. Im Rahmen dieses Artikels werde ich sie nicht vergleichen und nur über pg_repack sprechen, das wir nach einiger Verfeinerung zu Hause verwenden.
So funktioniert pg_repack
Nehmen wir an, wir haben eine ganz normale Tabelle – mit Indizes, Einschränkungen und leider auch mit Aufblähungen. Als ersten Schritt erstellt pg_repack eine Protokolltabelle, um alle Änderungen während der Ausführung zu verfolgen. Der Trigger repliziert diese Änderungen bei jedem Einfügen, Aktualisieren und Löschen. Anschließend wird eine Tabelle erstellt, die in ihrer Struktur dem Original ähnelt, jedoch ohne Indizes und Einschränkungen, um den Prozess des Einfügens von Daten nicht zu verlangsamen.
Als nächstes überträgt pg_repack Daten von der alten Tabelle in die neue Tabelle, filtert automatisch alle irrelevanten Zeilen heraus und erstellt dann Indizes für die neue Tabelle. Während der Ausführung all dieser Vorgänge werden Änderungen in der Protokolltabelle akkumuliert.
Der nächste Schritt besteht darin, die Änderungen in die neue Tabelle zu übertragen. Die Migration erfolgt in mehreren Iterationen. Wenn weniger als 20 Einträge in der Protokolltabelle übrig sind, erhält pg_repack eine starke Sperre, migriert die neuesten Daten und ersetzt die alte Tabelle durch die neue in den Postgres-Systemtabellen. Dies ist der einzige und sehr kurze Moment, in dem Sie nicht mit dem Tisch arbeiten können. Danach werden die alte Tabelle und die Tabelle mit Protokollen gelöscht und Speicherplatz im Dateisystem freigegeben. Prozess abgeschlossen.
Theoretisch sieht alles gut aus, aber wie sieht es in der Praxis aus? Wir haben pg_repack ohne Last und unter Last getestet und seine Funktion im Falle eines vorzeitigen Stopps (also durch Strg+C) überprüft. Alle Tests waren positiv.
Wir gingen zum Produkt – und dann ging alles schief, wie wir erwartet hatten.
Erster Pfannkuchen im Angebot
Beim ersten Cluster haben wir eine Fehlermeldung bezüglich einer Verletzung der eindeutigen Einschränkung erhalten:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.
Diese Einschränkung hatte den automatisch generierten Namen index_16508, erstellt von pg_repack. Durch die in seiner Zusammensetzung enthaltenen Attribute haben wir „unsere“ Einschränkung bestimmt, die ihm entspricht. Es stellte sich heraus, dass das Problem darin bestand, dass es sich nicht um eine ganz gewöhnliche, sondern um eine verzögerte Einschränkung handelte (), d.h. Seine Validierung wird später als der SQL-Befehl durchgeführt, was zu unerwarteten Konsequenzen führt.
Aufgeschobene Einschränkungen: Warum sie benötigt werden und wie sie funktionieren
Ein bisschen Theorie über verzögerte Einschränkungen.
Betrachten Sie ein einfaches Beispiel: Wir haben eine Autoverzeichnistabelle mit zwei Attributen – dem Namen und der Reihenfolge des Autos im Verzeichnis.
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique
);
Angenommen, wir müssten das erste und das zweite Auto stellenweise tauschen. Die direkte Lösung besteht darin, den ersten Wert auf den zweiten und den zweiten auf den ersten zu aktualisieren:
begin;
update cars set ord = 2 where name = 'audi';
update cars set ord = 1 where name = 'bmw';
commit;
Aber wenn wir diesen Code ausführen, ist mit einer Einschränkungsverletzung zu rechnen, da die Reihenfolge der Werte in der Tabelle eindeutig ist:
[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.
Wie kann man es anders machen? Option eins: Fügen Sie eine zusätzliche Ersetzung des Werts durch eine Reihenfolge hinzu, die garantiert nicht in der Tabelle vorhanden ist, zum Beispiel „-1“. In der Programmierung nennt man das „Austausch der Werte zweier Variablen durch eine dritte“. Der einzige Nachteil dieser Methode ist das zusätzliche Update.
Option zwei: Gestalten Sie die Tabelle neu, um einen Gleitkomma-Datentyp für den Exponentenwert anstelle von Ganzzahlen zu verwenden. Wenn Sie dann den Wert beispielsweise von 1 auf 2.5 aktualisieren, „steht“ der erste Eintrag automatisch zwischen dem zweiten und dritten. Diese Lösung funktioniert, es gibt jedoch zwei Einschränkungen. Erstens funktioniert es bei Ihnen nicht, wenn der Wert irgendwo in der Schnittstelle verwendet wird. Zweitens haben Sie abhängig von der Genauigkeit des Datentyps eine begrenzte Anzahl möglicher Einfügungen, bevor Sie die Werte aller Datensätze neu berechnen.
Option drei: Verschieben Sie die Einschränkung, sodass sie nur zum Zeitpunkt des Commits überprüft wird:
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique deferrable initially deferred
);Da die Logik unserer ersten Anfrage sicherstellt, dass alle Werte zum Zeitpunkt der Übergabe eindeutig sind, wird die Übergabe erfolgreich sein.
Das oben besprochene Beispiel ist natürlich sehr synthetisch, aber es offenbart die Idee. In unserer Anwendung verwenden wir verzögerte Einschränkungen, um die Logik zu implementieren, die für die Lösung von Konflikten verantwortlich ist, wenn Benutzer gleichzeitig mit gemeinsam genutzten Widget-Objekten auf dem Board interagieren. Durch die Verwendung solcher Einschränkungen können wir den Anwendungscode etwas einfacher gestalten.
Im Allgemeinen gibt es je nach Einschränkungstyp in Postgres drei Granularitätsebenen ihrer Validierung: Zeile, Transaktion und Ausdruck.
Source:
CHECK und NOT NULL werden immer auf Zeilenebene überprüft, für andere Einschränkungen gibt es, wie aus der Tabelle ersichtlich, unterschiedliche Möglichkeiten. Sie können mehr lesen .
Um es kurz zusammenzufassen: Aufgeschobene Einschränkungen führen in einer Reihe von Situationen zu besser lesbarem Code und weniger Befehlen. Dies muss jedoch dadurch erkauft werden, dass der Debug-Prozess komplizierter wird, da der Moment, in dem der Fehler auftritt, und der Moment, in dem Sie davon erfahren, zeitlich getrennt sind. Ein weiteres mögliches Problem besteht darin, dass der Planer möglicherweise nicht immer in der Lage ist, einen optimalen Plan zu erstellen, wenn die Abfrage eine verzögerte Einschränkung beinhaltet.
Verbesserung pg_repack
Wir haben besprochen, was verzögerte Einschränkungen sind, aber in welcher Beziehung stehen sie zu unserem Problem? Erinnern Sie sich an den Fehler, den wir zuvor erhalten haben:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.Dies geschieht, wenn Daten aus der Protokolltabelle in eine neue Tabelle kopiert werden. Das sieht seltsam aus, weil Die Daten in der Protokolltabelle werden zusammen mit den Daten in der Originaltabelle festgeschrieben. Wenn sie die Einschränkungen der ursprünglichen Tabelle erfüllen, wie können sie dann dieselben Einschränkungen in der neuen Tabelle verletzen?
Wie sich herausstellte, liegt die Ursache des Problems im vorherigen Schritt von pg_repack, der nur Indizes, aber keine Einschränkungen erstellt: Die alte Tabelle hatte eine eindeutige Einschränkung und die neue erstellte stattdessen einen eindeutigen Index.
Hier ist es wichtig zu beachten, dass, wenn die Einschränkung normal und nicht verzögert ist, der stattdessen erstellte eindeutige Index dieser Einschränkung entspricht, weil Eindeutige Einschränkungen in Postgres werden durch die Erstellung eines eindeutigen Index implementiert. Bei einer verzögerten Einschränkung ist das Verhalten jedoch nicht dasselbe, da der Index nicht verzögert werden kann und immer zum Zeitpunkt der Ausführung des SQL-Befehls überprüft wird.
Der Kern des Problems liegt also in der „verzögerten“ Prüfung: In der Originaltabelle erfolgt sie zum Zeitpunkt des Commits und in der neuen zum Zeitpunkt der Ausführung des SQL-Befehls. Daher müssen wir sicherstellen, dass die Prüfungen in beiden Fällen auf die gleiche Weise durchgeführt werden: entweder immer verzögert oder immer sofort.
Welche Ideen hatten wir also?
Erstellen Sie einen Index ähnlich dem „Deferred“.
Die erste Idee besteht darin, beide Prüfungen im Sofortmodus durchzuführen. Dies kann zu mehreren Fehlalarmen der Einschränkung führen, aber wenn es nur wenige davon gibt, sollte dies die Arbeit der Benutzer nicht beeinträchtigen, da solche Konflikte für sie eine normale Situation sind. Sie treten beispielsweise auf, wenn zwei Benutzer gleichzeitig mit der Bearbeitung desselben Widgets beginnen und der Client des zweiten Benutzers keine Zeit hat, die Information zu erhalten, dass das Widget bereits für die Bearbeitung durch den ersten Benutzer gesperrt ist. In einer solchen Situation antwortet der Server dem zweiten Benutzer mit einer Ablehnung, und sein Client macht die Änderungen rückgängig und sperrt das Widget. Etwas später, wenn der erste Benutzer die Bearbeitung abgeschlossen hat, erhält der zweite Benutzer die Information, dass das Widget nicht mehr blockiert ist, und kann seine Aktion wiederholen.
Um sicherzustellen, dass Prüfungen immer im nicht verzögerten Modus erfolgen, haben wir einen neuen Index erstellt, der der ursprünglichen verzögerten Einschränkung ähnelt:
CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;In der Testumgebung erhielten wir nur wenige erwartete Fehler. Erfolg! Wir haben pg_repack erneut auf prod ausgeführt und innerhalb einer Stunde Arbeit 5 Fehler beim ersten Cluster erhalten. Das ist ein akzeptables Ergebnis. Allerdings stieg die Fehleranzahl bereits beim zweiten Cluster deutlich an und wir mussten pg_repack stoppen.
Warum ist das geschehen? Die Wahrscheinlichkeit, dass ein Fehler auftritt, hängt davon ab, wie viele Benutzer gleichzeitig mit denselben Widgets arbeiten. Anscheinend gab es zu diesem Zeitpunkt viel weniger Wettbewerbsänderungen mit den im ersten Cluster gespeicherten Daten als im Rest, d. h. wir haben einfach „Glück“.
Die Idee hat nicht funktioniert. In diesem Moment sahen wir zwei andere Lösungen: unseren Anwendungscode neu schreiben, um aufgeschobene Einschränkungen aufzugeben, oder pg_repack „beibringen“, mit ihnen zu arbeiten. Wir haben uns für das zweite entschieden.
Ersetzen Sie Indizes in der neuen Tabelle durch verzögerte Einschränkungen aus der Originaltabelle
Der Zweck der Überarbeitung war offensichtlich: Wenn die ursprüngliche Tabelle eine verzögerte Einschränkung hat, muss für die neue Tabelle eine solche Einschränkung und kein Index erstellt werden.
Um unsere Änderungen zu testen, haben wir einen einfachen Test geschrieben:
- Tabelle mit verzögerter Einschränkung und einem Datensatz;
- wir fügen Daten in die Schleife ein, die mit dem vorhandenen Datensatz in Konflikt stehen;
- führen Sie ein Update durch – die Daten stehen nicht mehr in Konflikt;
- Änderungen festschreiben.
create table test_table
(
id serial,
val int,
constraint uk_test_table__val unique (val) deferrable initially deferred
);
INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
BEGIN
INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
UPDATE test_table set val = i where id = v_id;
COMMIT;
END;
END LOOP;Die Originalversion von pg_repack stürzte immer beim ersten Einfügen ab, die modifizierte Version funktionierte ohne Fehler. Großartig.
Wir gehen zu prod und erhalten in derselben Phase des Kopierens von Daten aus der Protokolltabelle in eine neue erneut eine Fehlermeldung:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.Eine klassische Situation: In Testumgebungen funktioniert alles, in der Produktion jedoch nicht?!
APPLY_COUNT und die Verbindung zweier Stapel
Wir begannen, den Code buchstäblich Zeile für Zeile zu analysieren und entdeckten einen wichtigen Punkt: Daten werden stapelweise von der Protokolltabelle in die neue übertragen, die APPLY_COUNT-Konstante gibt die Größe des Stapels an:
for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);
if (num > MIN_TUPLES_BEFORE_SWITCH)
continue; /* there might be still some tuples, repeat. */
...
}Das Problem besteht darin, dass die Daten der ursprünglichen Transaktion, bei der mehrere Vorgänge potenziell gegen die Einschränkung verstoßen können, während der Übertragung an der Kreuzung zweier Stapel landen können – die Hälfte der Befehle wird im ersten Stapel festgeschrieben, die andere Hälfte in dieser Sekunde. Und hier, was für ein Glück: Wenn die Teams der ersten Gruppe nichts verletzen, ist alles in Ordnung, aber wenn sie es tun, tritt ein Fehler auf.
APPLY_COUNT entspricht 1000 Datensätzen, was erklärt, warum unsere Tests erfolgreich waren – sie deckten den Fall der „Batch-Junction“ nicht ab. Wir haben zwei Befehle verwendet – Einfügen und Aktualisieren, sodass immer genau 500 Transaktionen mit zwei Befehlen in einem Stapel platziert wurden und wir keine Probleme hatten. Nach dem Hinzufügen des zweiten Updates funktionierte unsere Bearbeitung nicht mehr:
FOR i IN 1..10000 LOOP
BEGIN
INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
UPDATE test_table set val = i where id = v_id;
UPDATE test_table set val = i where id = v_id; -- one more update
COMMIT;
END;
END LOOP;Die nächste Aufgabe besteht also darin, sicherzustellen, dass die Daten aus der Originaltabelle, die in einer Transaktion geändert wurden, auch innerhalb einer Transaktion in die neue Tabelle gelangen.
Ablehnung der Chargenbildung
Und wir hatten wieder zwei Lösungen. Erstens: Verzichten wir vollständig auf eine Aufteilung in Stapel und machen wir die Datenübertragung zu einer einzigen Transaktion. Für diese Entscheidung sprach die Einfachheit – die erforderlichen Codeänderungen sind minimal (übrigens funktionierte pg_reorg in älteren Versionen damals so). Es gibt jedoch ein Problem: Wir schaffen eine langfristige Transaktion, und dies birgt, wie bereits erwähnt, die Gefahr einer neuen Aufblähung.
Die zweite Lösung ist komplizierter, aber wahrscheinlich richtiger: Erstellen Sie in der Protokolltabelle eine Spalte mit der Kennung der Transaktion, die der Tabelle Daten hinzugefügt hat. Beim Kopieren von Daten können wir diese dann nach diesem Attribut gruppieren und sicherstellen, dass zusammengehörige Änderungen gemeinsam übertragen werden. Der Stapel wird aus mehreren Transaktionen (oder einer großen) gebildet und seine Größe variiert je nachdem, wie viele Daten in diesen Transaktionen geändert wurden. Es ist wichtig zu beachten, dass die Daten verschiedener Transaktionen in zufälliger Reihenfolge in die Protokolltabelle gelangen und daher nicht mehr wie zuvor nacheinander gelesen werden können. seqscan für jede nach tx_id gefilterte Anfrage ist zu teuer, Sie benötigen einen Index, verlangsamt aber auch die Methode aufgrund des Aktualisierungsaufwands. Im Allgemeinen muss man wie immer etwas opfern.
Deshalb haben wir uns entschieden, mit der ersten, einfacheren Option zu beginnen. Zunächst musste geklärt werden, ob eine lange Transaktion ein echtes Problem darstellen würde. Da die Hauptübertragung von Daten von der alten zur neuen Tabelle auch in einer langen Transaktion erfolgt, wurde die Frage in „Um wie viel werden wir diese Transaktion erhöhen?“ umgewandelt. Die Dauer der ersten Transaktion hängt hauptsächlich von der Größe der Tabelle ab. Die Dauer des neuen hängt davon ab, wie viele Änderungen sich während der Datenübertragung in der Tabelle ansammeln, d. h. von der Intensität der Belastung ab. Die Ausführung von pg_repack fand zu einem Zeitpunkt mit minimaler Dienstlast statt und der Änderungsumfang war im Vergleich zur ursprünglichen Tabellengröße unvergleichlich gering. Wir haben entschieden, dass wir die Zeit einer neuen Transaktion vernachlässigen können (zum Vergleich: Der Durchschnitt liegt bei 1 Stunde und 2-3 Minuten).
Die Experimente waren positiv. Auch im Verkauf erhältlich. Zur Verdeutlichung hier ein Bild mit der Größe einer der Basen nach dem Lauf:
Da uns diese Lösung voll und ganz zusagte, haben wir nicht versucht, die zweite Lösung zu implementieren, sondern erwägen die Möglichkeit, sie mit den Entwicklern der Erweiterung zu besprechen. Unsere aktuelle Revision ist leider noch nicht zur Veröffentlichung bereit, da wir das Problem nur mit eindeutigen verzögerten Einschränkungen gelöst haben und für einen vollwertigen Patch Unterstützung für andere Typen erforderlich ist. Wir hoffen, dass wir dies in Zukunft tun können.
Vielleicht haben Sie eine Frage: Warum haben wir uns überhaupt auf diese Geschichte mit der Verfeinerung von pg_repack eingelassen und beispielsweise nicht dessen Analoga verwendet? Irgendwann haben wir auch darüber nachgedacht, aber die positive Erfahrung, es früher auf Tabellen ohne verzögerte Einschränkungen zu verwenden, motivierte uns, zu versuchen, den Kern des Problems zu verstehen und es zu beheben. Darüber hinaus erfordert die Verwendung anderer Lösungen auch Zeit für die Durchführung von Tests. Daher haben wir beschlossen, zunächst zu versuchen, das darin enthaltene Problem zu beheben. Wenn uns klar wird, dass wir dies nicht in angemessener Zeit tun können, werden wir über Analogien nachdenken.
Befund
Was wir aus eigener Erfahrung empfehlen können:
- Überwachen Sie Ihre Blähungen. Anhand der Überwachungsdaten können Sie nachvollziehen, wie gut das automatische Vakuum abgestimmt ist.
- Stellen Sie AUTOVACUUM ein, um das Aufblähen auf einem akzeptablen Niveau zu halten.
- Wenn die Aufblähung immer noch zunimmt und Sie sie mit vorgefertigten Tools nicht bewältigen können, scheuen Sie sich nicht, externe Erweiterungen zu verwenden. Die Hauptsache ist, alles gut zu testen.
- Scheuen Sie sich nicht, externe Lösungen an Ihre Bedürfnisse anzupassen – manchmal kann dies effizienter und sogar einfacher sein, als Ihren eigenen Code zu ändern.
Source: habr.com