Hey.
Mein Name ist Vanya und ich bin Java-Entwicklerin. Zufälligerweise arbeite ich viel mit PostgreSQL – ich baue die Datenbank auf, optimiere die Struktur, die Leistung und spiele an den Wochenenden ein wenig DBA.
Kürzlich habe ich mehrere Datenbanken in unseren Microservices aufgeräumt und eine Java-Bibliothek geschrieben , was diese Arbeit erleichtert, mir Zeit spart und mir hilft, einige häufige Fehler von Entwicklern zu vermeiden. Über diese Bibliothek werden wir heute sprechen.
Haftungsausschluss
Die Hauptversion von PostgreSQL, mit der ich arbeite, ist 10. Alle von mir verwendeten SQL-Abfragen sind auch auf Version 11 getestet. Die unterstützte Mindestversion ist 9.6.
Vorgeschichte
Alles begann vor fast einem Jahr mit einer für mich seltsamen Situation: Die kompetitive Erstellung eines Index aus heiterem Himmel endete mit einem Fehler. Der Index selbst verblieb wie üblich in einem ungültigen Zustand in der Datenbank. Die Protokollanalyse ergab einen Mangel . Und los geht's... Als ich tiefer nachforschte, entdeckte ich eine ganze Reihe von Problemen in der Datenbankkonfiguration und krempelte die Ärmel hoch und begann mit einem Funkeln in den Augen, sie zu beheben.
Problem eins – Standardkonfiguration
Wahrscheinlich hat jeder die Metapher über Postgres, das auf einer Kaffeemaschine ausgeführt werden kann, ziemlich satt, aber ... die Standardkonfiguration wirft tatsächlich eine Reihe von Fragen auf. Zumindest lohnt es sich, darauf zu achten wartung_arbeit_mem, temp_file_limit, Anweisung_Timeout и lock_timeout.
In unserem Fall, wartung_arbeit_mem war der Standardwert 64 MB und temp_file_limit etwa 2 GB – wir hatten einfach nicht genug Speicher, um einen Index für eine große Tabelle zu erstellen.
Daher in pg-index-health Ich habe eine Serie gesammelt Meiner Meinung nach die Parameter, die für jede Datenbank konfiguriert werden sollten.
Problem zwei – doppelte Indizes
Unsere Datenbanken befinden sich auf SSD-Laufwerken und wir verwenden HA-Konfiguration mit mehreren Rechenzentren, Master-Host und n-Anzahl der Replikate. Speicherplatz ist für uns eine sehr wertvolle Ressource. es ist nicht weniger wichtig als Leistung und CPU-Verbrauch. Daher benötigen wir einerseits Indizes zum schnellen Lesen und andererseits möchten wir keine unnötigen Indizes in der Datenbank sehen, da diese Speicherplatz verschlingen und die Datenaktualisierung verlangsamen.
Und jetzt, nachdem ich alles wiederhergestellt habe und genug gesehen zu haben Ich beschloss, eine „große“ Säuberung zu organisieren. Es stellte sich heraus, dass Entwickler Datenbankdokumentationen nicht gerne lesen. Es gefällt ihnen nicht besonders. Aus diesem Grund treten zwei typische Fehler auf: ein manuell erstellter Index für einen Primärschlüssel und ein ähnlicher „manueller“ Index für eine eindeutige Spalte. Tatsache ist, dass sie nicht benötigt werden – Postgres erledigt alles selbst. Solche Indizes können sicher gelöscht werden, und zu diesem Zweck wurden Diagnosen erstellt .
Problem drei – sich überschneidende Indizes
Die meisten unerfahrenen Entwickler erstellen Indizes für eine einzelne Spalte. Nach und nach beginnen die Leute, nachdem sie dieses Geschäft gründlich kennengelernt haben, ihre Abfragen zu optimieren und komplexere Indizes hinzuzufügen, die mehrere Spalten umfassen. So werden Indizes für Spalten angezeigt A, A + B, A + B + C usw. Die ersten beiden dieser Indizes können getrost weggelassen werden, da sie Präfixe des dritten sind. Dadurch wird auch viel Speicherplatz gespart und es gibt hierfür eine Diagnose .
Problem vier – Fremdschlüssel ohne Indizes
Mit Postgres können Sie Fremdschlüsseleinschränkungen erstellen, ohne einen Hintergrundindex anzugeben. In vielen Situationen stellt dies kein Problem dar und manifestiert sich möglicherweise gar nicht ... Vorerst ...
Bei uns war es genauso: Es ist nur so, dass irgendwann ein Job, der nach einem Zeitplan läuft und die Datenbank von Testaufträgen löscht, vom Master-Host zu uns „hinzugefügt“ wurde. CPU und E/A gingen verloren, Anfragen verlangsamten sich und es kam zu Zeitüberschreitungen, der Dienst lag bei fünfhundert. Schnelle Analyse zeigte, dass Abfragen wie:
delete from <table> where id in (…)In diesem Fall gab es natürlich einen Index nach ID in der Zieltabelle, und entsprechend der Bedingung wurden nur sehr wenige Datensätze gelöscht. Es schien, als ob alles funktionieren sollte, aber leider funktionierte es nicht.
Der Wunderbare kam zur Rettung erklären, analysieren und sagte, dass es zusätzlich zum Löschen von Datensätzen in der Zieltabelle auch eine referenzielle Integritätsprüfung gibt und diese Prüfung bei einer der zugehörigen Tabellen fehlschlägt sequenzieller Scan aufgrund des Fehlens eines geeigneten Indexes. Damit war die Diagnostik geboren .
Problem fünf – Nullwert in Indizes
Standardmäßig fügt Postgres Nullwerte in Btree-Indizes ein, sie werden dort jedoch normalerweise nicht benötigt. Deshalb versuche ich fleißig, diese Nullen (Diagnose) wegzuwerfen ), wodurch Teilindizes für nullfähige Spalten nach Typ erstellt werden where <A> is not null. Auf diese Weise konnte ich die Größe eines unserer Indizes von 1877 MB auf 16 KB reduzieren. Und bei einem der Dienste verringerte sich die Datenbankgröße aufgrund des Ausschlusses von Nullwerten aus den Indizes insgesamt um 16 % (um 4.3 GB in absoluten Zahlen). Enorme Einsparungen an Speicherplatz durch sehr einfache Änderungen. 🙂
Problem sechs – Mangel an Primärschlüsseln
Aufgrund der Art des Mechanismus Eine solche Situation ist möglich wenn die Größe Ihrer Tabelle aufgrund einer großen Anzahl toter Datensätze schnell zunimmt. Ich habe naiv geglaubt, dass uns das nicht gefährden würde und dass das unserer Basis nicht passieren würde, weil wir, wow!!!, normale Entwickler sind ... Wie dumm und naiv ich war ...
Eines Tages wurden bei einer wunderbaren Migration alle Datensätze in einer großen und aktiv genutzten Tabelle übernommen und aktualisiert. Wir haben aus heiterem Himmel +100 GB zur Tabellengröße bekommen. Es war verdammt schade, aber damit waren unsere Missgeschicke noch nicht zu Ende. Nachdem das Autovakuum auf diesem Tisch 15 Stunden später endete, wurde klar, dass der physische Standort nicht zurückkehren würde. Wir konnten den Dienst nicht stoppen und VACUUM FULL machen, also entschieden wir uns für die Nutzung . Und dann stellte sich heraus, dass pg_repack weiß nicht, wie Tabellen ohne Primärschlüssel oder andere Eindeutigkeitsbeschränkungen verarbeitet werden sollen, und unsere Tabelle hatte keinen Primärschlüssel. Damit war die Diagnostik geboren .
In der Bibliotheksversion 0.1.5 Es wurde die Möglichkeit hinzugefügt, Daten aus einer Fülle von Tabellen und Indizes zu sammeln und zeitnah darauf zu reagieren.
Probleme sieben und acht – unzureichende Indizes und nicht verwendete Indizes
Die folgenden zwei Diagnosen sind: и – sind erst vor relativ kurzer Zeit in ihrer endgültigen Form erschienen. Der Punkt ist, dass sie nicht einfach genommen und hinzugefügt werden konnten.
Wie ich bereits geschrieben habe, verwenden wir eine Konfiguration mit mehreren Replikaten und die Leselast auf verschiedenen Hosts ist grundsätzlich unterschiedlich. Infolgedessen stellt sich heraus, dass einige Tabellen und Indizes auf einigen Hosts praktisch nicht verwendet werden und Sie zur Analyse Statistiken von allen Hosts im Cluster sammeln müssen. Dies ist auch auf jedem Host im Cluster erforderlich; Sie können dies nicht nur auf dem Master tun.
Dieser Ansatz ermöglichte es uns, mehrere Dutzend Gigabyte einzusparen, indem wir nie verwendete Indizes entfernten und fehlende Indizes zu selten verwendeten Tabellen hinzufügten.
Als eine Schlussfolgerung
Natürlich können Sie für fast alle Diagnosen konfigurieren . Auf diese Weise können Sie schnell Prüfungen in Ihrer Anwendung implementieren, um das Auftreten neuer Fehler zu verhindern und alte nach und nach beheben.
Einige Diagnosen können in Funktionstests unmittelbar nach der Einführung von Datenbankmigrationen durchgeführt werden. Und das ist vielleicht eine der leistungsstärksten Funktionen meiner Bibliothek. Ein Anwendungsbeispiel finden Sie in .
Es ist sinnvoll, Prüfungen auf ungenutzte oder fehlende Indizes sowie auf Aufblähungen nur für eine echte Datenbank durchzuführen. Die gesammelten Werte können in erfasst werden oder an das Überwachungssystem gesendet werden.
Das hoffe ich wirklich pg-index-health wird nützlich und gefragt sein. Sie können auch zur Entwicklung der Bibliothek beitragen, indem Sie gefundene Probleme melden und neue Diagnosen vorschlagen.
Source: habr.com