Zabbix: determine what item checks are responsible for storing the most data

Question

I'm running Zabbix v2.4.6. How can I determine what item checks are responsible for storing the most data?

I have had an exponential increase in stored data over the last month. I have a suspicion that someone may have miss-configured the storage/collection properties on an item check and I would like to verify this.

Answer 1

The following queries would give you items that have the most entries in history table:

SELECT itemid, count(*) AS cnt
FROM history
GROUP BY itemid
ORDER BY cnt DESC;

SELECT h.itemid, i.key_, count(*) AS cnt
FROM history h, items i
WHERE h.itemid = i.itemid
GROUP BY h.itemid
ORDER BY cnt DESC;

SELECT p.host, h.itemid, i.key_, count(*) AS cnt
FROM history h, items i, hosts p
WHERE h.itemid = i.itemid AND i.hostid = p.hostid
GROUP BY h.itemid
ORDER BY cnt DESC;

They are ordered from most simple that gives less information to a bit more complicated that gives more information, but may or may not be a bit more expensive to perform.

Same query can be repeated for history_uint, history_str, history_text and history_log tables. Note that log items in particular can suddenly start collecting more information due to a problem or unusual activity in the monitored software, even without any changes to Zabbix configuration.