清理 zabbix 历史数据, 缩减 mysql 空间

zabbix 由于历史数据过大, history、history_uint表数据太多导致磁盘占用过多

停止 ZABBIX SERER 操作

`systemctl stop zabbix-server`

分析 history 表
```bash
MariaDB [zabbix]> desc history;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO   | MUL | NULL    |       |
| clock  | int(11)             | NO   |     | 0       |       |
| value  | double(16,4)        | NO   |     | 0.0000  |       |
| ns     | int(11)             | NO   |     | 0       |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.09 sec)

MariaDB [zabbix]> select max(itemid) from history;
+-------------+
| max(itemid) |
+-------------+
|      134241 |
+-------------+
1 row in set (0.91 sec)

MariaDB [zabbix]> select * from history where itemid=134241 limit 1, 20;
+--------+------------+---------+-----------+
| itemid | clock      | value   | ns        |
+--------+------------+---------+-----------+
| 134241 | 1629730640 | 62.0000 | 792500859 |
| 134241 | 1629730706 | 62.0000 | 621178697 |
| 134241 | 1629730763 | 62.0000 |  44956052 |
| 134241 | 1629730823 | 62.0000 | 478932709 |
| 134241 | 1629730879 | 62.0000 | 981772969 |
| 134241 | 1629730939 | 62.0000 | 545448487 |
| 134241 | 1629731003 | 62.0000 | 549181688 |
| 134241 | 1629731059 | 62.0000 | 705606432 |
| 134241 | 1629731119 | 62.0000 | 523306353 |
| 134241 | 1629731186 | 62.0000 | 260917428 |
| 134241 | 1629731241 | 62.0000 | 301152573 |
| 134241 | 1629731300 | 62.0000 |  36338093 |
| 134241 | 1629731360 | 62.0000 |  40130196 |
| 134241 | 1629731423 | 62.0000 |  74670098 |
| 134241 | 1629731483 | 62.0000 | 694171798 |
| 134241 | 1629731540 | 62.0000 | 411331656 |
| 134241 | 1629731603 | 62.0000 | 200118159 |
| 134241 | 1629731661 | 62.0000 | 750157765 |
| 134241 | 1629731721 | 62.0000 | 263307093 |
| 134241 | 1629731785 | 62.0000 | 329237998 |
+--------+------------+---------+-----------+
20 rows in set (0.06 sec)

MariaDB [zabbix]> select concat(round(sum(data_length/1024/1024),2),'MB') as data
    -> from information_schema.tables
    -> ;
+------------+
| data       |
+------------+
| 19415.00MB |
+------------+
1 row in set (1 min 21.05 sec)

```

删除两周前数据方法

取得时间戳, 时间只保留至 2021 3 25 日
```bash
[root@localhost ~]# date -d "2021-03-25 00:00:00" +%s
1616601600
```

删除 history, history_unit 表方法
```bash
MariaDB [zabbix]> delete from history where clock < 1616601600
;
Query OK, 468446 rows affected (24 min 1.07 sec)

MariaDB [zabbix]> delete from history_uint where clock < 1625068800
;
Query OK, 1218325 rows affected (1 hour 13 min 42.60 sec)
```

上面执行删除后，数据的存储空间是没有减少的，因为对于delete from table_name where xxx带条件的删除，不管是innodb还是MyISAM都不会释放空间，需要进行OPTIMIZE TABLE操作，进行释放空间。

注意：在optimize table '表名' 运行过程中，MySQL会进行锁表。

尝试对表进行缩减
```bash
MariaDB [zabbix]>  optimize table history_uint;
Stage: 1 of 2 'copy to tmp table'   8.06% of stage done
Stage: 1 of 2 'copy to tmp table'   20.1% of stage done

MariaDB [zabbix]>  optimize table history;

```

缩减前后文件大小比较
缩减前

[root@gd02-qa-plxt2-nodomain-web-96 mysql5_data]# find -size +50M -exec ls -lh {} \;
-rw-rw---- 1 apps apps 80M 04-02 10:29 ./zabbix/events.ibd
-rw-rw---- 1 apps apps 5.1G 04-02 11:44 ./zabbix/history.ibd
-rw-rw---- 1 apps apps 152M 04-02 10:29 ./zabbix/trends.ibd
-rw-rw---- 1 apps apps 4.3G 04-02 11:47 ./zabbix/history_uint.ibd
-rw-rw---- 1 apps apps 328M 04-02 10:29 ./zabbix/trends_uint.ibd
-rw-rw---- 1 apps apps 1.1G 04-02 11:47 ./ibdata1

缩减后

[root@gd02-qa-plxt2-nodomain-web-96 mysql5_data]# find -size +50M -exec ls -lh {} \;
-rw-rw---- 1 apps apps 80M 04-02 10:29 ./zabbix/events.ibd
-rw-rw---- 1 apps apps 3.6G 04-02 13:30 ./zabbix/history.ibd
-rw-rw---- 1 apps apps 152M 04-02 10:29 ./zabbix/trends.ibd
-rw-rw---- 1 apps apps 3.2G 04-02 12:24 ./zabbix/history_uint.ibd
-rw-rw---- 1 apps apps 328M 04-02 10:29 ./zabbix/trends_uint.ibd
-rw-rw---- 1 apps apps 1.1G 04-02 13:30 ./ibdata1

重启启动 zabbix,  php, nginx, mysql
新问题出现:

当前 zabbix 进行初始化, 会对 mysql 进行大量数据 r/w 操作
因此可能会发生下面警报, 经过 5 分钟后初始化, 下面报警会自动消除, 不用担心.

Disk I/O is overloaded on gd02-qa-plxt2-nodomain-web-96.vclound.com
Zabbix history syncer processes more than 75% busy
Zabbix timer processes more than 75% busy

参考文章：
[zabbix 存储数据清理](https://www.cnblogs.com/yanjieli/p/13044250.html "zabbix 存储数据清理")
[清理 zabbix 历史数据, 缩减 mysql 空间](https://www.cnblogs.com/Intermittent-psychosis/p/10717060.html "清理 zabbix 历史数据, 缩减 mysql 空间")