zabbix4.0表分区优化+备份

前言:

zabbix运行时间长了,卡顿现象严重。
当zabbix数据库达到7亿多条之后会出现洪水告警。原因是,zabbix数据库压力过大,写入新的数据需要10秒左右。
现在查询zabbix的history_uint 历史数据表,已经高达10亿条以上,现在每次查询都要十多秒,如下图

zabbix4.0表分区优化+备份

要做哪些?

  1. 保留之前的历史数据
  2. 对现在的数据历史相关的表进行分,如 historyhistory_uinthistory_strhistory_texthistory_logtrendstrends_uintevents

进行备份

	为了安全起见,可以先用xtarbackup 或者 mysqldump进行备份,然后进入数据库对表进行改名备份,做到双备份。
	如果日后想要调取之前的记录,也可以搭建一个测试zabbix直接连接之前的数据,即可查询,历史记录。
	(为什么不保留部分数据呢,这样不是查询更方便? 应为根据时间戳删除数据是对于小数据表有用,而目前表过大,
	删除数据的速度基本和写入速度持平了,而且数据量过大,根本无法根据时间戳删除部分数据。(ಥ_ಥ) )
  1. 停止关于zabbix的服务
systemctl stop  zabbix-server zabbix-agent   nginx  php-fpm  grafana-serve
  1. 备份表,创建新的表结构。
3. 克隆表结构,不要表数据
SET foreign_key_checks=0;
CREATE  TABLE IF NOT EXISTS  history_like (LIKE history);
CREATE  TABLE IF NOT EXISTS  history_uint_like (LIKE history_uint);
CREATE  TABLE IF NOT EXISTS  history_str_like (LIKE history_str);
CREATE  TABLE IF NOT EXISTS  history_text_like (LIKE history_text);
CREATE  TABLE IF NOT EXISTS  history_log_like (LIKE history_log);
CREATE  TABLE IF NOT EXISTS  trends_like (LIKE trends);
CREATE  TABLE IF NOT EXISTS  trends_uint_like (LIKE trends_uint);
CREATE  TABLE IF NOT EXISTS  events_like (LIKE events);
SET foreign_key_checks=1;

4. 对历史记录的表进行改名,做备份。
SET foreign_key_checks=0;
rename table history  to history_backup;
rename table history_uint  to history_uint_backup;
rename table history_str  to history_str_backup;
rename table history_text  to history_text_backup;
rename table history_log  to history_log_backup;
rename table trends  to trends_backup;
rename table trends_uint  to trends_uint_backup;
rename table events  to events_backup;
SET foreign_key_checks=1;

5. 对新克隆的表结构进行改名

SET foreign_key_checks=0;
rename table history_like  to history;
rename table history_uint_like  to history_uint;
rename table history_str_like  to history_str;
rename table history_text_like  to history_text;
rename table history_log_like  to history_log;
rename table trends_like  to trends;
rename table trends_uint_like  to trends_uint;
rename table events_like  to events;
SET foreign_key_checks=1;
  1. 原有149张表,昨晚之后应该有157张表。
    zabbix4.0表分区优化+备份

  2. 现在可以打开看下历史记录,已经没有了,速度立马变好 ( ᕦ(・ㅂ・)ᕤ
    zabbix4.0表分区优化+备份

进行表分区

  1. 下载分区脚本。
    这个脚本是根据官方提供的4个存储过程改的,方便好使。
wget https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh
  1. 对脚本内容进行修改

# 详细数据保存60天
daily_history_min=60

# 趋势数据保留12个月
monthly_history_min=12
DBHOST=localhost   # 数据库地址
DBUSER=zabbix	   # 数据库用户
DBPASS=zabbix	   # 数据库密码

  1. 运行脚本
chmod +x partitiontables_gt_zbx34.sh 
bash partitiontables_gt_zbx34.sh

Ready to partition tables.

Ready to update permissions of Zabbix user to create routines

Enter root DB user: 输入用户
Enter root password: 输入密码
mysql: [Warning] Using a password on the command line interface can be insecure.


## 是否做备份
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: 2021
and ending at: 2021
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

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): 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: kingxin    


  1. 查看一下计划任务执行的脚本,其实就是调用了官方提供的分区存储过程
vim /usr/local/zabbix/cron.d/housekeeping.sh

#!/bin/bash

MAILTO=kingxin
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

  1. 进入mysql 查看分区信息
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 |
+----------------+----------------------+-----------------------+------------+
| p20210609      |  clock               | 1623254400            |          0 |
| p20210610      |  clock               | 1623340800            |          0 |
| p20210611      |  clock               | 1623427200            |          0 |
| p20210612      |  clock               | 1623513600            |          0 |
| p20210613      |  clock               | 1623600000            |          0 |
| p20210614      |  clock               | 1623686400            |          0 |
| p20210615      |  clock               | 1623772800            |          0 |
| p20210616      |  clock               | 1623859200            |          0 |
| p20210617      |  clock               | 1623945600            |          0 |
| p20210618      |  clock               | 1624032000            |          0 |
| p20210619      |  clock               | 1624118400            |          0 |
| p20210620      |  clock               | 1624204800            |          0 |
| p20210621      |  clock               | 1624291200            |          0 |
| p20210622      |  clock               | 1624377600            |          0 |
| p20210623      |  clock               | 1624464000            |          0 |
| p20210624      |  clock               | 1624550400            |          0 |
| p20210625      |  clock               | 1624636800            |          0 |
| p20210626      |  clock               | 1624723200            |          0 |
| p20210627      |  clock               | 1624809600            |          0 |
| p20210628      |  clock               | 1624896000            |          0 |
| p20210629      |  clock               | 1624982400            |          0 |
| p20210630      |  clock               | 1625068800            |          0 |
| p20210701      |  clock               | 1625155200            |          0 |
| p20210702      |  clock               | 1625241600            |          0 |
| p20210703      |  clock               | 1625328000            |          0 |
| p20210704      |  clock               | 1625414400            |          0 |
| p20210705      |  clock               | 1625500800            |          0 |
| p20210706      |  clock               | 1625587200            |          0 |
| p20210707      |  clock               | 1625673600            |          0 |
| p20210708      |  clock               | 1625760000            |          0 |
| p20210709      |  clock               | 1625846400            |      25994 |
| p20210710      |  clock               | 1625932800            |          0 |
| p20210711      |  clock               | 1626019200            |          0 |
| p20210712      |  clock               | 1626105600            |          0 |
| p20210713      |  clock               | 1626192000            |          0 |
| p20210714      |  clock               | 1626278400            |          0 |
| p20210715      |  clock               | 1626364800            |          0 |
| p20210716      |  clock               | 1626451200            |          0 |
+----------------+----------------------+-----------------------+------------+
38 rows in set (0.00 sec)

可以看到   p20210709     也就是今天,多了一部分数据 


mysql> select partition_name ,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='trends';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p202101        |  clock               | 1612108800            |          0 |
| p202102        |  clock               | 1614528000            |          0 |
| p202103        |  clock               | 1617206400            |          0 |
| p202104        |  clock               | 1619798400            |          0 |
| p202105        |  clock               | 1622476800            |          0 |
| p202106        |  clock               | 1625068800            |          0 |
| p202107        |  clock               | 1627747200            |       1395 |
| p202108        |  clock               | 1630425600            |          0 |
| p202109        |  clock               | 1633017600            |          0 |
| p202110        |  clock               | 1635696000            |          0 |
+----------------+----------------------+-----------------------+------------+
10 rows in set (0.00 sec)


上一篇:Debug --> 计算程序运行时间


下一篇:C++中的this指针