Partizionare le tabelle di MySQL/MariaDB per Zabbix

administrator

Premessa: quanto scritto si applica indifferentemente a MySQL 8.0 o a MariaDB 10.x. Quindi nel seguito per comodità scriveremo MySQL, intendendo con questo entrambi i DB Server

Nelle installazioni di Zabbix con backend su MySQL può capitare che le dimensioni delle tabelle in cui Zabbix salva i dati raccolti (history* e trends*)crescano di dimensione, causando un problema di performances che a volte può essere piuttosto pesante.


La risposta a questo problema può essere trovata nel partizionamento delle tabelle di MySQL. Questo database server normalmente crea una directory con il nome del DB e, al suo interno, tanti files quante sono le tabelle e gli indici. E’ quindi piuttosto chiaro che, se una tabella assume dimensioni ragguardevoli, anche il relativo file subirà la stessa sorte, con gli inevitabili problemi che può avere l’accesso a un file di svariate decine di GB. Il partizionamento delle tabelle permette di suddividere il file in pezzi più piccoli, migliorando in questo modo le prestazio
Per questa attività abbiamo seguito l’articolo di Nathan Liefting pubblicato sul blog di Zabbix, opportunamente riveduto e corretto (https://blog.zabbix.com/partitioning-a-zabbix-mysql-database-with-perl-or-stored-procedures/13531/).
L’articolo propone due modi di gestire le partizioni, o tramite uno script Perl oppure tramite delle stored procedures. Per una questione di pulizia abbiamo scelto la seconda strada che, pur presentando qualche difficoltà in più permette di avere una soluzione autoconsistente (tutto ciò che serve è salvato nel DB).

Partizionamento delle tabelle

 

Il primo passo è il partizionamento delle tabelle. Questa operazione, se fatta su un DB di grandi dimensioni può richiedere dei tempi anche molto lunghi, ragion per cui sarebbe bene procedere a questa attività fin da subito, finché il DB è vuoto. In caso contrario il consiglio è di utilizzare tool come tmux o screen, per l’utilizzo dei quali vi rimando all’ampia documentazione rintracciabile in rete. In ogni caso armatevi di pazienza perché potrebbero servire anche giorni interi.
Supponiamo di partizionare le tabelle history* su base giornaliera e quelle trends* su base mensile. Iniziamo facendo login su MySQL:

mysql -u root -p

Dato che il partizionamento è basato sul tempo, cerchiamo il dato più vecchio presente su ogni tabella (ripetere il comando per ognuna delle 7 tabelle della lista precedente)

SELECT FROM_UNIXTIME(MIN(clock)) FROM history_uint;

Il numero di partizioni da creare sarà quindi pari al numero di giorni (o di mesi, nel caso delle trends*) intercorsi tra la data ottenuta con la query qua sopra e la data attuale, moltiplicato per 5 per le history* e per 2 per le trends*. Come si può intuire questo numero può essere molto grande, da cui il ragionamento fatto in precedenza per i tempi necessari.

ALTER TABLE history_uint PARTITION BY RANGE ( clock)
(PARTITION p2020_12_19 VALUES LESS THAN (UNIX_TIMESTAMP("2020-12-20 00:00:00")) ENGINE = InnoDB,
PARTITION p2020_12_20 VALUES LESS THAN (UNIX_TIMESTAMP("2020-12-21 00:00:00")) ENGINE = InnoDB,
PARTITION p2020_12_21 VALUES LESS THAN (UNIX_TIMESTAMP("2020-12-22 00:00:00")) ENGINE = InnoDB,
PARTITION p2020_12_22 VALUES LESS THAN (UNIX_TIMESTAMP("2020-12-23 00:00:00")) ENGINE = InnoDB,
…
PARTITION p2021_02_18 VALUES LESS THAN (UNIX_TIMESTAMP("2021-02-19 00:00:00")) ENGINE = InnoDB,
PARTITION p2021_02_19 VALUES LESS THAN (UNIX_TIMESTAMP("2021-02-20 00:00:00")) ENGINE = InnoDB,
PARTITION p2021_02_20 VALUES LESS THAN (UNIX_TIMESTAMP("2021-02-21 00:00:00")) ENGINE = InnoDB,
PARTITION p2021_02_21 VALUES LESS THAN (UNIX_TIMESTAMP("2021-02-22 00:00:00")) ENGINE = InnoDB,
PARTITION p2021_02_22 VALUES LESS THAN (UNIX_TIMESTAMP("2021-02-23 00:00:00")) ENGINE = InnoDB);

ALTER TABLE trends_uint PARTITION BY RANGE ( clock)
(PARTITION p2020_10 VALUES LESS THAN (UNIX_TIMESTAMP("2020-11-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2020_11 VALUES LESS THAN (UNIX_TIMESTAMP("2020-12-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2020_12 VALUES LESS THAN (UNIX_TIMESTAMP("2021-01-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2021_01 VALUES LESS THAN (UNIX_TIMESTAMP("2021-02-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2021_02 VALUES LESS THAN (UNIX_TIMESTAMP("2021-03-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2021_03 VALUES LESS THAN (UNIX_TIMESTAMP("2021-04-01 00:00:00")) ENGINE = InnoDB);

Qualora si partizionasse un DB vuoto è sufficiente invece lanciare una sola delle query qua sopra per ogni tabella, scegliendo una data che sia sufficientemente vecchia, in modo che venga rimossa alla prima esecuzione delle procedure.

Gestione delle partizioni

 

Adesso che abbiamo partizionato le nostre tabelle dobbiamo realizzare qualcosa che permetta di gestirle (ad esempio, creare le nuove partizioni e rimuovere quelle vecchie). Questo sarà fatto tramite strumenti interni dei MySQL. Se qualcuno preferisse farlo con lo script in Perl potete trovare le istruzioni nell’articolo di Nathan.
Anche in questo caso iniziamo facendo login su MySQL:

mysql -u root -p

Per prima cosa creiamo una tabella nella quale salveremo i parametri della nostra attività di manutenzione:

CREATE TABLE `zabbixnew`.`manage_partitions` (`tablename` VARCHAR(64) NOT NULL COMMENT 'table name' , `period` VARCHAR(64) NOT NULL COMMENT 'period - daily or monthly' , `keep_history` INT NOT NULL DEFAULT '1' COMMENT 'for how many days or months keep' , `last_updated` DATETIME NULL DEFAULT NULL COMMENT 'When a partition was added last time' , comments` VARCHAR(128) NOT NULL DEFAULT '1' COMMENT 'comments' ) ENGINE = InnoDB;

Poi popoliamola con i dati che ci servono (adeguare il numero di giorni e mesi di conservazione dei dati a seconda delle proprie esigenze):

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history', 'day', 30, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_uint', 'day', 30, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_str', 'day', 30, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_text', 'day', 30, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_log', 'day', 30, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends', 'month', 12, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends_uint', 'month', 12, now(), '');

Inseriti i parametri creiamo le stored procedures che ci servono. Tre di esse creeeranno le nuove partizioni, mentre le altre due provvederanno alla cancellazione delle partizioni allo scadere del periodo di conservazione (presente nella colonna keep_history della tabella appena popolata)

Procedura per la verifica dell’esistenza delle partizioni

 

DELIMITER $$
USE zabbix$$
DROP PROCEDURE IF EXISTS create_next_partitions$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_next_partitions`(IN_SCHEMANAME VARCHAR(64))
BEGIN DECLARE TABLENAME_TMP VARCHAR(64);DECLARE PERIOD_TMP VARCHAR(12);DECLARE DONE INT DEFAULT 0;

DECLARE get_prt_tables CURSOR FOR
SELECT `tablename`, `period`
FROM manage_partitions;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN get_prt_tables;

loop_create_part:LOOP
IF DONE THEN
LEAVE loop_create_part;
END IF;

FETCH get_prt_tables INTO TABLENAME_TMP, PERIOD_TMP;

CASE WHEN PERIOD_TMP = 'day' THEN
CALL `create_partition_by_day`(IN_SCHEMANAME, TABLENAME_TMP);
WHEN PERIOD_TMP = 'month' THEN
CALL `create_partition_by_month`(IN_SCHEMANAME, TABLENAME_TMP);
ELSE
BEGIN
ITERATE loop_create_part;
END;
END CASE;

UPDATE manage_partitions set last_updated = NOW() WHERE tablename = TABLENAME_TMP;

END LOOP loop_create_part;
CLOSE get_prt_tables;

END$$
DELIMITER ;

Procedura per la creazione delle tabelle giornaliere

 

DELIMITER $$

USE zabbix$$
DROP PROCEDURE IF EXISTS create_partition_by_day$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_partition_by_day`(IN `IN_SCHEMANAME` VARCHAR(64), IN `IN_TABLENAME` VARCHAR(64))
BEGIN DECLARE ROWS_CNT INT UNSIGNED;DECLARE BEGINTIME TIMESTAMP;DECLARE ENDTIME INT UNSIGNED;DECLARE PARTITIONNAME VARCHAR(16);SET BEGINTIME = DATE(NOW()) + INTERVAL 1 DAY;SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m_%d' );
SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 DAY);

SELECT COUNT(*) INTO ROWS_CNT
FROM information_schema.partitions
WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;

IF ROWS_CNT = 0 THEN
SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', ENDTIME, '));' );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
ELSE
SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
END IF;

END$$
DELIMITER ;

Procedura per la creazione delle tabelle per mese

 

DELIMITER $$

USE zabbix$$
DROP PROCEDURE IF EXISTS create_partition_by_month$$

CREATE PROCEDURE create_partition_by_month(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))BEGINDECLARE ROWS_CNT INT UNSIGNED;DECLARE BEGINTIME TIMESTAMP;DECLARE ENDTIME INT UNSIGNED;DECLARE PARTITIONNAME VARCHAR(16);SET BEGINTIME = DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH);SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, ‘p%Y_%m’ );

SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 MONTH);
SELECT COUNT(*) INTO ROWS_CNT
FROM information_schema.partitions
WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;

IF ROWS_CNT = 0 THEN
SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', ENDTIME, '));' );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
ELSE
SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
END IF;

END$$

DELIMITER ;

Procedura per la verifica dell’esistenza di partizioni vecchie

 

DELIMITER $$
USE zabbix$$
DROP PROCEDURE IF EXISTS drop_partitions$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `drop_partitions`(IN_SCHEMANAME VARCHAR(64))
BEGIN DECLARE TABLENAME_TMP VARCHAR(64);DECLARE PARTITIONNAME_TMP VARCHAR(64);DECLARE VALUES_LESS_TMP INT;DECLARE PERIOD_TMP VARCHAR(12);DECLARE KEEP_HISTORY_TMP INT;DECLARE KEEP_HISTORY_BEFORE INT;DECLARE DONE INT DEFAULT 0;DECLARE get_partitions CURSOR FOR SELECT p.table_name, p.partition_name, LTRIM(RTRIM(p.partition_description)), mp.period, mp.keep_history FROM information_schema.partitions p JOIN manage_partitions mp ON mp.tablename = p.table_name WHERE p.table_schema = IN_SCHEMANAME ORDER BY p.table_name, p.subpartition_ordinal_position;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN get_partitions;

loop_check_prt: LOOP
IF DONE THEN
LEAVE loop_check_prt;
END IF;

FETCH get_partitions INTO TABLENAME_TMP, PARTITIONNAME_TMP, VALUES_LESS_TMP, PERIOD_TMP, KEEP_HISTORY_TMP;
CASE WHEN PERIOD_TMP = 'day' THEN
SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP DAY));
WHEN PERIOD_TMP = 'month' THEN
SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP MONTH - INTERVAL DAY(NOW())-1 DAY));
ELSE
BEGIN
ITERATE loop_check_prt;
END;
END CASE;

IF KEEP_HISTORY_BEFORE >= VALUES_LESS_TMP THEN
CALL drop_old_partition(IN_SCHEMANAME, TABLENAME_TMP, PARTITIONNAME_TMP);
END IF;
END LOOP loop_check_prt;
CLOSE get_partitions;

END$$
DELIMITER ;

Procedura per la cancellazione delle partizioni scadute

 

DELIMITER $$

USE zabbix$$
DROP PROCEDURE IF EXISTS drop_old_partition$$

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `drop_old_partition`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64), IN_PARTITIONNAME VARCHAR(64))
BEGIN DECLARE ROWS_CNT INT UNSIGNED;
SELECT COUNT(*) INTO ROWS_CNT
FROM information_schema.partitions
WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = IN_PARTITIONNAME;

IF ROWS_CNT = 1 THEN
SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
' DROP PARTITION ', IN_PARTITIONNAME, ';' );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
ELSE
SELECT CONCAT("partition `", IN_PARTITIONNAME, "` for table `", IN_SCHEMANAME, ".", IN_TABLENAME, "` not exists") AS result;
END IF;

END$$
DELIMITER ;

Evento schedulato per il lancio delle procedure

 

DELIMITER $$

USE zabbix$$
CREATE EVENT IF NOT EXISTS e_part_manage

ON SCHEDULE EVERY 1 DAY STARTS '2021-02-19 04:00:00'
ON COMPLETION PRESERVE
ENABLE
DO BEGIN
CALL zabbix.drop_partitions('zabbix');
CALL zabbix.create_next_partitions('zabbix');
END$$
DELIMITER ;

Tutti questi comandi possono essere lanciati anche da tool grafici come phpMyadmin omettendo le parti in corsivo.

Per verificare l’effettivo funzionamente di tutto quanto sarà sufficiente controllare i files presenti nella directory [MYSQL_HOME]/zabbix: so dovranno trovare tanti files quante sono le partizioni create, il cui nome conterrà anno_mese_giorno (anno_mese nel caso delle trends*)

-rw-r----- 1 mysql mysql 114688 Mar 29 10:00 history#p#p2023_03_30.ibd
-rw-r----- 1 mysql mysql 114688 Mar 29 10:00 history_log#p#p2023_03_30.ibd
-rw-r----- 1 mysql mysql 114688 Mar 29 10:00 history_str#p#p2023_03_30.ibd
-rw-r----- 1 mysql mysql 114688 Mar 29 10:00 history_text#p#p2023_03_30.ibd
-rw-r----- 1 mysql mysql 114688 Mar 29 10:00 history_uint#p#p2023_03_30.ibd
...
-rw-r----- 1 mysql mysql 114688 Mar 29 10:00 trends#p#p2023_04.ibd
-rw-r----- 1 mysql mysql 114688 Mar 29 10:00 trends_uint#p#p2023_04.ibd
© 2024 Catfish Blog | Tema: Storto di CrestaProject WordPress Themes.