## 前言

Zabbix 默认会启用 housekeeping 功能用于清理 history/trend 等历史数据，当监控服务器数量增加，保留时间有要求的情况下，housekeeping 的清理策略就会造成 Zabbix Server 性能下降，比如查询历史监控数据等。Zabbix 官方的建议是直接在数据库按照时间唯独创建分区表并定时清理，好处自然就是减少 Zabbix Server 的负担提升 Zabbix 应用对于数据库读写性能。Zabbix 3.4 之后的版本增加了对 Elasticsearch 的支持，个人也非常推荐大家尝试。

Zabbix MySQL Database Partitioning 表分区优化禁用 housekeeping 提升历史数据清理性能

## 更新历史

2018 年 12 月 02 日 - 初稿

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 索引，请参考官方文档

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.

There are four functions in the stored procedures:

1. partition_create - This will create a partition on the given table in the given schema.
2. partition_drop - This will drop partitions older than the given timestamp on the given table in the given schema.
3. 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.
4. 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：本次一共生成多少个分区

mysql -uzabbix -pzabbix zabbix < partition_call.sql


## 创建定时任务

• cronjob 里的脚本包括了建新表和删除旧表，用 mysql 的 procedure 控制，删除旧表可以释放空间
• 想要修改短一点，需要修改 procedure partition_maintenance_all 里规定的时间
• 我的做法是 Drop 旧 procedure 再创建新的