My zabbix database grows to very big size. So I wrote some SQL queries, which helps me figure out, which items requires mandatory space of my disks.
Calculate data size per item type
<pre>
SELECT SUBSTRING_INDEX(key_, '[', 1) AS item_key,
/* 50 bytes per history record */
SUM( (i.history * 86400 / i.delay) * 50 ) AS history_size,
/* 128 bytes per trend record */
SUM( i.trends * 24 * 128 ) AS trend_size,
COUNT(*) AS number_of_items
FROM items AS i
INNER JOIN hosts AS h ON (h.hostid = i.hostid AND h.status = 0 /* monitored hosts */)
GROUP BY item_key
ORDER by history_size DESC
</pre>
...
Calculate data size per item type
<pre>
SELECT SUBSTRING_INDEX(key_, '[', 1) AS item_key,
/* 50 bytes per history record */
SUM( (i.history * 86400 / i.delay) * 50 ) AS history_size,
/* 128 bytes per trend record */
SUM( i.trends * 24 * 128 ) AS trend_size,
COUNT(*) AS number_of_items
FROM items AS i
INNER JOIN hosts AS h ON (h.hostid = i.hostid AND h.status = 0 /* monitored hosts */)
GROUP BY item_key
ORDER by history_size DESC
</pre>
...
