Even if houseekeper is enabled, won't the disk space used decrease? [MySQL-InnoDB]

[cesarsj]

Do I need to execute the optimize command? Do you guys do that? If so, how often?

https://dev.mysql.com/doc/refman/5.6...ize-table.html

[gofree]

how many hosts, items, average interaval for collection, setup of the db ( is it partitioned ? ), housekeeper setting .... you need to provide more info I guess or contact zabbix sales ( recently they started offering also support for DB realted issues )

iI had this problem with default setup of mysql db - ibdata was still big event when the hoisotry was removed - basically with big environemnt you have to do partitioning otherwise it will enf up badly

BTW in my opininon youre on the way to hell 10years history, is your DB at least partitioned ? if not your DB file will grow and grow and grow ( ibdata db file ) and youll soon run out of space, memory, zabbix will become slow, frontend unusable, no possibility to maintain DB, upgrades of zabbix will take long - update of DB schema >>>>> everybody will hate it

Some reading about history and trends - usually I go for 1month history and 1year trends. If youd like to have 10years of history maybe consider elasticsearch export.

P.S. I had couple of funny bosses too

https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/372535-ibdata1-large-file-size

[cesarsj]

Number of hosts: 862
Number of items: 8657
Required server performance, new values per second: 81.1
The database is not partitioned.
Enabled internal housekeeping for Events and alerts (10y), Services (10y), Audit (10y), User Sessions(10y), History and Trends (individual data storage period) .
We almost never see a queue.

File 'ibdata1' is small, have 204M; big is folder /var/lib/mysql/zabbix, wich have 238G.

BIggest tables of Zabbix, today, have:
1.5G trends.ibd
3.9G trends_uint.ibd
8.1G history_text.ibd
89G history_uint.ibd
134G history.ibd


My concern is that the disk where it is / var / lib is 51% busy, that is 238G / 493G, and the bank is growing 1G per day. It may take a while for the disk to fill 100%, but I worry about what action to take right now.

[gofree]

what is the value of indivudal data storage period for history and trends ( that is where your growth is comming from mainly)

i fyou have log history period and trend perisod ( especially if youre collecting text values, logs, snmp traps ) there is nothing you cant do and your db will grow and grow and grow .....unless it hits the housekeepr time and it will try to delete those things ( but if there will be to many items , history, etc it will not be able to keep with the speed of growth - thats why people are advised to use partitioning - than you just drop partitions older than 1 month for example - you dont need to do it via selects in DB - its faster and more effective than housekeeper )

also a thing to consider is interval of your items - if you have something like 1m or 30s that of course its growing really fast - optimize interval times ( 1m minute only where its absolutelly necessary , 5 min, 15, min, 1 hr, 12hr, 24hr )


try to assume your DB needs based od the documentation - Database size part

https://www.zabbix.com/documentation/4.0/manual/installation/requirements

[cesarsj]

My doubt still remains: the housekeeper is up and running. From time to time, I see that you have deleted 37000 history / trends. But then, do these removals free up disk space? Or do I have to run optimize (from MySQL) or dump / reimport to get the correct data?

What I mean is, from what I read, the Mysql database with Innodb tables, just like when, you want to free up disk space, but didn't empty the trash. The contents in the trash still occupy the disk. (it's a comparison). I want to know if this comparison is correct or not, because I may have read something and misunderstood.

[kloczek]

MySQL is not able to srink innodb files after delete some rows.
That is normal and has nothing to do with Zabbix.
if you want to keep minimum disk spaceused by MySQL stop housekeeper and start using paritioned hisory and trends tables.

[gofree]

Ill paste it again https://www.google.com/search?client...ql+file+zabbix

and yes youre right with the trash - but this has nothing to do with zabbix itself - its kinda how the default setup of the db youre using works - recommendation is go with partitioning ( among other benefits - speed, maintanability ) youll gain your free space when partitions will be dropeed not like with ibdata

simply said - your db setup is not fullfilling your needs