Few posts on the internet about this but quickest way i found of deleting old data below.
Save the below code as .sql and run from the server with…
#mysql zabbix -p ./deleteolddata.sql
SQL CODE –
-- 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);
You can change the bit at the top to cut down on the history kept or trends data.
Happy tidying.
I’m looking to clean up a 4.x database. Have you tried this script on zabbix 4.x?
Ive not had to do it since…its something i could look into
Well, I’ll save you the trouble, it works in 4.x. Thanks!
query for Zabbix+postgresql backend
DELETE from history where (extract(epoch from now() at time zone ‘utc’) * 1000)::bigint – clock > (7 * 24 * 60 * 60 * 1000);
…
then i’d like to do full vacuumdb –full to free up space.
Hello,
When i tried to execute DELETE FROM trends_uint WHERE (UNIX_TIMESTAMP(NOW()) – clock) > (@trends_interval * 24 * 60 * 60); command, always receive the following error message:
Failed to execute SQL : SQL DELETE FROM trends_uint WHERE (UNIX_TIMESTAMP(NOW()) – clock) > (@trends_interval * 24 * 60 * 60); failed : Lost connection to MySQL server during query
I tried to restart MySql and the Server, but i obtain the same error. Why is this happening?
Regards.