How to make partition under Zabbix

Jephe Wu - http://linuxtechres.blogspot.com

Environment: CentOS 6.2 64bit, newly configured Zabbix 1.8.6 server
Objective: make partition for those tables

Concepts:
In order to increase performance, you can create parititon for some tables, refer to http://zabbixzone.com/zabbix/partitioning-tables/ and http://pastebin.com/nY8WG9Fr


Steps:

1.  modified zabbix partition procedure, keep 2 days for daily partition and 2 months for monthly partition, only keep 12 months for trends and trends_uint tables.

# more createpartitionproc.sql
/**************************************************************
MySQL Auto Partitioning Procedure for Zabbix 1.8
  http://zabbixzone.com/zabbix/partitioning-tables/

  Author:  Ricardo Santos (rsantos at gmail.com)
  Version: 20110518
  Modied: Phil - 20110526
 should be run daily, can surive if run weekly for daily partitions

mysql -B -h localhost -u zabbix -pPASSWORD zabbix -e "CALL create_daily_zabbix_partitions();

 should be run monthly, can surive if run every year for monthly partitions

mysql -B -h localhost -u zabbix -pPASSWORD zabbix -e "CALL create_daily_zabbix_partitions();
mysql -B -h localhost -u zabbix -pPASSWORD zabbix -e "CALL create_monthly_zabbix_partitions();

**************************************************************/
DELIMITER //
DROP PROCEDURE IF EXISTS `zabbix`.`create_daily_zabbix_partitions` //
CREATE PROCEDURE `zabbix`.`create_daily_zabbix_partitions` ()
BEGIN
CALL zabbix.create_next_daily_partitions("zabbix","history");
CALL zabbix.create_next_daily_partitions("zabbix","history_log");
CALL zabbix.create_next_daily_partitions("zabbix","history_str");
CALL zabbix.create_next_daily_partitions("zabbix","history_text");
CALL zabbix.create_next_daily_partitions("zabbix","history_uint");
CALL zabbix.drop_old_daily_partitions("zabbix","history");
CALL zabbix.drop_old_daily_partitions("zabbix","history_log");
CALL zabbix.drop_old_daily_partitions("zabbix","history_str");
CALL zabbix.drop_old_daily_partitions("zabbix","history_text");
CALL zabbix.drop_old_daily_partitions("zabbix","history_uint");
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_monthly_zabbix_partitions` //
CREATE PROCEDURE `zabbix`.`create_monthly_zabbix_partitions` ()
BEGIN
CALL zabbix.create_next_monthly_partitions("zabbix","acknowledges");
CALL zabbix.create_next_monthly_partitions("zabbix","alerts");
CALL zabbix.create_next_monthly_partitions("zabbix","auditlog");
CALL zabbix.create_next_monthly_partitions("zabbix","events");
CALL zabbix.create_next_monthly_partitions("zabbix","service_alarms");
CALL zabbix.create_next_monthly_partitions("zabbix","trends");
CALL zabbix.create_next_monthly_partitions("zabbix","trends_uint");
CALL zabbix.drop_old_monthly_partitions("zabbix","acknowledges");
CALL zabbix.drop_old_monthly_partitions("zabbix","alerts");
CALL zabbix.drop_old_monthly_partitions("zabbix","auditlog");
CALL zabbix.drop_old_monthly_partitions("zabbix","events");
CALL zabbix.drop_old_monthly_partitions("zabbix","service_alarms");
CALL zabbix.drop_old_monthly_partitions2("zabbix","trends");
CALL zabbix.drop_old_monthly_partitions2("zabbix","trends_uint");
END //
/* create a week in advance, in case something happens to cron */
DROP PROCEDURE IF EXISTS `zabbix`.`create_next_daily_partitions` //
CREATE PROCEDURE `zabbix`.`create_next_daily_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE NEXTCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @totaldays = 7;
SET @i = 1;
createloop: LOOP
SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY);
SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' );
SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00'));
CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET @i=@i+1;
IF @i > @totaldays THEN
LEAVE createloop;
END IF;
END LOOP;
END //
/* we want to keep at least 7, but we will scan and make sure we haven't missed and so 7-14 */
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_daily_partitions` //
CREATE PROCEDURE `zabbix`.`drop_old_daily_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @mindays = 2;
SET @maxdays = @mindays+7;
SET @i = @maxdays;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY);
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );
CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i <= @mindays THEN
LEAVE droploop;
END IF;
END LOOP;
END //
/* we want to keep at least 12, but we will scan and make sure we haven't missed and so 7-14 */
DROP PROCEDURE IF EXISTS `zabbix`.`create_next_monthly_partitions` //
CREATE PROCEDURE `zabbix`.`create_next_monthly_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE NEXTCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @totalmonths = 12;
SET @i = 1;
createloop: LOOP
SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i MONTH);
SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m' );
SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 MONTH),'%Y-%m-01 00:00:00'));
CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET @i=@i+1;
IF @i > @totalmonths THEN
LEAVE createloop;
END IF;
END LOOP;
END //

/* we want to keep at least 12 months, but we will scan and make sure we haven't missed and so 7-14 */
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_monthly_partitions2` //
CREATE PROCEDURE `zabbix`.`drop_old_monthly_partitions2` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @minmonths = 12;
SET @maxmonths = @minmonths+3;
SET @i = @maxmonths;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i MONTH);
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m' );

CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i <= @minmonths THEN
LEAVE droploop;
END IF;
END LOOP;
END //
/* we want to keep at least 2 months, but we will scan and make sure we haven't missed and so 7-14 */
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_monthly_partitions` //
CREATE PROCEDURE `zabbix`.`drop_old_monthly_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @minmonths = 2;
SET @maxmonths = @minmonths+3;
SET @i = @maxmonths;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i MONTH);
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m' );

CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i <= @minmonths THEN
LEAVE droploop;
END IF;
END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_partition` //
CREATE PROCEDURE `zabbix`.`create_partition` (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_name` = PARTITIONNAME;

IF RETROWS = 0 THEN
SELECT CONCAT( "create_partition(", 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 //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_partition` //
CREATE PROCEDURE `zabbix`.`drop_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64))
BEGIN
DECLARE RETROWS int;
SELECT COUNT(1) INTO RETROWS
FROM `information_schema`.`partitions`
WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;

IF RETROWS = 1 THEN
SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
      SET @sql = CONCAT( 'ALTER TABLE `', SCHEMANAME, '`.`', TABLENAME, '`',
' DROP PARTITION ', PARTITIONNAME, ';' );
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END //
DELIMITER ;

2. import procedure into mysql database
mysql -uroot -ppassword < createpartitionproc.sql

3. alter table to create initial parititon

if you directly run command in cronjob like 'mysql -B -h localhost -u zabbix -pzabbix zabbix -e "CALL create_daily_zabbix_partitions();"', you will get error:
create_partition(zabbix,history,p20120517,1337263200)
ERROR 1505 (HY000) at line 1: Partition management on a not partitioned table is not possible

4. put cronjob in /etc/cron.d
[root@db01 cron.d]# more mysqlpartition
30 1 * * * mysql  mysql -B -h localhost -u zabbix -pzabbix zabbix -e "CALL create_daily_zabbix_partitions();"
30 1 1 * * mysql mysql -B -h localhost -u zabbix -pzabbix zabbix -e "CALL create_monthly_zabbix_partitions();"

5. create initial partitions

 alter table history partition by range(clock) (partition p20120516 values less than (unix_timestamp("2012-05-17 00:00:00")));
 alter table history_str partition by range(clock) (partition p20120516 values less than (unix_timestamp("2012-05-17 00:00:00")));
 alter table history_uint partition by range(clock) (partition p20120516 values less than (unix_timestamp("2012-05-17 00:00:00")));

 But when running for history_log and history_text tables, we got error below:
 mysql> alter table history_log partition by range(clock) (partition p20120516 values less than (1337176800));
 ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

 mysql> alter table history_text partition by range(clock) (partition p20120516 values less than (1337176800));
 ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

 You need to put 'clock' column into primary key column and unique index key column as follows:

 ALTER TABLE  `zabbix`.`history_log` DROP PRIMARY KEY ,
 ADD PRIMARY KEY (  `id` ,  `clock` ,  `itemid` )


 ALTER TABLE  `zabbix`.`history_log` DROP INDEX  `history_log_2` ,
 ADD UNIQUE  `history_log_2` (  `itemid` ,  `id` ,  `clock` )


  Same thing happens to monthly tables:

  mysql> alter table acknowledges partition by range (clock) ( partition p201205 values less than (unix_timestamp("2012-06-01 00:00:00")));
  ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
  mysql> alter table alerts partition by range (clock) ( partition p201205 values less than (unix_timestamp("2012-06-01 00:00:00")));
  ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
  mysql> alter table auditlog partition by range (clock) ( partition p201205 values less than (unix_timestamp("2012-06-01 00:00:00")));
  ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
  mysql> alter table events partition by range (clock) ( partition p201205 values less than (unix_timestamp("2012-06-01 00:00:00")));
  ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
  mysql> alter table service_alarms partition by range (clock) ( partition p201205 values less than (unix_timestamp("2012-06-01 00:00:00")));
  ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
  
  mysql> alter table trends partition by range (clock) ( partition p201205 values less than (unix_timestamp("2012-06-01 00:00:00")));
  Query OK, 64 rows affected (0.05 sec)
  Records: 64  Duplicates: 0  Warnings: 0


  mysql> alter table trends_uint partition by range (clock) ( partition p201205 values less than (unix_timestamp("2012-06-01 00:00:00")));
  Query OK, 90 rows affected (0.02 sec)
  Records: 90  Duplicates: 0  Warnings: 0

  6. manually run cronjob once to create necessary daily and monthly tables in advance.



mysql -B -h localhost -u zabbix -pzabbix zabbix -e "CALL create_daily_zabbix_partitions();
mysql -B -h localhost -u zabbix -pzabbix zabbix -e "CALL create_monthly_zabbix_partitions();


  7. verify tables with partitions
  mysql -uzabbix -pzabbix zabbix
  mysql> show create table history;

  8. References:
  http://zabbixzone.com/zabbix/partitioning-tables/
  http://pastebin.com/nY8WG9Fr