Advertisement
xsbr

MySQL Auto Partitioning Procedure for Zabbix 1.8

May 17th, 2011
2,586
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 3.81 KB | None | 0 0
  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 ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement