Shulou(Shulou.com)06/02 Report--

When doing zabbix performance optimization, sometimes when the data volume of db is relatively large, it is necessary to perform partition operation on the table, so that the data query time can be reduced. And since partition is used, we can delete historical data ourselves, so that we can disable zabbix housekeeping.

Simply say that partition operations are performed in version 2.0.x zabbix: 1. Backup data. If you use proxy structure, adjust ProxyOfflineBuffer to increase the cache time of data in offline, so that the data will be automatically supplemented after partition adjustment.

Determine the table that needs partition, and change the table structure (use clock for partition, partition key must be a part of primarykey). The tables involved are mainly tables that store history-related data: history,history_uint,history_text,history_log,history_str

Table structure changes:

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

The remaining three tables, history_str,history,history_uint, do not need to be changed.

2. Partitioning by clock

Examples:

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"))); Do the same for all tables that need partitioning. 3. Set storage rules and use cronjob to implement automatic partition operations 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` ()BEGINCALL 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))BEGINDECLARE NEXTCLOCK timestamp;DECLARE PARTITIONNAME varchar(16);DECLARE CLOCK int;SET @totaldays = 7;SET @i = 1;createloop: LOOPSET 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 THENLEAVE 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))BEGINDECLARE OLDCLOCK timestamp;DECLARE PARTITIONNAME varchar(16);DECLARE CLOCK int;SET @mindays = 30;SET @maxdays = @mindays+4;SET @i = @maxdays;droploop: LOOPSET 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 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;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.