zabbix db partition

确定需要partition的表，并更改表结构（使用clock进行parttition，partition key必须是primarykey的一部分），涉及的表主要是存储历史相关数据的表：history,history_uint,history_text,history_log,history_str

表结构更改：

ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_log` DROP KEY `history_log_2`;
ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_text` DROP KEY `history_text_2`;

例子：

ALTER TABLE `history_uint` PARTITION BY RANGE( clock ) (
PARTITION p20140101 VALUES LESS THAN (UNIX_TIMESTAMP("2014-01-02 00:00:00")),
PARTITION p20140102 VALUES LESS THAN (UNIX_TIMESTAMP("2014-01-03 00:00:00")),
PARTITION p20140103 VALUES LESS THAN (UNIX_TIMESTAMP("2014-01-04 00:00:00")),
PARTITION p20140104 VALUES LESS THAN (UNIX_TIMESTAMP("2014-01-05 00:00:00")),
PARTITION p20140105 VALUES LESS THAN (UNIX_TIMESTAMP("2014-01-06 00:00:00")),
PARTITION p20140106 VALUES LESS THAN (UNIX_TIMESTAMP("2014-01-07 00:00:00"))
);

mysql -u xxx -pxxx xxx < ./partition.sql

DELIMITER //
DROP PROCEDURE IF EXISTS `zabbix_server`.`create_zabbix_partitions` //
CREATE PROCEDURE `zabbix_server`.`create_zabbix_partitions` ()
BEGIN
CALL zabbix_server.create_next_partitions("zabbix_server","history");
CALL zabbix_server.create_next_partitions("zabbix_server","history_log");
CALL zabbix_server.create_next_partitions("zabbix_server","history_str");
CALL zabbix_server.create_next_partitions("zabbix_server","history_text");
CALL zabbix_server.create_next_partitions("zabbix_server","history_uint");
CALL zabbix_server.drop_old_partitions("zabbix_server","history");
CALL zabbix_server.drop_old_partitions("zabbix_server","history_log");
CALL zabbix_server.drop_old_partitions("zabbix_server","history_str");
CALL zabbix_server.drop_old_partitions("zabbix_server","history_text");
CALL zabbix_server.drop_old_partitions("zabbix_server","history_uint");
END //
DROP PROCEDURE IF EXISTS `zabbix_server`.`create_next_partitions` //
CREATE PROCEDURE `zabbix_server`.`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_server.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_server`.`drop_old_partitions` //
CREATE PROCEDURE `zabbix_server`.`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_server.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i <= @mindays THEN
LEAVE droploop;
END IF;
END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix_server`.`create_partition` //
CREATE PROCEDURE `zabbix_server`.`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_server`.`drop_partition` //
CREATE PROCEDURE `zabbix_server`.`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 ;

select name from mysql.proc;
show create procedure create_partition;

mysql  -B -xxx -pxxx xxx -e "CALL create_zabbix_partitions();"

CREATE TABLE `history` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` double(16,4) NOT NULL DEFAULT '0.0000',
  `ns` int(11) NOT NULL DEFAULT '0',
  KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( clock)
(PARTITION p20140101 VALUES LESS THAN (1388592000) ENGINE = InnoDB,
PARTITION p20140102 VALUES LESS THAN (1388678400) ENGINE = InnoDB,
PARTITION p20140103 VALUES LESS THAN (1388764800) ENGINE = InnoDB,
PARTITION p20140104 VALUES LESS THAN (1388851200) ENGINE = InnoDB,
PARTITION p20140105 VALUES LESS THAN (1388937600) ENGINE = InnoDB,
PARTITION p20140106 VALUES LESS THAN (1389024000) ENGINE = InnoDB,
PARTITION p20140107 VALUES LESS THAN (1389110400) ENGINE = InnoDB,
PARTITION p20140108 VALUES LESS THAN (1389196800) ENGINE = InnoDB,
PARTITION p20140109 VALUES LESS THAN (1389283200) ENGINE = InnoDB,
PARTITION p20140110 VALUES LESS THAN (1389369600) ENGINE = InnoDB) */

select b.hostname ,c.ip,a.update_percent as uppercent from ( select b.hostid,ROUND(IFNULL(a.aa,0)*100/b.bb,2) as update_percent from  (select hostid,count(*) as aa from items where lastclock > UNIX_TIMESTAMP()-1800 and delay < 900   and hostid in (select hostid from hosts where status=0)   and status = 0 group by hostid ) a RIGHT JOIN (select hostid,count(*) as bb from items where delay < 900 and status = 0 and hostid in (select hostid from hosts where status=0) group by hostid) b  ON a.hostid=b.hostid)a,(select hostid,lower(host) as hostname from hosts where status=0)b, (select hostid,ip from interface where type='1')c where a.hostid=b.hostid and b.hostid=c.hostid  order by uppercent;