If you run Zabbix long enough, eventually your database will grow to sizes you’d rather not see. And that begs the question: what items are causing the most storage in my Zabbix backend, be it MySQL, PostgreSQL or something else?
I investigated the same questions and found the following queries to be very useful.
Before you start to look into this, make sure you cleanup your database of older orphaned records first, see an older post of mine for more details.
What items have the most value records?
These probably also consume the most diskspace.
For the history_uint
table (holds all integer values):
SELECT COUNT(history.itemid), history.itemid, i.name, i.key_, h.host FROM history_uint AS history LEFT JOIN items AS i ON i.itemid = history.itemid LEFT JOIN hosts AS h ON i.hostid = h.hostid GROUP BY history.itemid ORDER BY COUNT(history.itemid) DESC LIMIT 100;
For the history
table (holds all float & double values):
SELECT COUNT(history.itemid), history.itemid, i.name, i.key_, h.host FROM history AS history LEFT JOIN items AS i ON i.itemid = history.itemid LEFT JOIN hosts AS h ON i.hostid = h.hostid GROUP BY history.itemid ORDER BY COUNT(history.itemid) DESC LIMIT 100;
For the history_text
table (holds all text values):
SELECT COUNT(history.itemid), history.itemid, i.name, i.key_, h.host FROM history_text AS history LEFT JOIN items AS i ON i.itemid = history.itemid LEFT JOIN hosts AS h ON i.hostid = h.hostid GROUP BY history.itemid ORDER BY COUNT(history.itemid) DESC LIMIT 100;