Calculate zabbix database size by SQL query

zabbix-monitoringMy 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.

Názor na “Calculate zabbix database size by SQL query

  1. Radoslav

    Zdravim, 23.5.2014 som posielal reakciu na tento clanok emailom.
    Dostali ste ten email ?

Nie je možné pridávať komentáre.