The post Finding the biggest data (storage) consumers in Zabbix appeared first on ma.ttias.be.

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;

The post Finding the biggest data (storage) consumers in Zabbix appeared first on ma.ttias.be.