Problem with clearing history of selected items

[Pada]

Hi,

I've now experienced a big problem with Zabbix, where I selected to clear the history of all (52 of) the items on a single host, which took so long (I never even let it finish) that Zabbix started to run out of History Cache (which was left at default of 8MB) and missed lots of items that it should've monitored.
I killed the MySQL query after it ran for too long and then I even had to stop our Zabbix

I'm using Zabbix Server 1.8.11 with a MySQL InnoDB schema.

The MySQL query that the Zabbix Server (and not the web frontend) executed that took so long was of the format:
"DELETE FROM history_uint WHERE (itemid IN (<list of item id's here>))"

We've never had any issues of this sort with our HouseKeeper.
Our Zabbix server is processing about 130 items/second and the housekeeper typically delete about 500k records per hour:
"housekeeper deleted: 430453 records from history and trends, 4000 records of deleted items, 0 events, 0 alerts, 0 sessions"

Our MySQL server isn't very fast either, but it handles its day to day job just fine. It has very little RAM (3GB), but it has like 6x 10000rpm drives on a RAID controller with 512MB RAM, so it could very well be the culprit. The InnoDB file is taking up 70GB, but this also includes all the deleted data if I'm not mistaken. We're in the process of upgrading the RAM on that server to say 16GB.

My question is: is there a way to limit/throttle that "Clear history for selected" so that it doesn't lock up our whole monitoring system?
So far my workaround is to full clone to host and then to delete the old one, and then letting the HouseKeeper do the cleanup.

Second question: what does the "MaxHousekeeperDelete" Zabbix Server configuration do? Does it imply that only 500 rows would be deleted by the HouseKeeper and then that delete operation is performed say 1000x in our case, until it deleted all 500k rows!?
If it works like this, can that "Clear history for selected" be done in the same way or is it already like that in v2.0.3?

Remarks and suggestions are welcome.

Thank you in advance!

[m_gularte]

Same problem here... Zabbix 2.0.2. And when i select the option "unlink and clear" from template this happens too (not aways), but when it happens some kind of database lock occurs.

Other problem after 2.0.2 is related with it services... They are much more slow to show the values.

[Pada]

Thanks for the reply!

I had a look at the source code and I can't find any table or a kind of lock during the deletion.
I've still had to restart my Zabbix server on 2 occasions where I tried to delete the history. The first time I had to restart the MySQL server machine just to recover from it.

I now went and had a look at the Zabbix source code and it seems like not all of the delete item history frontend calls make use of the HouseKeeper.

I have now modified the v1.8 frontend so that the delete_history_by_itemid action is ALWAYS performed by the HouseKeeper, by making the following replacements in /frontends/php/include/items.inc.php

if($use_housekeeper){

with

if(true){

and also

$result = delete_trends_by_itemid($itemids,$use_housekeeper);

with

$result = delete_trends_by_itemid($itemids,1);

To benefit from the HouseKeeper, you need to have MaxHousekeeperDelete value left on the default value (which is 500) or another lowish number - in the zabbix_server.conf. This will limit the amount of rows deleted with a single query. The query will then be called multiple times, but at least it shouldn't lock up your DB completely any more, I suppose!?

I'm not sure if I should attempt clearing the history even though I've made this mod.

Do you reckon I should file this as a bug?

[Pada]

After I applied that "fix", I haven't had any issues yet with cleaning the history of selected items.
Our MySQL server's CPU usage did go up during the HouseKeeping, but not as high as when cleaning the items directly from the php code.