Housekeeper not working right, database massive

[ebonweaver]

So we discovered that we missed a very important update note back in 2.2 where the housekeeper was turned off until you turned it back on in the new options. That should have been in flashing red at the top of the notes...
As a result, our database is massive.
history_uint is 57gb with around 750 million rows
history is 11gb with around 150 million rows

The housekeeper based on the log didn't do the job expected and clean out the last 2 years worth of junk, it only clipped a few entries:

housekeeper [deleted 1150831 hist/trends, 216409 items, 1844 events, 222650 sessions, 0 alarms, 24 audit items in 1261.742354 sec, idle 1 hour(s)]

Now, at the same time I was going in and turning back the history and trend on several template items that were just out of reason. The result above looks more like what I'd expect from those changes assuming the housekeeper was working before this. However, all indication in the documentation is that if all the enabled boxed in the housekeeper UI are unchecked, there is no housekeeping. So then why do the real world results seem to disagree? But then why are there hundreds of millions of rows still in the database?

Something seems very wrong with all of this and I can't make sense of it. We need to sort out what's going wrong and get this database size under control. Any ideas?

[ebonweaver]

Another point about housekeeping not working, I set discovery data to 90 days but despite that I still have data going back 3 years.

[Martins]

Hello,

Yeah I have similar issue with housekeeping, it is enabled, starts every hour, but anyway, I have graphs which show history for year and more, although in items I have set history to 7 and trending to 30 days ...

It is really confusing what housekeeping is deleting and what not.

[unficyp]

just a guess.
zabbix_server.conf: MaxHousekeeperDelete set too low so that housekeeping can't keep up with the amount of data ?

[Martins]

I tested with 0 (which is unlimited), set 1000000 and all the time I have same results. As written in documentation: https://www.zabbix.com/documentation.../zabbix_server

This parameter is supported since Zabbix 1.8.2 and applies only to deleting history and trends of already deleted items.

This is really confusing, where then is active item history/trend deletion configuration?

[unficyp]

You can configure global periods in Configure -> General -> Housekeeping (right dropdown)
If that was your question.

[Martins]

They all are enabled.

[Pedro.Almeida]

Be aware that Houseekeeper will not delete massive amount of data.
It has several limits on that!

# To prevent Housekeeper from being overloaded, no more than 4 times HousekeepingFrequency
# hours of outdated information are deleted in one housekeeping cycle, for each item.

And the default is HousekeepingFrequency = 1, so, 4 hours for every round...

And also:

# No more than 'MaxHousekeeperDelete' rows (corresponding to [tablename], [field], [value])
# will be deleted per one task in one housekeeping cycle.

The default is 500!. If you have guts, set it to 0 (not at my responsibility)

If you must, delete it manually and optimize/rebuild your tables/partitions.

[Pedro.Almeida]

Also, are your tables massive in terms of rows of sizes?

Do a:

select table_name,max_data_length,avg_row_length,partitio n_name,data_free,index_length+data_length total,table_rows,100*(data_free/(index_length+data_length)) busy,(data_length+index_length)/table_rows rate from information_schema.PARTITIONS;

Compare the sizes to the ones of the DB dirs.

[Pedro.Almeida]

Lastly, if you have 3.0, you can set all the limits and just fire up the Housekeeping process.

Or you can go nuts and run this that'll housekeep without any restriction of max deletion (while respecting the set history values):

#!/bin/bash

DBNAME=zabbix
DBSERVER=127.0.0.1


clock=`date +%s` # now

# group items by their history
for k in `echo "select history from items group by history order by history desc;" | mysql -h $DBSERVER -N $DBNAME`; do

echo "select itemid from items where history=$k;" | mysql -h $DBSERVER -N $DBNAME > itemids

# for each history length, get the oldest date we can have for that value
DAY=$(($clock-3600*24*$k))

total=`cat itemids | wc -l` # how many? (just for stats)

j=1;
p=0;
lp=101;
for i in `cat itemids` ; do
p=$(((100*j)/total));
if [ ! "$p" == "$lp" ]; then
echo -n "[H$k] $j/$total [$p%] | ";
date
fi
lp=$p
echo "delete from history_uint where itemid=$i and clock<$DAY" | mysql -h $DBSERVER $DBNAME ;
echo "delete from history_str where itemid=$i and clock<$DAY" | mysql -h $DBSERVER $DBNAME ;
echo "delete from history_log where itemid=$i and clock<$DAY" | mysql -h $DBSERVER $DBNAME ;
echo "delete from history_text where itemid=$i and clock<$DAY" | mysql -h $DBSERVER $DBNAME ;
echo "delete from history where itemid=$i and clock<$DAY" | mysql -h $DBSERVER $DBNAME ;

j=$((j+1));

done

done

exit 0