clean up Mysql database

[snmohanram]

Hello Team,

Could someone please share me steps how to clean up databases in Mysql. It looks Iam running out of space on my disk.I just want to maintain only 90 days data.Iam running zabbix version 2.4.6.Please share me steps to achieve my goal.

Thanks
Mohan

[ingus.vilnis]

Hi Mohan,

One of the best solutions for you could be partitioning for MySQL tables if you are using MySQL 5.5 or 5.6.

http://zabbix.org/wiki/Docs/howto/mysql_partitioning

Please read the manual very carefully before you do anything. But that will be a nice and flexible way how you will be able to manage your data in the future.

Best Regards,
Ingus

[snmohanram]

Hello Ingus,

Thankyou for your reply. Iam not good in Mysql, would you be able to send me steps to achieve this. My ultimate goal is to keep only last 90 days data. If there is an option to delete data before 90 days, that would be simple for me to take it forward.As database grows i will delete data as per my requirement.

Appreciate your help.

Thanks
Mohan

[LenR]

How big is your environment? Do you have the internal housekeeping active? (Under Admin -> General -> Housekeeping)

For small/medium environments, the internal housekeeping should work. The only problem with housekeeping is when you grow to the point where housekeeping can't keep up.

[ingus.vilnis]

Hello Mohan,

All steps required for this task are in the article I gave you earlier so read it all the way and understand how things happen there. Also be careful to choose whether you go with the stored procedures or use external script for partitioning.

If you are using the external script then you can configure the amount of days to keep the history in the script file itself.

Don't forget to disable Housekeeper for History and Trends tables if you go for partitioning.


LenR, housekeeping should indeed work for small to medium setups but there are some issues. See, Mohan originally said he is running out of disk space. Not sure how bad it is in this case but if he wanted to gain more free space by just running housekeeper, he will indeed delete data from DB but the disk usage will remain the same. It is because MySQL InnoDB uses space on disk when new data has to be added but it keeps the reserved space even if the data has been deleted. To get more space back you then need to backup certain tables or even whole database, drop the DB, create it again and restore from backup. Then the space will be used as much as there are real data.

Best Regards,
Ingus

[kloczek]

Hello Team,

Could someone please share me steps how to clean up databases in Mysql. It looks Iam running out of space on my disk.I just want to maintain only 90 days data.Iam running zabbix version 2.4.6.Please share me steps to achieve my goal.

Thanks
Mohan

On top of this forum you have sticky subject "Mysql Database Grow | How Optimize parameters"