问题描述：

今天同事说有个zabbix监控数据库历史数据越来越多了，

让我帮忙清一下，顺便熟悉练练手，做个笔记

zabbix监控运行一段时间以后，会留下大量的历史监控数据

zabbix数据库一直在增大可能会造成系统性能下降，查看历史数据室查询速度缓慢。

zabbix里面最大的表就是history和history_uint两个表，但是我这边查看到history_log表也是最大的。

而且zabbix里面的时间是使用的时间戳方式记录，所以可以根据时间戳来删除历史数据。

解决方法：

1、首先需要关闭zabbix和httpd服务：

我的系统是centos7系统，关闭服务是这种

[root@awza ~]# systemctl stop zabbix-server.service
[root@awza~]# systemctl stop httpd.service

2、查看数据库目录文件的大小

[root@awza zabbix]# pwd
/database/mysql/zabbix
[root@awza zabbix]# ll-h |grepG
total 129G-rw-rw---- 1 mysql mysql 2.9G Nov 4 15:15history.ibd-rw-rw---- 1 mysql mysql 121G Nov 4 15:15history_log.ibd-rw-rw---- 1 mysql mysql 4.2G Nov 4 15:15 history_uint.ibd

生成一个Unix时间戳，例如想删除2019-05-01号之前的数据(保留19年5月1号以后的监控数据)

zabbix 自动清理历史数据 zabbix查看历史数据_zabbix历史数据mysql

[root@awza zabbix]# date +%s -d "May 1, 2019 00:00:00"
1556640000

3、数据库备份(重要的话最好做一下备份)

我这边备份磁盘空间不足，说不用备份，出现了mysqldump: Got errno 28 on write就是磁盘空间不足

[root@awza ~]# /usr/bin/mysqldump -uzabbix -p --triggers --routines --events zabbix > /database/backup/zabbix-mysql-20191104.sql
Enter password:
mysqldump: Got errno28 on write

4、查询历史表数据数：

[root@awza backup]# mysql -uzabbix -p
Enter password:
MariaDB [(none)]>use zabbix
MariaDB [zabbix]> selectmax(itemid) from history;+-------------+
| max(itemid) |
+-------------+
| 62944 |
+-------------+
1 row in set (0.00sec)
MariaDB [zabbix]> selectmax(itemid) from history_uint;+-------------+
| max(itemid) |
+-------------+
| 62950 |
+-------------+
1 row in set (0.00sec)
MariaDB [zabbix]> selectmax(itemid) from history_log;+-------------+
| max(itemid) |
+-------------+
| 62863 |
+-------------+
1 row in set (0.00 sec)

5、按时间戳清理监控历史数据：

这边清理相关表按照历史表的大小来清理，根据(2、查看数据库目录文件的大小)

第一种清理方法：

use zabbix;
MariaDB [zabbix]> delete from history where clock < 1556640000;
Query OK,0 rows affected (41.70sec)
MariaDB [zabbix]> delete from history_uint where clock < 1556640000;
Query OK,0 rows affected (3 min 51.22sec)
MariaDB [zabbix]> delete from history_log where clock < 1556640000;
ERROR1206(HY000): The total number of locks exceeds the lock table size

##注意：这边时间戳按照自己的条件写，1556640000这个是我前面按照2019-05-01计算出来的时间戳

这边删除第三个表的时候，因为数据量太大，导致报错：

错误1206(HY000)：锁的总数超过了锁表的大小

这个表也不重要，所以就执行下面的truncate命令来清理

第二种清理方法：

truncate table history;
optimize table history;-------------------------------------------------------truncate table history_log;
optimize table history_log;-------------------------------------------------------truncate table history_uint;

optimize table history_uint;注意：这些命令会把zabbix所有的监控数据清空，操作前注意备份数据库

truncate是清空整张表，然后根据表结构重新建立，delete删除的是记录的数据没有修改表

truncate执行删除比较快，但是在事务处理安全性方面不如delete

如果我们执行truncate的表正在处理事务，这个命令退出并会产生错误信息

MariaDB [zabbix]>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 (7 min 4.03 sec)

6、开启服务

[root@awza database]# systemctl start zabbix-server.service
[root@awza database]# systemctl start httpd.service

注意提示：

当你删除数据时，mysql并不会回收已删除的数据所占据的存储空间，以及索引位。

而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源了。

所以对于写比较频繁的表，要定期进行optimize，看实际情况而定了，所以删除了历史数据，可以使用optimize来回收存储空间。