- Ausgehend von einer Warnung über knappen Speicherplatz wurde eine PostgreSQL-Bereinigung durchgeführt, bei der mehr als 70 GB an unoptimiertem und ungenutztem Speicher freigemacht wurden, ohne Indizes oder Daten zu löschen
- Zunächst wurden gängige Methoden zur Rückgewinnung geprüft, etwa ungenutzte Indizes, Table- und Index-Bloat,
REINDEX CONCURRENTLY, pg_repack und die B-Tree-Deduplizierung in PostgreSQL 13
- Der entscheidende Unterschied war, dass PostgreSQL im Gegensatz zu Oracle auch NULL-Werte indiziert. Nachdem ein vollständiger Index auf
cancelled_by_user_id, dessen Werte größtenteils NULL waren, in einen Partial Index umgewandelt wurde, schrumpfte er von 769 MB auf unter 5 MB
- Durch das Auffinden von Single-Column-Indizes mit hohem
pg_stats.null_frac wurden Kandidaten für Partial Indexes mit der Bedingung WHERE column IS NOT NULL ausgewählt; nach dem Zurücksetzen der Statistikzähler wurde mit pg_stat_all_indexes die tatsächliche Nutzung geprüft
- In Django kann ein nullable
ForeignKey standardmäßig einen B-Tree-Index erzeugen. Deshalb muss db_index=False explizit gesetzt und mit models.Index(..., condition=Q(...__isnull=False)) ein Partial Index direkt definiert werden
Bereinigung nach einer Warnung über knappen Speicherplatz
- Alle paar Monate meldete das Datenbank-Monitoring knappen Speicherplatz; normalerweise wurde das durch zusätzlichen Storage gelöst
- Damals war wegen einer Isolationsphase die Systemlast gering, sodass es ein guter Zeitpunkt war, eine aufwendigere Bereinigung zu versuchen
- Am Ende konnten mehr als 70 GB an unoptimiertem und ungenutztem Speicher freigemacht werden, ohne Indizes zu löschen oder Daten zu entfernen
- Schon die übliche Reorganisation von Indizes und Tabellen sparte viel Platz, und danach führte eine unerwartete Entdeckung zur zusätzlichen Entfernung von rund 20 GB ungenutzter Indexwerte
Zunächst vermutete Ursachen
-
Ungenutzte Indizes
- Ungenutzte Indizes werden zwar für Abfrageleistung erstellt, belegen aber Speicherplatz und können
INSERT und UPDATE verlangsamen
- In
pg_stat_all_indexes wurden Indizes mit idx_scan = 0, idx_tup_read = 0 und idx_tup_fetch = 0 gesucht, um seit dem letzten Statistik-Reset nicht gescannte oder gefetchte Indizes zu identifizieren
- Es gibt Fälle, in denen ein Index äußerlich ungenutzt aussieht, aber nicht entfernt werden darf
- Wenn der PostgreSQL-Optimizer nicht den Index selbst, sondern dessen Metadaten verwendet
- Bei Indizes, die Unique- oder Primary-Key-Constraints auf selten aktualisierten Tabellen absichern
- Ob ein Index tatsächlich gelöscht werden kann, muss Liste für Liste geprüft werden; die Statistikzähler wurden nach jeder Prüfung zurückgesetzt
- In diesem Fall gab es keine zu löschenden ungenutzten Indizes, weil diese Prüfung bereits regelmäßig durchgeführt wurde
-
Bloat bei Indizes und Tabellen
- PostgreSQL markiert beim Aktualisieren einer Zeile das bestehende Tuple als tot und legt ein neues Tuple an anderer Stelle ab; dadurch kann Bloat entstehen
- Bloat betrifft nicht nur Tabellen, sondern auch Indizes und führt dazu, dass mehr Speicher genutzt wird als tatsächlich nötig
- Da die Schätzung von Bloat nicht trivial ist, wurden mit den Queries aus dem PostgreSQL-Wiki und
pgsql-bloat-estimation Table- und B-Tree-Index-Bloat geschätzt
Methoden zur Verringerung von Index-Bloat
-
Indizes neu erstellen oder reorganisieren
- Um Index-Bloat zu beseitigen, muss der Index neu aufgebaut werden
- Mit direktem Drop und anschließendem Create lässt sich ein neuer, optimierter Index erzeugen
- PostgreSQLs
REINDEX reorganisiert einen bestehenden Index an Ort und Stelle
- Da die übliche Neuerstellung eine Sperre auf der Tabelle setzt und Änderungen blockiert, ist in Produktionsumgebungen
REINDEX INDEX CONCURRENTLY index_name besser geeignet
REINDEX CONCURRENTLY erzeugt einen neuen Index mit dem Suffix _ccnew, synchronisiert Änderungen und ersetzt nach Abschluss den alten Index, der dann gelöscht wird- Wird die Reorganisation unterwegs abgebrochen, kann der neue Index in invalidem Zustand verbleiben und Speicher belegen; deshalb sollten Indizes mit
_ccnew und ohne indisvalid gesucht und entfernt werden
-
B-Tree-Deduplizierung in PostgreSQL 13
- PostgreSQL 13 führte mit B-Tree Deduplication eine effizientere Speicherung doppelter Werte in B-Tree-Indizes ein
- Bis PostgreSQL 12 konnten viele Duplikate dazu führen, dass derselbe Wert wiederholt in den Leaf-Seiten eines Indexes gespeichert wurde und viel Platz verbrauchte
- Wenn in PostgreSQL 13 die Deduplizierung aktiv ist, wird ein doppelter Wert nur einmal gespeichert, was sich stark auf die Größe von Indizes mit vielen Wiederholungen auswirken kann
- In PostgreSQL 13 ist
deduplicate_items = ON der Standard; nach einem Upgrade von älteren Versionen müssen Indizes per REINDEX neu aufgebaut werden, um den vollen Effekt zu erhalten
- Im Beispiel mit 1 Million Zeilen schrumpfte ein nicht eindeutiger Index auf einer spaltenweise stark duplizierten Spalte mit Deduplizierung von 21 MB auf 6840 kB, während ein eindeutiger Index bei 21 MB unverändert blieb
- In diesem Fall war PostgreSQL 13 noch neu und wurde vom Cloud-Anbieter nicht unterstützt, sodass über Deduplizierung kein Speicher eingespart werden konnte
Table-Bloat und pg_repack
-
Einschränkungen bei der Beseitigung von Table-Bloat
- Auch Tabellen können durch tote Tupel Bloat und Fragmentierung entwickeln
- Anders als bei Indizes lassen sich Tabellen nicht einfach neu erstellen; nötig wären neue Tabellen, Datenübernahme, Synchronisierung sowie die Neuerstellung von Indizes, Constraints und Referenz-Constraints
VACUUM FULL table_name kann den durch Bloat und tote Tupel belegten Speicher zurückholen, erfordert aber eine Tabellensperre- Sowohl die Neuerstellung von Tabellen als auch
VACUUM FULL können große Entwicklungsarbeit oder Downtime bedeuten
-
Einsatz von pg_repack
- pg_repack ist eine PostgreSQL-Erweiterung, die Tabellen und Indizes ohne Downtime reorganisieren kann
- Zur Nutzung wird die Erweiterung mit
CREATE EXTENSION pg_repack; angelegt und anschließend in der Konsole etwa als pg_repack -k --table table_name db_name ausgeführt
pg_repack erstellt eine neue Tabelle, lädt die Originaldaten hinein, synchronisiert neue Änderungen, baut auch die Indizes neu auf und tauscht am Ende die beiden Tabellen aus- Beim Einsatz in Produktion gibt es einige Einschränkungen
- Es wird ungefähr zusätzlicher Speicherplatz in der Größe der zu reorganisierenden Tabelle und Indizes benötigt
- Wenn ein Repack fehlschlägt oder manuell abgebrochen wird, können Zwischenobjekte zurückbleiben und eine manuelle Bereinigung erfordern
- Bei bereits knappem Speicher ist das wegen des zusätzlichen Platzbedarfs eventuell ungeeignet; freier Speicher sollte deshalb überwacht und im Voraus geplant werden
Unerwartete Entdeckung: PostgreSQL indiziert auch NULL
- Auch nach dem Entfernen ungenutzter Indizes und dem Bereinigen von Table- und Index-Bloat mit üblichen Methoden blieb noch Einsparpotenzial übrig
- Eine der größten Tabellen speicherte Transaktionsdaten und erfasste auch Fälle, in denen ein Nutzer nach dem Kauf stornierte und eine Rückerstattung erhielt
- Die Transaktionstabelle hatte Foreign Keys sowohl für den kaufenden Nutzer als auch für den stornierenden Nutzer, und für beide Felder waren B-Tree-Indizes definiert
- Der kaufende Nutzer unterlag einer
NOT NULL-Constraint, daher gab es in jeder Zeile einen Wert
- Der stornierende Nutzer war nullable, und da Stornierungen selten waren, waren die meisten Werte NULL
- Der Index für den stornierenden Nutzer hätte viel kleiner als der für den kaufenden Nutzer sein sollen, war in der Praxis aber gleich groß
- Aus Oracle war bekannt, dass NULLs are not indexed, in PostgreSQL werden NULL-Werte jedoch mit indiziert
- Der bestehende vollständige Index sah so aus
CREATE INDEX transaction_cancelled_by_ix
ON transactions(cancelled_by_user_id);
- Zur Überprüfung der Hypothese wurde er in einen Partial Index umgewandelt, der NULL-Werte ausschließt
DROP INDEX transaction_cancelled_by_ix;
CREATE INDEX transaction_cancelled_by_part_ix
ON transactions(cancelled_by_user_id)
WHERE cancelled_by_user_id IS NOT NULL;
- Nach dem Reindexing war der vollständige Index 769 MB groß, obwohl mehr als 99 % der Werte NULL waren
- Der Partial Index ohne NULL war unter 5 MB groß und reduzierte den Index damit um mehr als 99 %
- Nach dem Zurücksetzen der Tabellenstatistiken und einer Beobachtungsphase zeigte sich, dass der neue Index wie der alte verwendet wurde; ohne Performance-Einbußen wurden mehr als 760 MB ungenutzter indizierter Tupel entfernt
Kandidaten für Partial Indexes finden
- Nach dem guten Ergebnis mit einem Partial Index wurde nach ähnlichen Indizes gesucht, indem Indizes auf Spalten mit hohem
pg_stats.null_frac identifiziert wurden
null_frac ist der von PostgreSQL geschätzte Anteil an NULL-Werten in dieser Spalte- Die Suchabfrage für Kandidaten setzte folgende Bedingungen
- Primary Keys wurden ausgeschlossen, da sie keine Partial Indexes sein können
- Bereits partielle Indizes wurden ausgeschlossen
- Composite Indexes wurden ausgeschlossen
- Es wurden nur Indizes mit einer Größe von mehr als 10 MB berücksichtigt
- Im Beispiel erschien
tx_cancelled_by_ix mit 1418 MB und null_frac von 96,15 % als Kandidat mit einem möglichen Einsparpotenzial von etwa 1363 MB
- Das Ausschließen aller NULL-Werte aus einem Index ist nicht immer vorteilhaft
- Queries mit der Bedingung
IS NULL, die NULL-Werte suchen, können vom Indexvorteil auf NULL-Werte profitieren
- Neben Nicht-NULL-Werten lassen sich auch andere, fast nie abgefragte Werte per Partial Index ausschließen
- Die 20 GB im Titel ergaben sich daraus, dass eine Einsparung von etwa 10 GB in der primären Datenbank aufgrund der Struktur auf den Replikaten zu einer ähnlichen zusätzlichen Speicherersparnis führte
Migration zu Partial Indexes im Django ORM
-
Nullable ForeignKey und implizite Indizes
- Django erzeugt standardmäßig implizit einen B-Tree-Index für
models.ForeignKey-Felder, sofern nicht db_index=False explizit gesetzt wird
- Wenn für ein nullable
ForeignKey, das den stornierenden Nutzer speichert, nichts konfiguriert wird, erstellt Django einen vollständigen Index
- Um einen Partial Index anzulegen, muss auf dem FK-Feld
db_index=False gesetzt und in Meta.indexes ein bedingter models.Index hinzugefügt werden
class Transaction(models.Model):
cancelled_by_user = models.ForeignKey(
to=User,
null=True,
on_delete=models.CASCADE,
db_index=False,
)
class Meta:
indexes = (
models.Index(
fields=('cancelled_by_user_id', ),
name='%(class_name)s_cancelled_by_part_ix',
condition=Q(cancelled_by_user_id__isnull=False),
),
)
- Nullable Foreign Keys eignen sich oft gut als Kandidaten für Partial Indexes
- Damit keine impliziten Funktionen unbemerkt Indizes erzeugen, wurde ein Django-Check erstellt, der erzwingt, dass bei Foreign Keys
db_index immer explizit gesetzt wird
-
Vollständigen Index ohne Downtime durch Partial Index ersetzen
- Um einen vollständigen Index ohne Downtime oder Performance-Verlust durch einen Partial Index zu ersetzen, sollte schrittweise vorgegangen werden
- Das betreffende Django-Modell wird so geändert, dass der vollständige Index durch einen Partial Index ersetzt wird, die von Django erzeugte Migration wird jedoch nicht unverändert ausgeführt
- Diese Migration könnte die FK-Constraint deaktivieren, den bestehenden vollständigen Index droppen und dann den neuen Partial Index erstellen, was Downtime und Performance-Probleme verursachen kann
- Mit
./manage.py sqlmigrate wird zunächst SQL erzeugt; daraus wird nur CREATE INDEX extrahiert, zu CONCURRENTLY geändert und dann manuell in der Datenbank ausgeführt
- Solange der bestehende vollständige Index noch vorhanden ist, können Queries ihn weiter nutzen, während der Partial Index aufgebaut wird
- Um zu prüfen, ob der neue Partial Index verwendet wird, werden die Statistikzähler des vollständigen Indexes mit
pg_stat_reset_single_table_counters(<full index oid>) zurückgesetzt
- Anschließend werden in
pg_stat_all_indexes idx_scan, idx_tup_read und idx_tup_fetch beobachtet und die Nutzung von vollständigem Index und Partial Index verglichen
- Wenn feststeht, dass der Partial Index verwendet wird, wird der vollständige Index gelöscht und der tatsächlich gewonnene Speicherplatz überprüft
- Sobald der Datenbankzustand wieder dem Modellzustand entspricht, wird die Django-Migration mit
./manage.py migrate --fake als ausgeführt markiert
- In Umgebungen wie Dev, QA oder Staging, in denen Downtime weniger kritisch ist, kann die Django-Migration normal ausgeführt werden und ersetzt den vollständigen Index durch den Partial Index
Abschließende Zusammenfassung
- Allein durch Festplatten-, Storage-Parameter- oder Konfigurationsoptimierung lassen sich Performance und Speicherverbrauch nur begrenzt verbessern
- Das letzte Verbesserungspotenzial lag in den Datenbankobjekten selbst, in diesem Fall in der Indexdefinition
- Der angewandte Ablauf lässt sich auf drei Schritte verdichten
- Ungenutzte Indizes entfernen
- Tabellen und Indizes repacken und, wenn möglich, B-Tree-Deduplizierung aktivieren
- Partial Indexes verwenden, damit nur notwendige Werte indiziert werden
- Solche Bereinigungen können eine praktische Möglichkeit sein, vor der Bereitstellung zusätzlichen Storages noch ein paar Tage Spielraum zu gewinnen
1 Kommentare
Meinungen auf Hacker News
Auch wenn viel Speicherplatz vorhanden ist, ist es gut, Indizes klein zu halten. So passen mehr Daten in das Hot Set.
Aus Sicht von Betreibern von Datenbanken im TB-Bereich ist „einfach mehr Speicherplatz dranhängen“ allerdings ebenfalls immer eine gültige Option. Besonders außerhalb der Cloud gilt das; bei eigener Hardware kosten Enterprise-NVMe-SSDs etwa 80 $/TB, DDR4-RAM etwa 1,20 $/GB. Wenn man 4 Stunden Engineering grob mit 1.000 $ ansetzt, kann man dafür 800 GB RAM oder 12 TB Speicherplatz kaufen.
Allein wöchentliche Backups für ein Jahr machen aus 1 TB schon 50 TB, selbst bei Offline-Speicher. Auch bei uns im Unternehmen war es einfach, der Live-DB mehr Speicherplatz zu geben, und wir haben das jahrelang so gemacht. Aber Multi-TB-Backups zu verschieben wird zunehmend lästig, sodass wir jetzt trotz Engineering-Kosten die Produktionsdaten reduzieren müssen.
Bei einer einmaligen Aktion kann die vorhandene Infrastruktur Kosten wie freien Rack-Platz oder zusätzliche PCIe-Slots vielleicht auffangen, aber diese Art der Abschreibung funktioniert nur in kleinem Maßstab gut. Außerdem sinkt mit wachsendem System wegen höherer Verwaltungskosten und Latenzen zwangsläufig die Performance pro Geräteeinheit. Wenn man Probleme immer nur durch weiteres Wachstum des Systems löst, wird das Gesamtsystem nach und nach träger und kann schließlich nicht mehr betreibbar sein. Eine Lösung, die den benötigten Ressourcenbedarf senkt, erhöht dagegen die Performance pro Ressourceneinheit und schafft für sich genommen ein hochwertigeres Asset.
Wenn eine Optimierung heute 1 TB spart, kann sie in ein paar Jahren 2 TB sparen; schon wenige solcher Entscheidungen können mehr als eine Größenordnung Unterschied ausmachen. In größerem Maßstab gilt das besonders, und am Ende muss man immer abwägen.
„Hardware draufwerfen“ kann gelegentlich eine kluge Wahl sein, aber in den meisten Fällen wird man einen heutigen Server mit 128 GB RAM morgen kaum für 1.000 $ auf 1 TB RAM umstellen können.
Allerdings handelt es sich nicht um eine einmalige Ausgabe, sondern um monatliche Kosten, was die Rechnung etwas komplizierter macht. Wenn die Datenbank mit konstanter Geschwindigkeit wächst, kommt natürlich auch einmaliges Provisioning on-premises faktisch monatlichen Kosten nahe.
Der Artikel erwähnt die B-Tree-Deduplizierung, die es in PostgreSQL 13 gibt, aber nicht in der verwendeten Version 12, und sagt, dass der Großteil der Werte einiger Foreign-Key-Indizes NULL ist.
Ich frage mich, ob B-Tree-Deduplizierung in diesem Fall geholfen hätte. Abschnitt 63.4.2 der PostgreSQL-13-Dokumentation sieht danach aus: „B-Tree deduplication is just as effective with ‘duplicates’ that contain a NULL value...“ Natürlich wäre das nicht so effektiv wie der im Artikel verwendete partielle Index, aber interessant ist es trotzdem.
https://www.postgresql.org/docs/13/btree-implementation.html
https://news.ycombinator.com/item?id=25989467
Dieser Artikel war nützlich, weil er nicht nur die zentrale „Entdeckung“ partieller Indizes beschreibt, sondern insgesamt gute Techniken zusammenfasst, die man kennen sollte, wenn man sich Sorgen über ineffiziente Speichernutzung in PostgreSQL macht.
Für Startups oder Unternehmen in einer frühen Phase sollte man sich aber merken: Bei solchen Speicherplatzproblemen ist es fast immer besser, mehr Plattenplatz anzuhängen, statt sich mit Größenoptimierung zu beschäftigen. Entwickler sind teuer, Festplatten billig.
Als ich früher bei einem schnell wachsenden Startup gearbeitet habe, bestand einer der Gründer darauf, immer nur einen einzelnen Server weiter aufzurüsten. Redundanz und Backups waren vorhanden, aber die Architektur war einfach, sodass Verwaltung, Debugging und Wiederherstellung leicht waren. In den ersten fünf Jahren des Unternehmens lief das gesamte System auf einem einzigen Server und wuchs trotzdem exponentiell, während es weltweit Millionen Nutzer bediente. Seitdem bin ich überzeugt, dass man nur dann aufrüsten sollte, wenn es nötig ist, und dann auf die einfachste und geradlinigste Weise.
Einen partiellen Index zu verwenden, der klar zum Use Case passt, etwa wenn die meisten Werte NULL sind, ist eher korrektes Modellieren und sollte nicht als vorschnelle Optimierung oder Verschwendung von Entwicklerzeit gesehen werden.
In einem ähnlichen Zusammenhang waren diese Skripte ziemlich hilfreich:
https://github.com/NikolayS/postgres_dba
Durch Neuordnung der Spalten einer großen Tabelle konnten wir etwa 10 % des Speicherplatzes freimachen, also ungefähr 100 GB.
Letzte Woche habe ich allein mit
reindexundpg_repackaus einer 600-GB-DB 200 GB freigemacht. Bei der schlimmsten Tabelle umfassten die Daten 17 GB, die Indizes aber 142 GB; nach dem Reindexing schrumpften sie auf 21 GB.Diese Tabelle war überindiziert, mit mehreren Indizes auf verschiedene Spaltenkombinationen. Einer der Gründe für die riesigen Indizes war vermutlich die Datenverteilung. Seit 2015 gab es fortlaufend Inserts, Updates und Deletes, und je älter die Daten sind, desto wahrscheinlicher werden sie gelöscht. Dadurch gibt es mehr Daten aus den jüngeren Jahren, aber immer noch etwa 0,1 % aus dem Jahr 2015. Ich vermute, dass diese schiefe Verteilung mit langem Tail es vacuum erschwert haben könnte, die Index-Bloat zu bereinigen.
Wurde damals ebenfalls diskutiert: An unexpected find that freed 20GB of unused index space in PostgreSQL – https://news.ycombinator.com/item?id=25988871 – Februar 2021, 78 Kommentare
pganalyze.com wird ausdrücklich empfohlen, um ungenutzte Indizes, Optimierungsmöglichkeiten und Queries mit hoher Latenz zu finden
„Mit diesem einen seltsamen Trick 20 GB sparen“ war wirklich keine Übertreibung. In alten Oracle-/SQL-Server-Umgebungen bekommt man oft Anfragen, ungewöhnliche Indexformen in PostgreSQL zu verwenden, von denen man dort fast nie gehört hat; dass auch NULL-Werte indexiert werden, war mir nicht klar
Bei meinem ersten großen Projekt konnte das Team nicht nachvollziehen, warum die Arbeit langsamer wurde, als das Dataset wuchs
Bei Indizes beträgt die Einfügezeit pro Datensatz log(n). Wenn die Test-DB von 1.000 Datensätzen auf knapp 65k wächst, steigt die Einfügezeit um 60 % (2^10 gegenüber 2^16). Erfolg macht alles langsamer, und Server-Upgrades haben Grenzen. Fügt man wegen einer vagen Funktion, die das Business wollte, noch ein paar weitere Indizes hinzu, ist man nun beim Doppelten
Ich betreue auch viele DBs mit Hunderten Millionen Datensätzen und über 40 Indizes pro Tabelle/Collection
Eine binäre Suche innerhalb einer B-Tree-Seite liegt unter 100 Zyklen. Auch eine B-Tree-Suche über 100 Millionen Datensätze sollte immer noch im Mikrosekundenbereich liegen, und die darüberliegende binäre Suche ebenfalls im Mikrosekundenbereich, selbst wenn es nicht nur ein paar Hundert Nanosekunden sind
Gilt dieses Prinzip auch, wenn ein einzelner Wert oder einige wenige Werte sehr häufig vorkommen? Zum Beispiel, wenn 90 % einer Integer-Spalte 0 sind
where val = 090 % der Zeilen übrig lässt, kann es statt des Index einen Table Scan wählen