How to Partition Zabbix MySQL Tables for Faster Performance

Overview of Zabbix Database Table Partitioning

In system monitoring, Zabbix has replaced Nagios+Cacti because of its rich graphics and high customizability. After running for nearly three years, the Zabbix web UI became sluggish, data insertion slowed, and the MySQL database grew to 83 GB, requiring partitioning to reduce size.

Steps

Modify table structure:

use zabbix;
Alter table history_text drop primary key, add index (id), drop index history_text_2, add index history_text_2 (itemid, id);

Alter table history_log drop primary key, add index (id), drop index history_log_2, add index history_log_2 (itemid, id);

Create four stored procedures:

Procedure for creating partitions:

DELIMITER $$
CREATE PROCEDURE `partition_create`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64), CLOCK INT)BEGIN
    DECLARE RETROWS INT;
    SELECT COUNT(1) INTO RETROWS
    FROM information_schema.partitions
    WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND partition_description >= CLOCK;
    IF RETROWS = 0 THEN
        SELECT CONCAT( "partition_create(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
        SET @SQL = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    END IF;
END$$
DELIMITER ;

Procedure for dropping partitions:

DELIMITER $$
CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE drop_part_name VARCHAR(16);
    DECLARE myCursor CURSOR FOR SELECT partition_name
        FROM information_schema.partitions
        WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) < DELETE_BELOW_PARTITION_DATE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION ");
    SET @drop_partitions = "";
    OPEN myCursor;
    read_loop: LOOP
        FETCH myCursor INTO drop_part_name;
        IF done THEN LEAVE read_loop; END IF;
        SET @drop_partitions = IF(@drop_partitions = "", drop_part_name, CONCAT(@drop_partitions, ",", drop_part_name));
    END LOOP;
    IF @drop_partitions != "" THEN
        SET @full_sql = CONCAT(@alter_header, @drop_partitions, ";");
        PREPARE STMT FROM @full_sql;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
        SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`;
    ELSE
        SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, "N/A" AS `partitions_deleted`;
    END IF;
END$$
DELIMITER ;

Procedure for partition maintenance:

DELIMITER $$
CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)
BEGIN
    DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16);
    DECLARE PARTITION_NAME VARCHAR(16);
    DECLARE OLD_PARTITION_NAME VARCHAR(16);
    DECLARE LESS_THAN_TIMESTAMP INT;
    DECLARE CUR_TIME INT;

    CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);
    SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'));
    SET @__interval = 1;
    create_loop: LOOP
        IF @__interval > CREATE_NEXT_INTERVALS THEN LEAVE create_loop; END IF;
        SET LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval * 3600);
        SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL * (@__interval - 1) * 3600, 'p%Y%m%d%H00');
        IF(PARTITION_NAME != OLD_PARTITION_NAME) THEN
            CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
        END IF;
        SET @__interval=@__interval+1;
        SET OLD_PARTITION_NAME = PARTITION_NAME;
    END LOOP;
    SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), '%Y%m%d0000');
    CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);
END$$
DELIMITER ;

Procedure for partition verification:

DELIMITER $$
CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT)
BEGIN
    DECLARE PARTITION_NAME VARCHAR(16);
    DECLARE RETROWS INT;
    DECLARE FUTURE_TIMESTAMP TIMESTAMP;
    SELECT COUNT(1) INTO RETROWS
    FROM information_schema.partitions
    WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND partition_name IS NULL;
    IF RETROWS = 1 THEN
        SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), " ", '00:00:00'));
        SET PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00');
        SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " PARTITION BY RANGE(`clock`) (PARTITION ", PARTITION_NAME, " VALUES LESS THAN (", UNIX_TIMESTAMP(FUTURE_TIMESTAMP), "));" );
        PREPARE STMT FROM @__PARTITION_SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    END IF;
END$$
DELIMITER ;

Using the stored procedures:

Call a single maintenance procedure:

CALL partition_maintenance(zabbix, 'history_uint', 31, 24, 14);

Run maintenance for all seven Zabbix tables:

DELIMITER $$
CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
   CALL partition_maintenance(SCHEMA_NAME, 'history', 31, 24, 14);
   CALL partition_maintenance(SCHEMA_NAME, 'history_log', 31, 24, 14);
   CALL partition_maintenance(SCHEMA_NAME, 'history_str', 31, 24, 14);
   CALL partition_maintenance(SCHEMA_NAME, 'history_text', 31, 24, 14);
   CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 31, 24, 14);
   CALL partition_maintenance(SCHEMA_NAME, 'trends', 180, 24, 14);
   CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 180, 24, 14);
END$$
DELIMITER ;

Scheduled task to automate the work:

01 01 * * * /opt/software/mysql/bin/mysql -uzabbix -pzabbix zabbix -e "CALL partition_maintenance_all('zabbix');"

Note: It is recommended to run the partitioning task together with backup tasks.