Hallo.
Ich heiße Ivan und bin Java-Entwickler. Es hat sich ergeben, dass ich viel mit PostgreSQL arbeite – ich kümmere mich um die Datenbankkonfiguration, Strukturoptimierung, Leistungssteigerung und spiele ein wenig am Wochenende DBA.
In letzter Zeit habe ich mehrere Datenbanken in unseren Mikrodiensten optimiert und eine Java-Bibliothek geschrieben , die diese Arbeit erleichtert, meine Zeit spart und hilft, einige typische Fehler zu vermeiden, die von Entwicklern gemacht werden. Genau um 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 Version 11 getestet. Die minimal unterstützte Version ist 9.6.
Hintergrund
Es begann vor fast einem Jahr mit einer für mich seltsamen Situation: Das konkurrierende Erstellen eines Indexes endete unerwartet mit einem Fehler. Der Index selbst blieb, wie es oft der Fall ist, in einem ungültigen Zustand in der Datenbank. Eine Analyse der Protokolle zeigte einen Mangel an . Und das war erst der Anfang… Als ich tiefer gruben, entdeckte ich eine ganze Reihe von Problemen in der Datenbankkonfiguration und krempelte die Ärmel hoch, um sie mit leuchtenden Augen zu beheben.
Das erste Problem – die Standardkonfiguration
Zugegeben, die Metapher, dass man Postgres auf einer Kaffeemaschine betreiben kann, ist möglicherweise abgedroschen, aber… die Standardkonfiguration wirft tatsächlich einige Fragen auf. Zumindest sollte man auf Folgendes achten: maintenance_work_mem, temp_file_limit, statement_timeout und lock_timeout.
In unserem Fall maintenance_work_mem lag standardmäßig bei 64 Megabyte, während temp_file_limit wir eigentlich etwa 2 Gigabyte benötigten – wir hatten einfach nicht genügend Speicher, um einen Index auf einer großen Tabelle zu erstellen.
Deshalb habe ich pg-index-health eine Reihe , die meiner Meinung nach für jede Datenbank angepasst werden sollten.
Das zweite Problem – doppelte Indizes
Unsere Datenbanken befinden sich auf SSDs, und wir nutzen HAeine Konfiguration mit mehreren Rechenzentren, einem Master-Host und neiner entsprechenden Anzahl von Replikaten. Speicherplatz auf der Festplatte ist für uns eine äußerst wertvolle Ressource; er ist ebenso wichtig wie Leistung und CPU-Verbrauch. Deshalb benötigen wir einerseits Indizes für schnelle Lesevorgänge, möchten andererseits aber keine überflüssigen Indizes in der Datenbank sehen, da diese Speicherplatz verbrauchen und die Aktualisierung von Daten verlangsamen.
Und nachdem wir alle wiederhergestellt haben und , ich beschloss, eine „große“ Bereinigung vorzunehmen. Es stellte sich heraus, dass Entwickler die Dokumentation zur Datenbank überhaupt nicht gerne lesen. Dies führt zu zwei typischen Fehlern – einem manuell erstellten Index auf dem Primärschlüssel und einem ähnlichen „manuellen“ Index auf einer eindeutigen Spalte. Das Problem ist, dass diese nicht nötig sind – Postgres erledigt alles selbst. Solche Indizes können ohne Bedenken gelöscht werden, und dafür gibt es eine Diagnose. .
Das dritte Problem – überlappende Indizes.
Die meisten unerfahrenen Entwickler erstellen Indizes für eine einzige Spalte. Mit der Zeit, nachdem sie Gefallen daran gefunden haben, beginnen die Leute, ihre Abfragen zu optimieren und komplexere Indizes zu erstellen, die mehrere Spalten enthalten. So entstehen Indizes für die Spalten A, A+B, A+B+C usw. Die ersten beiden dieser Indizes können bedenkenlos entfernt werden, da sie Präfixe des dritten sind. Das spart ebenfalls erheblich Platz auf der Festplatte, und dafür gibt es eine Diagnose. .
Das vierte Problem – Fremdschlüssel ohne Indizes.
Postgres ermöglicht das Erstellen von Einschränkungen für Fremdschlüssel, ohne einen unterstützenden Index anzugeben. In vielen Situationen ist das kein Problem und äußert sich sogar erst spät...
So erging es uns: Irgendwann begann der Job, der nach Zeitplan lief und die Datenbank von Testbestellungen befreite, unsere Hauptinstanz zu überlasten. CPU- und IO-Ressourcen waren überlastet, die Anfragen stockten und liefen aufgrund von Timeouts ins Leere, der Dienst lieferte Fehler 500. Eine schnelle Analyse zeigte, dass die Anfragen wie folgt hingen:
löschen von <table> wo id in (…)Dabei war der Index nach id in der Zieltabelle natürlich vorhanden, und es wurden nur sehr wenige Datensätze entfernt. Es schien, als müsste alles funktionieren, aber leider tat es das nicht.
Ein wunderbares explain analyze half uns, denn es stellte fest, dass neben der Entfernung von Datensätzen in der Zieltabelle auch die Überprüfung der referenziellen Integrität stattfand, wobei diese Überprüfung auf einer der verknüpften Tabellen in einen sequential scan wegen fehlender geeigneter Indizes fiel. So entstand die Diagnose .
Das fünfte Problem – null-Werte in Indizes.
Standardmäßig inkludiert Postgres Nullwerte in Btree-Indizes, aber in der Regel sind sie dort nicht erforderlich. Daher bemühe ich mich, diese Nullwerte zu entfernen (Diagnose ), indem ich partielle Indizes auf nullable-Spalten erstelle, wie etwa where is not null. Auf diese Weise konnte ich die Größe eines unserer Indizes von 1877 MB auf 16 kB reduzieren. In einem unserer Dienste verringerte sich die Gesamtgröße der Datenbank um 16 % (in absoluten Zahlen um 4,3 GB), indem ich Nullwerte aus den Indizes ausschloss. Eine enorme Einsparung an Speicherplatz durch relativ einfache Anpassungen. 🙂
Problem sechs – Fehlen von Primärschlüsseln
Aufgrund der Eigenheiten des Mechanismus kann es zu einer Situation kommen, in der die Größe Ihrer Tabelle schnell aufgrund einer großen Anzahl von toten Datensätzen wächst. Ich nahm naiverweise an, dass uns das nicht betreffen würde und dass mit unserer Datenbank so etwas nicht passiert, schließlich sind wir, wow!!!, normale Entwickler… Wie dumm und naiv ich war…
Eines schönen Tages führte eine wunderbare Migration dazu, dass alle Einträge in einer großen, aktiv genutzten Tabelle aktualisiert wurden. Wir erhielten plötzlich +100 GB an zusätzlichem Platz für die Tabelle. Es war äußerst frustrierend, aber unsere Probleme hörten damit nicht auf. Nach 15 Stunden war der Autovacuum-Prozess für diese Tabelle abgeschlossen, und es wurde klar, dass der physische Platz nicht zurückkehren würde. Den Dienst anzuhalten und ein VACUUM FULL durchzuführen, war nicht möglich, daher entschieden wir uns für . Und hier stellte sich heraus, dass pg_repack nicht in der Lage ist, Tabellen ohne Primärschlüssel oder andere eindeutige Einschränkungen zu verarbeiten, und unsere Tabelle hatte keinen Primärschlüssel. So entstand die Diagnose .
In der Version der Bibliothek 0.1.5 wurde die Möglichkeit hinzugefügt, Daten über die Bloat von Tabellen und Indizes zu sammeln und entsprechend darauf zu reagieren.
Die Probleme sieben und acht – Mangel an Indizes und ungenutzte Indizes
Die nächsten beiden Diagnosen – und – erschienen in ihrer endgültigen Form relativ neu. Der Grund dafür ist, dass sie nicht einfach hinzugefügt werden konnten.
Wie ich bereits erwähnt habe, verwenden wir eine Konfiguration mit mehreren Replikaten, und die Lese-Last auf verschiedenen Hosts ist grundsätzlich unterschiedlich. Infolgedessen ergibt sich eine Situation, in der einige Tabellen und Indizes auf bestimmten Hosts praktisch nicht verwendet werden, und um Analysen durchzuführen, müssen wir Statistiken von allen Hosts im Cluster sammeln. muss ebenfalls auf jedem Host im Cluster erfolgen, es ist nicht möglich, dies nur auf dem Master zu tun.
Dieser Ansatz hat es uns ermöglicht, mehrere Dutzend Gigabyte durch das Entfernen von Indizes zu sparen, die nie verwendet wurden, und zusätzlich fehlende Indizes für selten verwendete Tabellen hinzuzufügen.
Zusammenfassend
Natürlich kann für nahezu alle Diagnosen konfiguriert werden. So können Sie schnell Prüfungen in Ihrer Anwendung implementieren, um das Auftreten neuer Fehler zu verhindern, und dann schrittweise alte Fehler beheben.
Ein Teil der Diagnosen kann bereits in Funktionstest durchgeführt werden, sofort nach dem Einspielen der Datenbankmigrationen. Und das ist wahrscheinlich eine der stärksten Funktionen meiner Bibliothek. Ein Beispiel für die Nutzung finden Sie in .
Überprüfungen auf ungenutzte oder fehlende Indizes sowie auf Bloat sollten sinnvollerweise nur auf einer realen Datenbank durchgeführt werden. Die gesammelten Werte können in 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
