zabbix db partition

 在做zabbix的性能优化时，有时候在db的数据量比较大的时候，需要对表进行partition操作，这样可以在数据查询减少用时。并且由于使用了partition，我们可以自己实现历史数据的删除操作，这样就可以禁用zabbix的housekeeping功能。

简单的说下再2.0.x版本的zabbix中进行partition的操作：

1.备份数据，如果使用proxy的结构的话，调整ProxyOfflineBuffer,加大数据在offline情况的缓存时间，这样在partition调整完后数据会自动补充。

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

表结构更改：

1 2 3 4

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`;

剩下的history_str,history,history_uint 3个表不需要做更改。

2.按clock进行分区

例子：

1 2 3 4 5 6 7 8

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" )) );

把需要分区的表都进行相同的操作。

3.设置存储规则，并使用cronjob来实现自动partition操作

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

其中partition.sql的内容如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

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 ;

查看存储过程：

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

cronjob形式的调用：

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

4.测试partition的结果：

1)查看表结构

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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) */

2) explain 查看执行计划

explain partitions xxx

5.关闭housekeeping，并验证host update percent的情况

1)DisableHousekeeping=1

2)

1

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;

本文转自菜菜光 51CTO博客，原文链接：http://blog.51cto.com/caiguangguang/1354093，如需转载请自行联系原作者