Zabbix清理历史数据库，缩减表大小
2024-09-03 13:06:02
zabbix 由于历史数据过大, 因此导致磁盘空间暴涨, 下面是解决方法步骤：
一、分析数据库：
1. 统计数据库中每个表所占的空间：
mysql> SELECT table_name AS "Tables",
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = 'zabbixdb'
ORDER BY (data_length + index_length) DESC;
+----------------------------+------------+
| Tables | Size in MB |
+----------------------------+------------+
| history_uint | 29417.00 |
| history | 8287.00 |
| events | 2367.67 |
| trends_uint | 1781.91 |
| event_recovery | 1211.33 |
| trends | 548.00 |
| history_str | 381.53 |
| history_text | 58.13 |
| alerts | 46.67 |
| problem | 31.06 |
| items | 25.23 |
| auditlog | 16.53 |
| items_applications | 6.20 |
| triggers | 5.31 |
| item_discovery | 5.30 |
| housekeeper | 3.52 |
| functions | 3.33 |
| graphs | 2.23 |
| graphs_items | 2.17 |
| item_preproc | 1.88 |
| auditlog_details | 1.69 |
| images | 1.53 |
| profiles | 0.80 |
| trigger_discovery | 0.42 |
| applications | 0.38 |
| application_template | 0.38 |
| trigger_depends | 0.33 |
| graph_discovery | 0.25 |
| hosts | 0.20 |
| item_condition | 0.16 |
| sessions | 0.14 |
| widget_field | 0.11 |
| dservices | 0.09 |
| sysmaps_elements | 0.09 |
| sysmaps | 0.08 |
| httptest | 0.08 |
| interface | 0.08 |
| mappings | 0.08 |
| hosts_templates | 0.08 |
| group_prototype | 0.06 |
| hostmacro | 0.06 |
| acknowledges | 0.06 |
| host_inventory | 0.06 |
| scripts | 0.06 |
| sysmaps_links | 0.06 |
| slides | 0.05 |
| maintenances_groups | 0.05 |
| users_groups | 0.05 |
| dashboard_usrgrp | 0.05 |
| sysmap_usrgrp | 0.05 |
| maintenances | 0.05 |
| dashboard_user | 0.05 |
| actions | 0.05 |
| opgroup | 0.05 |
| sysmap_user | 0.05 |
| httpstepitem | 0.05 |
| config | 0.05 |
| item_application_prototype | 0.05 |
| opcommand_hst | 0.05 |
| services_links | 0.05 |
| host_discovery | 0.05 |
| opcommand_grp | 0.05 |
| service_alarms | 0.05 |
| problem_tag | 0.05 |
| sysmap_element_trigger | 0.05 |
| task | 0.05 |
| drules | 0.05 |
| media | 0.05 |
| optemplate | 0.05 |
| slideshows | 0.05 |
| icon_mapping | 0.05 |
| screens | 0.05 |
| slideshow_usrgrp | 0.05 |
| sysmaps_link_triggers | 0.05 |
| icon_map | 0.05 |
| opmessage_usr | 0.05 |
| maintenances_windows | 0.05 |
| screen_usrgrp | 0.05 |
| application_prototype | 0.05 |
| httptestitem | 0.05 |
| opmessage_grp | 0.05 |
| slideshow_user | 0.05 |
| maintenances_hosts | 0.05 |
| screen_user | 0.05 |
| rights | 0.05 |
| hosts_groups | 0.05 |
| application_discovery | 0.05 |
| httptest_field | 0.03 |
| opmessage | 0.03 |
| event_tag | 0.03 |
| corr_condition_group | 0.03 |
| regexps | 0.03 |
| operations | 0.03 |
| group_discovery | 0.03 |
| users | 0.03 |
| corr_condition | 0.03 |
| opconditions | 0.03 |
| services_times | 0.03 |
| dashboard | 0.03 |
| sysmap_url | 0.03 |
| conditions | 0.03 |
| trigger_tag | 0.03 |
| interface_discovery | 0.03 |
| services | 0.03 |
| sysmap_shape | 0.03 |
| graph_theme | 0.03 |
| autoreg_host | 0.03 |
| corr_operation | 0.03 |
| opcommand | 0.03 |
| sysmap_element_url | 0.03 |
| escalations | 0.03 |
| media_type | 0.03 |
| httpstep_field | 0.03 |
| screens_items | 0.03 |
| httpstep | 0.03 |
| dhosts | 0.03 |
| widget | 0.03 |
| valuemaps | 0.03 |
| expressions | 0.03 |
| dchecks | 0.03 |
| groups | 0.03 |
| usrgrp | 0.03 |
| opinventory | 0.02 |
| proxy_history | 0.02 |
| proxy_dhistory | 0.02 |
| correlation | 0.02 |
| task_remote_command | 0.02 |
| proxy_autoreg_host | 0.02 |
| task_close_problem | 0.02 |
| corr_condition_tagvalue | 0.02 |
| task_acknowledge | 0.02 |
| globalvars | 0.02 |
| corr_condition_tagpair | 0.02 |
| ids | 0.02 |
| globalmacro | 0.02 |
| corr_condition_tag | 0.02 |
| history_log | 0.02 |
| timeperiods | 0.02 |
| task_remote_command_result | 0.02 |
| dbversion | 0.02 |
+----------------------------+------------+
140 rows in set (0.68 sec)
二、清理zabbix一周之前的历史数据：
!/bin/bash
User="zabbixuser"
Passwd="zabbixpass"
Date=`date -d $(date -d "-7 day" +%Y%m%d) +%s` #取7天之前的时间戳
$(which mysql) -u${User} -p${Passwd} -e "
use zabbixdb;
DELETE FROM history WHERE 'clock' < $Date;
optimize table history;
DELETE FROM history_str WHERE 'clock' < $Date;
optimize table history_str;
DELETE FROM history_uint WHERE 'clock' < $Date;
optimize table history_uint;
DELETE FROM history_text WHERE 'clock' < $Date;
optimize table history_text;
DELETE FROM trends WHERE 'clock' < $Date;
optimize table trends;
DELETE FROM trends_uint WHERE 'clock' < $Date;
optimize table trends_uint;
DELETE FROM events WHERE 'clock' < $Date;
optimize table events;
"
5.6以后用alter table table_name engine="InnoDB";
3、添加到系统计划任务：
#remove the zabbix mysql data before day's ago
* * /usr/local/script/clearzabbix.sh > /usr/local/script/clearzabbix.log
4、另：可以使用truncate命令直接清空数据库：
truncate table history;
truncate table history_uint;
truncate table history_str;
truncate table history_text;
truncate table trends;
truncate table trends_uint;
truncate table events;
如果想要删除表的所有数据，truncate语句要比 delete 语句快。
因为 truncate 删除了表，然后根据表结构重新建立它，而 delete 删除的是记录，并没有尝试去修改表。
不过truncate命令虽然快，却不像delete命令那样对事务处理是安全的。
因此，如果我们想要执行truncate删除的表正在进行事务处理，这个命令就会产生退出并产生错误信息。
最新文章
- jxl读取Excel表格数据
- Base64编码及其作用
- RESTORE DATABASE的standby选项
- The quieter you become,The more you are able to hear.
- Java中的基本数据类型
- free-library-converts-2d-image-to-3d
- Neo4j 高可用集群安装
- iOS网页开发技术总结
- 自适应游标共享技术02(一个简单的例子来走近ACS)
- Navicat for mysql 11.0破解方法
- Oracle 客户端 NLS_LANG 的设置(转)
- [ios2] iOS常用控件尺寸大集合 【转】
- Javac编译与JIT编译
- [Domino]Java访问Domino必需配置的服务器设置
- Exchange Online Mailbox Restoration
- VRS——备忘
- day7 字符集转换
- 中间件——dubbo
- [leetcode]Reverse Words in a String @ Python
- libaio.so.1: undefined reference to `__stack_chk_fail@GLIBC_2.4'
热门文章
- LeetCode简单算法之分割平衡字符串 #1221
- leetcode 847. Shortest Path Visiting All Nodes 无向连通图遍历最短路径
- 设置div背景图片填满div
- TZOJ 3042 切蛋糕(并查集)
- SSM-5zookeeper在LINUX上自启
- js的几种继承方式
- 作业-[luogu4396][AHOI2013]-莫队
- 分布式事务 XA 两段式事务 X/open CAP BASE 一次分清
- 你需要一个新的model实体的时候必须new一个.奇怪的问题: 使用poi解析Excel的把数据插入数据库同时把数据放在一个list中,返回到页面展示,结果页面把最后一条数据显示了N次
- 2019.10.22 csp-s模拟测试82 反思总结