MySQL database is growing massively after updating to 3.4

[al3xprey]

Hi,

I had a zabbix server 3.0 with about 1500 hosts that has been running for about 7 months. I updated to zabbix 3.4. At first, my pollers were not enough and I increased them from 15 to 150 (had more resources now). Housekeeping has never ran, although it was enabled. By default 365 days. My database grew from 20GB to about 120 in 10 days. I enabled housekeeping today but it keeps growing every minute. I tried deleting orphaned data, no difference. I can see in the logs that the housekeeper actually does delete something, but my database keeps growing. I have increased the item check times, but still no go. Any idea what might cause this? Has anyone experienced something similar? I read lots of topics here about this issue, I tried a couple of things and none of them seem to work. I'd be very happy if you could help me, give me some advice? Is mysql partitioning the thing that will save my database? Thanks!!

[kloczek]

Please read first sticky subject on the forum

https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/10176-mysql-database-grow-how-optimize-parameters

[al3xprey]

Hi! thanks for sharing an 11 year old thread. I saw that earlier, and it's not relevant to the case. I saw that I might be better off using OPTIMIZE TABLE from time to time, as housekeeping does not actually free space.

[kloczek]

Hi! thanks for sharing an 11 year old thread. I saw that earlier, and it's not relevant to the case. I saw that I might be better off using OPTIMIZE TABLE from time to time, as housekeeping does not actually free space.

Yes, zabbix is using almost the same way its DB backed from almost begging and nothing substantially changed since that moment.
You are using passive monitoring ant this is causing your pollers issue.
I'm guessing that you are not using partitioned history/trends tables and by this you have houskeeping bottleneck.
Optimising history/trends tables is pointless as generally zabbix only adds new data to those tables.
Optimisation needs full DB locking and by this it cannot be done om primary zabbix DB backend. In extreweame cases optimisations may take hours or days and in meantime you will have no new data ingestion to DB backend,
Do you have slave DB instance to do optimisation on slave and then failover to that slave as new master?
All those DB backend maintenance operations topics have been discussed many times here.

[solomonaom]

you can archive DB to new mount point and backup this volume it will be best practice

[al3xprey]

it all started afrer upgrading. the queue was absolutely filled up, had to increase pollers. the db size also started growing. we’re talking something like 2GB PER DAY. Housekeeper is running, absolutely useless. No master slave setup on mysql, because I didnt think I’d need one after upgrading to 3.4 in order to not fill up a database afrer ten days. So far I saw many discussions and NO actual fix, but MySQL partitioning.