Issues with Zabbix database growing too large

[centenor]

Hello,

We recently moved to Zabbix 3.0 LTS and are runing it on an Ubuntu 16.04 box with a PostgreSQL backend on version 9.5.6. Everything is working great, we have set certain paramters in the Zabbix server config file that all seem to be okay. The only issue we are running into is that the database just keeps growing. It's as if the housekeeper job isn't deleting anything. We've set our housekeeping retention to 60 days so I assume it shouldn't be getting too overgrown. We've allocated almost 300GB's and it's almost grown through all of that already. Would anyone happen to know what is causing this issue? The only workaround i've found so far is to truncate the history_log table, which then clears about 30GB's of storage but the amount it deletes keeps decreasing everytime I do that so at some point it's going to hit the 300GB allocated and crash.

Here is our Zabbix server config parameters

CacheSize=1024M
HistoryCacheSize=256M
ValueCacheSize=64M
#HistoryCacheSize=32M
TrendCacheSize=128M
StartPollers=150
StartPingers=40
StartDiscoverers=40
StartTrappers=50
AlertScriptsPath=/etc/zabbix/alertscripts
StartPollersUnreachable=20

I've also uploaded a screenshot of our Zabbix environment according to the dashboard. If anyone has any clue what might be going on it would be really appreaciated!

Let me know if you need anymore information from our end or additional logs

[batchenr]

Hello,

We recently moved to Zabbix 3.0 LTS and are runing it on an Ubuntu 16.04 box with a PostgreSQL backend on version 9.5.6. Everything is working great, we have set certain paramters in the Zabbix server config file that all seem to be okay. The only issue we are running into is that the database just keeps growing. It's as if the housekeeper job isn't deleting anything. We've set our housekeeping retention to 60 days so I assume it shouldn't be getting too overgrown. We've allocated almost 300GB's and it's almost grown through all of that already. Would anyone happen to know what is causing this issue? The only workaround i've found so far is to truncate the history_log table, which then clears about 30GB's of storage but the amount it deletes keeps decreasing everytime I do that so at some point it's going to hit the 300GB allocated and crash.

Here is our Zabbix server config parameters

CacheSize=1024M
HistoryCacheSize=256M
ValueCacheSize=64M
#HistoryCacheSize=32M
TrendCacheSize=128M
StartPollers=150
StartPingers=40
StartDiscoverers=40
StartTrappers=50
AlertScriptsPath=/etc/zabbix/alertscripts
StartPollersUnreachable=20

I've also uploaded a screenshot of our Zabbix environment according to the dashboard. If anyone has any clue what might be going on it would be really appreaciated!

Let me know if you need anymore information from our end or additional logs

i had same issue,
you will have to change db too work per table like this :
https://stackoverflow.com/questions/...-file-in-mysql

make backup before!!
my db used to grow GB a day now its GB per 14 days or so.

good luck

[centenor]

i had same issue,
you will have to change db too work per table like this :
https://stackoverflow.com/questions/...-file-in-mysql

make backup before!!
my db used to grow GB a day now its GB per 14 days or so.

good luck

I've seen a couple of posts suggesting that and also suggesting on partitioning the tables and creating a job that does a clean every month or so. A lot of those guides are unfortunately for MySQL setups. I haven't seen a lot for PostgreSQL. Is there any that you've seen for PostgreSQL because i haven't much on my end

[batchenr]

I've seen a couple of posts suggesting that and also suggesting on partitioning the tables and creating a job that does a clean every month or so. A lot of those guides are unfortunately for MySQL setups. I haven't seen a lot for PostgreSQL. Is there any that you've seen for PostgreSQL because i haven't much on my end

sorry i dont know, if u ever find the answer post here.

[dfisicaro]

I'm having the same thing with our setup of Zabbix as well.
It grows GB's per day and I can't work out why.

I'm trying to see about setting the internals on the item checks to a larger time period. But trying to understand how to do it.

Using mysql though.

[batchenr]

I'm having the same thing with our setup of Zabbix as well.
It grows GB's per day and I can't work out why.

I'm trying to see about setting the internals on the item checks to a larger time period. But trying to understand how to do it.

Using mysql though.

if you have MySQL follow the steps in the link i added

[dfisicaro]

We have a new version of MYSQL installed, using MariaDB 10.x.

Our ibdata1 is not growing so much. It's the tables in the Zabbix database.

I've managed to clean up the DB using another link which worked fine, but I want to know how to stop logging/storing so much data. And if the housekeeping can do this.

[batchenr]

We have a new version of MYSQL installed, using MariaDB 10.x.

Our ibdata1 is not growing so much. It's the tables in the Zabbix database.

I've managed to clean up the DB using another link which worked fine, but I want to know how to stop logging/storing so much data. And if the housekeeping can do this.

i have mariadb also
housekeeping should help you - i can copy my setting:
go to Administration -> General-> scrool down menu choose housekeeping

Trigger data storage period (in days)

3
Internal data storage period (in days)

3
Network discovery data storage period (in days)

21
Auto-registration data storage period (in days)

1

Data storage period (in days)

7
trends
Data storage period (in days)

0

[dfisicaro]

Thanks.
I'll compare mine to your ones.

Appreciate the suggestions.

[centenor]

i have mariadb also
housekeeping should help you - i can copy my setting:
go to Administration -> General-> scrool down menu choose housekeeping

Trigger data storage period (in days)

3
Internal data storage period (in days)

3
Network discovery data storage period (in days)

21
Auto-registration data storage period (in days)

1

Data storage period (in days)

7
trends
Data storage period (in days)

0

Our housekeeping is set to just about the same values except our history storage is set to 30 days since we want to keep some historical data. Could it be maybe that we have over 500 servers monitored through Zabbix so it just needs more space? There is no real standard for how much storage to have dependant on the size of the environment so i'm not sure what the best number is. Currently we have 300GB allocated to it and already gone through all that.

One question for those whose housekeeping seems to be working... does it track in the logs that things are being deleted? I don't see anything in the logs for housekeeping, but every hour the housekeeper process runs for about 10-15+ minutes.

[centenor]

Another possible issue that we may running into is that we have around 533 hosts in Zabbix. Our environment is mostly Windows so there is a lot of information gathered from those. Because of that we are at around 64,000 items. Would it be the high number of items that is causing our Zabbix Database to keep growing? I only have the history set to 30 days and autovacuum is enabled on Postgresql. The database is currently set at 380GB and it's close to getting past that as well so i'm not too sure what else to do. I don't want to just keep increasing the size, because I don't want to run into a situation where we're allocating a 1TB just for zabbix...

[Linwood]

I put some comments here to what is either your question, or one just like it; to keep from repeating:

https://serverfault.com/questions/86...gresql-backend