Finding the biggest data (storage) consumers in Zabbix

Want to help support this blog? Try out Oh Dear, the best all-in-one monitoring tool for your entire website, co-founded by me (the guy that wrote this blogpost). Start with a 10-day trial, no strings attached.

We offer uptime monitoring, SSL checks, broken links checking, performance & cronjob monitoring, branded status pages & so much more. Try us out today!

Profile image of Mattias Geniar

Mattias Geniar, March 16, 2017

Follow me on Twitter as @mattiasgeniar

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;


Want to subscribe to the cron.weekly newsletter?

I write a weekly-ish newsletter on Linux, open source & webdevelopment called cron.weekly.

It features the latest news, guides & tutorials and new open source projects. You can sign up via email below.

No spam. Just some good, practical Linux & open source content.