zabbix数据库history相关数据占用空间太大解决

zabbix数据库history相关数据占用空间太大解决

今天有台服务器磁盘报警，使用du -sh * （当前文件夹下的文件大小）一步步排查，发现/var/lib/mysql/zabbix/这个目录占用磁盘空间较大

[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# du -sh zabbix
5.9G	zabbix

在进入到zabbix中，进一步看到是其中history_uint.ibd和history.ibd两个表的数据过大

[root@localhost zabbix]# du -sh  * 
3.1G	history_uint.ibd
2.6G	history.ibd

对应到数据库中就是history_uint和history两个表数据比较大

mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb,table_rows from information_schema.tables where table_schema='zabbix' order by total_mb desc;
+----------------------------+---------------+------------+
| table_name                 | total_mb      | table_rows |
+----------------------------+---------------+------------+
| history_uint               | 2820.17187500 |   28546891 |
| history                    | 2034.39062500 |   19045070 |
| trends_uint                |  119.59375000 |    1579101 |
| trends                     |  107.67187500 |    1420596 |

清理数据

将数据只保留一个月，删除超时一个月的数据
获取时间戳（网上有在线的）
在删除后，执行optimize table table_name 会立刻释放磁盘空间

mysql> delete from history  where clock < 1603708135;
Query OK, 9803097 rows affected (5 min 40.56 sec)

mysql> optimize table history;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (58.10 sec)

mysql> delete from history_uint  where clock < 1603708135;
Query OK, 16088660 rows affected (8 min 14.34 sec)

mysql> optimize table history_uint;
+---------------------+----------+----------+-------------------------------------------------------------------+
| Table               | Op       | Msg_type | Msg_text                                                          |
+---------------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history_uint | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history_uint | optimize | status   | OK                                                                |
+---------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2 min 25.52 sec)

在看一下删除历史数据后的大小，少了一半多

mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb,table_rows from information_schema.tables where table_schema='zabbix' order by total_mb desc;
+----------------------------+--------------+------------+
| table_name                 | total_mb     | table_rows |
+----------------------------+--------------+------------+
| history_uint               | 936.98437500 |   11173353 |
| history                    | 655.92187500 |    7790017 |
| trends_uint                | 119.59375000 |    1579694 |
| trends                     | 107.67187500 |    1421290 |

数据分区

不过数据量还是很大，继续对数据库进行分区操作，提高效率

下载脚本

网上有写好的分区脚本，直接去下载

[root@localhost zabbix]# wget https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh
--2020-11-26 20:04:46--  https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh
正在解析主机 dl.cactifans.com (dl.cactifans.com)... 222.186.135.67
正在连接 dl.cactifans.com (dl.cactifans.com)|222.186.135.67|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：15458 (15K) [application/octet-stream]
正在保存至: “partitiontables_gt_zbx34.sh”

100%[===================================================================================================================================================================================================================================>] 15,458      --.-K/s 用时 0s      

2020-11-26 20:04:47 (883 MB/s) - 已保存 “partitiontables_gt_zbx34.sh” [15458/15458])

编辑脚本参数

history和trends相关表数据量很大，一个是历史数据，一个是趋势数据，脚本中默认详情数据保留30天，趋势数据保留12个月，如需修改，请修改以下内容：

# How long to keep the daily history
daily_history_min=30
# How long to keep the monthly history (months)
monthly_history_min=12

修改数据库连接信息

DBHOST=localhost
DBUSER=zabbix
DBPASS=zabbix

增加执行权限

[root@localhost zabbix]# chmod +x partitiontables_gt_zbx34.sh

关闭zabbix-server

[root@localhost zabbix]# systemctl stop zabbix-server.service 

执行脚本

[root@localhost zabbix]# ./partitiontables_gt_zbx34.sh
Ready to partition tables.

Ready to update permissions of Zabbix user to create routines

Enter root DB user: zabbix
Enter root password: XXXXX
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements



Do you want to backup the database (recommended) (Y/n): n
Are you certain you have a backup (y/N): 
y


Ready to proceed:

Starting yearly partioning at: 2020
and ending at: 2020
With 30 days of daily history


Ready to proceed (Y/n): 
y
Altering table: history
Altering table: history_log
Altering table: history_str
Altering table: history_text
Altering table: history_uint
Altering table: trends
Altering table: trends_uint
Creating monthly partitions for table: trends
Creating monthly partitions for table: trends_uint
Creating daily partitions for table: history
Creating daily partitions for table: history_log
Creating daily partitions for table: history_str
Creating daily partitions for table: history_text
Creating daily partitions for table: history_uint

Ready to apply script to database, this may take a while.(Y/n): 
y
mysql: [Warning] Using a password on the command line interface can be insecure.
Altering tables
history
history_log
history_str
history_text
history_uint
trends
trends_uint
trends
trends_uint
history
history_log
history_str
history_text
history_uint
Installing procedures

If Zabbix Version = 2.0 
Do you want to update the /etc/zabbix/zabbix_server.conf
to disable housekeeping (Y/n): y
./partitiontables_gt_zbx34.sh:行463: /etc/init.d/zabbix-server: 没有那个文件或目录
./partitiontables_gt_zbx34.sh:行465: /etc/init.d/zabbix-server: 没有那个文件或目录

Do you want to update the crontab (Y/n): y
The crontab entry can be either in /etc/cron.daily, or added
to the crontab for root

Do you want to add this to the /etc/cron.daily directory (Y/n): y

Enter email of who should get the daily housekeeping reports: XXX@163.com

查看生成的定时任务

上面的脚本会在/etc/cron.daily目录下生成一个名称为zabbixhousekeeping的脚本

[root@localhost zabbix]# vim /etc/cron.daily/zabbixhousekeeping 

#!/bin/bash
/usr/local/zabbix/cron.d/housekeeping.sh

里面的脚本就是调用了Mysql的创建分区表存储过程

[root@localhost zabbix]# vim /usr/local/zabbix/cron.d/housekeeping.sh 

#!/bin/bash

MAILTO=XXX@163.com
tmpfile=/tmp/housekeeping$$

date >$tmpfile
/usr/bin/mysql --skip-column-names -B -h localhost -uzabbix -pXXXX zabbix -e "CALL create_zabbix_partitions();" >>$tmpfile 2>&1
/usr/bin/mail -s "Zabbix MySql Partition Housekeeping" $MAILTO <$tmpfile
rm -f $tmpfile

查看分区后的数据（history）

mysql> select partition_name ,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='history';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p20201027      |  clock               | 1603814400            |     268320 |
| p20201028      |  clock               | 1603900800            |     217152 |
| p20201029      |  clock               | 1603987200            |     218400 |
| p20201030      |  clock               | 1604073600            |     218400 |
| p20201031      |  clock               | 1604160000            |     218400 |
| p20201101      |  clock               | 1604246400            |     217776 |
| p20201102      |  clock               | 1604332800            |     218088 |
| p20201103      |  clock               | 1604419200            |     207792 |
| p20201104      |  clock               | 1604505600            |     207480 |
| p20201105      |  clock               | 1604592000            |     215904 |
| p20201106      |  clock               | 1604678400            |     217776 |
| p20201107      |  clock               | 1604764800            |     218088 |
| p20201108      |  clock               | 1604851200            |     218088 |
| p20201109      |  clock               | 1604937600            |      75816 |
| p20201110      |  clock               | 1605024000            |     205608 |
| p20201111      |  clock               | 1605110400            |     217464 |
| p20201112      |  clock               | 1605196800            |     218088 |
| p20201113      |  clock               | 1605283200            |     175968 |
| p20201114      |  clock               | 1605369600            |     217152 |
| p20201115      |  clock               | 1605456000            |     218088 |
| p20201116      |  clock               | 1605542400            |     218400 |
| p20201117      |  clock               | 1605628800            |     217464 |
| p20201118      |  clock               | 1605715200            |     217776 |
| p20201119      |  clock               | 1605801600            |     273624 |
| p20201120      |  clock               | 1605888000            |     420888 |
| p20201121      |  clock               | 1605974400            |     420888 |
| p20201122      |  clock               | 1606060800            |     422136 |
| p20201123      |  clock               | 1606147200            |     421824 |
| p20201124      |  clock               | 1606233600            |     260520 |
| p20201125      |  clock               | 1606320000            |     421512 |
| p20201126      |  clock               | 1606406400            |     320424 |
| p20201127      |  clock               | 1606492800            |          0 |
| p20201128      |  clock               | 1606579200            |          0 |

分区成功
本次zabbix的数据库清理，先是删除了2个大文件中大于一个月的数据，并且对数据库进行分区操作，更加提高效率
重启zabbix服务