If you are using Zabbix for a long time, then you might notice that the database size of Zabbix is increasing every day. Then you should delete the old data from the database.
- login to the MySQL.
- Select your database (Which is used for zabbix)
- Run below commands (Please change the interval day as your requirement)
DELETE FROM history_uint WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
DELETE FROM history WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
DELETE FROM trends_uint WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
DELETE FROM trends WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
DELETE FROM events WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
DELETE FROM alerts WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
In my case I am keeping only last 35 Days data, so change as per your requirement.
Thanks
Hello!
I have a problem with deleting data from postgresql database…
zabbix_db=# DELETE FROM trends WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 60 DAY));
ERROR: syntax error at or near "60"
LINE 1: …RE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 60 DAY));
Do you have any ideas what's wrong with it?
Hello Den,
Thank you for your comment and sorry for my typos! this tutorial is only for MySQL.
However, you can try with the below command:
DELETE FROM WHERE timestamp < NOW() - INTERVAL '2 days';
PostgreSQL doesn’t have exactly the same built-in functions, here’s the SQL that works for me:
DELETE FROM trends WHERE to_timestamp(clock) < NOW() – INTERVAL '30 days'
I am also trying to clean my zabbix database but it seems impossible.
First most of the time I get the following error;
“ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction”
If the error doesn’t come up. It just get stuck and hours, supposedly deletng but never finishes it.
I think your MySQL database size is huge and that is why it is getting stuck.
I recommended, at first stop the Zabbix server and then do these steps.
If you still face this issue then increase your “innodb_lock_wait_timeout” as per your requirement.
If you run the SQL query from ssh then increase the ssh session timeout time.
For ssh session please have a look my this post – https://www.try2answer.com/28/02/2019/increase-ssh-session-timeout/
Thanks
Hi!
I have a problem with table “history_uint” with 84541 MB, I execute:
MariaDB [zabbix]> select itemid, FROM_UNIXTIME(clock), ns FROM history_uint limit 0,5;
+——–+———————-+———–+
| itemid | FROM_UNIXTIME(clock) | ns |
+——–+———————-+———–+
| 37125 | 2017-07-28 17:02:35 | 388657237 |
| 37126 | 2017-07-28 17:02:35 | 388657237 |
| 37126 | 2017-07-28 17:03:35 | 944036464 |
| 37125 | 2017-07-28 17:03:35 | 944036464 |
| 37125 | 2017-07-28 17:04:37 | 336764628 |
+——–+———————-+———–+
I will like to delete all old data and only keep data of last 365 days.. For this, I execute:
DELETE FROM history_uint WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 365 DAY));
And the result is:
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
Any idea? I need help.
Thanks a lot
Hello There,
Please tweak your innodb_buffer_pool_size and try once. For your reference find below URL:
https://stackoverflow.com/questions/6901108/the-total-number-of-locks-exceeds-the-lock-table-size
https://major.io/2010/01/29/mysql-the-total-number-of-locks-exceeds-the-lock-table-size
Sorry for the late response.