Force housekeeper on hudge DB

[thomas.dub]

Hi,

In first, sorry for my english

I'm using Zabbix 2.0.8 for my production monitoring, and I've some items who are checked (more than 40.000 items).

I want to keep a lot of items, and more trends.
My server have two SSD for mysql, using InnoDB, but the table « history » it a bit hudge (more than 1.500.000.000 values, for approx. 140GB volume).

I've setted a small value for item retention, and configured the Housekeeper to remove all values every hours (setting MaxHousekeeperDelete=0 in zabbix_server.conf) but items are deleted too slowly.

For example, I've had an item with 1 year retention and 3 years trends, for approx. 1.700.000 values in history table.
I've changed the retention to 60 days and trends to 3 years, but there's still items from the last year in the table, after 1 week.

I can remove them with a script, but in fact, the last item history is older than the last trend, so if I remove items, I will lost data.

I think that trends are calculated by the housekeeper, and this one have an hardcoded max value to delete.

There's no huge load on the database.


So, I want to start manually housekeeper for deleting more items in one hour.


Did you have a solution for that ?

Some infos :

grep delet zabbix_server.log
11401:20131108:070312.717 housekeeper deleted: 597187 records from history and trends, 0 records of deleted items, 62 events, 0 alerts, 0 sessions
11401:20131108:080347.964 housekeeper deleted: 597715 records from history and trends, 0 records of deleted items, 60 events, 0 alerts, 0 sessions
11401:20131108:090422.270 housekeeper deleted: 597412 records from history and trends, 0 records of deleted items, 60 events, 0 alerts, 0 sessions
11401:20131108:100457.740 housekeeper deleted: 597166 records from history and trends, 0 records of deleted items, 62 events, 0 alerts, 0 sessions

Thanks for your help !

[bbrendon]

Probably late, but in case anyone else is looking. Upgrade and run.

zabbix_server -c /usr/local/etc/zabbix_server.conf -R housekeeper_execute

See: https://www.zabbix.com/documentation...oncepts/server

[tritsako]

Hi Thomas,


you can try also the below from mysqlone by one, AFTER you make a backup of you Database:


1 -- intervals in days
2 SET @history_interval = "Your History Interval in days";
3 SET @trends_interval = "the number of days you want to keep";


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

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

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


Costas