See www.zabbix.com for the official Zabbix site.

Docs/howto/MySQL Table Partitioning (variant)

From Zabbix.org
Jump to: navigation, search

Slight Twist on MySQL Partitioning for Zabbix v3.4 and newer

This is a trimmed down version of the tutorial that excludes the use of external scripts and is only for versions of Zabbix 3.2 and newer, so that it can be used with Amazon AWS RDS Aurora/MySQL and can be found here: YAMP

The primary difference between this an the other version of the tutorial is the addition of the partition management history table. As a DBA, its really nice to keep track of what has happened to the database and actually have proof of it. The version of Zabbix that this tutorial was used against was 3.4.13 built in AWS with the database running on RDS Aurora (MySQL) and has worked extremely well. Because of the fact that it was built in AWS, the external script was not an option, thus the trimmed down version.

It is also assumed that you have a basic understanding of MySQL partitioning and its many benefits.

Historical data in Zabbix

There are several tables in Zabbix that keep various types of historical data. If you know the purpose of each table, it becomes easier to estimate the periods that needs to be stored. Below is a list of tables for keeping data gathered by items.

Purpose Data type Maximum size
history Keeps raw history Numeric (float) double(16,4) - 999999999999.9999
history_uint Keeps raw history Numeric (unsigned) bigint(20) - 264+1
history_str Keeps raw short string data Character varchar(255) - 255
history_text Keeps raw long string data Text text - 65535
history_log Keeps raw log strings Log text - 65535
trends Keeps reduced dataset (trends) Numeric (float) double(16,4) - 999999999999.9999
trends_uint Keeps reduced dataset (trends) Numeric (unsigned) bigint(20) - 264+1


Note: Data gathered by items with Character, Log and Text type of information, kept in history_str, history_log and history_text tables, don't have trends. This fact must be considered when partitioning these tables.

Tables trends and trends_uint are used for reduced dataset, collected for one-hour periods for each item. Each record contains the average of the hour, the minimum and maximum value of the hour, selected from the complete history of that hour as well as the number of raw values in that hour.

Partitioning decisions

Before performing partitioning in Zabbix, several aspects must be considered:

  1. Range partitioning will be used for table partitioning.
  2. Housekeeper will not be needed for some data types anymore. This Zabbix functionality for clearing old history and trend data from the database can be controlled in Administration -> General -> Housekeeping.
  3. The values of History storage period (in days) and Trend storage period (in days) fields in item configuration will not be used anymore as old data will be cleared by the range i.e. the whole partition. They can (and should be) overridden in Administration -> General -> Housekeeping - the period should match the period for which we are expecting to keep the partitions.
  4. Even with the housekeeping for items disabled, Zabbix server and web interface will keep writing housekeeping information for future use into the housekeeper table. To avoid this, you can set ENGINE = Blackhole for this table:
ALTER TABLE housekeeper ENGINE = BLACKHOLE;

Partitioning Zabbix database

A tutorial for automatic table partitioning follows. There will be partitioning examples based on the procedures and Event Scheduler of MySQL. The drawing illustrates a partitioning-by-table example, you, however, may choose the type of partitioning (by-the-month or by-the-day) that fits your preferences.

Daily partitioning Monthly partitioning
history

history_log

history_str

history_text

history_uint

trends

trends_uint

First Steps

Ensure first that the event scheduler is enabled.

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)

You should also put a line in the 'my.cnf' file like "event_scheduler=ON" in case of reboot.

Partitioning Zabbix Tables

Note: If the tables for partitioning are large then SQL queries below may take a while!
  • In versions of Zabbix prior to v3.2, changes beyond adding table partitions were required (i.e. adding/dropping indexes). For version 3.2 and newer, only adding table partitions is required for the following tables: history, history_uint, history_str, history_text, history_log, trends and trends_uint.
  • As partitioning is usually performed for a database with existing historical data - for every table you must specify partitions starting from a minimum value of the clock field and up to the current moment (day, month) of tables to be partitioned.

The minimum value of the clock in a table can be found out by a query like this:

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

perform similar queries for all the tables you are going to partition.

Note: Total number of partitions for a table may not exceed 1024 due to an internal MySQL limitation. This limit has been increased to 8192 since MySQL 5.6.7.
  • Using the minimal clock values received in previous step you can prepare a set of SQLs to partition every table.

Examples for adding partitions to already existing tables.

Partitioning a table by day

ALTER TABLE `history_uint` PARTITION BY RANGE (clock)
(PARTITION p2018_01_01 VALUES LESS THAN (UNIX_TIMESTAMP("2018-01-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_01_02 VALUES LESS THAN (UNIX_TIMESTAMP("2018-01-25 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_01_03 VALUES LESS THAN (UNIX_TIMESTAMP("2018-01-26 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_01_04 VALUES LESS THAN (UNIX_TIMESTAMP("2018-01-27 00:00:00")) ENGINE = InnoDB,
...
 PARTITION p2018_08_27 VALUES LESS THAN (UNIX_TIMESTAMP("2018-08-27 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_08_28 VALUES LESS THAN (UNIX_TIMESTAMP("2018-08-28 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_08_29 VALUES LESS THAN (UNIX_TIMESTAMP("2018-08-29 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_08_30 VALUES LESS THAN (UNIX_TIMESTAMP("2018-08-31 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_08_31 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-01 00:00:00")) ENGINE = InnoDB);

Partitioning a table by month:

ALTER TABLE `trends_uint` PARTITION BY RANGE (clock)
(PARTITION p2017_01 VALUES LESS THAN (UNIX_TIMESTAMP("2017-02-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2017_02 VALUES LESS THAN (UNIX_TIMESTAMP("2017-03-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2017_03 VALUES LESS THAN (UNIX_TIMESTAMP("2017-04-01 00:00:00")) ENGINE = InnoDB,
...
 PARTITION p2018_08 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_09 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_10 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_11 VALUES LESS THAN (UNIX_TIMESTAMP("2018-12-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_12 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-01 00:00:00")) ENGINE = InnoDB);

Note: in examples above some lines are trimmed to keep examples shorter, but you have to specify complete ranges for every partitioned table.

Example commands for manual partition initiating/adding/deletion, just in case:

ALTER TABLE `history` PARTITION BY RANGE (clock) (PARTITION p2018_08_31 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-01 00:00:00")));
ALTER TABLE `history` ADD PARTITION p2011_10_23 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-24 00:00:00")) ENGINE = InnoDB;
ALTER TABLE `trends` DROP PARTITION p2011_06;

NOTE: for clean/new installations (without collected history) it's enough to repeat the initiating SQL as is (1st in the list) for each required history/trends table. After the script is executed the first time, these old partitions will be dropped and new ones will be created.

Partitioning Management DDL for MySQL

The following DDL creates two tables, populates one with the tables to be maintained by the stored procedures and then creates the stored procedures and event.

Note: Before creating procedures or event scheduler rules, read through the whole solution and understand how it works.
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;

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_uint', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_str', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_text', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_log', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends', 'month', 24, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends_uint', 'month', 24, 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 ;

DELIMITER $$
CREATE EVENT `e_zbx_part_mgmt` 
	ON SCHEDULE EVERY 1 DAY STARTS '2018-01-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 ;

Considerations

  • Make sure the partition ranges do not overlap when creating/adding new partitions, otherwise the effort will return an error.
  • A MySQL table is either partitioned completely or not at all. No such records must be left over that do not fit in any of the created partitions.
  • Use MySQL 5.6 or later. It is has been optimised for and is more stable with partitioned tables.