Calculate zabbix database size by SQL query

zabbix-monitoring

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

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

List zabbix items by required resources

SELECT i.type, i.delay, i.history, (i.history / i.delay) AS rate,
  ((i.history / i.delay)) * count(*) AS significance,
  i.trends, count(*) AS totalcount
FROM items AS i
INNER JOIN hosts AS h ON (h.hostid = i.hostid AND h.status = 0 /* monitored hosts */)
GROUP BY i.type, i.delay, i.history, i.trends
ORDER BY `significance` DESC

SELECT
  /* 50 bytes per history record */
  SUM( (i.history *86400 / i.delay) *50 ) / 1048576  AS history_size,
  /* 128 bytes per trend record */
  SUM( i.trends *24 *128 ) /1048576 AS trend_size,
  COUNT( * ) AS number_of_items
  /* history and trends grow per year */
FROM items AS i
INNER JOIN HOSTS AS h ON ( h.hostid = i.hostid
AND h.status = 0 /* monitored hosts */ )

Optimize your zabbix instance:

UPDATE items SET delay = 60 WHERE delay < 60
UPDATE items SET history = 30 WHERE history > 30

Before optimization:

history_size    trend_size  number_of_items     
10436.03181839  23418.4570  2303

After optimization:

history_size    trend_size  number_of_items     
1744.19546127   23418.4570  2303

Storage space for database has been changed from 10G to 1.7G for history data. 23G size for trends doesn’t hurt us because it will grow to this size after 10 years.