I think Nightmere is coming (Huge Database)

[arut55]

Hello Dears.
I have installed zabbix server and every second my MYSQL database become huge and huge. Now db size approximetly 150 GB.
What can I do to optimize size without loosing any data? because all history and graphs is very important.
I'm saving each record for 365 days.

[salzkrebs]

Hallo,

First you can think about if the trends are enough for you (Trend= min/max/avg per hour of an Item), then you could maybe decrease the History value of you items.

The next thing you could do is to increase the poll timer. The lesser new values per second the lesser space will be required. Maybe if you have some descriptions which won't change every 10 minutes or so you can adjust the poll interval.

Those three Variables (History, Trend, Delay) will have a direct impact on your DB size.

You can read more on this topic at the Documentation of Zabbix: https://www.zabbix.com/documentation...n/requirements

Br Manuel

[mushero]

How many hosts, items, new values/second ?

150GB is pretty darn huge for even tens or hundreds of thousands of items unless you have 1 million at 1 minute resolution, etc.

Make sure your housekeeper is running and you are not keeping history data (normally you keep trend data, make sure you know the difference).

Now if you want 1-5 minute data kept forever, well, yes, buy more disks and think about partitioning.

[skygge]

My Zabbix database size is 222 GB now and still feels good
1630 monitored hosts, 101 new values per second

[mushero]

Hmm, that's pretty huge for not so many hosts or NVPS - you must be keeping all your history ? At this rate you'll be at 1TB or more soon which is generally unmanageable in MySQL, on fast disks, RAM, graphs, etc.

The idea is to only keep detailed history data for a week or month, then let the system summarize to trends, then you'd have far less data.

We have this many hosts and far more NVPS but have our DB down to 50-100GB I think with 30 day history (and less on some things).

[nick0909]

I would guess your housekeeper is not running. I had this same problem, it is off by default in 2.2+ I believe. Go to Administration > General > Housekeeping drop down and make sure all the boxes are checked. Once you enable it it may take a month or two to shrink down as far as it should, but it should at least start shrinking. You can also see if it is actually running by running grep on the zabbix server logs and search for housekeeper, you should see something like this:

$ sudo cat zabbix_server.log | grep housekeeper
1743:20150324:071916.288 executing housekeeper
1743:20150324:072026.357 housekeeper [deleted 518987 hist/trends, 0 items, 64 events, 0 sessions, 0 alarms, 0 audit items in 70.068503 sec, idle 1 hour(s)]
1743:20150324:082026.357 executing housekeeper
1743:20150324:082138.893 housekeeper [deleted 519955 hist/trends, 0 items, 63 events, 0 sessions, 0 alarms, 0 audit items in 72.535771 sec, idle 1 hour(s)]

[roduf]

You should be fine, Zabbix can handle that amount of data.

[Zaniwoop]

30455 items
322 new values per second
350 GB database (mySQL)

The server is quite bored, doesn't have enough to do.

[misteryoda]

You might want to consider partitionning your database and disabling housekeeper for the partitionned tables as you can then just drop the old tables partitions when cleaning up.

I use MySQL (Percona) and a slightly modified perl script you can find here http://www.zabbix.org/wiki/Docs/howt...l_partitioning. Works like a charm.

[stephane.papin]

You might want to consider partitionning your database and disabling housekeeper for the partitionned tables as you can then just drop the old tables partitions when cleaning up.

I use MySQL (Percona) and a slightly modified perl script you can find here http://www.zabbix.org/wiki/Docs/howt...l_partitioning. Works like a charm.

Mysql Partition feature is one of most efficience way to avoid slow zabbix. Got 2 zabbix central servers with mysql 1.4 To : before partitionning queues are most of time overflow. Now queues are under 10 items à 5sec. (3k hosts, 300k items per servers, 1 of 2 server perform high monitoring also around 130K triggers).