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

╰+攻爆jí腚メ 2022-12-24 04:58 777阅读 0赞

# 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服务