Zabbix 1.8.5 MySQL Database Size growth out of proportions

[Shriram.Fowkar]

Hi,

We have got a Zabbix 1.8.5 installation. On 13th May 2011 the database size was 25 GB, on 17th May 2011 it became 107GB and today morning ie: 19th May 2011 it became 136 GB.

Our initial troubleshooting showed the following tables taking the maximum space :

history_log (this table was showing the max. 127 GB consumption)
history
history_uint

We fail to understand this high growth suddenly in Zabbix database.

Nothing was changed as far as data polling nor any new templates were added.

Can anybody let us know what is the use of the above tables and point in correct direction regarding the database growth.

Thanks & Regards

[fmrapid]

See the Zabbix manual.

Database size will keep growing until the maximum retention time for the history tables and trends table. Which may take a short time for history table with a very high rampup of dtabase size and a slower growth for the growth of the trends tables. I am puposefully skipping the tables related to events, messages, etc.

There is a formula in the documentation for version 1.8, section 2.6, to calculate the amount of space the database will use. Table partitioning, compression and reducing the retention time are three methods of dealing with the inherent limitations of the innodb storage engine to deal with large datasets. Ricordo Santos just put up a how-to for partitioning tables in Zabbix, see his weblog zabbixzone.com.

To go beyond that, the Zabbix developpers need to think about using some type of NoSQL, TokuDB like backend. Some people have been experimenting with TokuDB, but it is obscenely costly.

[Shriram.Fowkar]

Thanks for your reply. Will go through the section in the manual suggested by you and the link.

We zeroed in to the problem for this db growth. Here we have implemented the Windows Logging template which captures data from Windows Event Viewer (Application log, Security Log, System Log). On one of the servers there were some issues and lot of stuff was getting added to eventlog for One week every second. This was the reason for the DB growth. Now challange for us is to deleted that junk data. May be that is present in history_log which has taken 127 GB size. From yesterday that windows server has become normal and the DB has not grown even by 1 GB from yesterday. Back to normal. Now I will have to disable this windows logging template to avoid this kind of sudden DB growth.

Anybody got any better suggestion for this rather than removing the windows logging template ??

Thanks in advance.

[MrKen]

A quick look at that Template shows that Item history is kept for 90 days and Trends are kept for 365 days.

You could change that such that Item history is kept for only 7 or 10 days, and Trends for 30 days. Housekeeper would then keep your database at a reasonable size.

MrKen

[Jason]

We use windows log file monitoring, but use filters so only bring back the bits we're interested in. At least file by severity level.

For cleaning up the database you should be able to purge data from the history_log for that host older than a few days... Afterwards run an optimize on that table to recover the disk space.