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

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

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

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

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

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

 

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

  1. [root@awza ~]# systemctl stop zabbix-server.service 
  2. [root@awza ~]# systemctl stop httpd.service 
  1. [root@awza zabbix]# pwd
  2. /database/mysql/zabbix
  4. [root@awza zabbix]# ll -h |grep G
  5. total 129G
  6. -rw-rw---- 1 mysql mysql 2.9G Nov  4 15:15 history.ibd
  7. -rw-rw---- 1 mysql mysql 121G Nov  4 15:15 history_log.ibd
  8. -rw-rw---- 1 mysql mysql 4.2G Nov  4 15:15 history_uint.ibd

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

或者使用站长时间戳工具生成：https://tool.lu/timestamp/

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

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

  1. [root@awza ~]# /usr/bin/mysqldump -uzabbix -p --triggers --routines --events zabbix > /database/backup/zabbix-mysql-20191104.sql
  2. Enter password: 
  3. mysqldump: Got errno 28 on write
  1. [root@awza backup]# mysql -uzabbix -p
  2. Enter password: 
  4. MariaDB [(none)]> use zabbix
  6. MariaDB [zabbix]> select max(itemid) from history;
  7. +-------------+
  8. | max(itemid) |
  9. +-------------+
  10. |       62944 |
  11. +-------------+
  12. 1 row in set (0.00 sec)
  14. MariaDB [zabbix]> select max(itemid) from history_uint;
  15. +-------------+
  16. | max(itemid) |
  17. +-------------+
  18. |       62950 |
  19. +-------------+
  20. 1 row in set (0.00 sec)
  22. MariaDB [zabbix]> select max(itemid) from history_log;
  23. +-------------+
  24. | max(itemid) |
  25. +-------------+
  26. | 62863 |
  27. +-------------+
  28. 1 row in set (0.00 sec)
  1. use zabbix;
  2. MariaDB [zabbix]> delete from history where clock < 1556640000;
  3. Query OK, 0 rows affected (41.70 sec)
  5. MariaDB [zabbix]> delete from history_uint where clock < 1556640000;
  6. Query OK, 0 rows affected (3 min 51.22 sec)
  8. MariaDB [zabbix]> delete from history_log where clock < 1556640000;
  9. ERROR 1206 (HY000): The total number of locks exceeds the lock table size
  12. ##注意：这边时间戳按照自己的条件写，1556640000这个是我前面按照2019-05-01计算出来的时间戳
  14. 这边删除第三个表的时候，因为数据量太大，导致报错：
  15. 错误1206（HY000）：锁的总数超过了锁表的大小
  16. 这个表也不重要，所以就执行下面的truncate命令来清理
  1. truncate table history;
  2. optimize table history;
  3. ------------------------------------------------------- 
  4. truncate table history_str;
  5. optimize table history_str;
  6. -------------------------------------------------------
  7. truncate table history_log;
  8. optimize table history_log;
  9. -------------------------------------------------------
  10. truncate table history_uint;
  11. optimize table history_uint;
  12. -------------------------------------------------------
  13. truncate table trends;
  14. optimize table trends;
  15. -------------------------------------------------------
  16. truncate table trends_uint; 
  17. optimize table trends_uint; 
  18. -------------------------------------------------------
  19. truncate table events;
  20. optimize table events;
  21. -------------------------------------------------------
  22. 注意：这些命令会把zabbix所有的监控数据清空，操作前注意备份数据库
  24. truncate是清空整张表，然后根据表结构重新建立，delete删除的是记录的数据没有修改表
  26. truncate执行删除比较快，但是在事务处理安全性方面不如delete
  28. 如果我们执行truncate的表正在处理事务，这个命令退出并会产生错误信息

  1. MariaDB [zabbix]> optimize table history;
  2. +----------------+----------+----------+-------------------------------------------------------------------+
  3. | Table          | Op       | Msg_type | Msg_text                                                          |
  4. +----------------+----------+----------+-------------------------------------------------------------------+
  5. | zabbix.history | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
  6. | zabbix.history | optimize | status   | OK                                                                |
  7. +----------------+----------+----------+-------------------------------------------------------------------+
  8. 2 rows in set (7 min 4.03 sec)

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

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

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

 

版权声明：本文为Sungeek原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/Sungeek/p/11792684.html