daily pastebin goal
59%
SHARE
TWEET

MySQL Auto Partitioning Procedure for Zabbix 1.8

xsbr May 17th, 2011 2,102 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /**************************************************************
  2.   MySQL Auto Partitioning Procedure for Zabbix 1.8
  3.   http://zabbixzone.com/zabbix/partitioning-tables/
  4.  
  5.   Author:  Ricardo Santos (rsantos at gmail.com)
  6.   Version: 20110518
  7. **************************************************************/
  8. DELIMITER //
  9. DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_partitions` //
  10. CREATE PROCEDURE `zabbix`.`create_zabbix_partitions` ()
  11. BEGIN
  12.         CALL zabbix.create_next_partitions("zabbix","history");
  13.         CALL zabbix.create_next_partitions("zabbix","history_log");
  14.         CALL zabbix.create_next_partitions("zabbix","history_str");
  15.         CALL zabbix.create_next_partitions("zabbix","history_text");
  16.         CALL zabbix.create_next_partitions("zabbix","history_uint");
  17.         CALL zabbix.drop_old_partitions("zabbix","history");
  18.         CALL zabbix.drop_old_partitions("zabbix","history_log");
  19.         CALL zabbix.drop_old_partitions("zabbix","history_str");
  20.         CALL zabbix.drop_old_partitions("zabbix","history_text");
  21.         CALL zabbix.drop_old_partitions("zabbix","history_uint");
  22. END //
  23. DROP PROCEDURE IF EXISTS `zabbix`.`create_next_partitions` //
  24. CREATE PROCEDURE `zabbix`.`create_next_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
  25. BEGIN
  26.         DECLARE NEXTCLOCK timestamp;
  27.         DECLARE PARTITIONNAME varchar(16);
  28.         DECLARE CLOCK int;
  29.         SET @totaldays = 7;
  30.         SET @i = 1;
  31.         createloop: LOOP
  32.                 SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY);
  33.                 SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' );
  34.                 SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00'));
  35.                 CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
  36.                 SET @i=@i+1;
  37.                 IF @i > @totaldays THEN
  38.                         LEAVE createloop;
  39.                 END IF;
  40.         END LOOP;
  41. END //
  42. DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` //
  43. CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
  44. BEGIN
  45.         DECLARE OLDCLOCK timestamp;
  46.         DECLARE PARTITIONNAME varchar(16);
  47.         DECLARE CLOCK int;
  48.         SET @mindays = 3;
  49.         SET @maxdays = @mindays+4;
  50.         SET @i = @maxdays;
  51.         droploop: LOOP
  52.                 SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY);
  53.                 SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );
  54.                 CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
  55.                 SET @i=@i-1;
  56.                 IF @i <= @mindays THEN
  57.                         LEAVE droploop;
  58.                 END IF;
  59.         END LOOP;
  60. END //
  61. DROP PROCEDURE IF EXISTS `zabbix`.`create_partition` //
  62. CREATE PROCEDURE `zabbix`.`create_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
  63. BEGIN
  64.         DECLARE RETROWS int;
  65.         SELECT COUNT(1) INTO RETROWS
  66.                 FROM `information_schema`.`partitions`
  67.                 WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;
  68.        
  69.         IF RETROWS = 0 THEN
  70.                 SELECT CONCAT( "create_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
  71.                 SET @sql = CONCAT( 'ALTER TABLE `', SCHEMANAME, '`.`', TABLENAME, '`',
  72.                                 ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
  73.                 PREPARE STMT FROM @sql;
  74.                 EXECUTE STMT;
  75.                 DEALLOCATE PREPARE STMT;
  76.         END IF;
  77. END //
  78. DROP PROCEDURE IF EXISTS `zabbix`.`drop_partition` //
  79. CREATE PROCEDURE `zabbix`.`drop_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64))
  80. BEGIN
  81.         DECLARE RETROWS int;
  82.         SELECT COUNT(1) INTO RETROWS
  83.                 FROM `information_schema`.`partitions`
  84.                 WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;
  85.        
  86.         IF RETROWS = 1 THEN
  87.                 SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
  88.                 SET @sql = CONCAT( 'ALTER TABLE `', SCHEMANAME, '`.`', TABLENAME, '`',
  89.                                 ' DROP PARTITION ', PARTITIONNAME, ';' );
  90.                 PREPARE STMT FROM @sql;
  91.                 EXECUTE STMT;
  92.                 DEALLOCATE PREPARE STMT;
  93.         END IF;
  94. END //
  95. DELIMITER ;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top