daily pastebin goal
32%
SHARE
TWEET

Untitled

a guest Feb 28th, 2012 685 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DELIMITER //
  2. DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_monthly_partitions` //
  3. CREATE PROCEDURE `zabbix`.`create_zabbix_monthly_partitions` ()
  4. BEGIN
  5. CALL zabbix.create_next_monthly_partitions("zabbix","acknowledges");
  6. CALL zabbix.create_next_monthly_partitions("zabbix","alerts");
  7. CALL zabbix.create_next_monthly_partitions("zabbix","auditlog");
  8. CALL zabbix.create_next_monthly_partitions("zabbix","events");
  9. CALL zabbix.create_next_monthly_partitions("zabbix","service_alarms");
  10. CALL zabbix.create_next_monthly_partitions("zabbix","trends");
  11. CALL zabbix.create_next_monthly_partitions("zabbix","trends_uint");
  12. CALL zabbix.drop_old_monthly_partitions("zabbix","acknowledges");
  13. CALL zabbix.drop_old_monthly_partitions("zabbix","alerts");
  14. CALL zabbix.drop_old_monthly_partitions("zabbix","auditlog");
  15. CALL zabbix.drop_old_monthly_partitions("zabbix","events");
  16. CALL zabbix.drop_old_monthly_partitions("zabbix","service_alarms");
  17. CALL zabbix.drop_old_monthly_partitions("zabbix","trends");
  18. CALL zabbix.drop_old_monthly_partitions("zabbix","trends_uint");
  19. END //
  20. DROP PROCEDURE IF EXISTS `zabbix`.`create_next_monthly_partitions` //
  21. CREATE PROCEDURE `zabbix`.`create_next_monthly_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
  22. BEGIN
  23. DECLARE NEXTCLOCK timestamp;
  24. DECLARE PARTITIONNAME varchar(16);
  25. DECLARE CLOCK int;
  26. SET @totalmonths = 3;
  27. SET @i = 1;
  28. createloop: LOOP
  29. SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i MONTH);
  30. SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m' );
  31. SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 MONTH),'%Y-%m-01 00:00:00'));
  32. CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
  33. SET @i=@i+1;
  34. IF @i > @totalmonths THEN
  35. LEAVE createloop;
  36. END IF;
  37. END LOOP;
  38. END //
  39. DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_monthly_partitions` //
  40. CREATE PROCEDURE `zabbix`.`drop_old_monthly_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
  41. BEGIN
  42. DECLARE OLDCLOCK timestamp;
  43. DECLARE PARTITIONNAME varchar(16);
  44. DECLARE CLOCK int;
  45. SET @minmonths = 12;
  46. SET @maxmonths = @minmonths+3;
  47. SET @i = @maxmonths;
  48. droploop: LOOP
  49. SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i MONTH);
  50. SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m' );
  51. CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
  52. SET @i=@i-1;
  53. IF @i <= @minmonths THEN
  54. LEAVE droploop;
  55. END IF;
  56. END LOOP;
  57. END //
  58. 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