Database : Cleaning problem

[tof233]

Hello,
I have a problem with the database. It's alway groing more and more (184Go), moreover the history table (95Go).
I know it hardly depends on the "Keep history" parameter but this one never exceeds 7 days. So normally it should be stabalized after 7 days.

Moreover by using the formula with my 33319 items :
days*(items/refresh rate)*24*3600*bytes -> 7*(33319/30)*24*3600*50=33 585.552 MB.

By looking in the history table, I have some items values from "Tue, 05 Jul 2011 09:20:00 GMT" and I can't access them using http://zbx.cirb.lan/items.php?form=u...ac04d817c3c16f : ERROR: No permissions !
I'm thinking of a cleaning problem in the database but don't now how to fix it.

[Inc0]

Hello,

I'm sure there are some good procedures but, have you tried to dump and restore the DB? Have you tried to optimize tables?

[tof233]

The problem with restoring and optimizing the database is that it would mean a long unavailabilty for Zabbix (in production environement).

[cesarsj]

What is your database server? I optimized in a production environment with MariaDB 10.0.38 without noticing locks.

[Inc0]

I see and you are right.

I hope someone will help us: atm I'm optimizing tables during the week-end but, as you can imagine, it's not a solution and I would like to have a real solution to the problem.

[tof233]

I increased MaxHousekeeperDelete and now my database size is stable.
It's no more growing (but also not decreasing).
I guess I would have to delete by myself the old records.
But I'm quite affraid this would overload the database process.

### Option: HousekeepingFrequency
#   How often Zabbix will perform housekeeping procedure (in hours).
#   Housekeeping is removing unnecessary information from history, alert, and alarms tables.
#
# Mandatory: no
# Range: 1-24
# Default:
# HousekeepingFrequency=12
HousekeepingFrequency=12


### Option: MaxHousekeeperDelete
#   The table "housekeeper" contains "tasks" for housekeeping procedure in the format:
#   [housekeeperid], [tablename], [field], [value].
#   No more than 'MaxHousekeeperDelete' rows (corresponding to [tablename], [field], [value])
#   will be deleted per one task in one housekeeping cycle.
#   SQLite3 does not use this parameter, deletes all corresponding rows without a limit.
#   If set to 0 then no limit is used at all. In this case you must know what you are doing!
#
# Mandatory: no
# Range: 0-1048576
# Default:
# MaxHousekeeperDelete=500
MaxHousekeeperDelete=20000

[MrKen]

It is not just how long you Keep History, but also how long you Keep Trends.

However, your database will never get smaller unless you dump it. After a dump your 184Gb database could shrink to around 100 - 120Gb.

You have done the right thing by increasing MaxHousekeeperDelete, but you should check your zabbix_server.log to see how many records are being deleted each time Housekeeper runs. You may need to increase it even more. Currently my Housekeeper runs hourly and deletes almost 300,000 records every hour.

If you are concerned about downtime for your Zabbix production server, you might want to consider building a 2nd Zabbix for such times as db dumps or other unplanned outages.

Have fun.

MrKen

[tof233]

Thanks MrKen
In fact, I have a secondary Zabbix_server with Mysql Master/Slave config, but it is not enough powerful to follow database replication. So I am waiting for a new one.

I have reviewed the zabbix_server.log (DebugLevel 3) but can't see informations about Housekeeper. I Only have informations about synchronisation with Zabbix Proxies.
I tried DebugLevel 4 but get too much messages.


I think that the housekeeping only deletes recent history/trends but not the very old ones. That would explain why the database doesn't decrease but keep the same size. So I may probably try a manual delete next week (during less criticaly time).

[MrKen]

As your Housekeeper only runs every 12 hours it might be difficult to catch that one line which tells how many records have been Deleted. Try doing

cat zabbix_server.log | grep Deleted


You can manually delete the old history and trends as often as you want, but that will not decrease the size of the database. The only way to decrease the size is to do mysqldump.

MrKen

[tof233]

I may have missunderstood the HousekeepingFrequency paramater. I thought it was per hour (so 60mn/12 -> every 5mn).
I corrected it by setting HouseKeepingFrequency to 1.

Right now, no "delete" in the logs. (starting from yesterday 22:30)

Please correct me if I'm wrong, but you mean that manually deleting won't updated indexes, so they should be recreated by dumping and restoring the DB?

[MrKen]

Right now, no "delete" in the logs. (starting from yesterday 22:30)

Case sensitive! Check what I wrote above, "Delete".

Please correct me if I'm wrong, but you mean that manually deleting won't updated indexes, so they should be recreated by dumping and restoring the DB?

No, that's not what I mean.

What I mean is that deleting records from the database does not reclaim that previously used space.
I am not a DBA but I would think that to ensure your database's integrity you can 'optimize' your mysql database. For more about that see here http://www.zabbix.com/forum/showthread.php?t=17396


MrKen

[tof233]

Case sensitive! Check what I wrote above, "Delete".

Of course I did a "-i"

No, that's not what I mean.

What I mean is that deleting records from the database does not reclaim that previously used space.
I am not a DBA but I would think that to ensure your database's integrity you can 'optimize' your mysql database. For more about that see here http://www.zabbix.com/forum/showthread.php?t=17396

Optimizing is a good idea but means locking the table.
You are probably right about the way Mysql normally handle disk space. But I would like to check if it is also the case when using innodb_file_per_table (and with partitionned tables).

thank you

[jrprado]

Solved my problem by partitioning the database. Because I found that the housekeeper did not deleted all historical, especially those that I altered retention. Partition the table by historical date that suits you best. I separated the historical per month, so the months that you no longer want to keep you just drop that partition, this task does not take one minute to be made by mysql and it would release the space immediately.

The gain is very large, whereas if you were to delete the data in database for delete would take too long and would use a lot of resources and would not release disk space as I was informed only after the dump and restore.

On this site it tells the best procedure: http://zabbixzone.com/zabbix/partitioning-tables/

If you need to post how I did in my environment for example.

[tof233]

Hello jrprado
Actually, I already partioned my tables by indexes, but not by dates. Partioning by dates seems to be a good solution.
I will test it when I will have my secondary server.
Thank you for your advice