Hallo.
Ich heiße Wanja und bin Java-Entwickler. Es hat sich so ergeben, dass ich viel mit PostgreSQL arbeite – ich kümmere mich um die Datenbankkonfiguration, die Struktur, die Leistung und spiele ein wenig am Wochenende DBA.
In letzter Zeit habe ich mehrere Datenbanken in unseren Mikrodiensten in Ordnung gebracht und eine Java-Bibliothek geschrieben , die diese Arbeit erleichtert, meine Zeit spart und hilft, einige typische Fehler, die Entwickler machen, zu vermeiden. Genau über diese Bibliothek wird es heute gehen.
Haftungsausschluss
Die Hauptversion von PostgreSQL, mit der ich arbeite, ist die 10. Alle von mir verwendeten SQL-Abfragen wurden auch auf der 11. Version getestet. Die minimal unterstützte Version ist 9.6.
Vorgeschichte
Vor etwa einem Jahr begann alles mit einer für mich seltsamen Situation: die gleichzeitige Erstellung eines Indexes an einem leeren Ort endete mit einem Fehler. Der Index blieb, wie es nun mal ist, im ungültigen Zustand in der Datenbank. Die Analyse der Protokolle zeigte einen Mangel an . Und dann ging es los... Als ich tiefer grub, entdeckte ich eine ganze Reihe von Problemen in der Datenbankkonfiguration und packte, mit einem Funkeln in den Augen, voller Elan an, diese zu beheben.
Das erste Problem – die Standardkonfiguration
Wahrscheinlich ist die Metapher über Postgres, das man auf einer Kaffeemaschine ausführen kann, den meisten schon ziemlich leid, aber... die Standardkonfiguration wirft tatsächlich einige Fragen auf. Zumindest sollte man auf maintenance_work_mem, temp_file_limit, statement_timeout und lock_timeout.
achten. In unserem Fall maintenance_work_mem betrug der Standardwert 64 MB, während temp_file_limit wir etwa 2 GB benötigten – uns fehlte einfach der Speicher, um einen Index auf einer großen Tabelle zu erstellen.
Deshalb habe ich in pg-index-health eine Reihe von , die, meiner Meinung nach, für jede Datenbank angepasst werden sollten, zusammengestellt.
Das zweite Problem – doppelte Indizes
Unsere Datenbanken laufen auf SSD-Laufwerken und wir verwenden eine HA-Konfiguration mit mehreren Rechenzentren, einem Master-Host und n-irgendwie vielen Replikaten. Der Platz auf der Festplatte ist für uns eine sehr wertvolle Ressource; er ist nicht weniger wichtig als die Leistung und der CPU-Verbrauch. Daher brauchen wir auf der einen Seite Indizes für schnelles Lesen, auf der anderen Seite wollen wir keine überflüssigen Indizes in der Datenbank sehen, da sie Platz verbrauchen und die Datenaktualisierung verlangsamen.
Und so, nachdem ich alle wiederhergestellt habe und mir die , ich habe mich entschieden, eine „große“ Bereinigung durchzuführen. Es stellte sich heraus, dass die Entwickler die Dokumentation zur Datenbank nicht gerne lesen. Sehr ungern. Dadurch entstehen zwei typische Fehler – manuell erstellter Index auf dem Primärschlüssel und ein ähnlicher „manueller“ Index auf einer einzigartigen Spalte. Das Problem ist, dass diese nicht notwendig sind – Postgres erledigt alles selbst. Solche Indizes können bedenkenlos gelöscht werden, und dafür gibt es eine Diagnose. .
Drittes Problem – sich überschneidende Indizes.
Die meisten Anfänger-Entwickler erstellen Indizes für eine einzelne Spalte. Mit der Zeit, wenn sie den Geschmack dafür entwickeln, fangen die Leute an, ihre Abfragen zu optimieren und komplexere Indizes hinzuzufügen, die mehrere Spalten enthalten. So entstehen Indizes für Spalten. A, A+B, A+B+C usw. Die ersten beiden dieser Indizes können bedenkenlos verworfen werden, da sie Präfixe des dritten sind. Das spart auch ordentlich Platz auf der Festplatte und dafür gibt es eine Diagnose. .
Viertes Problem – Fremdschlüssel ohne Indizes.
Postgres erlaubt es, Einschränkungen von Fremdschlüsseln ohne Angabe eines unterstützenden Index zu erstellen. In vielen Situationen ist das kein Problem und zeigt sich sogar gar nicht ... bis zu einem gewissen Zeitpunkt ...
So war es auch bei uns: einfach in einem bestimmten Moment blieb der Job, der nach Zeitplan läuft und die Datenbank von Testbestellungen bereinigt, bei uns hängen. CPU und IO gingen durch die Decke, Abfragen wurden langsamer und brachen wegen Zeitüberschreitung ab, der Service zeigte Fehler 500. Eine schnelle Analyse. zeigte, dass Abfragen der Art hingen:
löschen von <table> wo id in (…)In der Ziel-Tabelle war natürlich ein Index nach id vorhanden, und die Datensätze wurden unter der Bedingung nur sehr wenig gelöscht. Es schien, als sollte alles funktionieren, aber leider tat es das nicht.
Zur Hilfe kam ein wunderbares explain analyze und erklärte, dass neben dem Löschen von Datensätzen in der Ziel-Tabelle auch die Überprüfung der referenziellen Integrität durchgeführt wird, und bei einer der verknüpften Tabellen fällt diese Überprüfung in einen sequentiellen Scan aufgrund des Fehlens eines geeigneten Index. So entstand die Diagnose. .
Fünftes Problem – null-Werte in Indizes.
Standardmäßig schließt Postgres null-Werte in btree-Indizes ein, aber in der Regel sind sie dort nicht nötig. Daher bemühe ich mich, diese null-Werte zu eliminieren (Diagnose ), indem ich partielle Indizes für nullable-Spalten vom Typ where is not null. Auf diese Weise gelang es mir, die Größe eines unserer Indizes von 1877 MB auf 16 KB zu reduzieren. In einem unserer Dienste verringerte sich die Gesamtgröße der DB um 16 % (um 4,3 GB in absoluten Zahlen), indem Nullwerte aus den Indizes entfernt wurden. Eine enorme Einsparung an Speicherplatz bei relativ einfachen Anpassungen. 🙂
Problem sechs – Fehlen von Primärschlüsseln
Aufgrund der Besonderheiten des Mechanismus kann eine solche Situation entstehen wie , wenn die Größe Ihrer Tabelle schnell aufgrund der Vielzahl von toten Datensätzen wächst. Ich nahm naiv an, dass uns das nicht betreffen würde und dass so etwas mit unserer Datenbank nicht passieren würde, denn wir sind, wow!!!, schließlich normale Entwickler… Wie dumm und naiv ich war…
Eines Tages nahm eine wunderbare Migration alle Datensätze in einer großen und aktiv genutzten Tabelle und aktualisierte sie. Wir erhielten +100 GB an Tabellengröße aus dem Nichts. Es war verdammt ärgerlich, aber unser Unglück endete damit nicht. Nachdem nach 15 Stunden der Autovacuum für diese Tabelle abgeschlossen war, wurde klar, dass der physische Speicherplatz nicht zurückkehren würde. Wir konnten den Dienst nicht stoppen und ein VACUUM FULL durchführen, daher wurde beschlossen, . Und dann stellte sich heraus, dass pg_repack nicht mit Tabellen ohne Primärschlüssel oder anderer Eindeutigkeitsbeschränkung umgehen kann, und in unserer Tabelle gab es keinen Primärschlüssel. So entstand die Diagnose .
In der Version der Bibliothek 0.1.5 wurde die Möglichkeit hinzugefügt, Daten zu den Bloat von Tabellen und Indizes zu sammeln und rechtzeitig darauf zu reagieren.
Probleme sieben und acht – Mangel an Indizes und ungenutzte Indizes
Die nächsten beiden Diagnosen sind und – in ihrer endgültigen Form sind sie relativ neu aufgetaucht. Der Grund ist, dass man sie nicht einfach so hinzufügen konnte.
Wie ich bereits schrieb, verwenden wir eine Konfiguration mit mehreren Replikaten, und die Leseauslastung auf verschiedenen Hosts ist prinzipiell unterschiedlich. Infolgedessen ergibt sich die Situation, dass bestimmte Tabellen und Indizes auf bestimmten Hosts praktisch nicht genutzt werden und für die Analyse Statistiken von allen Hosts im Cluster gesammelt werden müssen. auch auf jedem Host im Cluster zurückgesetzt werden, es kann nicht nur auf dem Master erfolgen.
Dieser Ansatz hat es uns ermöglicht, mehrere Dutzend Gigabyte zu sparen, indem wir nicht verwendete Indizes entfernt und fehlende Indizes für selten genutzte Tabellen hinzugefügt haben.
Als Fazit
Natürlich kann praktisch für alle Diagnosen eingerichtet werden. So können Sie schnell Überprüfungen in Ihrer Anwendung implementieren, um das Auftreten neuer Fehler zu verhindern, und dann schrittweise alte Fehler beheben.
Einige Diagnosen können bereits bei funktionalen Tests direkt nach dem Einspielen der DB-Migrationen durchgeführt werden. Und das ist wahrscheinlich eine der mächtigsten Funktionen meiner Bibliothek. Ein Anwendungsbeispiel finden Sie in .
Überprüfungen auf nicht verwendete oder fehlende Indizes sowie auf Bloat sollten nur in einer echten DB durchgeführt werden. Die gesammelten Werte können in geschrieben oder an ein Überwachungssystem gesendet werden.
Ich hoffe sehr, dass pg-index-health nützlich und gefragt sein wird. Sie können auch zur Weiterentwicklung der Bibliothek beitragen, indem Sie auf entdeckte Probleme hinweisen und neue Diagnosen vorschlagen.
Quelle: habr.com
