Zabbix DB growing too big!

[surreal42]

Hello, I am running zabbix v2.0.12 in an environment with around 900 hosts, 33K items.

Here are the sizes of the largest tables in the DB;

+-----------------------+------------+
| Tables | Size in MB |
+-----------------------+------------+
| history_uint | 103806.27 |
| history | 24841.17 |
| trends | 7309.44 |
| history_text | 6047.63 |
| trends_uint | 5430.64 |
| history_str | 2426.39 |


This is all stored on a single mysql innodb file, which is now 160GB and growing about 2GB a week. Housekeeper is enabled but is not able to keep it appears.

I first looked at purging any records from these tables older than 1 month, but the tables are so large and unweildy that the server struggles to run any queries on them.

I'm willing to sacrifice all our historical data to stop the tables growing any larger, so is it safe to just go ahead and truncate the history_uint and history tables? Will this cause any consistency issues, or any other potential problems going forward after clearing these tables?

Thanks!

[akbar415]

Zabbix database can become a monster kkk..
I passed for the same problem. Two things help-me solved.

1 - Duplicate all of my templates. One for production environment and other for test environment and other things that are not critical (on this I rised the interval of verification and decreases the history data time)

2 - I started using the flexible range checks. Out of the work hours, I increase the interval of check ten times.


Before I make this changes, the databases was growing 400MB -700MB per day. Now is only 50MB-80MB per day.

[surreal42]

Hi akbar, thanks for your reply, however I am more so trying to find out if it is safe to just clear/truncate the history tables on the DB, and will this have any effect on the operation of zabbix afterwards.

I could go ahead and make a dump of the entire DB, to then truncate the history tables, then confirm everything is ok, and then restore in-case there were any problems, however I would prefer a method whereby there is minimal downtime on zabbix as its critical to our system monitoring.

[syndeysider]

partitioning

https://www.zabbix.org/wiki/Docs/howto/mysql_partition

This x 1000000.

I went through the same teething pains. It's alot simpler to setup than you think.

Basically, by allowing partitioning you can take control of how long you want data to be stored, where it is stored and when you would like to "free" up some space.

Also, I spent ALOT of time going through the "default" templates I applied. Removing items i didn't need, setting history/trend values to within reasonable limits etc.

I have over 1500 hosts with 200 items on average per host. 14 days history 180 days trends with 1300 NVP's. My DB is about 180GB and stable...

[surreal42]

Hello fellow Sydney human - Yep was actually looking at this, Its definitely in the pipeline to clean up this zabbix environment to something more stable (Inherited a mess, trying to fix it...), but atm Im first looking to just reduce the size of the current DB to prevent the innodb file from growing anymore (as it cannot be shrunk).

Just a couple questions about when you did the partitioning, was your DB also massive (100Gb+) when you went through this process? You were able to run the alter queries and stored procedures without issues/downtime?

When you made the initial index changes (Alter SQL commands) that remove the primary keys, did you go with the suggestion below or leave it as is ?

Based on the information there, the index changes above may need to be tweaked at some point to include making a primary key on the history and history_uint tables (since they are the most heavily used history tables).

Also, will this MySQL partitioning process later affect my ability to upgrade to zabbix 2.2 without issues?

thanks,

[surreal42]

I've reduced the history and trends durations of all items across the board via the templates to way lower now (7 days history, 90 days trends) which is fine for our needs, and this has stopped the rampant growing of the DB, which is great.

However I am still wanting to remove all the unwanted data in the history_uint and history tables (combined 130GB~), but am still not 100% sure if its safe to just go ahead and truncate these two tables? Can anyone confirm??

[syndeysider]

Hey mate. Sorry for the late reply.

To answer your questions :

was your DB also massive (100Gb+) when you went through this process

Yes. I had a DB of 430GB when I had to relook at how I managed my data.

You were able to run the alter queries and stored procedures without issues/downtime

Yes and No. Yes I was able to alert during the outage (mySQL Master/Slave) but I lost the history/trend data during the migration (I didn't update my slave with changes, I rebuilt it)

When you made the initial index changes (Alter SQL commands) that remove the primary keys, did you go with the suggestion below or leave it as is

Left as is.

MySQL partitioning process later affect my ability to upgrade to zabbix 2.2 without issues

No. I did this on 2.0.7 and currently I'm on 2.4.5. Upgraded without an issue.

If you in Sydney. PM me, we can meet up for coffee and I'll give you a hand if you need.

[surreal42]

Thanks for your response syndeysider. I am still searching for a definite answer though on if its safe to truncate the history tables without breaking anything