前言：
zabbix运行时间长了，卡顿现象严重。
当zabbix数据库达到7亿多条之后会出现洪水告警。原因是，zabbix数据库压力过大，写入新的数据需要10秒左右。
现在查询zabbix的history_uint 历史数据表，已经高达10亿条以上，现在每次查询都要十多秒，如下图
要做哪些？
- 保留之前的历史数据
- 对现在的数据历史相关的表进行分，如
history、history_uint、history_str、history_text、history_log、trends、trends_uint、events表
进行备份
为了安全起见，可以先用xtarbackup 或者 mysqldump进行备份，然后进入数据库对表进行改名备份，做到双备份。
如果日后想要调取之前的记录，也可以搭建一个测试zabbix直接连接之前的数据，即可查询，历史记录。
（为什么不保留部分数据呢，这样不是查询更方便？ 应为根据时间戳删除数据是对于小数据表有用，而目前表过大，
删除数据的速度基本和写入速度持平了，而且数据量过大，根本无法根据时间戳删除部分数据。(ಥ_ಥ) ）
- 停止关于zabbix的服务
systemctl stop zabbix-server zabbix-agent nginx php-fpm grafana-serve
- 备份表，创建新的表结构。
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;
-
原有149张表，昨晚之后应该有157张表。
-
现在可以打开看下历史记录，已经没有了，速度立马变好 ( ᕦ(･ㅂ･)ᕤ
进行表分区
- 下载分区脚本。
这个脚本是根据官方提供的4个存储过程改的，方便好使。
wget https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh
- 对脚本内容进行修改
# 详细数据保存60天
daily_history_min=60
# 趋势数据保留12个月
monthly_history_min=12
DBHOST=localhost # 数据库地址
DBUSER=zabbix # 数据库用户
DBPASS=zabbix # 数据库密码
- 运行脚本
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
- 查看一下计划任务执行的脚本，其实就是调用了官方提供的分区存储过程
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
- 进入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)