zabbix mysql分区_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

去掉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 lineinterfacecan be insecure.

ERROR1044 (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, pressreturn for default of /tmp/zabbix.sql

mysql: [Warning] Using a password on the command lineinterfacecan be insecure.

ERROR1044 (42000) at line 1: Access denied for user 'zabbix'@'localhost' to database 'zabbix'mysqldump: [Warning] Using a password on the command lineinterfacecan be insecure.

Mysqldump succeeded!, proceeding with upgrade...

Ready to proceed:

Starting yearly partioning at:2019and ending at:2019With30days 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 partitionsfortable: trends

Creating monthly partitionsfortable: trends_uint

Creating daily partitionsfortable: history

Creating daily partitionsfortable: history_log

Creating daily partitionsfortable: history_str

Creating daily partitionsfortable: history_text

Creating daily partitionsfortable: history_uint

Ready to apply script to database,this may take a while.(Y/n):

y

mysql: [Warning] Using a password on the command lineinterfacecan be insecure.

Altering tables

history

history_log

ERROR1091 (42000) at line 4: Can't DROP'history_log_2'; check that column/key exists

If Zabbix Version= 2.0Do 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 eitherin /etc/cron.daily, or added

to the crontabforroot

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

Enter email of who shouldget 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_sh19: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

rm-f $tmpfile

[root@manager1 script_sh19:26:51]#

ok