zabbix監控執行一段時間以後，會留下大量的歷史監控資料，zabbix資料庫一直在增大；可能會造成系統效能下降，檢視歷史資料室查詢速度緩慢，圖形出現斷圖現象，隔幾秒出現斷圖，發現CPU採集資料的時候使用率都是100%，如下圖
zabbix裡面最大的表就是history和history_uint兩個表，而且zabbix裡面的時間是使用的時間戳方式記錄，所以可以根據時間戳來刪除歷史資料
1、先生成unix時間戳，時間設定為2024年7月23日之前的資料全部刪除，先記下 1721664000 這個時間戳，後面執行刪除時要用
date +%s -d "2024-07-23"
2、停止zabbix-server、Apache2程序
sudo systemctl stop zabbix-server.service apache2.service
3、查詢檔案佔用空間
cd /
sudo du -sh * | grep G
4、備份資料庫（可選，備份時間較長）
sudo mysqldump -uroot -p zabbix>./zabbix.sql
5、登入資料庫，刪除操作，時間可能較長請耐心等待，中間不要終止，否則容易資料丟失
sudo mysql -uroot -p #登入mysql資料庫
mysql> use zabbix; #切換到zabbix資料庫
mysql> delete from history where clock < 1721664000; #刪除2024年7月23日之前的歷史資料
Query OK, 0 rows affected (0.61 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 (0.83 sec)
mysql> delete from history_uint where clock < 1721664000; #刪除2024年7月23日之前的歷史資料
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
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 (3 hours 27 min 58.55 sec)
mysql> delete from trends where clock < 1721664000; #刪除2024年7月23日之前的歷史資料
ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
No connection. Trying to reconnect...
Connection id: 12
Current database: zabbix
Query OK, 8172 rows affected (2.74 sec)
mysql> optimize table trends; #最佳化表空間結構
+---------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+-------------------------------------------------------------------+
| zabbix.trends | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.trends | optimize | status | OK |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.29 sec)
mysql> delete from trends_uint where clock < 1721664000; #刪除2024年7月23日之前的歷史資料
Query OK, 354220 rows affected (5.71 sec)
mysql> optimize table trends_uint; #最佳化表空間結構
+--------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------+----------+----------+-------------------------------------------------------------------+
| zabbix.trends_uint | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.trends_uint | optimize | status | OK |
+--------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.39 sec)
mysql> delete from events where clock < 1721664000; #刪除2024年7月23日之前的歷史資料
Query OK, 3106 rows affected (0.86 sec)
mysql> optimize table events; #最佳化表空間結構
+---------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+-------------------------------------------------------------------+
| zabbix.events | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.events | optimize | status | OK |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.43 sec)
mysql> delete from auditlog where clock < 1721664000; #刪除2024年7月23日之前的歷史資料
Query OK, 144055 rows affected (13.33 sec)
mysql> optimize table auditlog; #最佳化表空間結構
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------+----------+----------+-------------------------------------------------------------------+
| zabbix.auditlog | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.auditlog | optimize | status | OK |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.34 sec)
6、啟動服務
sudo systemctl start zabbix-server.service apache2.service
# 使用truncate命令清空zabbix 所有監控資料 (未嘗試)
-------------------------------------------------------
truncate table history;
optimize table history;
-------------------------------------------------------
truncate table history_str;
optimize table history_str;
-------------------------------------------------------
truncate table history_uint;
optimize table history_uint;
-------------------------------------------------------
truncate table trends;
optimize table trends;
-------------------------------------------------------
truncate table trends_uint;
optimize table trends_uint;
-------------------------------------------------------
truncate table events;
optimize table events;
-------------------------------------------------------
# 注意：這些命令會把zabbix所有的監控資料清空，操作前注意備份資料庫
# truncate是刪除了表，然後根據表結構重新建立，delete刪除的是記錄的資料沒有修改表
# truncate執行刪除比較快，但是在事務處理安全性方面不如delete,如果我們執行truncat的表正在處理事務，這個命令退出並會產生錯誤資訊