Tabellen- und Index-Bloat tritt auf, wenn der VACUUM-Vorgang nicht mit der Rate der DML-Vorgänge (Data Manipulation Language, Datenbearbeitungssprache) für eine Tabelle mithalten kann. Ein gewisser Tabellen- und Index-Bloat ist unvermeidlich und in der Regel akzeptabel, da Transaktionen diesen Speicherplatz wiederverwenden können. Schwerwiegende Bloat-Probleme führen zu Leistungsproblemen wie längeren Abfragezeiten, erhöhten E/A-Vorgängen, einer weniger effizienten Nutzung von gemeinsam genutzten Puffern und Caches sowie erhöhten Speicherkosten.
Am besten schätzen Sie die Größe mit dem Modul pgstattuple. Die Verwendung von pgstattuple ist jedoch weniger effizient, wenn die Erweiterung alle Seiten einer Beziehung lesen muss. Wenn Sie pgstattuple nicht verwenden können, haben Sie die Möglichkeit, den Bloat mit anderen Skripts zu schätzen. Diese Methode ist jedoch weniger genau.
pgstattuple-Ausgabe lesen
Wenn Sie pgstattuple verwenden, um die physischen Speichereigenschaften einer Tabelle oder eines Index zu melden, hat die Ausgabe das folgende Format:
> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len | 7431888896
tuple_count | 66666666
tuple_len | 2796296271
tuple_percent | 37.63
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 3873153652
free_percent | 52.12
Im Folgenden wird die Ausgabe beschrieben:
table_len(7431888896)- Die Gesamtgröße der Tabelle in Byte. Dazu gehören alle Live-Tupel, Dead-Tupel und freier Speicherplatz. In diesem Fall belegt
large_tableetwa 7.088 GB. tuple_count(66666666)- Gibt die Anzahl der aktiven Tupel an, d. h. der sichtbaren und aktiven Zeilen in der Tabelle. Die Tabelle enthält etwa 66 Millionen aktive Zeilen.
tuple_len(2796296271)- Die Gesamtlänge aller aktiven Tupel in Byte, die ungefähr 2,6 GB beträgt.
tuple_percent(37.63)Der Prozentsatz der
table_len, die von aktiven Tupeln belegt werden. So wird der Wert berechnet:(
tuple_len/table_len) ∗ 100 = (2796296271/7431888896) ∗ 100 ≈37.63%Diese Ausgabe gibt an, dass aktive Daten weniger als 40% des Festplattenspeichers der Tabelle belegen.
dead_tuple_count(0)Die Anzahl der fehlerhaften Tupel – Zeilen, die nach Aktualisierungen oder Löschvorgängen noch nicht durch VACUUM-Vorgänge entfernt wurden. Ein Wert von
0bedeutet, dass die Tabelle bereinigt ist und keine inaktiven Tupel darauf warten, zurückgefordert zu werden. Das deutet oft darauf hin, dassVACUUModer Autovacuum effizient für die Tabelle ausgeführt wird.dead_tuple_len(0)Die Gesamtlänge aller inaktiven Tupel in Byte. Entsprechend
dead_tuple_countbedeutet ein Wert von0, dass inaktive Zeilen keinen Speicherplatz belegen.dead_tuple_percent(0)Der Prozentsatz der
table_len, die von fehlerhaften Tupeln belegt werden. So wird der Wert berechnet:(
dead_tuple_len/table_len) ∗ 100 = (0/7431888896) ∗ 100 =0%free_space(3873153652)Dieser Wert gibt den freien Speicherplatz auf den Datenseiten der Tabelle an. Sie können diesen Speicherplatz für neue Einfügungen oder Aktualisierungen wiederverwenden, ohne dass die Tabelle erweitert werden muss. Das bedeutet, dass noch viel freier Speicherplatz vorhanden ist, etwa 3,7 GB.
free_percent(52.12)Dieser Wert gibt den Prozentsatz des
table_lenan, der freier Speicherplatz ist. So wird der Wert berechnet:(
free_space÷table_len) × 100 = (3873153652÷7431888896) × 100 ≈52.12%
Bloat mithilfe von Abfragen schätzen
Sie können den Bloat mithilfe von Abfragen für Systemkatalogtabellen schätzen. Diese Methode ist jedoch weniger genau als die Verwendung von Erweiterungen wie pgstattuple. Wenn Sie Skripts wie PostgreSQL table bloat check verwenden, müssen Ihre Tabellenstatistiken auf dem neuesten Stand sein, da die Schätzung sonst ungenau ist.
Wenn Sie die Ausgabe einer Abfrage mit dem pgstattuple-Modul vergleichen, sind die Schätzungen vergleichbar, aber keine genauen Messungen von Bloat. Das Modul ist jedoch immer noch der genauere Indikator für Bloat, da die SQL-Abfrage auf dem kumulativen Statistikmodul von PostgreSQL basiert, während das Modul die tatsächlichen Datenseiten liest.
Im folgenden Beispiel wird die Ausgabe von zwei verschiedenen Methoden zum Analysieren von Tabellen-Bloat in PostgreSQL für eine Tabelle mit dem Namen large_table verglichen.
> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len | 2475892736
tuple_count | 22000000
tuple_len | 922925931
tuple_percent | 37.28
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 1293696900
free_percent | 52.25
Vs.
-[ RECORD 1 ]+------------
databasename | postgres
schemaname | public
tablename | large_table
can_estimate | t
est_rows | 21965400
pct_bloat | 54
mb_bloat | 1269.24
table_mb | 2361.195
Adressen-Schwemme
Sie haben folgende Möglichkeiten, Bloat zu verwalten. Jede Methode hat Vor- und Nachteile, die sorgfältig geplant werden müssen:
- Führen Sie eine vollständige Reinigung durch. Für diesen Vorgang ist während des gesamten Vorgangs eine vollständige Tabellensperre erforderlich, was zu Ausfallzeiten führt.
- Verschieben Sie die Daten in eine neue Tabelle. Damit Änderungen nicht in die neue Relation übertragen werden, müssen Sie Tabellenänderungen inaktivieren.
- Verwende
pg_squeeze. Diese Erweiterung hilft, Bloatware im Internet zu vermeiden. Die Erweiterung erstellt eine Schattenkopie der Tabelle, fügt alle Daten ein und verfolgt dann Tabellenänderungen mithilfe eines Replikations-Slots bis zum letzten Schritt nach. In diesem Schritt wird die aufgeblähte Tabelle durch die neu erstellte, nicht aufgeblähte Tabelle ersetzt. Anschließend wird eine kurze Tabellensperre angefordert, um die nicht aufgeblähte Tabelle zu verschieben und die aufgeblähte Tabelle zu ersetzen. Bei dieser Methode ist für die Dauer des Vorgangs doppelt so viel Speicherplatz für das Objekt erforderlich, aber sie beeinträchtigt den normalen Betrieb am wenigsten.