zabbix mysql调优

liyeho

DELIMITER //　　
DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_partitions` //
　　
CREATE PROCEDURE `zabbix`.`create_zabbix_partitions` ()
　　
BEGIN
　　
CALL zabbix.create_next_partitions("zabbix","history");
　　
CALL zabbix.create_next_partitions("zabbix","history_log");
　　
CALL zabbix.create_next_partitions("zabbix","history_str");
　　
CALL zabbix.create_next_partitions("zabbix","history_text");
　　
CALL zabbix.create_next_partitions("zabbix","history_uint");
　　
CALL zabbix.create_next_partitions("zabbix","trends");
　　
CALL zabbix.create_next_partitions("zabbix","trends_uint");
　　
CALL zabbix.drop_old_partitions("zabbix","history");
　　
CALL zabbix.drop_old_partitions("zabbix","history_log");
　　
CALL zabbix.drop_old_partitions("zabbix","history_str");
　　
CALL zabbix.drop_old_partitions("zabbix","history_text");
　　
CALL zabbix.drop_old_partitions("zabbix","history_uint");
　　
CALL zabbix.drop_old_partitions("zabbix","trends");
　　
CALL zabbix.drop_old_partitions("zabbix","trends_uint");
　　
END //
　　
DROP PROCEDURE IF EXISTS `zabbix`.`create_next_partitions` //
　　
CREATE PROCEDURE `zabbix`.`create_next_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 //
　　
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` //
　　
CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
　　
BEGIN
　　
DECLARE OLDCLOCK timestamp;
　　
DECLARE PARTITIONNAME varchar(16);
　　
DECLARE CLOCK int;
　　
SET @mindays = 30;
　　
SET @maxdays = @mindays+4;
　　
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