Zabbix MySQL Database Partitioning 表分区优化
前言
Zabbix 默认会启用 housekeeping 功能用于清理 history/trend 等历史数据,当监控服务器数量增加,保留时间有要求的情况下,housekeeping 的清理策略就会造成 Zabbix Server 性能下降,比如查询历史监控数据等。Zabbix 官方的建议是直接在数据库按照时间唯独创建分区表并定时清理,好处自然就是减少 Zabbix Server 的负担提升 Zabbix 应用对于数据库读写性能。Zabbix 3.4 之后的版本增加了对 Elasticsearch 的支持,个人也非常推荐大家尝试。
Zabbix MySQL Database Partitioning 表分区优化禁用 housekeeping 提升历史数据清理性能
更新历史
2018 年 12 月 02 日 - 初稿
阅读原文 - https://liaojiaxin158.github.io/post/zabbix-mysql-partition/
扩展阅读
MySQL Database Partitioning - https://www.zabbix.org/wiki/Docs/howto/mysql_partition
禁用 housekeeping
Zabbix 2.2+ 版本请直接通过 UI 界面禁用 housekeeping
Zabbix UI in the “Administration” -> “General” section. Make sure you select “Housekeeping” in the drop-down in the upper right.
- Make sure that the checkboxes with the title “Enable internal housekeeping” are unchecked for both History and Trends.
- Make sure that the checkboxes with the title “Override item <trend/history> period” are checked for both History and Trends.
- Set the “Data storage period (in days)” box for history and trends to the amount of time you are retaining both. In the examples that were given, the correct values are 28 and 730 (as seen in the screenshot).
创建存储过程
Zabbix 数据库优化
目的: 快速清理历史数据,并减少数据存储容量
方法: 历史表使用分区表 (删除分区表速度快)
Zabbix 版本: 3.2 + 以上版本,其它版本需要创建 Index 索引,请参考官方文档
涉及表项: 存储不同类型 item 的历史数据,最终 1 小时或者 1 天等段时间的绘图数据从其中获取 history、history_log、history_str、history_text、history_uint,存储不同类型 item 的历史趋势数据,每隔一小时从历史数据中统计一次,并计算统计区间的平均值,最大值,最小值 trends、trends_uint
Alright, let’s go ahead and add the necessary stored procedures to the Zabbix database. These procedures are documented in the Documentation section if you want more details about how they work. This section is going to look a little messy, but don’t be intimidated. Just copy/paste each section of SQL code. Due to weirdness in how the output will look, I can’t really show the”mysql>”prompts here. As long as you see”Query OK, 0 rows affected (0.00 sec)”after running each section AND make sure the”DELIMITER ;”SQL actually runs (sometimes you’ll have to press ENTER), you’ll be fine.
1 | # login mysql |
There are four functions in the stored procedures:
- partition_create - This will create a partition on the given table in the given schema.
- partition_drop - This will drop partitions older than the given timestamp on the given table in the given schema.
- partition_maintenance - This function is what is called by users. It is responsible for parsing the given parameters and then creating/dropping partitions as needed.
- partition_verify - Checks if partitioning is enabled on the given table in the given schema. If it is not enable, it creates a single partition.
存储过程执行后将可以使用命令对想要分区的表进行表分区了,其中的参数我这里解释一下。
CALL partition_maintenance('<zabbix_db_name>', '<table_name>', <days_to_keep_data>, <hourly_interval>, <num_future_intervals_to_create>)
这是举例:
CALL partition_maintenance(zabbix, 'history_uint', 31, 24, 14);
zabbix_db_name:库名
table_name:表名
days_to_keep_data:保存多少天的数据
hourly_interval:每隔多久生成一个分区
num_future_intervals_to_create:本次一共生成多少个分区
这个例子就是 history_uint 表最多保存 31 天的数据,每隔 24 小时生成一个分区,这次一共生成 14 个分区
这里可以将上面四个存储过程保存为一个文件,导入到数据库中,文件我稍后将会放在附件中,这里使用的命令是
mysql -uzabbix -pzabbix zabbix < partition_call.sql
然后可以将 CALL 统一调用也做成一个文件
创建定时任务
- cronjob 里的脚本包括了建新表和删除旧表,用 mysql 的 procedure 控制,删除旧表可以释放空间
- 想要修改短一点,需要修改 procedure partition_maintenance_all 里规定的时间
- 我的做法是 Drop 旧 procedure 再创建新的
- login mysql zabbix
- DROP PROCEDURE IF EXISTS partition_maintenance_all
- 根据需要修改括号内第三列的时间,估计得改成 45 或者 30 了。每列的定义请参照最上面给的链接
- 再手动跑下 cronjob 内的那个指令就好
1 | [root@sg-gop-10-65-200-90 mysql]# grep -Ev '^$|#' /etc/zabbix/zabbix_server.conf |
参考文档
https://www.zabbix.org/wiki/Docs/howto/mysql_partition
https://cloud.tencent.com/developer/article/1006301
https://liaojiaxin158.github.io/post/zabbix-mysql-partition/