🥇Nutzung von Partitionierung in MySQL für Zabbix mit einer großen Anzahl an überwachten Objekten

Zur Überwachung von Servern und Diensten nutzen wir seit langem und nach wie vor erfolgreich eine kombinierte Lösung auf der Basis von Nagios und Munin. Diese Kombination hat jedoch einige Nachteile, weshalb wir, wie viele andere, aktiv Zabbix. In diesem Artikel berichten wir darüber, wie man mit minimalem Aufwand das Leistungsproblem bei einer steigenden Anzahl von abgerufenen Metriken und dem Wachstum von MySQL-Datenbanken lösen kann.

Probleme bei der Nutzung der MySQL-Datenbank zusammen mit Zabbix

Solange die Datenbank klein war und die Anzahl der darin gespeicherten Metriken gering, verlief alles hervorragend. Der standardmäßige Housekeeper-Prozess, der vom Zabbix-Server gestartet wird, entfernte erfolgreich veraltete Einträge aus der Datenbank, ohne sie wachsen zu lassen. Doch als die Anzahl der abgerufenen Metriken zunahm und das Volumen der Datenbank eine bestimmte Größe erreichte, wurde alles schlechter. Der Housekeeper konnte die Daten nicht mehr innerhalb des ihm zugewiesenen Zeitrahmens löschen, sodass alte Daten in der Datenbank blieben. Während des Betriebs des Housekeepers kam es zu einer erhöhten Belastung des Zabbix-Servers, die längere Zeit bestehen blieb. Es wurde klar, dass diese Situation irgendwie gelöst werden musste.

Dies ist ein bekanntes Problem, praktisch jeder, der mit großen Mengen an Monitoring in Zabbix gearbeitet hat, ist damit konfrontiert worden. Es gab auch einige Lösungen: zum Beispiel der Wechsel von MySQL zu PostgreSQL oder sogar Elasticsearch, aber die einfachste und bewährteste Lösung war der Übergang zur Partitionierung der Tabellen, die die Metrikdaten in der MySQL-Datenbank speichern. Wir haben uns entschieden, genau diesen Weg zu gehen.

Der Übergang von normalen MySQL-Tabellen zu partitionierten Tabellen

Zabbix ist gut dokumentiert und die Tabellen, in denen er die Metriken speichert, sind bekannt. Es handelt sich um die Tabellen: history, in denen Float-Werte gespeichert werden, history_str, in denen kurze String-Werte gespeichert werden, history_text, in denen lange Textwerte gespeichert sind und history_uint, in denen ganzzahlige Werte gespeichert werden. Es gibt auch die Tabelle trends, die die Dynamik der Änderungen speichert, aber diese haben wir beschlossen, nicht zu berühren, da ihre Größe gering ist und wir später darauf zurückkommen werden.

Im Allgemeinen war klar, welche Tabellen verarbeitet werden mussten. Wir entschieden uns, Partionen für jede Woche zu erstellen, mit Ausnahme der letzten, basierend auf den Tageszahlen des Monats, d.h. vier Partitionen pro Monat: vom 1. bis zum 7., vom 8. bis zum 14., vom 15. bis zum 21. und vom 22. bis zum 1. (des nächsten Monats). Die Schwierigkeit bestand darin, die benötigen Tabellen „on-the-fly“ in partitionierte zu verwandeln, ohne den Zabbix-Server und die Metrik-Sammlung zu unterbrechen.

Komischerweise kam uns dabei die Datenstruktur der Tabellen zugute. Zum Beispiel hat die Tabelle history die folgende Struktur:

`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',

wobei

KEY `history_1` (`itemid`,`clock`)

Wie wir sehen können, wird jede Metrik letztendlich in einer Tabelle mit zwei sehr wichtigen und für uns praktischen Feldern itemid und clockeingetragen. Somit können wir durchaus eine temporäre Tabelle mit dem Namen history_tmp, einrichten und dafür das Partitionieren konfigurieren und anschließend alle Daten aus der Tabelle historydorthin übertragen, und dann die Tabelle history in history_oldumbenennen und die Tabelle history_tmp in history, nach der wir dann die noch nicht übertragenen Daten aus history_old in history nachtragen und history_oldlöschen. Das kann vollkommen sicher geschehen, wir verlieren nichts, denn die oben genannten Felder itemid und clock stellen die Bindung einer bestimmten Metrik an einen bestimmten Zeitpunkt sicher, nicht an eine fortlaufende Nummer.

Der gesamte Übergangsprozess

Achtung! Es ist sehr empfehlenswert, vor Beginn irgendwelcher Maßnahmen eine vollständige Datensicherung der Datenbank zu erstellen. Wir sind schließlich alle Menschen und können einen Fehler bei der Eingabe der Befehle machen, was zu Datenverlust führen könnte. Ja, eine Datensicherung garantiert nicht die maximale Aktualität, aber es ist besser, eine solche zu haben, als gar keine.

Also schalten wir nichts ab und halten nichts an. Wichtig ist, dass auf dem MySQL-Server ausreichend Freiraum auf der Festplatte vorhanden ist, d.h. dass für jede der oben genannten Tabellen history, history_text, history_str, history_uintmindestens genug Platz für die Erstellung einer Tabelle mit dem Suffix „_tmp“ zur Verfügung steht, wobei zu berücksichtigen ist, dass diese denselben Umfang wie die ursprüngliche Tabelle haben wird.

Wir werden nicht alles mehrmals für jede der oben genannten Tabellen beschreiben und betrachten alles nur am Beispiel einer von ihnen — der Tabelle history.

Also erstellen wir eine leere Tabelle history_tmp basierend auf der Struktur der Tabelle history.

CREATE TABLE `history_tmp` LIKE `history`;

Wir erstellen die benötigten Partitionen. Als Beispiel werden wir dies für einen Monat tun. Jede Partition wird auf der Grundlage einer Partitionierungsregel erstellt, die auf dem Wert des Feldes basiert clock, den wir mit dem Zeitstempel vergleichen:

ALTER TABLE `history_tmp` PARTITION BY RANGE( clock ) (
PARTITION p20190201 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-01 00:00:00")),
PARTITION p20190207 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-07 00:00:00")),
PARTITION p20190214 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-14 00:00:00")),
PARTITION p20190221 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-21 00:00:00")),
PARTITION p20190301 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-01 00:00:00"))
);

Dieser Operator fügt die Partitionierung für die von uns erstellte Tabelle hinzu history_tmp. Lassen Sie uns präzisieren, dass die Daten, bei denen der Wert des Feldes clock weniger als "2019-02-01 00:00:00" ist, in die Partition p20190201, dann die Daten, bei denen der Wert des Feldes clock größer als "2019-02-01 00:00:00" aber weniger als "2019-02-07 00:00:00" sind, in die Partition p20190207 und so weiter.

Wichtiger Hinweis: Was passiert, wenn wir in der partitionierten Tabelle Daten haben, bei denen der Wert des Feldes clock größer oder gleich "2019-03-01 00:00:00" ist? Da es für diese Daten keine passende Partition gibt, werden sie nicht in die Tabelle aufgenommen und gehen verloren. Daher müssen Sie daran denken, rechtzeitig zusätzliche Partitionen zu erstellen, um solche Datenverluste zu vermeiden (wie unten beschrieben).

Nun, die temporäre Tabelle ist vorbereitet. Lassen Sie uns die Daten einpflegen. Der Prozess kann eine beträchtliche Zeit in Anspruch nehmen, aber glücklicherweise blockiert er keine anderen Abfragen, sodass man einfach Geduld haben muss:

INSERT IGNORE INTO `history_tmp` SELECT * FROM history;

Das Schlüsselwort IGNORE ist beim ersten Einpflegen nicht zwingend erforderlich, da sich ohnehin keine Daten in der Tabelle befinden, es wird jedoch benötigt, wenn Sie nachträgliche Daten hinzufügen. Außerdem kann es nützlich sein, wenn Sie während des Datenimports den Prozess abbrechen und neu starten mussten.

Also, nach einer gewissen Zeit (vielleicht sogar mehreren Stunden) ist der erste Datenimport erfolgreich abgeschlossen. Wie Sie verstehen, enthält die Tabelle jetzt history_tmp nicht alle Daten aus der Tabelle history, sondern nur die, die zu dem Zeitpunkt, als die Abfrage ausgeführt wurde, vorhanden waren. Hier haben Sie tatsächlich die Wahl: entweder wir machen einen weiteren Durchgang (wenn der Import lange gedauert hat), oder wir wechseln sofort zur Umbenennung der Tabellen, von der weiter oben die Rede war. Lassen Sie uns zuerst über den zweiten Durchgang sprechen. Zunächst müssen wir den Zeitpunkt des letzten eingefügten Datensatzes in history_tmp:

SELECT max(clock) FROM history_tmp;

Angenommen, Sie haben erhalten: 1551045645Jetzt verwenden wir den erhaltenen Wert im zweiten Durchgang der Dateneingabe:

INSERT IGNORE INTO `history_tmp` SELECT * FROM history WHERE clock>=1551045645;

Dieser Durchgang sollte wesentlich schneller abgeschlossen werden. Aber wenn der erste Durchgang Stunden gedauert hat und der zweite ebenfalls lange dauert, könnte es sinnvoll sein, einen dritten Durchgang zu machen, der genau wie der zweite ausgeführt wird.

Am Ende führen wir erneut die Abfrage der Zeit der letzten eingefügten Datensätze in history_tmp, durch:

SELECT max(clock) FROM history_tmp;

Angenommen, Sie haben erhalten 1551085645. Speichern Sie diesen Wert - wir werden ihn für das Nachfüllen benötigen.

Und jetzt, nachdem die erste Dateneingabe in history_tmp abgeschlossen ist, beginnen wir mit der Umbenennung der Tabellen:

BEGIN;
RENAME TABLE history TO history_old;
RENAME TABLE history_tmp TO history;
COMMIT;

Wir haben diesen Block als eine Transaktion gestaltet, um den Moment der Datenaufnahme in eine nicht existierende Tabelle zu vermeiden, da die Tabelle nach der ersten UMBENENNUNG bis zur Ausführung der zweiten UMBENENNUNG history nicht existieren wird. Aber selbst wenn zwischen den UMBENENNUNGEN Daten in die Tabelle history gelangen, und die Tabelle noch nicht existiert (wegen der Umbenennung), werden wir eine kleine Anzahl von Einfügefehlern erhalten, die vernachlässigbar sind (wir haben Monitoring, keinen Bankbetrieb).

Jetzt haben wir eine neue Tabelle history mit Partitionierung, aber es fehlen die Daten, die während des letzten Einfügens in die Tabelle history_tmperhalten wurden. Aber diese Daten haben wir in der Tabelle history_old und wir werden sie jetzt von dort nachfüllen. Dazu benötigen wir den zuvor gespeicherten Wert 1551085645. Warum wir diesen Wert gespeichert haben und nicht die maximale Einfügezeit aus der aktuellen Tabelle verwendet haben history? Потому что новые данные уже в неё поступают и мы получим неверное время. Итак, дозаливаем данные:

INSERT IGNORE INTO `history` SELECT * FROM history_old WHERE clock>=1551045645;

Nach Abschluss dieser Operation haben wir in der neuen, partitionierten Tabelle history alle Daten, die in der alten waren, plus die, die bereits nach der Umbenennung der Tabelle eingegangen sind. Die Tabelle history_old ist nicht mehr nötig. Man kann sie sofort löschen oder zuvor eine Sicherungskopie erstellen (wenn man paranoid ist).

Der oben beschriebene Prozess muss für die Tabellen wiederholt werden history_str, history_text und history_uint.

Was in den Zabbix Server-Einstellungen angepasst werden muss

Jetzt liegt die Wartung der Datenbank in Bezug auf die Datenhistorie in unserer Verantwortung. Das bedeutet, dass Zabbix keine alten Daten mehr löschen muss – das werden wir selbst übernehmen. Damit der Zabbix-Server nicht versucht, die Daten selbst zu bereinigen, müssen Sie sich in die Weboberfläche von Zabbix einloggen, im Menü „Administration“ wählen, anschließend das Untermenü „Allgemein“ wählen und dann im Dropdown-Menü rechts „Historie bereinigen“ auswählen. Auf der erscheinenden Seite müssen Sie alle Häkchen der Gruppe „Historie“ entfernen und auf die Schaltfläche „Aktualisieren“ klicken. Dies verhindert eine unnötige Bereinigung der Tabellen. historie* über den Housekeeper.

Beachten Sie auf dieser Seite die Gruppe „Änderungsdynamik“. Das ist genau die Tabelle, trends, auf die wir zurückkommen wollten. Wenn sie ebenfalls zu groß geworden ist und partitioniert werden muss, entfernen Sie auch bei dieser Gruppe die Häkchen und bearbeiten Sie diese Tabelle genauso wie die Tabellen historie*.

Weitere Wartung der Datenbank

Wie zuvor erwähnt, ist es für die ordnungsgemäße Funktion von partitionierten Tabellen wichtig, rechtzeitig Partitionen zu erstellen. Das kann so erfolgen:

ALTER TABLE `historie` ADD PARTITION (PARTITION p20190307 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-07 00:00:00")));

Da wir zudem partitionierte Tabellen erstellt und dem Zabbix-Server das Bereinigen untersagt haben, liegt nun auch die Löschung alter Daten in unserer Verantwortung. Zum Glück gibt es hierbei überhaupt keine Probleme. Das geschieht einfach durch das Löschen der Partition, deren Daten uns nicht mehr benötigt werden.

Zum Beispiel:

ALTER TABLE historie DROP PARTITION p20190201;

Im Unterschied zu den DELETE FROM-Befehlen mit Angabe eines Datumsbereichs wird das DROP PARTITION in wenigen Sekunden ausgeführt, was die Datenbank nicht belastet Server und funktioniert ebenso problemlos bei der Verwendung von MySQL-Replikation.