Zabbix partitioning with MySQL stored procedures

5 days retention period, size of one partition is 24h, will create 2 partitions in future:

DROP PROCEDURE partition_maintenance_all;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 5, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 5, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'history_str', 5, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 5, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 5, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'trends', 5, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 5, 24, 2);
END ;;
DELIMITER ;

Download stored procedures base:

Install procedure to database:

cat mysql.partitioning.sql | mysql zabbix

Second digit dictates the size of partition in hours. 24 = 1d, 1 = 1h. First digit dictates the retention period. This is higly related with second digit. Third digit dictates how many partitions must be made in future.

DROP PROCEDURE partition_maintenance_all;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 5, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 5, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 3, 1, 30);
END ;;
DELIMITER ;
CALL partition_maintenance_all('zabbix');

Reinstall definition. 33 days for integers and decimals. 8 days for text. No partitioning for trends:

--keep raw history for 33 days. one partition size 24 hours, screate one parition in future
DROP PROCEDURE partition_maintenance_all;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 33, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 33, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 8, 24, 2);
END ;;
DELIMITER ;

Reinstall definition. 8 days for integers and decimals. 8 days for text. No partitioning for trends:

--keep raw history for 8 days. one partition size 24 hours, screate one parition in future
DROP PROCEDURE partition_maintenance_all;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 8, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 8, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 8, 24, 2);
END ;;
DELIMITER ;

Reinstall definition. 15 days for integers and decimals. 8 days for text. No partitioning for trends:

--keep raw history for 15 days. one partition size 24 hours, screate one parition in future
DROP PROCEDURE partition_maintenance_all;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 15, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 15, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 8, 24, 2);
END ;;
DELIMITER ;

What argumats are supported:

PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)

Reinstall:

DROP PROCEDURE partition_maintenance_all;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 42, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 42, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_str', 42, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 42, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 42, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'trends', 365, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 365, 24, 14);
END ;;
DELIMITER ;

Maintail only for raw integers and floats:

--keep raw history for 3 days. one partition size 24 hours, screate one parition in future
DROP PROCEDURE partition_maintenance_all;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 8, 24, 2);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 8, 24, 2);
END ;;
DELIMITER ;

2 weeks for raw data, 1h partitions, 1 year for trends, 2 partitions for premade:

Cronjob:

#!/bin/bash
# (auth is in ~/.my.cnf)

command="CALL partition_maintenance_all('zabbix');"

# perform partition maintenance to drop all rolloff data
mysql zabbix -e "$command"

Drop existing procedures:

SHOW PROCEDURE STATUS;
DROP PROCEDURE partition_create;
DROP PROCEDURE partition_drop;
DROP PROCEDURE partition_maintenance;
DROP PROCEDURE partition_maintenance_all;
DROP PROCEDURE partition_verify;
SHOW PROCEDURE STATUS;

For PostgreSQL:

Problem with timezone. Solution is to increase the last existing partition to match end with current time zone:

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201912170000,1576645200) |
+-----------------------------------------------------------+
ERROR 1517 (HY000) at line 1: Duplicate partition name p201912170000

1576645200 +
86400 = 
1576731600

ALTER TABLE history REORGANIZE PARTITION p201912170000 INTO (PARTITION p201912170000temp VALUES LESS THAN (1576731600));
ALTER TABLE history REORGANIZE PARTITION p201912170000temp INTO (PARTITION p201912170000 VALUES LESS THAN (1576731600));