应用场景

线上的Zabbix库的数据表默认使用的是InnoDB存储引擎，随着时间的增长，数据量和占用的存储空间越来越多，单表数据达到了3亿之多。同时，Zabbix的Housekeeping每次在定期清理历史数据时，执行时间都超过了1分多钟，严重影响数据库的性能。

TokuDB引擎的高压缩比、高INSERT性能，正好适用于Zabbix这种INSERT多、UPDATE少，而且数据量大的环境。所以准备把相关的数据表修改为TokuDB引擎。
对Zabbix数据表根据时间字段进行分区后，可以通过删除旧的分区，来达到快速清除历史数据的目的。而且，查询Zabbix最近的数据时，只需要查询单个或几个分区即可，而不需要访问整张表，这样查询性能也得到了提升。

通过查看Zabbix数据库各表数据的存储大小，可以发现，主要是history和trends相关的表所占的存储空间比较大。因此，只对这些表使用TokuDB引擎及分区表。
以下是需要分区的表：

history
history_log
history_str
history_text
history_uint
trends
trends_uint

将这些表按照clock时间字段进行分区，每个分区包含一个月的数据。

系统环境

OS: CentOS 7
DB: MariaDB Enterprise 10.0.23
Zabbix Version: 3.0.1

开启TokuDB引擎

关于MariaDB如何开启TokuDB引擎，可参考之前的文章《MariaDB开启TokuDB存储引擎》。

修改存储引擎及开启分区表

注意事项

由于TokuDB不支持外键，且InnoDB分区表也不支持外键，所以只要有使用TokuDB引擎或分区表，肯定不支持相关的表的外键。
以上7个history和trends相关的表，都没有外键，因此可以使用TokuDB引擎和分区表。
由于分区表的限制，分区表所使用的列必须是主键和唯一键的一部分或全部。存在以下四种情况：
1. 分区表只有主键，没有唯一键。则分区表所使用的列必须是主键的一部分或全部。
2. 分区表没有主键，只有唯一键。则分区表所使用的列必须是唯一键的一部分或全部。
3. 分区表既有主键也有唯一键。则分区表所使用的列必须是主键和唯一键的共同部分，如果主键和唯一键没有共同部分，则表无法进行分区。
4. 分区表既没有主键也没有唯一键。则分区表所使用的列不受上述条件限制。

由于history_log、history_text表结构具有主键和唯一键，且主键和唯一键都不包含分区使用的clock字段，所以需要调整这两个表，以进行表分区。

表history_log、history_text的原始结构如下：

mysql> SHOW CREATE TABLE history_log\G
*************************** 1. row ***************************
       Table: history_log
Create Table: CREATE TABLE `history_log` (
  `id` bigint(20) unsigned NOT NULL,
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `timestamp` int(11) NOT NULL DEFAULT '0',
  `source` varchar(64) NOT NULL DEFAULT '',
  `severity` int(11) NOT NULL DEFAULT '0',
  `value` text NOT NULL,
  `logeventid` int(11) NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `history_log_2` (`itemid`,`id`),
  KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE history_text\G
*************************** 1. row ***************************
       Table: history_text
Create Table: CREATE TABLE `history_text` (
  `id` bigint(20) unsigned NOT NULL,
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` text NOT NULL,
  `ns` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `history_text_2` (`itemid`,`id`),
  KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

表history_log、history_text的主键和索引调整如下：

1
2

mysql> ALTER TABLE history_text DROP PRIMARY KEY, ADD INDEX (id), DROP INDEX history_text_2, ADD INDEX history_text_2 (itemid, id);
mysql> ALTER TABLE history_log DROP PRIMARY KEY, ADD INDEX (id), DROP INDEX history_log_2, ADD INDEX history_log_2 (itemid, id);

由于表的数据量比较多，如果直接在原表上执行以上调整主键和索引的SQL、修改存储引擎、及开启分区表的话，耗时会比较长。这里采取的是先将各表数据分月份导出，然后重建已经修改的分区表结构，再将数据导入到修改后的表里，这样稍微会快些。

重建表结构

将原表进行重命名，并使用新的已分区的表结构创建新表。这样可以保证在数据导入导出，修改表结构的时候，不会导致Zabbix丢失这段时间的监控数据。

重建表结构脚本：

USE zabbix;

-- history
RENAME TABLE history TO history_old;
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
PARTITION BY RANGE(clock)
(PARTITION p201609 VALUES LESS THAN (UNIX_TIMESTAMP('2016-10-01')) ENGINE = TokuDB,
 PARTITION p201610 VALUES LESS THAN (UNIX_TIMESTAMP('2016-11-01')) ENGINE = TokuDB,
 PARTITION p201611 VALUES LESS THAN (UNIX_TIMESTAMP('2016-12-01')) ENGINE = TokuDB,
 PARTITION pmore VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB
);

-- history_log
RENAME TABLE history_log TO history_log_old;
CREATE TABLE `history_log` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `itemid` BIGINT(20) UNSIGNED NOT NULL,
  `clock` INT(11) NOT NULL DEFAULT '0',
  `timestamp` INT(11) NOT NULL DEFAULT '0',
  `source` VARCHAR(64) NOT NULL DEFAULT '',
  `severity` INT(11) NOT NULL DEFAULT '0',
  `value` TEXT NOT NULL,
  `logeventid` INT(11) NOT NULL DEFAULT '0',
  `ns` INT(11) NOT NULL DEFAULT '0',
  KEY (`id`),
  KEY `history_log_2` (`itemid`,`id`),
  KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(clock)
(PARTITION p201609 VALUES LESS THAN (UNIX_TIMESTAMP('2016-10-01')) ENGINE = TokuDB,
 PARTITION p201610 VALUES LESS THAN (UNIX_TIMESTAMP('2016-11-01')) ENGINE = TokuDB,
 PARTITION p201611 VALUES LESS THAN (UNIX_TIMESTAMP('2016-12-01')) ENGINE = TokuDB,
 PARTITION pmore VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB
);

-- history_str
RENAME TABLE history_str TO history_str_old;
CREATE TABLE `history_str` (
  `itemid` BIGINT(20) UNSIGNED NOT NULL,
  `clock` INT(11) NOT NULL DEFAULT '0',
  `value` VARCHAR(255) NOT NULL DEFAULT '',
  `ns` INT(11) NOT NULL DEFAULT '0',
  KEY `history_str_1` (`itemid`,`clock`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(clock)
(PARTITION p201609 VALUES LESS THAN (UNIX_TIMESTAMP('2016-10-01')) ENGINE = TokuDB,
 PARTITION p201610 VALUES LESS THAN (UNIX_TIMESTAMP('2016-11-01')) ENGINE = TokuDB,
 PARTITION p201611 VALUES LESS THAN (UNIX_TIMESTAMP('2016-12-01')) ENGINE = TokuDB,
 PARTITION pmore VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB
);

-- history_text
RENAME TABLE history_text TO history_text_old;
CREATE TABLE `history_text` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `itemid` BIGINT(20) UNSIGNED NOT NULL,
  `clock` INT(11) NOT NULL DEFAULT '0',
  `value` TEXT NOT NULL,
  `ns` INT(11) NOT NULL DEFAULT '0',
  KEY (`id`),
  KEY `history_text_2` (`itemid`,`id`),
  KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(clock)
(PARTITION p201609 VALUES LESS THAN (UNIX_TIMESTAMP('2016-10-01')) ENGINE = TokuDB,
 PARTITION p201610 VALUES LESS THAN (UNIX_TIMESTAMP('2016-11-01')) ENGINE = TokuDB,
 PARTITION p201611 VALUES LESS THAN (UNIX_TIMESTAMP('2016-12-01')) ENGINE = TokuDB,
 PARTITION pmore VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB
);

-- history_uint
RENAME TABLE history_uint TO history_uint_old;
CREATE TABLE `history_uint` (
  `itemid` BIGINT(20) UNSIGNED NOT NULL,
  `clock` INT(11) NOT NULL DEFAULT '0',
  `value` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
  `ns` INT(11) NOT NULL DEFAULT '0',
  KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(clock)
(PARTITION p201609 VALUES LESS THAN (UNIX_TIMESTAMP('2016-10-01')) ENGINE = TokuDB,
 PARTITION p201610 VALUES LESS THAN (UNIX_TIMESTAMP('2016-11-01')) ENGINE = TokuDB,
 PARTITION p201611 VALUES LESS THAN (UNIX_TIMESTAMP('2016-12-01')) ENGINE = TokuDB,
 PARTITION pmore VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB
);

-- trends
RENAME TABLE trends TO trends_old;
CREATE TABLE `trends` (
  `itemid` BIGINT(20) UNSIGNED NOT NULL,
  `clock` INT(11) NOT NULL DEFAULT '0',
  `num` INT(11) NOT NULL DEFAULT '0',
  `value_min` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
  `value_avg` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
  `value_max` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`itemid`,`clock`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(clock)
(PARTITION p201609 VALUES LESS THAN (UNIX_TIMESTAMP('2016-10-01')) ENGINE = TokuDB,
 PARTITION p201610 VALUES LESS THAN (UNIX_TIMESTAMP('2016-11-01')) ENGINE = TokuDB,
 PARTITION p201611 VALUES LESS THAN (UNIX_TIMESTAMP('2016-12-01')) ENGINE = TokuDB,
 PARTITION pmore VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB
);

-- trends_uint
RENAME TABLE trends_uint TO trends_uint_old;
CREATE TABLE `trends_uint` (
  `itemid` BIGINT(20) UNSIGNED NOT NULL,
  `clock` INT(11) NOT NULL DEFAULT '0',
  `num` INT(11) NOT NULL DEFAULT '0',
  `value_min` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
  `value_avg` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
  `value_max` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`itemid`,`clock`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(clock)
(PARTITION p201609 VALUES LESS THAN (UNIX_TIMESTAMP('2016-10-01')) ENGINE = TokuDB,
 PARTITION p201610 VALUES LESS THAN (UNIX_TIMESTAMP('2016-11-01')) ENGINE = TokuDB,
 PARTITION p201611 VALUES LESS THAN (UNIX_TIMESTAMP('2016-12-01')) ENGINE = TokuDB,
 PARTITION pmore VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB
);

导出表数据

使用SELECT ... INTO OUTFILE ...命令将旧表数据分月份导出到文件。

数据导出脚本：

-- history
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_old_201609' FROM zabbix.history_old WHERE clock >= UNIX_TIMESTAMP('2016-09-01') AND clock < UNIX_TIMESTAMP('2016-10-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_old_201610' FROM zabbix.history_old WHERE clock >= UNIX_TIMESTAMP('2016-10-01') AND clock < UNIX_TIMESTAMP('2016-11-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_old_201611' FROM zabbix.history_old WHERE clock >= UNIX_TIMESTAMP('2016-11-01') AND clock < UNIX_TIMESTAMP('2016-12-01');

-- history_log
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_log_old_201609' FROM zabbix.history_log_old WHERE clock >= UNIX_TIMESTAMP('2016-09-01') AND clock < UNIX_TIMESTAMP('2016-10-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_log_old_201610' FROM zabbix.history_log_old WHERE clock >= UNIX_TIMESTAMP('2016-10-01') AND clock < UNIX_TIMESTAMP('2016-11-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_log_old_201611' FROM zabbix.history_log_old WHERE clock >= UNIX_TIMESTAMP('2016-11-01') AND clock < UNIX_TIMESTAMP('2016-12-01');

-- history_str
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_str_old_201609' FROM zabbix.history_str_old WHERE clock >= UNIX_TIMESTAMP('2016-09-01') AND clock < UNIX_TIMESTAMP('2016-10-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_str_old_201610' FROM zabbix.history_str_old WHERE clock >= UNIX_TIMESTAMP('2016-10-01') AND clock < UNIX_TIMESTAMP('2016-11-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_str_old_201611' FROM zabbix.history_str_old WHERE clock >= UNIX_TIMESTAMP('2016-11-01') AND clock < UNIX_TIMESTAMP('2016-12-01');

-- history_text
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_text_old_201609' FROM zabbix.history_text_old WHERE clock >= UNIX_TIMESTAMP('2016-09-01') AND clock < UNIX_TIMESTAMP('2016-10-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_text_old_201610' FROM zabbix.history_text_old WHERE clock >= UNIX_TIMESTAMP('2016-10-01') AND clock < UNIX_TIMESTAMP('2016-11-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_text_old_201611' FROM zabbix.history_text_old WHERE clock >= UNIX_TIMESTAMP('2016-11-01') AND clock < UNIX_TIMESTAMP('2016-12-01');

-- history_uint
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_uint_old_201609' FROM zabbix.history_uint_old WHERE clock >= UNIX_TIMESTAMP('2016-09-01') AND clock < UNIX_TIMESTAMP('2016-10-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_uint_old_201610' FROM zabbix.history_uint_old WHERE clock >= UNIX_TIMESTAMP('2016-10-01') AND clock < UNIX_TIMESTAMP('2016-11-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/history_uint_old_201611' FROM zabbix.history_uint_old WHERE clock >= UNIX_TIMESTAMP('2016-11-01') AND clock < UNIX_TIMESTAMP('2016-12-01');

-- trends
SELECT * INTO OUTFILE '/opt/tmp_zabbix/trends_old_201609' FROM zabbix.trends_old WHERE clock >= UNIX_TIMESTAMP('2016-09-01') AND clock < UNIX_TIMESTAMP('2016-10-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/trends_old_201610' FROM zabbix.trends_old WHERE clock >= UNIX_TIMESTAMP('2016-10-01') AND clock < UNIX_TIMESTAMP('2016-11-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/trends_old_201611' FROM zabbix.trends_old WHERE clock >= UNIX_TIMESTAMP('2016-11-01') AND clock < UNIX_TIMESTAMP('2016-12-01');

-- trends_uint
SELECT * INTO OUTFILE '/opt/tmp_zabbix/trends_uint_old_201609' FROM zabbix.trends_uint_old WHERE clock >= UNIX_TIMESTAMP('2016-09-01') AND clock < UNIX_TIMESTAMP('2016-10-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/trends_uint_old_201610' FROM zabbix.trends_uint_old WHERE clock >= UNIX_TIMESTAMP('2016-10-01') AND clock < UNIX_TIMESTAMP('2016-11-01');
SELECT * INTO OUTFILE '/opt/tmp_zabbix/trends_uint_old_201611' FROM zabbix.trends_uint_old WHERE clock >= UNIX_TIMESTAMP('2016-11-01') AND clock < UNIX_TIMESTAMP('2016-12-01');

导入表数据

使用LOAD DATA INFILE命令将各表数据分月份导入到新表。
由于在导出时，已按照月份将每月数据分别导出到对应的单独文件，所以在导入时可以指定每个数据文件对应的分区，以加快导入速度。

在导入时，可以临时禁用当前session的binlog，设置sync_binlog = 0，tokudb_commit_sync = 0，以加快导入速度。同时，也可以增大tokudb_lock_timeout的值，以避免在导入本月分区的数据时，Zabbix写入数据等待超时而导致的这段时间的监控数据丢失。具体操作如下：

首先设置以下GLOBAL变量：

1
2
3

SET GLOBAL sync_binlog = 0;
SET GLOBAL tokudb_commit_sync = 0;
SET GLOBAL tokudb_lock_timeout = 3600000;  -- 这里设置为1小时

Kill掉Zabbix的数据库连接，以使刚才设置的全局变量生效；
退出当前登录的MySQL会话，重新登录以使刚才设置的全局变量生效。并设置以下SESSION变量，临时禁用binlog：
1
SET SESSION sql_log_bin = 0;
执行以下数据导入脚本；
导入数据完成后，再恢复设置以上更改的变量值；

数据导入脚本：

-- history
LOAD DATA INFILE '/opt/tmp_zabbix/history_old_201609' INTO TABLE zabbix.history PARTITION (p201609);
LOAD DATA INFILE '/opt/tmp_zabbix/history_old_201610' INTO TABLE zabbix.history PARTITION (p201610);
LOAD DATA INFILE '/opt/tmp_zabbix/history_old_201611' INTO TABLE zabbix.history PARTITION (p201611);

-- history_log
LOAD DATA INFILE '/opt/tmp_zabbix/history_log_old_201609' INTO TABLE zabbix.history_log PARTITION (p201609);
LOAD DATA INFILE '/opt/tmp_zabbix/history_log_old_201610' INTO TABLE zabbix.history_log PARTITION (p201610);
LOAD DATA INFILE '/opt/tmp_zabbix/history_log_old_201611' INTO TABLE zabbix.history_log PARTITION (p201611);

-- history_str
LOAD DATA INFILE '/opt/tmp_zabbix/history_str_old_201609' INTO TABLE zabbix.history_str PARTITION (p201609);
LOAD DATA INFILE '/opt/tmp_zabbix/history_str_old_201610' INTO TABLE zabbix.history_str PARTITION (p201610);
LOAD DATA INFILE '/opt/tmp_zabbix/history_str_old_201611' INTO TABLE zabbix.history_str PARTITION (p201611);

-- history_text
LOAD DATA INFILE '/opt/tmp_zabbix/history_text_old_201609' INTO TABLE zabbix.history_text PARTITION (p201609);
LOAD DATA INFILE '/opt/tmp_zabbix/history_text_old_201610' INTO TABLE zabbix.history_text PARTITION (p201610);
LOAD DATA INFILE '/opt/tmp_zabbix/history_text_old_201611' INTO TABLE zabbix.history_text PARTITION (p201611);

-- history_uint
LOAD DATA INFILE '/opt/tmp_zabbix/history_uint_old_201609' INTO TABLE zabbix.history_uint PARTITION (p201609);
LOAD DATA INFILE '/opt/tmp_zabbix/history_uint_old_201610' INTO TABLE zabbix.history_uint PARTITION (p201610);
LOAD DATA INFILE '/opt/tmp_zabbix/history_uint_old_201611' INTO TABLE zabbix.history_uint PARTITION (p201611);

-- trends
LOAD DATA INFILE '/opt/tmp_zabbix/trends_old_201609' INTO TABLE zabbix.trends PARTITION (p201609);
LOAD DATA INFILE '/opt/tmp_zabbix/trends_old_201610' INTO TABLE zabbix.trends PARTITION (p201610);
LOAD DATA INFILE '/opt/tmp_zabbix/trends_old_201611' INTO TABLE zabbix.trends PARTITION (p201611);

-- trends_uint
LOAD DATA INFILE '/opt/tmp_zabbix/trends_uint_old_201609' INTO TABLE zabbix.trends_uint PARTITION (p201609);
LOAD DATA INFILE '/opt/tmp_zabbix/trends_uint_old_201610' INTO TABLE zabbix.trends_uint PARTITION (p201610);
LOAD DATA INFILE '/opt/tmp_zabbix/trends_uint_old_201611' INTO TABLE zabbix.trends_uint PARTITION (p201611);

删除旧表

将表数据导入到新表后，查看Zabbix数据、图表展示是否正常。如果正常，即可删除旧表。

-- truncate table
TRUNCATE TABLE zabbix.history_old;
TRUNCATE TABLE zabbix.history_log_old;
TRUNCATE TABLE zabbix.history_str_old;
TRUNCATE TABLE zabbix.history_text_old;
TRUNCATE TABLE zabbix.history_uint_old;
TRUNCATE TABLE zabbix.trends_old;
TRUNCATE TABLE zabbix.trends_uint_old;

-- drop table
DROP TABLE zabbix.history_old;
DROP TABLE zabbix.history_log_old;
DROP TABLE zabbix.history_str_old;
DROP TABLE zabbix.history_text_old;
DROP TABLE zabbix.history_uint_old;
DROP TABLE zabbix.trends_old;
DROP TABLE zabbix.trends_uint_old;

禁用Zabbix的Housekeeping

进行表分区后，通过删除表的历史分区即可清除历史数据，而Zabbix的Housekeeping默认会每隔一小时执行清除历史数据操作，所以需要关闭Housekeeping的定时清理任务。

进入Zabbix菜单：Administration -> General，选择右上角下拉菜单的“Housekeeping”，取消勾选“History”和“Trends”栏目下的“Enable internal housekeeping”，保存既可。

参考文档

Zabbix官方文档：
http://zabbix.org/wiki/Docs/howto/mysql_partition
处理Zabbix历史数据库办法二——使用MySQL表分区：
http://john88wang.blog.51cto.com/2165294/1771557
迁移Zabbix数据库到TokuDB：
http://imysql.com/2014/06/24/migrate-zabbix-db-to-tokudb.shtml