zabbix（4）数据库表分区优化

 

 

 

 

 

一、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