Table Partitioning on Zabbix 2.2

[Crypty]

Anyone?

Thank you...

[Crypty]

I would really appreciate any help with this... Thank you!

[kevinw]

crypty,
In my experience, the database size does reach a steady state as you describe, where old data ages off as new data replaces it. This is true regardless of whether or not you partition. As you have already seen the history data is the largest consumer of space so your History aging is more important for db size than Trend aging, which is a small fraction of history.

The difference is that without partitioning you are using Housekeeper to age off the old data based on the individual history/trend settings configured for each item. While that is more flexible it does make it harder to estimate since one item could have 3 days of history and another could have a year.

When you use partitioning fully, you abandon the Housekeeper (and the corresponding settings on the items) in favor of a daily job that drops older records en masse. Easier on the database but less granular than Housekeeper.

Our db has reached steady state and and only fluctuates as the monitoring population changes (i.e. as NVPS nudges up or down). We use 21-day history and 1-year trend settings and you can see the db size flatten out 21 days after large changes in the population occur.

Regards,
Kevin

[Crypty]

Thank you Kevinw!

Unfortunately, on several units, we MUST have individual storage for various values...

So if I do not use the global parameter for history/trends, then it is NOT possible to use partitioning, is that correct?

And what if i change something? E.g. I change the Item type from numeric to text, are the old values rewritten? Doesn't it cause that if the numeric values had 1 GB, I changed to text so the new space is required instead of rewriting the old one? Will be the old 1GB of historical data rewritten or will stay somewhere hidden?

Thanks.

[kevinw]

Well, you can still do partitioning on other tables if you wanted (i.e. events), but to do individual aging of items it requires HK running. In theory you could mix them and only drop partitions older than your longest retention time, however you still incur the overhead of running HK. IMHO it's better to use partitioning with the max of all your items with the only downside that you have more days of data on some items than you desired.

As for changing item types, you should assume that it will wipe the old data. You should be able to trust that it will clean out the old rows from history and create new rows in history_str (in your example).

Kevin

[Crypty]

Great, thanks, Kevin!