Zabbix : partitionnement des tables MySQL

Boris HUISGEN
Boris HUISGEN
|

Dans le cas où vous monitorez un nombre important de serveurs et d’items avec Zabbix, le partitionnement des tables SQL est une solution viable afin d’optimiser les performances de votre serveur. Le principe est de scinder horizontalement chaque table en divisant l’ensemble des enregistrements en sous-ensembles plus légers, optimisant ainsi les traitements. Les partitions sont considérées en interne comme de nouvelles tables et bénéficient donc de leurs propres index. Lors des requêtes, MySQL consulte uniquement les partitions nécessaires (un EXPLAIN PARTITION d’une requête confirme ce point). Plusieurs types de partitionnement existent, le plus simple probablement à comprendre étant celui par plage (range).

Dans le cas d’une base de données Zabbix, le partitionnement par range s’effectue au niveau du champ clock des tables. Certaines tables sont à partitionner par jour (history) et d’autres par mois ( trends). Le gain direct consiste en une souplesse de suppression des données, limitées dans ce cas à la suppression des partitions les plus anciennes. Le processus de HouseKeeping de Zabbix, visant à nettoyer toutes les tables par un DELETE sur chaque item, n’est donc plus nécessaire. Gain associé : il est possible d’augmenter la période de rétention des trends.

Voici la procédure de partitionnement des tables Zabbix.

Modification des clés primaires

Le partitionnement par range nécessite certaines modifications au niveaux des clés de certaines tables :

ALTER TABLE `acknowledges` DROP PRIMARY KEY, ADD KEY `acknowledgedid` (`acknowledgeid`);
ALTER TABLE `alerts` DROP PRIMARY KEY, ADD KEY `alertid` (`alertid`);
ALTER TABLE `auditlog` DROP PRIMARY KEY, ADD KEY `auditid` (`auditid`);
ALTER TABLE `events` DROP PRIMARY KEY, ADD KEY `eventid` (`eventid`);
ALTER TABLE `service_alarms` DROP PRIMARY KEY, ADD KEY `servicealarmid` (`servicealarmid`);
ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_log` DROP KEY `history_log_2`;
ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_text` DROP KEY `history_text_2`;

Ajout des partitions sur les tables mensuelles

Les tables mensuelles sont :

  • acknowledges
  • alerts
  • auditlog
  • events
  • service_alarms
  • trends
  • trends_uint

Conservant 12 mois de données, il faut créer pour chaque table 12 partitions + 1 pour le mois suivant le temps de création. A chaque début de mois, une nouvelle partition sera à ajouter (pour le mois suivant) et la plus ancienne est à supprimer.

Sur chacune de ces tables, les partitions sont créées de cette façon (au 31/01/2013) :

ALTER TABLE `acknowledges` PARTITION BY RANGE(clock) (
   PARTITION p201201 VALUES LESS THAN (UNIX_TIMESTAMP("2012-02-01 00:00:00")),
   PARTITION p201202 VALUES LESS THAN (UNIX_TIMESTAMP("2012-03-01 00:00:00")),
   PARTITION p201203 VALUES LESS THAN (UNIX_TIMESTAMP("2012-04-01 00:00:00")),
   PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")),
   PARTITION p201205 VALUES LESS THAN (UNIX_TIMESTAMP("2012-06-01 00:00:00")),
   PARTITION p201206 VALUES LESS THAN (UNIX_TIMESTAMP("2012-07-01 00:00:00")),
   PARTITION p201207 VALUES LESS THAN (UNIX_TIMESTAMP("2012-08-01 00:00:00")),
   PARTITION p201208 VALUES LESS THAN (UNIX_TIMESTAMP("2012-09-01 00:00:00")),
   PARTITION p201209 VALUES LESS THAN (UNIX_TIMESTAMP("2012-10-01 00:00:00")),
   PARTITION p201210 VALUES LESS THAN (UNIX_TIMESTAMP("2012-11-01 00:00:00")),
   PARTITION p201211 VALUES LESS THAN (UNIX_TIMESTAMP("2012-12-01 00:00:00")),
   PARTITION p201212 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-01 00:00:00")),
   PARTITION p201301 VALUES LESS THAN (UNIX_TIMESTAMP("2013-02-01 00:00:00"))
);

Ajout des partitions sur les tables journalières

Les tables journalières sont :

  • history
  • history_log
  • history_str
  • history_text
  • history_uint

L’historique étant de 7 jours, chaque table nécessite 7 partitions + 1 pour le jour suivant le temps de création. Chaque jour, une nouvelle partition sera également à ajouter, la plus ancienne à supprimer.

Sur chacune de ces tables, les partitions sont créées de cette façon (au 31/01/2013) :

ALTER TABLE `history` PARTITION BY RANGE(clock) (
   PARTITION p20130124 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-25 00:00:00")),
   PARTITION p20130125 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-26 00:00:00")),
   PARTITION p20130126 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-27 00:00:00")),
   PARTITION p20130127 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-28 00:00:00")),
   PARTITION p20130128 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-29 00:00:00")),
   PARTITION p20130129 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-30 00:00:00")),
   PARTITION p20130130 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-31 00:00:00")),
   PARTITION p20130131 VALUES LESS THAN (UNIX_TIMESTAMP("2013-02-01 00:00:00"))
);

Automatisation de la rotation des partitions

La rotation des partitions peut être effectuée par des triggers SQL. Ceux-si seront alors appelés par script cron.

DELIMITER //

CREATE PROCEDURE `zabbix`.`cron_monthly` ()
BEGIN
    CALL zabbix.create_monthly_partition("acknowledges");
    CALL zabbix.create_monthly_partition("alerts");
    CALL zabbix.create_monthly_partition("auditlog");
    CALL zabbix.create_monthly_partition("events");
    CALL zabbix.create_monthly_partition("service_alarms");
    CALL zabbix.create_monthly_partition("trends");
    CALL zabbix.create_monthly_partition("trends_uint");
    CALL zabbix.drop_monthly_partition("acknowledges");
    CALL zabbix.drop_monthly_partition("alerts");
    CALL zabbix.drop_monthly_partition("auditlog");
    CALL zabbix.drop_monthly_partition("events");
    CALL zabbix.drop_monthly_partition("service_alarms");
    CALL zabbix.drop_monthly_partition("trends");
    CALL zabbix.drop_monthly_partition("trends_uint");
END //

CREATE PROCEDURE `zabbix`.`cron_daily` ()
BEGIN
    CALL zabbix.create_daily_partition("history");
    CALL zabbix.create_daily_partition("history_log");
    CALL zabbix.create_daily_partition("history_str");
    CALL zabbix.create_daily_partition("history_text");
    CALL zabbix.create_daily_partition("history_uint");
    CALL zabbix.drop_daily_partition("history");
    CALL zabbix.drop_daily_partition("history_log");
    CALL zabbix.drop_daily_partition("history_str");
    CALL zabbix.drop_daily_partition("history_text");
    CALL zabbix.drop_daily_partition("history_uint");
END //

CREATE PROCEDURE `zabbix`.`create_monthly_partition` (TABLENAME VARCHAR(64))
BEGIN
    DECLARE NEXTCLOCK TIMESTAMP;
    DECLARE PARTITIONNAME VARCHAR(16);
    DECLARE CLOCK INT;

    SET NEXTCLOCK = DATE_ADD(NOW(), INTERVAL 1 MONTH);
    SET PARTITIONNAME = DATE_FORMAT(NEXTCLOCK, 'p%Y%m');
    SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NEXTCLOCK, INTERVAL 1 MONTH), '%Y-%m-01 00:00:00'));
    CALL zabbix.create_partition(TABLENAME, PARTITIONNAME, CLOCK);
END //

CREATE PROCEDURE `zabbix`.`drop_monthly_partition` (TABLENAME VARCHAR(64))
BEGIN
    DECLARE OLDCLOCK TIMESTAMP;
    DECLARE PARTITIONNAME VARCHAR(16);
    DECLARE CLOCK INT;

    SET @maxdays = 365;
    SET @old = @maxdays+1;
    SET OLDCLOCK = DATE_SUB(NOW(), INTERVAL @old DAY);
    SET PARTITIONNAME = DATE_FORMAT(OLDCLOCK, 'p%Y%m');
    CALL zabbix.drop_partition(TABLENAME, PARTITIONNAME);
END //

CREATE PROCEDURE `zabbix`.`create_daily_partition` (TABLENAME VARCHAR(64))
BEGIN
    DECLARE NEXTCLOCK timestamp;
    DECLARE PARTITIONNAME VARCHAR(16);
    DECLARE CLOCK INT;

    SET NEXTCLOCK = DATE_ADD(NOW(), INTERVAL 1 DAY);
    SET PARTITIONNAME = DATE_FORMAT(NEXTCLOCK, 'p%Y%m%d');
    SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NEXTCLOCK, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'));
    CALL zabbix.create_partition(TABLENAME, PARTITIONNAME, CLOCK);
END //

CREATE PROCEDURE `zabbix`.`drop_daily_partition` (TABLENAME VARCHAR(64))
BEGIN
    DECLARE OLDCLOCK TIMESTAMP;
    DECLARE PARTITIONNAME VARCHAR(16);
    DECLARE CLOCK INT;

    SET @maxdays = 7;
    SET @old = @maxdays+1;
    SET OLDCLOCK = DATE_SUB(NOW(), INTERVAL @old DAY);
    SET PARTITIONNAME = DATE_FORMAT(OLDCLOCK, 'p%Y%m%d');
    CALL zabbix.drop_partition(TABLENAME, PARTITIONNAME);
END //

CREATE PROCEDURE `zabbix`.`create_partition` (TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64), CLOCK INT)
BEGIN
    DECLARE RETROWS INT;
    SELECT COUNT(1) INTO RETROWS
        FROM `information_schema`.`partitions`
        WHERE `table_schema` = 'zabbix' AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;

    IF RETROWS = 0 THEN
        SELECT CONCAT("create_partition(", PARTITIONNAME, ",", CLOCK, ")") AS msg;
        SET @sql = CONCAT('ALTER TABLE `zabbix`.`', TABLENAME, '`', ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));');
        PREPARE STMT FROM @sql;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    END IF;
END //

CREATE PROCEDURE `zabbix`.`drop_partition` (TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64))
BEGIN
    DECLARE RETROWS INT;
    SELECT COUNT(1) INTO RETROWS
        FROM `information_schema`.`partitions`
        WHERE `table_schema` = 'zabbix' AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;

    IF RETROWS = 1 THEN
        SELECT CONCAT( "drop_partition(", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
        SET @sql = CONCAT( 'ALTER TABLE `zabbix`.`', TABLENAME, '`',' DROP PARTITION ', PARTITIONNAME, ';' );
        PREPARE STMT FROM @sql;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    END IF;
END //

DELIMITER ;

Deux script à planifier en cron vont appeller ces triggers; le premier à chaque début de mois et le second chaque jour :

root@zabbix:~# crontab -l

0 1 1 * * /root/scripts/zabbix_monthly.sh
0 1 * * * /root/scripts/zabbix_daily.sh

root@zabbix:~# cat /root/scripts/zabbix_monthly.sh

#!/bin/sh

mysql -B -h localhost -u root -psecret zabbix -e "CALL cron_monthly();"

root@zabbix:~# cat /root/scripts/zabbix_daily.sh

#!/bin/sh

mysql -B -h localhost -u root -psecret zabbix -e "CALL cron_daily();"

Pour finaliser, le process HouseKeeper de Zabbix est à désactiver (option de configuration DisableHousekeeping).

Boris HUISGEN
Boris HUISGEN
Blog owner
  • #mysql
  • #zabbix