How to delete old information in tables history_uint and history?

[nikolam]

Hello,

I've noticed that DB gets bloated by two tables, history_uint and history.

Is there some secure way or to configure in zabbix so that those tables are cleaned older than 30 days or something.

Seems like these two are bloating up DB unnecessary and would like to sort it out without truncating those tables.


Screenshot from phpmyadmin showing DB bloat.

Kind regards,
Nikola

[mellis]

-- intervals in days
SET @history_interval = 7;
SET @trends_interval = 90;

DELETE FROM alerts WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM acknowledges WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM events WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);

DELETE FROM history WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_uint WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_str WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_text WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_log WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);

DELETE FROM trends WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@trends_interval * 24 * 60 * 60);
DELETE FROM trends_uint WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@trends_interval * 24 * 60 * 60);

[tim.mooney]

I've noticed that DB gets bloated by two tables, history_uint and history.

Is there some secure way or to configure in zabbix so that those tables are cleaned older than 30 days or something.

Zabbix has automated processing ("the housekeeper") that should be running periodically and cleaning any history values (including values in the other history_* tables that mellis shows) that have passed beyond their history period.

If you don't want to keep any history longer than 30 days, make sure that the "keep history" setting for all your items is 30d or less. Trends can be longer, as explained in the doc.

[doutdex]

Hi,

Easy just delete all data, without affect trends is executing the following queries:

truncate table history;

truncate table history_log;

truncate table history_str;

truncate table history_uint;

truncate table history_text ;

Best regards,

Hello,

I've noticed that DB gets bloated by two tables, history_uint and history.

Is there some secure way or to configure in zabbix so that those tables are cleaned older than 30 days or something.

Seems like these two are bloating up DB unnecessary and would like to sort it out without truncating those tables.


Screenshot from phpmyadmin showing DB bloat.

Kind regards,
Nikola

[cyber]

Hi,

Easy just delete all data, without affect trends is executing the following queries:

truncate table history;

truncate table history_log;

truncate table history_str;

truncate table history_uint;

truncate table history_text ;

Best regards,

Most idiotic way ... just delete data... Why bother to collect it at all, eh?

[doutdex]

Sorry I wrote a bad sentence once I wrote before consider to truncate history table, I double tested again because I have some virtual machines running on production I have copied two zabbix server /etc/centos-release
CentOS Linux release 7.7.1908 (Core)

Linux 3.10.0-1062.18.1.el7.x86_64 Home SMP Tue Mar 17 23:49:17 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

[root@ ]# rpm -qa |grep zabbix
zabbix-web-pgsql-scl-5.0.23-1.el7.noarch
zabbix-agent2-5.0.24-1.el7.x86_64
zabbix-web-5.0.23-1.el7.noarch
zabbix-agent-5.0.20-1.el7.x86_64
zabbix-nginx-conf-scl-5.0.23-1.el7.noarch
zabbix-release-5.0-1.el7.noarch
zabbix-web-deps-scl-5.0.23-1.el7.noarch
zabbix-server-pgsql-5.0.20-1.el7.x86_64

Currently data on tables:

history | 1692459008 | 1614 MB
history_log | 5944328192 | 5669 MB


Just only to truncate logs:

truncate table history_log;

truncate table history_str;

truncate table history_uint;

truncate table history_text ;

It Will delete LOGs , please keep aware to do NOT truncate "history" table.