Utilisation du partitionnement dans MySQL pour Zabbix avec un grand nombre d'objets de surveillance

Pour le monitoring des serveurs et des services, nous utilisons depuis longtemps et avec succès une solution combinée basée sur Nagios et Munin. Cependant, cette combinaison présente un certain nombre d'inconvénients, c'est pourquoi nous, comme beaucoup d'autres, exploitons activement Zabbix. Dans cet article, nous allons expliquer comment, avec un minimum d'efforts, résoudre le problème de performance lors de l'augmentation du nombre de métriques collectées et de la croissance des volumes de la base de données MySQL.

Problèmes d'utilisation de la base de données MySQL avec Zabbix

Tant que la base de données était petite et que le nombre de métriques stockées était limité, tout allait très bien. Le processus de nettoyage automatique, qui est lancé par le serveur Zabbix, a réussi à supprimer les enregistrements obsolètes de la base de données, empêchant ainsi sa croissance. Cependant, dès que le nombre de métriques collectées a augmenté et que le volume de la base de données a atteint une certaine taille, la situation s'est dégradée. Le processus de nettoyage a cessé de pouvoir supprimer les données dans le temps imparti, laissant des données anciennes dans la base de données. Pendant l'exécution du processus de nettoyage, une charge accrue sur le serveur Zabbix se produisait, qui pouvait persister longtemps. Il est devenu clair qu'il fallait trouver une solution à cette situation.

C'est un problème bien connu, pratiquement tous ceux qui ont travaillé avec de grands volumes de surveillance sur Zabbix ont rencontré la même chose. Il existait également plusieurs solutions : par exemple, remplacer MySQL par PostgreSQL ou même Elasticsearch, mais la solution la plus simple et éprouvée a été de passer au partitionnement des tables contenant les données des métriques dans la base de données MySQL. Nous avons décidé de suivre cette voie.

Passage des tables MySQL classiques aux tables partitionnées

Zabbix est bien documenté et les tables où il stocke les métriques sont connues. Ce sont les tables : history, qui stocke des valeurs float, history_str, qui stockent des valeurs de chaîne courtes, history_text, qui contiennent des valeurs de texte longues et history_uint, qui stockent des valeurs entières. Il existe aussi une table trends, qui conserve la dynamique des changements, mais que nous avons décidé de ne pas toucher, car sa taille est petite et nous y reviendrons un peu plus tard.

En général, il était clair quelles tables devaient être traitées. Nous avons décidé de créer des partitions chaque semaine, à l'exception de la dernière, en nous basant sur les chiffres du mois, c'est-à-dire en formant quatre partitions par mois : du 1er au 7, du 8 au 14, du 15 au 21 et du 22 au 1 (du mois suivant). La difficulté résidait dans le fait qu'il fallait transformer les tables dont nous avions besoin en tables partitionnées « à la volée », sans interrompre le fonctionnement de Zabbix Server et la collecte des métriques.

Étrangement, la structure même des données des tables nous a aidés dans ce processus. Par exemple, la table history a la structure suivante :

`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',

en outre,

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

Comme nous le voyons, chaque métrique est finalement inscrite dans une table avec deux champs très importants et utiles pour nous : itemid et clock. Ainsi, nous pouvons tout à fait créer une table temporaire, par exemple, nommée history_tmp, configurer la partition pour elle, puis y transférer toutes les données de la table history, puis renommer la table history dans history_old, et la table history_tmp dans history, après quoi nous pourrions ajouter les données qui n'ont pas été transférées depuis history_old dans history et supprimer. history_oldCela peut être fait en toute sécurité, nous ne perdrons rien, car les champs mentionnés ci-dessus itemid et clock assurent une liaison entre une métrique spécifique et un moment précis, plutôt qu'à un numéro d'ordre quelconque.

La procédure de transition

Attention ! Il est fortement conseillé, avant de commencer toute action, de faire une sauvegarde complète de la base de données. Nous sommes tous des êtres humains et nous pouvons commettre une erreur dans la saisie des commandes, ce qui peut entraîner une perte de données. Oui, une sauvegarde ne garantira pas une actualité maximale, mais il vaut mieux en avoir une que rien.

Donc, nous n'éteignons ni n'arrêtons quoi que ce soit. L'essentiel est que le serveur MySQL dispose de suffisamment d'espace libre sur le disque, c'est-à-dire que pour chacune des tables mentionnées ci-dessus history, history_text, history_str, history_uint, il doit y avoir au minimum de la place pour créer une table avec le suffixe « _tmp », en tenant compte du fait qu'elle sera de taille équivalente à celle de la table d'origine.

Nous ne répéterons pas tout plusieurs fois pour chacune des tables susmentionnées et nous examinerons tout uniquement par l'exemple d'une d'entre elles — la table history.

Alors, créons une table vide history_tmp sur la base de la structure de la table history.

CREATE TABLE `history_tmp` LIKE `history`;

Créons les partitions dont nous avons besoin. Par exemple, faisons cela pour un mois. Chaque partition est créée sur la base de la règle de partitionnement, qui est fondée sur la valeur du champ clock, que nous comparons à l'horodatage :

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"))
);

Cette commande ajoute le partitionnement à la table que nous avons créée history_tmp. Précisons que les données dont la valeur du champ clock est inférieure à «2019-02-01 00:00:00» iront dans la partition p20190201, ensuite les données dont la valeur du champ clock est supérieure à «2019-02-01 00:00:00» mais inférieure à «2019-02-07 00:00:00» iront dans la partition p20190207 et ainsi de suite.

Remarque importante : Que se passe-t-il si nous avons des données dans la table partitionnée où la valeur du champ clock est supérieure ou égale à «2019-03-01 00:00:00» ? Étant donné qu'il n'y a pas de partition appropriée pour ces données, elles ne seront pas insérées dans la table et seront perdues. Il est donc nécessaire de se rappeler de créer à temps des partitions supplémentaires afin d’éviter de telles pertes de données (comme expliqué ci-dessous).

Ainsi, la table temporaire est prête. Nous allons télécharger les données. Ce processus peut prendre un certain temps, mais heureusement, il ne bloque aucune autre requête, il suffit donc de faire preuve de patience :

INSERT IGNORE INTO `history_tmp` SELECT * FROM history;

Le mot-clé IGNORE lors du premier téléchargement n'est pas obligatoire, car il n'y a de toute façon pas de données dans la table, mais il sera nécessaire lors du téléchargement supplémentaire. De plus, cela peut être utile si vous avez dû interrompre ce processus et recommencer.

Après un certain temps (peut-être même plusieurs heures), le premier téléchargement de données a été effectué. Comme vous pouvez le comprendre, la table history_tmp contient désormais des données incomplètes de la table history, mais seulement celles qui étaient présentes au moment du début de l'exécution de la requête. Vous avez alors le choix : soit nous faisons un autre passage (si le processus de téléchargement a duré longtemps), soit nous passons immédiatement au renommage des tables, comme mentionné ci-dessus. Commençons par le second passage. Pour cela, nous devons comprendre l'heure de la dernière inscription dans history_tmp:

SELECT max(clock) FROM history_tmp;

Supposons que vous ayez obtenu : 1551045645. Maintenant, utilisons la valeur obtenue lors du deuxième passage du chargement des données :

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

Ce passage devrait se terminer beaucoup plus rapidement. Mais si le premier passage durait des heures, et que le deuxième dure aussi longtemps, il serait peut-être judicieux de faire un troisième passage, qui s'effectue de manière tout à fait similaire au second.

À la fin, nous exécutons à nouveau l'opération d'obtention de l'heure de la dernière insertion du fichier dans history_tmp, en exécutant :

SELECT max(clock) FROM history_tmp;

Supposons que vous ayez obtenu 1551085645. Conservez cette valeur, elle nous sera nécessaire pour le complément de chargement.

Et maintenant, en fait, lorsque le chargement initial des données dans history_tmp est terminé, nous allons procéder au renommage des tables :

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

Nous avons formé ce bloc en une seule transaction pour éviter le moment où des données sont insérées dans une table inexistante, car après le premier RENAME et avant l'exécution du second RENAME, la table history n'existera plus. Mais même si des données arrivent dans la table entre les opérations RENAME, et que la table n'existe pas encore (en raison du renommage), nous obtiendrons un petit nombre d'erreurs d'insertion, que nous pouvons ignorer (nous faisons du monitoring, pas une banque). history Maintenant, nous avons une nouvelle table

avec partitionnement, mais il manque des données qui ont été obtenues lors du dernier passage d'insertion des données dans la table history . Mais ces données, nous les avons dans la table history_tmpet nous allons les ajouter maintenant. Pour cela, nous aurons besoin de la valeur précédemment sauvegardée 1551085645. Pourquoi avons-nous conservé cette valeur, et pas utilisé l'heure maximale de chargement déjà dans la table actuelle ? history_old INSERT IGNORE INTO `history` SELECT * FROM history_old WHERE clock>=1551045645; history? Потому что новые данные уже в неё поступают и мы получим неверное время. Итак, дозаливаем данные:

À l'issue de cette opération, nous avons dans la nouvelle table partitionnée

toutes les données qui étaient dans l'ancienne, plus celles qui sont déjà arrivées après le renommage de la table. La table history n'est plus nécessaire. Nous pouvons la supprimer immédiatement, ou faire une sauvegarde avant de la supprimer (si vous êtes paranoïaque). history_old Tout le processus décrit ci-dessus doit être répété pour les tables

Ce qu'il faut corriger dans les paramètres de Zabbix Server history_str, history_text et history_uint.

Qu'est-ce qu'il faut corriger dans les paramètres de Zabbix Server

La gestion de la base de données concernant l'historique des données est désormais de notre responsabilité. Cela signifie que Zabbix n'a plus besoin de supprimer les anciennes données – nous nous en occuperons nous-mêmes. Pour que Zabbix Server ne tente pas de nettoyer les données automatiquement, vous devez vous connecter à l'interface web de Zabbix, sélectionner dans le menu « Administration », puis le sous-menu « Général », et enfin dans le menu déroulant à droite choisir « Nettoyage de l'historique ». Sur la page qui s'affiche, vous devez décocher toutes les cases pour le groupe « Historique » et cliquer sur le bouton « Mettre à jour ». Cela empêchera le nettoyage inutile des tables. history* via housekeeper.

Notez également sur cette même page le groupe « Dynamique des changements ». C'est précisément le tableau trends, auquel nous avons promis de revenir. S'il est également devenu trop volumineux et nécessite une partition, décochez les cases dans ce groupe, puis traitez ce tableau de la même manière que pour les tableaux history*.

Maintenance ultérieure de la base de données

Comme mentionné précédemment, pour un bon fonctionnement sur des tableaux partitionnés, il est nécessaire de créer des partitions à temps. Voici comment procéder :

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

De plus, puisque nous avons créé des tableaux partitionnés et interdit à Zabbix Server de les nettoyer, la suppression des anciennes données est maintenant de notre ressort. Heureusement, il n'y a absolument aucun problème à cela. Cela se fait simplement en supprimant la partition dont les données ne nous sont plus nécessaires.

Par exemple :

ALTER TABLE history DROP PARTITION p20190201;

Contrairement aux commandes DELETE FROM avec une spécification de plage de dates, DROP PARTITION s'exécute en quelques secondes, sans aucune charge significative serveur et fonctionne tout aussi bien en cas d'utilisation de la réplication MySQL.

Conclusion

La solution décrite a fait ses preuves. Le volume des données augmente mais aucune dégradation notable des performances n'a été constatée.

Source : habr.com