why strange sql queries in housekeeper?

[majekw]

Hi.

I had a lot of items with history time set to 365 days. Last time I changed this to 7 days and ... history remains and only some entries are deleted each hour. I started digging in housekeeper and I found two things whitch I can't understand:
1. When you prepare list of items for history delete, there is a sql:
select itemid,lastdelete,history,delay from items where lastdelete<=%d.
There always select all items (I can't imagine situation with lastdelete in future).
2. When deleting items history:
delete from history where itemid=%d and clock<%d limit %d
where limit is 2*CONFIG_HOUSEKEEPING_FREQUENCY*3600/item.delay (2x number of samples in CONFIG_HOUSEKEEPING_FREQUENCY). Why this limit is here and why this is only in HAVE_MYSQL condition?

BTW: Thanks for the geat tool.

[Alexei]

1. When you prepare list of items for history delete, there is a sql:
select itemid,lastdelete,history,delay from items where lastdelete<=%d.
There always select all items (I can't imagine situation with lastdelete in future).
The lastdelete field does not play any role currently, so the SQL statement can be simplified and no extra update of the field is required as well.

Several years ago the field was used to ensure hourly housekeeping for each item using "lastdelete+3600<now" condition. Now it has value only for potential researchers of ZABBIX sources.


2. When deleting items history:
delete from history where itemid=%d and clock<%d limit %d
where limit is 2*CONFIG_HOUSEKEEPING_FREQUENCY*3600/item.delay (2x number of samples in CONFIG_HOUSEKEEPING_FREQUENCY). Why this limit is here and why this is only in HAVE_MYSQL condition? Because MySQL is not proper database (BDB,MyISAM). It locks tables for any delete operations. So, in order to prevent situation like you have (purging of massive amount of data, which takes several hours sometimes) I decided to delete only small pieces of data, thus ensuring parallelism on the database backend.

[majekw]

Because MySQL is not proper database (BDB,MyISAM). It locks tables for any delete operations. So, in order to prevent situation like you have (purging of massive amount of data, which takes several hours sometimes) I decided to delete only small pieces of data, thus ensuring parallelism on the database backend.

Thanks for explanation.
And next related question: why history.clock is not indexed? Is it also db performance issue?

[Alexei]

And next related question: why history.clock is not indexed? Is it also db performance issue? Why should it be indexed? To simplify housekeeping? It makes no sense.

If you have 50GB data history, this index alone will probably add at least 10GB , maybe close to 20GB. Also cost of each INSERT statement will be increased significantly.

[majekw]

Why should it be indexed? To simplify housekeeping? It makes no sense.

If you have 50GB data history, this index alone will probably add at least 10GB , maybe close to 20GB. Also cost of each INSERT statement will be increased significantly.

Ok. Thank you. No questions (for now) :)