disk space consumption

[jvgenderen]

hi ..
we have zabbix server 3.0 running without any problems . but over the last 14days the disk space has decreased almost 5Gb !!! the logfile settings are

LogFileSize=1
DebugLevel=0

the rest is default ..

i have deleted a .old logfile this increased the hdd size about 500Mb.

are there any "hidden" logfiles or setting i can change or remove ???..


grt Joost ..

[DmitryL]

Hello jvgenderen!

Could you please execute this querys and show me result?

SELECT table_name, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.tables WHERE table_schema = "zabbix";

select history as "Keep history", trends as "Keep trends", delay as "Update interval", count(history) as "Count" from items group by history, trends, delay; select history as "Keep history", count(history) as "Count" from items group by history; select trends as "Keep trends", count(trends) as "Count" from items group by trends; select delay as "Update interval", count(delay) as "Count" from items group by delay;

Do you use housekeeper? How often does it runs?
You can check it in zabbix_server.conf
HousekeepingFrequency=
Default: 1h


Best regards,
Dmitry

[jvgenderen]

Hi Dmirty

i am sorry to say ,, how do i execute these commands ??? ;-) #newbie

i have the housekeeper in the GUI now turned off ..

the housekeeper setting in the zabbix_server.config

# HousekeepingFrequency=1
everything about the housekeeper is still default.


grt Joost ..

[DmitryL]

Are you using table partitioning?
If not - housekeeping shouldn't be turned off.

To execute this commands, just type in console

mysql -u<db_login> -p
use <zabbix_db_name>;
And then just copy paste and run

[jvgenderen]

SELECT table_name, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.tables WHERE table_schema = "zabbix";

gives this output :

+----------------------------+------------+-------------+--------------+------------+
| table_name | table_rows | data_length | index_length | Size in MB |
+----------------------------+------------+-------------+--------------+------------+
| acknowledges | 15 | 16384 | 49152 | 0.06 |
| actions | 11 | 16384 | 32768 | 0.05 |
| alerts | 366 | 196608 | 98304 | 0.28 |
| application_discovery | 0 | 16384 | 32768 | 0.05 |
| application_prototype | 0 | 16384 | 32768 | 0.05 |
| application_template | 30 | 16384 | 32768 | 0.05 |
| applications | 174 | 16384 | 16384 | 0.03 |
| auditlog | 1725 | 229376 | 147456 | 0.36 |
| auditlog_details | 161 | 16384 | 16384 | 0.03 |
| autoreg_host | 2 | 16384 | 16384 | 0.03 |
| conditions | 13 | 16384 | 16384 | 0.03 |
| config | 1 | 16384 | 32768 | 0.05 |
| dbversion | 1 | 16384 | 0 | 0.02 |
| dchecks | 0 | 16384 | 16384 | 0.03 |
| dhosts | 0 | 16384 | 16384 | 0.03 |
| drules | 0 | 16384 | 32768 | 0.05 |
| dservices | 0 | 16384 | 32768 | 0.05 |
| escalations | 3 | 16384 | 16384 | 0.03 |
| events | 82944 | 5783552 | 6324224 | 11.55 |
| expressions | 4 | 16384 | 16384 | 0.03 |
| functions | 91 | 49152 | 32768 | 0.08 |
| globalmacro | 1 | 16384 | 16384 | 0.03 |
| globalvars | 0 | 16384 | 0 | 0.02 |
| graph_discovery | 2 | 16384 | 16384 | 0.03 |
| graph_theme | 2 | 16384 | 16384 | 0.03 |
| graphs | 390 | 114688 | 98304 | 0.20 |
| graphs_items | 1403 | 147456 | 98304 | 0.23 |
| group_discovery | 0 | 16384 | 16384 | 0.03 |
| group_prototype | 5 | 16384 | 49152 | 0.06 |
| groups | 42 | 16384 | 16384 | 0.03 |
| history | 208254315 | 11542855680 | 5731876864 | 16474.47 |
| history_log | 10 | 16384 | 32768 | 0.05 |
| history_str | 29847 | 1556480 | 1556480 | 2.97 |
| history_text | 920357 | 291323904 | 44138496 | 319.92 |
| history_uint | 1708464 | 104988672 | 43253760 | 141.38 |
| host_discovery | 2 | 16384 | 32768 | 0.05 |
| host_inventory | 56 | 16384 | 0 | 0.02 |
| hostmacro | 0 | 16384 | 16384 | 0.03 |
| hosts | 182 | 65536 | 98304 | 0.16 |
| hosts_groups | 295 | 16384 | 32768 | 0.05 |
| hosts_templates | 22 | 16384 | 32768 | 0.05 |
| housekeeper | 0 | 16384 | 0 | 0.02 |
| httpstep | 0 | 16384 | 16384 | 0.03 |
| httpstepitem | 0 | 16384 | 32768 | 0.05 |
| httptest | 0 | 16384 | 65536 | 0.08 |
| httptestitem | 0 | 16384 | 32768 | 0.05 |
| icon_map | 0 | 16384 | 32768 | 0.05 |
| icon_mapping | 0 | 16384 | 32768 | 0.05 |
| ids | 40 | 16384 | 0 | 0.02 |
| images | 246 | 2637824 | 16384 | 2.53 |
| interface | 162 | 16384 | 32768 | 0.05 |
| interface_discovery | 0 | 16384 | 16384 | 0.03 |
| item_application_prototype | 0 | 16384 | 32768 | 0.05 |
| item_condition | 20 | 16384 | 16384 | 0.03 |
| item_discovery | 151 | 16384 | 32768 | 0.05 |
| items | 1986 | 1572864 | 442368 | 1.92 |
| items_applications | 656 | 98304 | 98304 | 0.19 |
| maintenances | 0 | 16384 | 32768 | 0.05 |
| maintenances_groups | 0 | 16384 | 32768 | 0.05 |
| maintenances_hosts | 0 | 16384 | 32768 | 0.05 |
| maintenances_windows | 0 | 16384 | 32768 | 0.05 |
| mappings | 143 | 16384 | 16384 | 0.03 |
| media | 7 | 16384 | 32768 | 0.05 |
| media_type | 1 | 16384 | 16384 | 0.03 |
| opcommand | 0 | 16384 | 16384 | 0.03 |
| opcommand_grp | 0 | 16384 | 32768 | 0.05 |
| opcommand_hst | 0 | 16384 | 32768 | 0.05 |
| opconditions | 0 | 16384 | 16384 | 0.03 |
| operations | 12 | 16384 | 16384 | 0.03 |
| opgroup | 1 | 16384 | 32768 | 0.05 |
| opinventory | 0 | 16384 | 0 | 0.02 |
| opmessage | 10 | 16384 | 16384 | 0.03 |
| opmessage_grp | 4 | 16384 | 32768 | 0.05 |
| opmessage_usr | 7 | 16384 | 32768 | 0.05 |
| optemplate | 1 | 16384 | 32768 | 0.05 |
| profiles | 1732 | 180224 | 196608 | 0.36 |
| proxy_autoreg_host | 0 | 16384 | 16384 | 0.03 |
| proxy_dhistory | 0 | 16384 | 16384 | 0.03 |
| proxy_history | 0 | 16384 | 16384 | 0.03 |
| regexps | 3 | 16384 | 16384 | 0.03 |
| rights | 35 | 16384 | 32768 | 0.05 |
| screen_user | 0 | 16384 | 32768 | 0.05 |
| screen_usrgrp | 0 | 16384 | 32768 | 0.05 |
| screens | 12 | 16384 | 32768 | 0.05 |
| screens_items | 46 | 16384 | 16384 | 0.03 |
| scripts | 3 | 16384 | 49152 | 0.06 |
| service_alarms | 0 | 16384 | 32768 | 0.05 |
| services | 0 | 16384 | 16384 | 0.03 |
| services_links | 0 | 16384 | 32768 | 0.05 |
| services_times | 0 | 16384 | 16384 | 0.03 |
| sessions | 35 | 16384 | 16384 | 0.03 |
| slides | 0 | 16384 | 32768 | 0.05 |
| slideshow_user | 0 | 16384 | 32768 | 0.05 |
| slideshow_usrgrp | 0 | 16384 | 32768 | 0.05 |
| slideshows | 0 | 16384 | 32768 | 0.05 |
| sysmap_element_url | 0 | 16384 | 16384 | 0.03 |
| sysmap_url | 0 | 16384 | 16384 | 0.03 |
| sysmap_user | 0 | 16384 | 32768 | 0.05 |
| sysmap_usrgrp | 0 | 16384 | 32768 | 0.05 |
| sysmaps | 9 | 16384 | 65536 | 0.08 |
| sysmaps_elements | 84 | 16384 | 81920 | 0.09 |
| sysmaps_link_triggers | 0 | 16384 | 32768 | 0.05 |
| sysmaps_links | 66 | 16384 | 49152 | 0.06 |
| timeperiods | 0 | 16384 | 0 | 0.02 |
| trends | 1117806 | 121192448 | 0 | 115.58 |
| trends_uint | 29911 | 3391488 | 0 | 3.23 |
| trigger_depends | 43 | 16384 | 32768 | 0.05 |
| trigger_discovery | 2 | 16384 | 16384 | 0.03 |
| triggers | 332 | 81920 | 49152 | 0.13 |
| users | 13 | 16384 | 16384 | 0.03 |
| users_groups | 15 | 16384 | 32768 | 0.05 |
| usrgrp | 7 | 16384 | 16384 | 0.03 |
| valuemaps | 17 | 16384 | 16384 | 0.03

[DmitryL]

Well, as you can see your history table is 16gb big.
How big History storage period you use on your templates/items (I would recommend 7 days)?

Since your housekeer is turned off, history/trends table never gets cleared and it just keeps growing.

My suggestion:
1) Turn back on housekeeper in your frontend.
2) Change History storage period to 7 days on all templates/items
Or you can just override them all by going to Administration -> General -> Housekeeping -> check Override item history period, and set it to 7.
3) From your console run zabbix_server -R housekeeper_execute
4) In frontend -> Monitoring -> Graphs -> Zabbix internal process busy You will see that housekeeper is working. After its done, you can execute my sql query and check history table size again.

[jvgenderen]

hi dmitry.


#1 done
#2 done
#3 done with zabbx-server -R housekeeper_execute
answer : zabbix-server [18245] : command sent successfully


how long does the cleanup normaly takes???


it is stil "big" after 15min

grt Joost

[DmitryL]

Hi again!

By default in zabbix_server.conf

MaxHousekeeperDelete=5000

That means housekeeper will delete 5000 lines from each item history in one hour.
You can change this value in range 0-1000000.

Another session by default will start after 1 hour.

HousekeepingFrequency=1

Currently you have 208254315 rows in history table;
You need to delete 208254315 - rows for last 7 days.

From your first post i assume you disabled housekeeper 14 days ago.
If i am right, history older than 7 days should be cleared in about 10 housekeeper sessions.

However, housekeeper uses DELETE function that doesnt free disk space.
That means your DB still will be same size, but it wont get worse now.

After this is done, we will need to set table partitioning to reclaim this space.

[jvgenderen]

Hi Dmitry.
i haven reset the values you mentioned ..

ik will see what happends ..

ik wil keep you posted ..

many thanks so far .. it has been a great help sofar .. :-)


grt Joost .

[DmitryL]

You're welcome!

Just keep in mind that this process wont make DB smaller and you wont get extra free space on server. By doing housekeeping we are preventing DB from further growing.

Probably tomorrow we could do step by step table partitioning, that will fix your issue with free space

P.S. You should set DebugLevel= back to 3 in zabbix_server.conf and keep it like that.
Otherwise you wont see any zabbix server logs, and it will make debugging very complicated.

[kloczek]

hi ..
we have zabbix server 3.0 running without any problems . but over the last 14days the disk space has decreased almost 5Gb !!! the logfile settings are (..)

These are not logs but database content.
On top of Troubleshooting and Problems forum you have sticky subject " sticky thread "Mysql Database Grow | How Optimize parameters"

https://www.zabbix.com/forum/showthread.php?t=9925

[jvgenderen]

hi Dmirty..

the housekeeper freq = 1h
maxhouskeeperdelete = 1000

logsize =10
loglevel = 3


this is the output of the database as this moment.

+----------------------------+------------+-------------+--------------+------------+
| table_name | table_rows | data_length | index_length | Size in MB |
+----------------------------+------------+-------------+--------------+------------+
| acknowledges | 9 | 16384 | 49152 | 0.06 |
| actions | 11 | 16384 | 32768 | 0.05 |
| alerts | 610 | 196608 | 98304 | 0.28 |
| application_discovery | 0 | 16384 | 32768 | 0.05 |
| application_prototype | 0 | 16384 | 32768 | 0.05 |
| application_template | 30 | 16384 | 32768 | 0.05 |
| applications | 174 | 16384 | 16384 | 0.03 |
| auditlog | 1240 | 229376 | 147456 | 0.36 |
| auditlog_details | 162 | 16384 | 16384 | 0.03 |
| autoreg_host | 2 | 16384 | 16384 | 0.03 |
| conditions | 13 | 16384 | 16384 | 0.03 |
| config | 1 | 16384 | 32768 | 0.05 |
| dbversion | 1 | 16384 | 0 | 0.02 |
| dchecks | 0 | 16384 | 16384 | 0.03 |
| dhosts | 0 | 16384 | 16384 | 0.03 |
| drules | 0 | 16384 | 32768 | 0.05 |
| dservices | 0 | 16384 | 32768 | 0.05 |
| escalations | 3 | 16384 | 16384 | 0.03 |
| events | 82164 | 5783552 | 6324224 | 11.55 |
| expressions | 4 | 16384 | 16384 | 0.03 |
| functions | 91 | 49152 | 32768 | 0.08 |
| globalmacro | 1 | 16384 | 16384 | 0.03 |
| globalvars | 0 | 16384 | 0 | 0.02 |
| graph_discovery | 2 | 16384 | 16384 | 0.03 |
| graph_theme | 2 | 16384 | 16384 | 0.03 |
| graphs | 455 | 114688 | 98304 | 0.20 |
| graphs_items | 1403 | 147456 | 98304 | 0.23 |
| group_discovery | 0 | 16384 | 16384 | 0.03 |
| group_prototype | 5 | 16384 | 49152 | 0.06 |
| groups | 42 | 16384 | 16384 | 0.03 |
| history | 186135607 | 11601461248 | 5643714560 | 16446.28 |
| history_log | 10 | 16384 | 32768 | 0.05 |
| history_str | 747 | 1064960 | 16384 | 1.03 |
| history_text | 734518 | 244613120 | 41009152 | 272.39 |
| history_uint | 1709440 | 106037248 | 43106304 | 142.23 |
| host_discovery | 2 | 16384 | 32768 | 0.05 |
| host_inventory | 56 | 16384 | 0 | 0.02 |
| hostmacro | 0 | 16384 | 16384 | 0.03 |
| hosts | 189 | 65536 | 98304 | 0.16 |
| hosts_groups | 295 | 16384 | 32768 | 0.05 |
| hosts_templates | 22 | 16384 | 32768 | 0.05 |
| housekeeper | 0 | 16384 | 0 | 0.02 |
| httpstep | 0 | 16384 | 16384 | 0.03 |
| httpstepitem | 0 | 16384 | 32768 | 0.05 |
| httptest | 0 | 16384 | 65536 | 0.08 |
| httptestitem | 0 | 16384 | 32768 | 0.05 |
| icon_map | 0 | 16384 | 32768 | 0.05 |
| icon_mapping | 0 | 16384 | 32768 | 0.05 |
| ids | 40 | 16384 | 0 | 0.02 |
| images | 202 | 2637824 | 16384 | 2.53 |
| interface | 162 | 16384 | 32768 | 0.05 |
| interface_discovery | 0 | 16384 | 16384 | 0.03 |
| item_application_prototype | 0 | 16384 | 32768 | 0.05 |
| item_condition | 20 | 16384 | 16384 | 0.03 |
| item_discovery | 151 | 16384 | 32768 | 0.05 |
| items | 2086 | 1572864 | 442368 | 1.92 |
| items_applications | 966 | 98304 | 98304 | 0.19 |
| maintenances | 0 | 16384 | 32768 | 0.05 |
| maintenances_groups | 0 | 16384 | 32768 | 0.05 |
| maintenances_hosts | 0 | 16384 | 32768 | 0.05 |
| maintenances_windows | 0 | 16384 | 32768 | 0.05 |
| mappings | 143 | 16384 | 16384 | 0.03 |
| media | 7 | 16384 | 32768 | 0.05 |
| media_type | 1 | 16384 | 16384 | 0.03 |
| opcommand | 0 | 16384 | 16384 | 0.03 |
| opcommand_grp | 0 | 16384 | 32768 | 0.05 |
| opcommand_hst | 0 | 16384 | 32768 | 0.05 |
| opconditions | 0 | 16384 | 16384 | 0.03 |
| operations | 12 | 16384 | 16384 | 0.03 |
| opgroup | 1 | 16384 | 32768 | 0.05 |
| opinventory | 0 | 16384 | 0 | 0.02 |
| opmessage | 10 | 16384 | 16384 | 0.03 |
| opmessage_grp | 4 | 16384 | 32768 | 0.05 |
| opmessage_usr | 7 | 16384 | 32768 | 0.05 |
| optemplate | 1 | 16384 | 32768 | 0.05 |
| profiles | 1319 | 196608 | 212992 | 0.39 |
| proxy_autoreg_host | 0 | 16384 | 16384 | 0.03 |
| proxy_dhistory | 0 | 16384 | 16384 | 0.03 |
| proxy_history | 0 | 16384 | 16384 | 0.03 |
| regexps | 3 | 16384 | 16384 | 0.03 |
| rights | 35 | 16384 | 32768 | 0.05 |
| screen_user | 0 | 16384 | 32768 | 0.05 |
| screen_usrgrp | 0 | 16384 | 32768 | 0.05 |
| screens | 12 | 16384 | 32768 | 0.05 |
| screens_items | 46 | 16384 | 16384 | 0.03 |
| scripts | 3 | 16384 | 49152 | 0.06 |
| service_alarms | 0 | 16384 | 32768 | 0.05 |
| services | 0 | 16384 | 16384 | 0.03 |
| services_links | 0 | 16384 | 32768 | 0.05 |
| services_times | 0 | 16384 | 16384 | 0.03 |
| sessions | 3 | 16384 | 16384 | 0.03 |
| slides | 0 | 16384 | 32768 | 0.05 |
| slideshow_user | 0 | 16384 | 32768 | 0.05 |
| slideshow_usrgrp | 0 | 16384 | 32768 | 0.05 |
| slideshows | 0 | 16384 | 32768 | 0.05 |
| sysmap_element_url | 0 | 16384 | 16384 | 0.03 |
| sysmap_url | 0 | 16384 | 16384 | 0.03 |
| sysmap_user | 0 | 16384 | 32768 | 0.05 |
| sysmap_usrgrp | 0 | 16384 | 32768 | 0.05 |
| sysmaps | 9 | 16384 | 65536 | 0.08 |
| sysmaps_elements | 84 | 16384 | 81920 | 0.09 |
| sysmaps_link_triggers | 0 | 16384 | 32768 | 0.05 |
| sysmaps_links | 66 | 16384 | 49152 | 0.06 |
| timeperiods | 0 | 16384 | 0 | 0.02 |
| trends | 1232755 | 116965376 | 0 | 111.55 |
| trends_uint | 24922 | 2293760 | 0 | 2.19 |
| trigger_depends | 43 | 16384 | 32768 | 0.05 |
| trigger_discovery | 2 | 16384 | 16384 | 0.03 |
| triggers | 341 | 81920 | 49152 | 0.13 |
| users | 13 | 16384 | 16384 | 0.03 |
| users_groups | 15 | 16384 | 32768 | 0.05 |
| usrgrp | 7 | 16384 | 16384 | 0.03 |
| valuemaps | 17 | 16384 | 16384 | 0.03 |
+----------------------------+------------+-------------+--------------+------------+

[DmitryL]

Hello jvgenderen!

Thank you for response!
So i assume that housekeeper did his job with data older than 7 days.
That mean, that your table now is about 1/3 with data and 2/3 with free space.
This space will be used when actuall nvps will increase.

Take in mind, that your DB actually is very small.

I know that i mentioned table partitioning yesterday.
But it is more likely used in BIG zabbix environment and is quite complicated thing.

You can read about it here.
https://dev.mysql.com/doc/refman/5.7...-overview.html

Best regards,
Dmitry

[jvgenderen]

thanks Dmitry ..

i will read the url you mentioned ..

i learned a lot ;-) thanks for your time and tips ..

with kind regards Joost ..

[wreckeur]

Hi all,
I am having this exact same issue and I went through and did all of the steps above, but my MySQL DB values are not changing. In fact, after running the above my disk space consumed increased so I have less space now than when I started. I am happy to provide any information needed to help sort this out. Thank you