MySQL: партиционирование таблиц Zabbix

Данный материал является адаптированным переводом с некоторыми изменениями исходной статьи с оф. сайта Zabbix.

Нижеописанное будет примерно к Zabbix Server 4.0.5 и MySQL 5.6

Для решения проблемы больших объёмов базы есть несколько вариантов:

  • Удалить ненужные узлы сети, уменьшить период хранения истории (history) и динамики изменений (trends), отключить сбор ненужных метрик к шаблонах и т.д.
  • Если же размер базы всё равно достаточно объемный, можно применить партиционирование таблиц, о котором и будет рассказано.

Партиционирование появилось в MySQL v5.1 и позволяет добиться от базы более оптимальной\быстрой работы запросов. Это относится к тем моментам, когда выборка происходит из большого количества данных в таблицах.

При использовании в конфиге MySQL параметра file_per_table, каждая таблица будет иметь физический файл на диске. При использовании партиций, большие таблицы разбиваются на логические части. С точки зрения БД, это будет одна таблица, но с точки зрения физического хранения уже получается несколько физических файлов.

Виды партиций

Тип партиций, который будет использован – Range. При его использовании, таблица делится на диапазоны, установленные в ключевом столбце. Диапазоны не должны перекрываться (например, диапазоны дат или идентификаторов). Есть также другие виды партиций, например, hash, list и прочие. Но обсуждение этих типов выходит за рамки данной статьи.

Для детального изучения вопроса о партиционировании в MySQL, лучше всего обратиться к оф. докуметации.

Способ управления партициями

Имеется два основных решения:

  • использование процедур MySQL
  • использование внешних скриптов (perl).

Первый вариант более сложен для отладки, но в данном материале я буду использовать именно его. Внешний скрипт на Perl можно найти в исходной статье.

Таблицы с данными в Zabbix

В Zabbix есть несколько таблиц, которые хранят различные типы исторических данных. Если вы знаете назначение каждой таблицы, становится легче оценить периоды, которые необходимо сохранить. Ниже приведен список таблиц для хранения данных.

  • history
  • history_uint
  • history_str
  • history_text
  • history_log
  • trends
  • trends_uint

Таблицы trends и trends_uint используются для сокращённого набора данных, их значение – усредненное за один час.

Очистка данных

Для некоторых типов данных housekepeer теперь не нужен и его можно будет отключить. Администрирование -> Общие -> Очистка истории

Даже при отключенном хаускипинге для всех таблиц истории, в БД всё равно будут вноситься записи. Для отключения, нужно сменить тип движка у таблицы housekeeper на некий аналог /dev/null:

ALTER TABLE housekeeper ENGINE = BLACKHOLE;

Разделение на партиции

Теперь подошли непосредственно к вопросу о создании партиций. В данном случае, я буду использовать партиционирование по месяцам и применительно только к таблицам history_uint и history.

Для начала нужно убедиться, что в MySQL включен планировщик (и исправить, если не включён, в т.ч. в конфиге):

mysql>  SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>  SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

my.cnf:

event_scheduler=ON

Поскольку разделение таблиц обычно выполняется для базы с уже существующими данными, для каждой таблицы необходимо указывать разделы, начиная с минимального значения поля clock и до текущего месяца включительно. Минимальное значение clock таблицы можно получить так (выполняется достаточно долго):

SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_uint`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `history`;

Полученные значения датируются мартом 2019 года, значит минимальная партиция будет за март и далее создаются для последующих месяцев:

ALTER TABLE `history` PARTITION BY RANGE (clock)
(PARTITION p2019_03 VALUES LESS THAN (UNIX_TIMESTAMP("2019-04-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2019_04 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2019_05 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2019_06 VALUES LESS THAN (UNIX_TIMESTAMP("2019-07-01 00:00:00")) ENGINE = InnoDB);
ALTER TABLE `history_uint` PARTITION BY RANGE (clock)
(PARTITION p2019_03 VALUES LESS THAN (UNIX_TIMESTAMP("2019-04-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2019_04 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2019_05 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2019_06 VALUES LESS THAN (UNIX_TIMESTAMP("2019-07-01 00:00:00")) ENGINE = InnoDB);

Здесь важный момент: название партиции за один месяц, а значение UNIX_TIMESTAMP на месяц больше.

Процесс, выполняющий операцию секционирования в таблице, получает блокировку записи в таблицу. Чтения из таких таблиц относительно незатронуты; ожидающие операции INSERT и UPDATE будут выполнены, как только завершится операция разбиения на партиции.

При возникновении ошибки ERROR 1526 (HY000): Table has no partition for value необходимо проверить, что партиции созданы вплоть до текущего месяца, где последняя запись должна быть примерно вида:

...
PARTITION p<curent_year>_<current_month> VALUES LESS THAN (UNIX_TIMESTAMP("<current_year>-<current_month + 1>-01 00:00:00")) ENGINE = InnoDB);

Время выполнения вышеописанных SQL-команд достаточно долгое, т.к. происхдит копирование сначала во временную таблицу и только потом происходит разбиение. После выполнения, можно увидеть, что появились новые файлы:

# ls -lht /var/lib/mysql/zabbix/history*
-rw-rw---- 1 mysql mysql 4.2G Jun 19 11:48 /var/lib/mysql/zabbix/history#P#p2019_06.ibd
-rw-rw---- 1 mysql mysql 3.0G Jun 19 10:53 /var/lib/mysql/zabbix/history#P#p2019_05.ibd
-rw-rw---- 1 mysql mysql 1.9G Jun 19 10:34 /var/lib/mysql/zabbix/history#P#p2019_04.ibd
-rw-rw---- 1 mysql mysql 640M Jun 19 10:25 /var/lib/mysql/zabbix/history#P#p2019_03.ibd

Проверить текущие партиции можно командой:

SELECT TABLE_NAME,PARTITION_NAME FROM information_schema.partitions WHERE TABLE_SCHEMA='<db_name>' AND TABLE_NAME = 'history';

На этом сам процесс создания партиций закончен. Но после этого возникает нюанс – в случае отсутствия новой партиции на следующий месяц, данные не будут записаны. Для решения данного вопроса будут использоваться процедуры БД, которые в автоматическом режиме всё будут делать сами.

Процедуры MySQL

Детально описывать назначение процедур не стану, из названий в общем всё и так понятно. Для начала создаются таблицы для хранения информации о текущих партициях и действиях с ними:

USE `zabbix`;

CREATE TABLE IF NOT EXISTS `manage_partitions` (
  `tablename` VARCHAR(64) NOT NULL COMMENT 'Table name',
  `period` VARCHAR(64) NOT NULL COMMENT 'Period - daily or monthly',
  `keep_history` INT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'For how many days or months to keep the partitions',
  `last_updated` DATETIME DEFAULT NULL COMMENT 'When a partition was added last time',
  `comments` VARCHAR(128) DEFAULT '1' COMMENT 'Comments',
  PRIMARY KEY (`tablename`)
) ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS `manage_partitions_history` (
  `schema_name` varchar(64) NOT NULL COMMENT 'Zabbix schema name',
  `table_name` varchar(64) NOT NULL COMMENT 'Zabbix table name',
  `table_partition_name` varchar(64) NOT NULL COMMENT 'Zabbix table partition name',
  `partition_action` varchar(64) NOT NULL COMMENT 'Zabbix table partition action',
  `partition_action_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When a partition was added or dropped'
) ENGINE=InnoDB;

Если при создании второй таблицы manage_partitions_history возникает ошибка ERROR 1067 (42000): Invalid default value for ‘partition_action_date’ , то вы используете MySQL < 5.6, лучше обновиться на более новую версию (по рекомендациям Zabbix). Более подробно тут.

Добавляются данные о партициях, которые были созданы с их временем хранения (3 месяца – это же значение нужно переопределить в админке очистки данных для истории и динамики):

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history', 'month', 3, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_uint', 'month', 3, now(), '');

А далее создаются процедуры по работе с партициями (создание, удаление и т.д.):

DROP PROCEDURE IF EXISTS `create_next_partitions`;
DROP PROCEDURE IF EXISTS `create_partition_by_day`;
DROP PROCEDURE IF EXISTS `create_partition_by_month`;
DROP PROCEDURE IF EXISTS `drop_partitions`;
DROP PROCEDURE IF EXISTS `drop_old_partition`;
DROP EVENT IF EXISTS `e_zbx_part_mgmt`;
DELIMITER $$
CREATE 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 ;
DELIMITER $$
CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE BEGINTIME TIMESTAMP;
    DECLARE ENDTIME INT UNSIGNED;
    DECLARE PART_ACTION VARCHAR(12);
    DECLARE PARTITIONNAME VARCHAR(16);
    DECLARE ROWS_CNT INT UNSIGNED;
    SET BEGINTIME = DATE(NOW()) + INTERVAL 1 DAY;
    SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 DAY);
    SET PART_ACTION = 'ADD';
    SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m_%d' );
    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('INSERT INTO `manage_partitions_history`(`schema_name`,`table_name`,`table_partition_name`,`partition_action`) VALUES ("', IN_SCHEMANAME,'","', IN_TABLENAME,'","', PARTITIONNAME,'","', PART_ACTION,'");');
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
        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 ;
DELIMITER $$
CREATE PROCEDURE `create_partition_by_month`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE BEGINTIME TIMESTAMP;
    DECLARE ENDTIME INT UNSIGNED;
    DECLARE PART_ACTION VARCHAR(12);
    DECLARE PARTITIONNAME VARCHAR(16);
    DECLARE ROWS_CNT INT UNSIGNED;
    SET BEGINTIME = DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH);
    SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 MONTH);
    SET PART_ACTION = 'ADD';
    SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m' );
    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('INSERT INTO `manage_partitions_history`(`schema_name`,`table_name`,`table_partition_name`,`partition_action`) VALUES ("', IN_SCHEMANAME,'","', IN_TABLENAME,'","', PARTITIONNAME,'","', PART_ACTION,'");');
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
        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 ;
DELIMITER $$
CREATE 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 ;
DELIMITER $$
CREATE PROCEDURE `drop_old_partition`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64), IN_PARTITIONNAME VARCHAR(64))
BEGIN
    DECLARE PART_ACTION VARCHAR(12);
    DECLARE PART_ACTION_DATE INT;
    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;
    SET PART_ACTION = 'DROP';
    IF ROWS_CNT = 1 THEN
        SET @SQL = CONCAT('INSERT INTO `manage_partitions_history`(`schema_name`,`table_name`,`table_partition_name`,`partition_action`) VALUES ("', IN_SCHEMANAME,'","', IN_TABLENAME,'","', IN_PARTITIONNAME,'","', PART_ACTION,'");');
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
        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, "` does not exist") AS result;
    END IF;
END$$
DELIMITER ;

И в конце создается планировщик, который будет выполняться, начиная с 00.00 2019.07.01:

DELIMITER $$
CREATE EVENT `e_zbx_part_mgmt` 
	ON SCHEDULE EVERY 1 MONTH STARTS '2019-07-01 00:00:00' 
	ON COMPLETION PRESERVE 
	ENABLE 
	COMMENT 'Creating and dropping partitions' 
	DO BEGIN
		CALL zabbix.drop_partitions('zabbix');
		CALL zabbix.create_next_partitions('zabbix');
	END$$
DELIMITER ;

Для проверки, что планировщик запущен, выполнить:

SHOW EVENTS FROM zabbix;

А также вручную можно вызвать созданные процедуру и убедиться, что они отрабатывают без ошибок.

Для ручного удаления партиций:

ALTER TABLE history_uint DROP PARTITION p2019_04;

Помимо процедур и perl-скрипта (на исходном сайте статьи), есть bash-скрипт для создания и удаления партиций, найти можно по ссылке. Его не применял и не проверял, но навскидку посмотрел – по идее, должно работать также, как и процедуры в БД, только используя crontab.

Ваш комментарий будет первым

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *