简介面向Zabbix运维与监控系统管理员的数据库优化操作记录针对Zabbix 7.0 LTS及3.0后各版本在MySQL/MariaDB场景下历史表、趋势表数据膨胀导致的性能下降与housekeeper繁忙告警提供基于zbx_db_partitiong.sql的分区实施方案。内容涵盖备份提醒、SQL脚本下载与执行、分区过程自动调度、前端保留天数配置及自定义分区周期等关键环节并附操作注意事项适合有一定Zabbix基础、需要落地数据库分区策略的读者。资料为单个PDF文档大小835KB已吸引354人学习查看。通过阅读可系统掌握用分区替代DELETE清理旧数据、提升维护效率的具体步骤与排错思路避免大型库执行分区时的常见风险。1. Zabbix 7.0 LTS 数据库分区先把历史表和趋势表的删除压力拆掉Zabbix 7.0 LTS 部署完成后真正让人睡不安稳的往往不是采集器或前端而是数据库。监控规模一上来history和trends系列表的数据量会以肉眼可见的速度膨胀默认的 housekeeper 通过DELETE清理旧数据每次触发都把 InnoDB 的 undo log 和二级索引搅得天翻地覆直接表现就是告警“Zabbix housekeeper processes more than 75% busy”。分区不是玄学它把“删行”变成“删表”让旧数据的清理从 SQL 层的逐行操作变成文件系统层面的目录摘除。这份资源核心就是一套zbx_db_partitiong.sql脚本和配套操作记录适用 Zabbix 3.0 之后的所有版本包括 7.0 LTS直接能落地。2. 分区脚本选型与部署拿到脚本先改三个参数再入库2.1 为什么选 RANGE COLUMNS 而不是 HASH 分区监控数据的查询模式非常固定按itemid加时间范围取数写入天然是顺序追加几乎没有跨分区更新的需求。看到脚本里用的是PARTITION BY RANGE (clock)这点选型是对的。按时间范围做 RANGE 分区每个分区对应一个时间窗口查询优化器能直接做分区裁剪只扫必要的那几个分区而不是全表扫描。HASH 分区虽然能把数据打散到不同分区但对时间范围查询不友好Zabbix 的历史表按时间清理时也必须跨分区扫描等于白做。Zabbix 7.0 LTS 对 MySQL 8.0 和 MariaDB 10.5 的兼容性都已经很成熟分区语法在这两个分支下没有差别。值得留意的是 MySQL 8.0 之后不再支持PARTITION BY RANGE里的TO_DAYS()这类函数写法脚本用的是VALUES LESS THAN (unix_timestamp)的原始格式这反而是兼容性最好的做法。2.2 下载解压并核对脚本内容这份资源的脚本包体积不大按记录里的做法是 wget 加 tar 解压我在实际部署时习惯先下载到/opt/scripts目录避免随手丢在 root 家目录里造成管理混乱。解压后不要直接执行先看一眼脚本里的三个关键参数。mkdir -p /opt/scripts cd /opt/scripts wget http://bestmonitoringtools.com/dl/zbx_db_partitiong.tar.gz tar -zxvf zbx_db_partitiong.tar.gz ls -l zbx_db_partitiong.sql这里有个习惯问题tar 包解压出来的文件名是固定的zbx_db_partitiong.sql如果你之前下载过旧版本新文件会直接覆盖旧文件。我一般先在别的目录解压确认内容后再复制到工作目录。脚本本身不包含建库建表的 DDL它只处理已有的 Zabbix 库表所以不用担心误初始化数据。2.3 入库前必改的三个参数脚本默认的保留策略是历史数据 7 天、趋势数据 365 天这是给小型监控环境兜底的保守值。但凡你的监控项超过 500 个或者有高频采集30 秒以下间隔7 天的 history 保留期会让分区数量增长速度超出预期。我在生产环境落这个脚本时通常直接改成 30 天历史、400 天趋势这个量级对 disk 和查询性能都比较均衡。vim zbx_db_partitiong.sql # 找到 CREATE PROCEDURE partition_maintenance_all 部分修改 keep 天数 CALL partition_maintenance(SCHEMA_NAME, history, 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, history_log, 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, history_str, 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, history_text, 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, history_uint, 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, trends, 400, 24, 3); CALL partition_maintenance(SCHEMA_NAME, trends_uint, 400, 24, 3);每个partition_maintenance调用的四个参数含义分别是库名、表名、保留数据天数、分区时间间隔小时、预创建分区数量。24表示按天分区3表示提前创建 3 个未来分区这个预创建机制很关键避免跨天瞬间没有可用分区导致写入失败。改完参数后保存再执行下一步导入。3. 存储过程逻辑拆解与首轮执行四段式结构背后的运行顺序3.1 核心存储过程的功能拆解这套脚本并不是一次性跑完就结束它在数据库里创建了四个存储过程真正干活的是partition_maintenance_all这个调度入口。拆开看四个过程各司其职partition_verify检查目标表是否已有分区没有分区时先做初始化分区。partition_create按给定时间戳创建新分区创建前会查 information_schema 防止重复。partition_drop扫描分区名把时间早于保留阈值的分区整体 DROP 掉。partition_maintenance串联前三个过程按间隔计算分区名和时间戳控制预创建数量。3.2 脚本执行命令与首次运行诊断导入脚本的命令语法在记录里写得很明确但实际执行有几个细节容易翻车。先确认 Zabbix 数据库名和账号权限如果 Zabbix 库名不是zabbix比如有人习惯叫zabbix_prod命令里的库名必须同步改。mysql -u zabbix -pzabbixDBpass zabbix zbx_db_partitiong.sql提示执行导入时不要用 root 账号去跑Zabbix 库的读写账号没有 SUPER 权限也不影响存储过程创建但 root 执行会把历史遗留的权限问题掩盖掉后面调度阶段容易出幺蛾子。首次导入脚本时如果库里已经积累了几个月的数据partition_verify会执行一次全表重建分区这个操作在 InnoDB 下会触发 Online DDL大表可能要跑一两个小时。期间不要手动去 kill 会话我见过有人等不及直接 CtrlC结果表结构停留在中间状态后面所有写入都报Table has no partition for value修复比等待更痛苦。导入完成后马上验证存储过程是否到位这是判断脚本有没有成功的第一步。SELECT routine_name FROM information_schema.routines WHERE routine_schema zabbix;正常情况下能看到partition_create、partition_drop、partition_maintenance、partition_verify四个存储过程。如果缺了某个多半是导入时 SQL 语法报错被忽略把 mysql 客户端的错误输出重定向到文件再查一次。3.3 手动触发一次全流程验证表分区结构脚本导入成功不代表分区逻辑正确必须先手动调用一次partition_maintenance_all观察返回信息。这一步是上线前唯一能低成本验证整套逻辑的机会。mysql -u zabbix -pzabbixDBpass zabbix -e CALL partition_maintenance_all(zabbix);正常输出会包含partition_create(zabbix, history, p202501200000, 1737331200)这样的信息行以及partition_drop返回的N/A或实际删除的分区名。看到这些输出后再用SHOW CREATE TABLE history确认分区定义。SHOW CREATE TABLE history\G输出中PARTITION BY RANGE (clock)之后跟着一串PARTITION p202501190000 VALUES LESS THAN (1737244800)的定义说明分区已经生效。如果这里看到PARTITION p202501200000 VALUES LESS THAN (1737331200)对应的时间戳是和 UTC 对齐的注意 Zabbix 7.0 前端时区设置和数据库时区不一致会导致分区边界偏移后面避坑章会展开说。4. 定时调度事件调度器和 Crontab 双方案及故障切换4.1 MySQL 事件调度器的开启与权限边界分区过程必须周期性执行才能持续生效最推荐的做法是 MySQL 自带的事件调度器Event Scheduler它把调度逻辑留在数据库内部不依赖外部 cron 环境即使 Zabbix server 所在的机器重装只要数据库还在分区任务就不会丢。开启事件调度器需要改 MySQL 配置文件注意 MySQL 8.0 和 MariaDB 的配置文件路径不同不要照搬网上教程。# 在 [mysqld] 段下追加 [mysqld] event_scheduler ON修改后重启 MySQL 服务。如果是 MariaDB配置文件可能在/etc/mysql/mariadb.conf.d/下如果是 MySQL 8.0通常改/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf。重启后确认状态SHOW VARIABLES LIKE event_scheduler;检查完状态后创建事件。每条记录里的事件名我建议带上环境后缀比如zbx_partitioning_prod方便多环境管理。间隔选 12 小时一次而不是 24 小时这样即使某次执行失败还有一次补救机会。CREATE EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR DO CALL partition_maintenance_all(zabbix);这个事件创建后可以用SHOW EVENTS\G查看LAST_EXECUTED字段确认是否按时跑过。如果你发现STATUS是DISABLED检查创建事件时是否用了ENABLE关键字或者事件调度器在重启后没有重新激活。4.2 Crontab 方案的适用场景与命令细节事件调度器要求 MySQL 配置里显式开启某些托管数据库实例不给 SUPER 权限无法开启全局 event_scheduler这时候 Crontab 是更现实的替代方案。用 Crontab 有个隐性好处每次执行的输出会写入日志文件排障时能直接看数据库返回信息而不是去翻 MySQL 的 error log。sudo crontab -e # 每天凌晨 3:30 执行一次分区维护 30 03 * * * /usr/bin/mysql -u zabbix -pzabbixDBpass zabbix -e CALL partition_maintenance_all(zabbix); /tmp/CronDBpartitiong.log 21注意 cron 环境变量问题crontab 里运行的 mysql 默认用的是/usr/bin/mysql这个绝对路径如果你用which mysql查出来是/usr/local/mysql/bin/mysql必须改成实际路径否则 job 会静默失败。密码直接写在 crontab 文件里有安全风险但 Zabbix 数据库分区这个场景通常运行在内网权衡下来可以接受更讲究的做法是创建一个~/.my.cnf文件存放凭据把密码从命令行参数里去掉。4.3 调度方案切换时的常见错位从事件调度器切到 Crontab或者反过来最容易出的问题是两边同时启用分区过程被并发调用。partition_create里有去重检查但partition_drop并发执行时可能因为另一个会话已经删掉了分区而报错。切换方案时先禁用旧方案观察一个完整周期确认没有新的分区维护执行再启用新方案。-- 临时停用事件而不是直接 DROP ALTER EVENT zbx_partitioning DISABLE;保留事件定义而不是删除是给自己留一条后悔药。万一 Crontab 脚本路径写错或者权限配置失误只需要一条ALTER EVENT ... ENABLE就能恢复。这个习惯救过我一次当时某台机器的 crontab 被系统加固脚本重置分区停了三天没跑靠重新启用事件调度器补回来了。5. 前端 Housekeeper 配置与常见问题排查五个高频翻车点5.1 前端保留天数必须和存储过程参数对齐分区脚本解决了数据库层面的删除效率问题但 Zabbix 前端还有一套自己的清理逻辑。前端Administration → General → Housekeeper里的“数据存储期”设置控制的是 housekeeper 对历史表和趋势表的 DELETE 行为。如果这里的天数大于分区保留天数会出现一个尴尬局面分区已经把 30 天前的历史表数据 DROP 掉了但 housekeeper 认为还有数据需要保留扫描表时发现分区已经不存在反复报错白白消耗 CPU。配置原则只有一条前端 Housekeeper 里的历史记录和趋势存储天数必须小于等于存储过程里的 KEEP_DATA_DAYS。我的习惯是前端设置比后端少 1 天比如后端保留 30 天前端设 29 天用前端的时间触顶让 housekeeper 的 DELETE 永远落在已有分区内分区 DROP 再去清理更早的边界数据两层逻辑互不踩踏。5.2 避坑一分区边界时间戳与前端时区错位现象分区表创建成功后Zabbix 报表页面出现部分时间点数据空白但数据库查SELECT COUNT(*)有数据。原因partition_maintenance里用UNIX_TIMESTAMP(DATE_FORMAT(NOW(), %Y-%m-%d 00:00:00))计算当天零点的时间戳这个NOW()取的是 MySQL 会话时区。如果 MySQL 的time_zone是SYSTEM且和 Zabbix 前端的时区不一致比如数据库用 UTC前端用 Asia/Shanghai分区边界会比前端显示时间晚 8 个小时新数据落入前一天的分区前端按当天分区查询时自然缺数据。解决统一 MySQL 和 Zabbix 前端的时区。最稳的做法是在 MySQL 配置里固定时区而不是依赖操作系统的SYSTEM时区。[mysqld] default-time-zone 08:00改完重启 MySQL 后执行CALL partition_maintenance_all(zabbix)清理一次错位的分区边界再观察数据写入是否落到正确的分区。5.3 避坑二大表首次分区耗时过长且无进度反馈现象导入脚本后执行CALL partition_maintenance_all在history_uint表上卡住跑了两个多小时还没结束既没有报错也没有返回。原因history_uint表如果已经有上亿行数据首次分区相当于全表重建InnoDB 需要重写聚簇索引和二级索引耗时和数据量成正比。这个操作本身就是阻塞性的期间表不可写Zabbix 采集数据会持续积压。解决不要在生产环境直接对最大的表做首次分区。我的做法是拆分两步先把history_uint和trends_uint单独处理用partition_verify单独初始化再跑partition_maintenance_all。如果表实在太大建议在维护窗口做或者先停掉对应监控项的采集让表停止增长再操作。另外information_schema.partitions查询在分区初始化期间会锁元数据不要频繁执行诊断查询除了增加压力没有意义。5.4 避坑三事件执行报错Table has no partition for value现象开启事件调度器后Zabbix 日志持续刷[Z3005] query failed: [1526] Table has no partition for value ...前端图表大面积空白。原因事件调度器指定的执行间隔是 12 小时但partition_maintenance_all预创建的分区数量是 3 个按天分区足够覆盖问题通常出在事件创建语句本身写错表名或库名。执行CALL partition_maintenance_all(zabbix)时传入的库名和实际 Zabbix 数据库名不一致分区创建到错误的库下原表一直没有新分区。解决确认传入的库名参数和连接数据库时使用的库名完全一致。这里有个坑mysql -e CALL partition_maintenance_all(zabbix)环境里如果命令行连接时没有指定默认库存储过程内部仍然能执行但 create 语句里拼接的库名是错的。所以调度命令必须写成mysql zabbix -e CALL ...这种带库名的形式和导入脚本时的命令保持同一个库上下文。5.5 避坑四磁盘空间告警不降反升现象分区配置完成后磁盘空间占用没有明显下降甚至短暂上涨。原因DROP PARTITION确实比DELETE高效但在 InnoDB 下删除分区释放的空间不会立刻归还给操作系统而是先标记为可复用。如果表空间是独立文件模式innodb_file_per_tableONDROP 分区会删除对应的.ibd文件片段空间能真正释放如果用的是系统表空间碎片会留在文件内部看起来占用没变。解决确认innodb_file_per_table是开启状态MySQL 8.0 默认开启老版本迁移上来的实例要检查。如果已经用系统表空间跑了很久分区后空间没有释放需要OPTIMIZE TABLE重建一次这个操作耗时较长建议维护窗口做。另外Zabbix 的history写入高峰期和定时分区执行时间重叠时新分区创建需要分配额外空间短期内磁盘占用不降反升是正常的把调度时间调整到采集低峰期能缓解。5.6 避坑五MySQL 事件调度器重启后状态丢失现象MySQL 服务重启后SHOW VARIABLES LIKE event_scheduler显示ON但SHOW EVENTS显示所有事件的STATUS为DISABLED。原因MySQL 的事件状态默认不持久化服务重启后事件会恢复到DISABLED状态除非在事件定义时显式指定ON COMPLETION PRESERVE ENABLE。网上很多教程没有提这一点照抄创建语句的人最容易踩这个坑。解决创建事件时显式声明状态。CREATE EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR ON COMPLETION PRESERVE ENABLE DO CALL partition_maintenance_all(zabbix);如果事件已经创建用ALTER EVENT修改状态ALTER EVENT zbx_partitioning ENABLE;为了保证 MySQL 重启后事件自动恢复启用我一般会写一个小的 shell 脚本来做状态校验放在 cron 里每天检查一次。脚本逻辑很简单查询事件状态如果发现DISABLED就自动ALTER EVENT ... ENABLE并记录日志。这个脚本不在这份资源里但属于分区运维的必备补充。6. 修改保留天数与数据一致性验证从存储过程到前端对齐的关键步分区方案上线一段时间后业务方会提出新需求历史数据保留期要从默认值改成自定义值比如历史 90 天、趋势 18 个月。直接改前端 Housekeeper 的天数不够存储过程里的 KEEP_DATA_DAYS 参数也必须同步改否则分区 DROP 会把你前端还想保留的数据提前删掉。正确做法是创建一个新的存储过程而不是修改原来的过程保留旧过程作为回滚方案。6.1 创建新存储过程与事件切换以历史 90 天、趋势 550 天为例创建新的存储过程保持和原来相同的调度入口风格但参数值换掉。DELIMITER $$ CREATE PROCEDURE partition_maintenance_all_90and550(SCHEMA_NAME VARCHAR(32)) BEGIN CALL partition_maintenance(SCHEMA_NAME, history, 90, 24, 3); CALL partition_maintenance(SCHEMA_NAME, history_log, 90, 24, 3); CALL partition_maintenance(SCHEMA_NAME, history_str, 90, 24, 3); CALL partition_maintenance(SCHEMA_NAME, history_text, 90, 24, 3); CALL partition_maintenance(SCHEMA_NAME, history_uint, 90, 24, 3); CALL partition_maintenance(SCHEMA_NAME, trends, 550, 24, 3); CALL partition_maintenance(SCHEMA_NAME, trends_uint, 550, 24, 3); END$$ DELIMITER ;创建完成后把事件调度器指向新过程。这里注意不要直接 DROP 旧过程保留它对后续参数回退有帮助。ALTER EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR DO CALL partition_maintenance_all_90and550(zabbix);如果用的是 Crontab 方案直接改 crontab 里的调用命令把partition_maintenance_all换成新过程的名称。旧过程的调用行可以注释掉不要删除下次改参数时翻历史记录能快速对比。6.2 数据一致性验证的四个检查点切换保留天数后不能只看前端图表正常就认为万事大吉需要从数据层面做一致性验证。我的验证顺序是从分区边界开始逐步向上层查每一步都确认没有异常再进入下一步。验证分区创建和删除是否符合新参数重点看预创建的分区数量和保留边界。SELECT TABLE_NAME, PARTITION_NAME, PARTITION_DESCRIPTION FROM information_schema.partitions WHERE TABLE_SCHEMA zabbix AND TABLE_NAME history ORDER BY PARTITION_DESCRIPTION DESC LIMIT 5;正常结果里最顶上的分区应该覆盖未来两到三天的时间戳且不存在PARTITION_DESCRIPTION为NULL的非分区表记录。如果这里看到的都是过去时间戳说明事件调度没跑起来或者跑的是旧过程。验证历史数据和趋势数据的分区覆盖时长。SELECT MIN(FROM_UNIXTIME(clock)), MAX(FROM_UNIXTIME(clock)) FROM history;查询结果最小时间应该不早于当前时间减去保留天数。如果出现更早的数据说明partition_drop还没执行到位等下一个调度周期再看或者手动执行一次过程。同时注意history和history_uint两张表的覆盖范围不同是正常的取决于各表实际写入的监控项类型。验证 Zabbix 前端 Housekeeper 配置的一致性。# 登录 Zabbix 前端Administration - General - Housekeeper # 对比历史记录、趋势的数据存储期与数据库分区保留天数前端天数要小于等于数据库保留天数。这一步最容易被忽略前端改完数据库没改等到边界期一到housekeeper 会尝试删除已被分区 DROP 的数据报错日志里全是No partition found for ...。最后验证事件调度器的 LAST_EXECUTED 时间戳确认调度周期正常。SELECT EVENT_NAME, STATUS, LAST_EXECUTED FROM information_schema.events WHERE EVENT_NAME zbx_partitioning;6.3 调度时间窗口与分区预创建数量的调优改动保留天数时顺带可以重新评估partition_maintenance里的CREATE_NEXT_INTERVALS参数。默认值是 3代表预创建未来 3 个分区。对按天分区的场景3 个预创建分区只覆盖未来 3 天如果事件调度器连续故障两天恢复后就会面对“当前时间没有可用分区”的写入失败。我会把CREATE_NEXT_INTERVALS从 3 调整到 7成本是每次循环多创建几个空分区但缓冲时间拉长到一周调度故障的容错空间大了很多。这个参数在存储过程定义里改动需要重建存储过程或手工 DROP 后重新创建。-- 在 partition_maintenance 里把 3 改成 7 后重跑创建语句 SET __interval1; create_loop: LOOP IF __interval 7 THEN LEAVE create_loop; END IF; -- 后续逻辑不变 END LOOP;调整完这个参数检查分区表和information_schema的一致性确认新的未来分区已经创建出来。另外注意LAST_EXECUTED距离当前时间超过 24 小时基本可以断定调度链路出了问题不是调整参数能解决的需要回到事件状态或 crontab 配置去排查。6.4 保留天数调整后的回滚路径新参数上线运行后发现业务方不接受比如趋势数据保留 550 天的查询性能下降明显需要回退到原来的 400 天。不用重新导入整个脚本把调度入口指回旧过程即可。事件调度器方案一条命令能做回滚ALTER EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR DO CALL partition_maintenance_all(zabbix);Crontab 方案则把被注释的旧命令取消注释新命令注释掉。执行完成后跑一次CALL partition_maintenance_all(zabbix)手动触发强制把多余分区的 DROP 逻辑执行一遍快速收敛到旧保留边界。从那以后我每次改保留天数都强制走一遍 6.2 里的一整套验证流程先看分区边界再看数据覆盖最后核对前端配置这套顺序跑完才敢宣布操作完成。希望帮到你。本文还有配套的精品资源点击获取