利用event为zabbix数据表定期添加和删除分区

导读

利用MySQL的event来自动维护表分区。

我们去年就开始把zabbix数据库改成用TokuDB来支撑,并且启用了表分区(详情见:迁移Zabbix数据库到TokuDB)。这样做的好处很明显,较早的历史数据可以通过删除分区快速废弃掉。要知道,zabbix数据表默认是没有针对时间字段创建索引的,因此如果执行删除的SQL命令,其效率会很差,而直接删除分区就快多了。

先看history表的分区规则:

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=TokuDB DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_QUICKLZ
 PARTITION BY RANGE (clock)
 (PARTITION p20150531 VALUES LESS THAN (1433088000) ENGINE = TokuDB,
 ...
 PARTITION p20160411 VALUES LESS THAN (1460390400) ENGINE = TokuDB);

对这个表,我们每天要的是:创建一个新的分区,而后删除N个月前的历史旧分区。这个工作可以通过系统的cron来实施,也可以利用MySQL自身的event来做,在这里我们选择用event,没什么特殊的原因,只是想顺便尝试下event而已,呵呵。

一个定期调度的event写起来并不难,下面是参考样例,相信很快就能看明白:

delimiter $$$
 drop event if exists zabbix_alter_partition_daily;
 CREATE EVENT zabbix_alter_partition_daily
 ON SCHEDULE EVERY 1 DAY -- 每天执行
 DO
 begin

 -- 记日志
 insert into zlogs select 0, now(), date_format(date_sub(now(), INTERVAL 180 DAY),
 " ALTER TABLE history DROP PARTITION p%Y%m%d");

 -- 删除history表180天前的旧分区
 -- 用PREPARE & EXECUTE 准备和执行删除的SQL
 SET @drop_p_stmt = date_format(date_sub(now(), INTERVAL 180 DAY)," ALTER TABLE history DROP PARTITION p%Y%m%d");
 PREPARE drop_p_stmt FROM @drop_p_stmt;
 EXECUTE drop_p_stmt;

 -- 创建history表30天后的新分区
 insert into zlogs select 0, now(), concat(
 date_format(date_add(now(), INTERVAL 180 DAY)," ALTER TABLE history ADD PARTITION ( PARTITION p%Y%m%d VALUES LESS THAN "),
 "(", 
 unix_timestamp( date_add(date_format(now(), "%Y%m%d"), INTERVAL 31 DAY) ),
 "))");

 SET @add_p_stmt = concat(
 date_format(date_add(now(), INTERVAL 30 DAY)," ALTER TABLE history ADD PARTITION ( PARTITION p%Y%m%d VALUES LESS THAN "),
 "(",
 unix_timestamp( date_add(date_format(now(), "%Y%m%d"), INTERVAL 31 DAY) ),
 "))");

 PREPARE add_p_stmt FROM @add_p_stmt;
 EXECUTE add_p_stmt;

 end $$$
 delimiter ;

This post has already been read 3655 times!

叶金荣

Oracle MySQL ACE Director,腾讯云TVP成员

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

Time limit is exhausted. Please reload CAPTCHA.