在做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

表結構更改：

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進行分區

例子：

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操作mysql -u xxx -pxxx xxx < ./partition.sql其中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;cronjob形式的調用：mysql -B -xxx -pxxx xxx -e "CALL create_zabbix_partitions();"4.測試partition的結果：1)查看表結構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)

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;

轉載于:https://blog.51cto.com/caiguangguang/1354093

總結

以上是生活随笔為你收集整理的zabbix db partition的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。