公司运维监控系统zabbix主机，空间占用量比较大，经检查磁盘空间：

进入MySQL 数据目录data，发现是zabbix数据库占空间大（history_uint.ibd 历史数据表占空间）：

对应到数据库中就是history_uint和history两个表数据比较大

mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb,table_rows from information_schema.tables where table_schema='zabbix' order by total_mb desc;

清理数据

将数据只保留一个月，删除超时一个月的数据
获取时间戳（网上有在线的）
在删除后，执行optimize table table_name 会立刻释放磁盘空间

mysql> delete from history  where clock < 1603708135;
Query OK, 9803097 rows affected (5 min 40.56 sec)

mysql> optimize table history;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (58.10 sec)

mysql> delete from history_uint  where clock < 1603708135;
Query OK, 16088660 rows affected (8 min 14.34 sec)

mysql> optimize table history_uint;
+---------------------+----------+----------+-------------------------------------------------------------------+
| Table               | Op       | Msg_type | Msg_text                                                          |
+---------------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history_uint | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history_uint | optimize | status   | OK                                                                |
+---------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2 min 25.52 sec)

在看一下删除历史数据后的大小，少了一半多

mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb,table_rows from information_schema.tables where table_schema='zabbix' order by total_mb desc;
+----------------------------+--------------+------------+
| table_name                 | total_mb     | table_rows |
+----------------------------+--------------+------------+
| history_uint               | 936.98437500 |   11173353 |
| history                    | 655.92187500 |    7790017 |
| trends_uint                | 119.59375000 |    1579694 |
| trends                     | 107.67187500 |    1421290 |

数据分区

不过数据量还是很大，继续对数据库进行分区操作，提高效率

脚本下载链接：

wget https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh