導讀
利用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_aprtition_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) ),
"))");
-- 用PREPARE & EXECUTE 準備和執行建立新分區的SQL
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 ;