zabbix(4)数据库表分区优化

时间:2024-03-04 20:35:29

 

 

 

 

 

一、zabbix 数据库存储

  zabbix-server将采集到的数据存储在数据库(mysql、oracle等),而数据存储的大小与每秒处理的数量量有关,因此数据存储取决于以下两个因数:

  (1)Required server performance, new values per second(每秒处理的数据量)

  (2)Housekeeper的设置(zabbix_server.conf文件中):删除数据

  zabbix-server将采集到数据主要存储于Histroy和Trends表中,其表结构中的数据类型如下:

  对于存储超过期限的数据,主要使用DELETE sql语句进行删除,当数据量太多,这将严重影响数据库的性能。

  查询zabbix数据库中各种表存储的大小和行数:

mysql> select table_name, (data_length + index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema=\'zabbix\';

  从上图可知,我这仅仅只是一个测试环境,histroy表存储的数据已经几十万行,如果是生成环境,估计几千、上亿万行。

 

二、数据库 表分区

1、

  数据库的优化有横向和纵向扩展,这里使用数据的分布式,而分表可以看做是分布式的一种。。

  在zabbix_server.conf文件中,找到如下两个参数:

    (1)HousekeepingFrequency=1 解释:多久删除一次数据库里面的过期数据(间隔时间),默认一小时

    (2)MaxHousekeeperDelete=5000  解释:每次删除数据量的上线(最大删除量),默认5000

  采用表分区,需要关闭Housekeeping功能,关闭流程如下:Administration ---> General ---> Housekeepin

 

  去掉Histroy和Trends的勾选状态,如下图:

2、

   (1)进行表分区,这里采用GitHub上一位大神写的表分区脚本,连接如下:

    wget  https://github.com/itnihao/zabbix-book/blob/master/03-chapter/partitiontables.sh

     该脚本具有如下功能:

      > 备份数据库

      > 对表进行分区间

      > 添加定时任务

     如果数据量过大,可以将表数据删除(当然这样数据会全部被删除)

     清空语句如下:

sql> use zabbix;
sql> truncate table histroy;
sql> optimize table histroy; 

sql> truncate table histroy_str;
sql> optimize table histroy_str; 

sql> truncate table histroy_uint;
sql> optimize table histroy_unit; 

sql> truncate table trends;
sql> optimize table trends; 

sql> truncate table trends_unit;
sql> optimize table trends_unit; 

sql> truncate table events;
sql> optimize table events; 

  (2)运行表分区脚本

    为了防止网络中断引起脚本运行中断而造成数据库故障,这里选用screen后台执行方法。

    # yum install -y screen

    # screen -R zabbix    需要退出的话可以按 Ctral + A 以后再执行 Ctral + D

    #bash partitiontables.sh

    执行过程如下:

Ready to partition tables.

Ready to update permissions of Zabbix user to create routines

Enter root DB user: zabbix
Enter zabbix password: zabbix
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1044 (42000) at line 1: Access denied for user \'zabbix\'@\'localhost\' to database \'zabbix\'

#   上述错误可以忽略

Do you want to backup the database (recommended) (Y/n): y

Enter output file, press return for default of /tmp/zabbix.sql

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1044 (42000) at line 1: Access denied for user \'zabbix\'@\'localhost\' to database \'zabbix\'
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Mysqldump succeeded!, proceeding with upgrade...


Ready to proceed:

Starting yearly partioning at: 2019
and ending at: 2019
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
ERROR 1091 (42000) at line 4: Can\'t DROP \'history_log_2\'; check that column/key exists

If Zabbix Version = 2.0 
Do you want to update the /etc/zabbix/zabbix_server.conf
to disable housekeeping (Y/n): n

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:

  执行完脚本以后,查看计划任务:

[root@manager1 script_sh 19:24:08]#cat /etc/cron.daily/zabbixhousekeeping 
#!/bin/bash
/usr/local/zabbix/cron.d/housekeeping.sh
[root@manager1 script_sh 19:26:30]#cat /usr/local/zabbix/cron.d/housekeeping.sh
#!/bin/bash
MAILTO=root@localhost
tmpfile=/tmp/housekeeping$$
date >$tmpfile
/usr/bin/mysql --skip-column-names -B -h localhost -uzabbix -pzabbix zabbix -e "CALL create_zabbix_partitions();" >>$tmpfile 2>&1
/usr/bin/mail -s "Zabbix MySql Partition Housekeeping" $MAILTO <$tmpfile
rm -f $tmpfile
[root@manager1 script_sh 19:26:51]#

ok