利用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 ;

[MySQL FAQ]系列 — 大数据量时如何部署MySQL Replication从库

我们在部署MySQL Replication从库时,通常是一开始就做好一个从库,然后随着业务的变化,数据也逐渐复制到从服务器。

但是,如果我们想对一个已经上线较久,有这大数据量的数据库部署复制从库时,应该怎么处理比较合适呢?

本文以我近期所做Zabbix数据库部署MySQL Replication从库为例,向大家呈现一种新的复制部署方式。由于Zabbix历史数据非常多,在转TokuDB之前的InnoDB引擎时,已经接近700G,转成TokuDB后,还有300多G,而且主要集中在trends_uint、history_uint等几个大表上。做一次全量备份后再恢复耗时太久,怕对主库写入影响太大,因此才有了本文的分享。

我大概分为几个步骤来做Zabbix数据迁移的:

1、初始化一个空的Zabbix库

2、启动复制,但设置忽略几个常见错误(这几个错误代码对应具体含义请自行查询手册)
#忽略不重要的错误,极端情况下,甚至可以直接忽略全部错误,例如
#slave-skip-errors=all
slave-skip-errors=1032,1053,1062

3、将大多数小表正常备份导出,在SLAVE服务器上导入恢复。在这里,正常导出即可,无需特别指定 --master-data 选项

4、逐一导出备份剩下的几个大表。在备份大表时,还可以分批次并发导出,方便并发导入,使用mysqldump的"-w"参数,然后在SLAVE上导入恢复(可以打开后面的参考文章链接)

5、全部导入完成后,等待复制没有延迟了,关闭忽略错误选项,重启,正式对外提供服务

上述几个步骤完成后,可能还有个别不一致的数据,不过会在后期逐渐被覆盖掉,或者被当做过期历史数据删除掉。

本案例的步骤并不适用于全部场景,主要适用于:

不要求数据高一致性,且数据量相对较大,尤其是单表较大的情况,就像本次的Zabbix数据一样。

参考文章:

迁移Zabbix数据库到TokuDB

[MySQL FAQ]系列— mysqldump加-w参数备份

迁移Zabbix数据库到TokuDB

背景介绍

线上的Zabbix数据库有几个大表数据量疯狂增长,单表已经超过500G,而且在早期也没做成分区表,后期维护非常麻烦。比如,想删除过期的历史数据,在原先的模式下,history、history_uint等几个大表是用 (itemid, clock) 两个字段做的联合主键,只用 clock 字段检索效率非常差。

TokuDB 是一个高性能、支持事务处理的 MySQL 和 MariaDB 的存储引擎。TokuDB 的主要特点是高压缩比,高 INSERT 性能,支持大多数在线修改索引、添加字段,特别适合像 Zabbix 这种高 INSERT,少 UPDATE 的应用场景。

迁移准备

欲使用 TokuDB 引擎,服务层可以选择和 MariaDB ,也可以选择 Percona ,鉴于我以往使用 Percona 的较多,因此本次也选择使用 Percona 版本集成 TokuDB 引擎。

当前最新版下载地址:http://www.percona.com/redir/downloads/Percona-Server-5.6/LATEST/binary/tarball/Percona-Server-5.6.17-rel66.0-608.TokuDB.Linux.x86_64.tar.gz

按照正常方式安装即可,配置文件中增加3行:

malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so
plugin-dir = /usr/local/mysql/lib/mysql/plugin/
plugin-load=ha_tokudb.so

如果不加载jemalloc,启动时就会有类似下面的报错:

[ERROR] TokuDB not initialized because jemalloc is not loaded
[ERROR] Plugin 'TokuDB' init function returned error.
[ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed.

并且,修改内核配置,禁用transparent_hugepage,不关闭的话可能会导致TokuDB内存泄露(建议写到 /etc/rc.local 中,重启后仍可生效):

echo never > /sys/kernel/mm/redhat_transparent_hugepage/defrag
echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

如果不修改内核设置,启动时就会有类似下面的报错:

Transparent huge pages are enabled, according to /sys/kernel/mm/redhat_transparent_hugepage/enabled
Transparent huge pages are enabled, according to /sys/kernel/mm/transparent_hugepage/enabled
[ERROR] TokuDB will not run with transparent huge pages enabled.
[ERROR] Please disable them to continue.
[ERROR] (echo never > /sys/kernel/mm/transparent_hugepage/enabled)
[ERROR]
[ERROR] ************************************************************
[ERROR] Plugin 'TokuDB' init function returned error.
[ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed.

然后,初始化数据库,启动即可。

我的服务器配置:E5-2620 * 2,64G内存,1T可用磁盘空间(建议datadir所在分区设置为xfs文件系统),下面是我使用的相关选项,仅供参考:

#
#my.cnf
# 
# Percona-5.6.17, TokuDB-7.1.6,用于Zabbix数据库参考配置
# 我的服务器配置:E5-2620 * 2,64G内存,1T可用磁盘空间(建议datadir所在分区设置为xfs文件系统)
# TokuDB版本:Percona-5.6.17, TokuDB-7.1.6(插件加载模式)
# 
# created by yejr(http://imysql.com), 2014/06/24
# 
[client]
port            = 3306
socket          = mysql.sock
#default-character-set=utf8
 
[mysql]
prompt="\\u@\\h \\D \\R:\\m:\\s [\\d]>
#pager="less -i -n -S"
tee=/home/mysql/query.log
no-auto-rehash
 
[mysqld]
open_files_limit = 8192
max_connect_errors = 100000
 
#buffer & cache
table_open_cache = 2048
table_definition_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
 
#innodb
#只有部分小表保留InnoDB引擎,因此InnoDB Buffer Pool设置为1G基本上够了
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_file_per_table = 1
innodb_status_file = 1
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

#tokudb
malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so
plugin-dir = /usr/local/mysql/lib/mysql/plugin/
plugin-load=ha_tokudb.so
 
#把TokuDB datadir以及logdir和MySQL的datadir分开,美观点,也可以不分开,注释掉本行以及下面2行即可
tokudb-data-dir = /data/mysql/zabbix_3306/tokudbData
tokudb-log-dir = /data/mysql/zabbix_3306/tokudbLog
 
#TokuDB的行模式,建议用 FAST 就足够了,如果磁盘空间很紧张,建议用 SMALL
#tokudb_row_format = tokudb_small
tokudb_row_format = tokudb_fast
tokudb_cache_size = 44G
 
#其他大部分配置其实可以不用修改的,只需要几个关键配置即可
tokudb_commit_sync = 0
tokudb_directio = 1
tokudb_read_block_size = 128K
tokudb_read_buf_size = 128K

迁移过程

建议在一台全新的服务器上启动Percona(TokuDB)实例进程,初始化新的Zabbix数据库,直接将大表转成TokuDB引擎,并且开启分区模式。这样相比直接在线ALTER TABLE或者INSERT…SELECT导入数据都要来的快一些(我简单测试了下,差不多能快2-3倍,甚至更高)。

在做数据迁移时,建议在目标服务器上做库表结构初始化,在源服务器上采用分段方式导出,一个表导出多个备份文件,方便在恢复时可以并发导入。在导入时,并且记得临时关闭 binlog,最起码设置 sync_binlog = 0 以及 tokudb_commit_sync = 0,以提高导入速度。采用 mysqldump 增加 -w 参数即可实现根据条件分段导出,具体可参考上一次的文章:[MySQL FAQ]系列— mysqldump加-w参数备份,或者是用MySQLDumper

需要用到外键的表继续保留InnoDB引擎,其他表都可以转成TokuDB,history_str、trends、trends_uint、history、history_uint等几个大表是一定要转成TokuDB的,events由于需要用到外键,所以继续保留InnoDB引擎。

我将表结构初始化SQL脚本提供下载了,一份是没有采用分区表的,一份是采用分区表的,大家可自行选择。一般如果记录数超过1亿,就建议使用分区表,根据时间字段(clock)分区,方便后期维护,例如删除过期历史数据什么的。

收尾

剩下的基本没啥可做的了,就是观察下运行状态,是否还有个别慢查询堵塞。在我的环境中,一开始把items表也转成TokuDB了,结果有个画图的SQL执行计划不准确,非常慢。后来发现items表也需要用到外键,于是又转回InnoDB表,这个SQL也恢复正常了。

数据库初始化脚本我整理后提供下载了,大家可以直接使用。

附件1:不使用分区表附件2:使用分区表

适用版本:

Zabbix版本:Zabbix 2.2.0
TokuDB版本:Percona-5.6.17, TokuDB-7.1.6(插件加载模式)

如果还有什么问题,欢迎加入我的QQ群(272675472)讨论。