Improved API history.get

[nelsonab]

Currently the return data from the API call history.get is quite challenging to understand. It also tends to waste a fair bit of bandwidth. This patch does the following:

1) It greatly simplifies the output for the call
Example JSON output:

 
Call:
{
"auth":"*********",
"method":"history.get",
"id":99,
"params":{"history":0,
  "output":"extend",
  "itemids":[22194,22196,22197],
  "limit":10},
"jsonrpc":"2.0"
}

Reply:
{"jsonrpc":"2.0","result":
[{"itemid":"22194","clock":"1284536929","value":"0.0800"},
{"itemid":"22194","clock":"1284536934","value":"0.0800"},
{"itemid":"22194","clock":"1284536939","value":"0.0700"},
{"itemid":"22194","clock":"1284536944","value":"0.1400"},
{"itemid":"22194","clock":"1284536949","value":"0.1300"},
{"itemid":"22194","clock":"1284536954","value":"0.1200"},
{"itemid":"22194","clock":"1284536959","value":"0.1100"},
{"itemid":"22194","clock":"1284536964","value":"0.1000"},
{"itemid":"22194","clock":"1284536969","value":"0.0900"},
{"itemid":"22194","clock":"1284536974","value":"0.1700"}],
"id":99}

The following link shows the current output format:
http://www.zabbix.com/documentation/1.8/api/history/get

2) It alters the order in which items are sorted. Presently history data is sorted with the itemid having precedence, not the clock. This is reversed in this patch. Thus if you call this API function with 5 different itemids and limit your return to 100 items, you will get the latest 100 items in chronological order, which means it is possible not not see one of the items you wanted if it was not polled in the time period of your query. Before it would show you the first 100 history points for the first item, and never show you any of the others.

This patch was tested with Zabbix 1.8.3 (API 1.3).
It is applied against the api/classes/class.chistory.php file.

One last thing to note, this is mostly for the devs. There is a provision in the call for grouping the output, however this data is never used. I think this code should be used. I did not make changes to that aspect of the call.

[Aly]

1. MySQL can't use such keys, you will need to add manually new indexed key for history clock, itemid (reverse of currently existed). Without it, on large systems such query will kill DB.

2. While I agree that there is data duplication (I will remove "items"), that's doesn't mean that other code (currently unused) is useless, It's for possible use in future.

My 0.02$

[nelsonab]

1. MySQL can't use such keys, you will need to add manually new indexed key for history clock, itemid (reverse of currently existed). Without it, on large systems such query will kill DB.

Hmm, then that's a problem because the ability to pass a list of item id's to the history function is practically useless unless you do a get which is constrained only by time.

How bad is the performance hit if the clock field is indexed?

2. While I agree that there is data duplication (I will remove "items"), that's doesn't mean that other code (currently unused) is useless, It's for possible use in future.

My 0.02$

I think the unused code is very useful, and I would love to actually see it used in some way. :-)

[Aly]

Hmm, then that's a problem because the ability to pass a list of item id's to the history function is practically useless unless you do a get which is constrained only by time.

How bad is the performance hit if the clock field is indexed?



I think the unused code is very useful, and I would love to actually see it used in some way. :-)

1. It hits pretty hard, because History table may contain billions of rows. More data you have in history table -> slower query.

2. Some day...

[nelsonab]

1. It hits pretty hard, because History table may contain billions of rows. More data you have in history table -> slower query.

No doubt, but I was wondering if there was some quantitative data on that? How much of a performance hit is taken on write, and how much of a performance gain is seen upon table lookups. I would argue that Data is written once and all retrieved at least twice (or may more). Once when it's converted to a trend and once when it's deleted, add in graph views and triggers and you get even more lookups of the same data point. Would a time index truly be helpful here?

While we're on this, since multiple itemid lookups can be such a hit on the DB and since they have what I would argue is an undesired functionality how about making it so that history.get can only work on one item, and make aggregation a function of the client? Yes some developers would say that sucks just as bad, but it does not cause the poor performance of lookup up for multiple itemids and only retrieving one due to the lack of indexing on clock.

[Aly]

Would a time index truly be helpful here?

A can't say for sure about server side (it contains almost all needed data in memory ), but when we peek up the data by itemid, we are interested in data per single item, and not interested when,which item arrived in chronological perspective.

While we're on this, since multiple itemid lookups can be such a hit on the DB and since they have what I would argue is an undesired functionality how about making it so that history.get can only work on one item, and make aggregation a function of the client? Yes some developers would say that sucks just as bad, but it does not cause the poor performance of lookup up for multiple itemids and only retrieving one due to the lack of indexing on clock.

You may sort returned data.. even more it can be added in API

So it would look like: get history by itemids, for some period. Then sort returned data by clock, and wuala.