History Tables growing out of control.

[sslsiv]

Hello,

I'm looking for advice on how to properly scale a large zabbix environment. We're running into issues with the history and hisotry_uint tables growing out of control due to the housekeeper not keeping up. We have over 3000 hosts and will be adding more over time. We're only retaining history for 7 days. Trends however are retained for 365 days.

-rw-rw---- 1 mysql mysql 140G Dec 10 22:04 history_uint.ibd
-rw-rw---- 1 mysql mysql 486G Dec 10 22:04 history.ibd

-Tavis

[Colttt]

hmm.. how many nvps do you have? maybe you must partition you tables! you will get a performance boost, other wise you can clean it manually, but it is not recommended..

[sslsiv]

[drucej31]

I had a similar problem... what i did was follow the instructions on how to setup partitioning which enabled the tables to be gradually cleared down ..

Also the other thing to watch out for is to check you don't have the interval checks to high in some templates... I adjusted mine to some to hourly checks and other to 5 min checks.. try and avoid anything lower than this as it can cause massive bloat on tables..

[kloczek]

https://www.google.com/search?q=zabbix+mysql+table+partitioning

[clahti]

So we are in a predicament ourselves, here is our database statistics:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME,(INDEX_LENGTH+DATA_LENGTH)/(1024*1024) AS SIZE_MB, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ("mysql", "information_schema") ORDER BY SIZE_MB DESC LIMIT 10;
+--------------+--------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME   | SIZE_MB      | TABLE_ROWS |
+--------------+--------------+--------------+------------+
| zabbix       | history_log  | 1183428.0938 |  784913911 |
| zabbix       | history_uint |   78612.0781 |  977410227 |
| zabbix       | history      |   24986.4844 |  327365946 |
| zabbix       | trends_uint  |    7996.5781 |  115735972 |
| zabbix       | trends       |    2347.7969 |   33966564 |
| zabbix       | history_text |    2312.0000 |   13284873 |
| zabbix       | history_str  |     923.0000 |    9362726 |
| zabbix       | events       |     148.7344 |    1097766 |
| zabbix       | alerts       |     102.3125 |      93381 |
| zabbix       | items        |      11.6719 |      20291 |
+--------------+--------------+--------------+------------+
10 rows in set (1.27 sec)

Our overall database size is 1.5TB. I have reviewed the following wiki articles:

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

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

They both reference zabbix 2.2 and/or 2.1. Does anyone have an updated guide to zabbix 2.4 for large environments? My plan is to build a new database server, copy the existing data, do the partitioning and then point zabbix server to the new database server. We are running zabbix 2.2 but I am piloting zabbix 2.4 in our lab for production upgrade. Should I do the partitioning before or after the upgrade? Here are my zabbix server stats:

Number of hosts (monitored/not monitored/templates)	609	488 / 37 / 84
Number of items (monitored/disabled/not supported)	17865	17148 / 376 / 341
Number of triggers (enabled/disabled) [problem/ok]	5998	5429 / 569 [26 / 5403]