Page 1 of 1

OPTIMIZE tables in MySQL

Posted: Fri Dec 16, 2016 7:12 pm
by vs2015sv
I am looking for some assistance with running the OPTIMIZE command on our MySQL database.
We are running Zimbra 8.7.0GA

We have deleted a lot of accounts and email's throughout our zimbra database, but the disk space is staying the same.
This was done by clearing specific folders and deleting the email's that were contained in those folders (sent, trash).

Does anyone have any information or links that you could provide me with that will assist in freeing up disk space that is being held in the MySQL database?

MySQL Ver 15.1 Distrib 10.1.14-MariaDB, for Linux (x86_64) using readline 5.1


Thanks

Re: OPTIMIZE tables in MySQL

Posted: Fri Dec 16, 2016 7:27 pm
by phoenix
Your question doesn't really make sense to me. The space occupied by the mail for the accounts that you've deleted doesn't reside in the MySQL database, why would you want to do anything to the MySQL database (I'd suggest you leave it alone)?

Re: OPTIMIZE tables in MySQL

Posted: Fri Dec 16, 2016 8:20 pm
by vs2015sv
I was following this tutorial - https://wiki.zimbra.com/wiki/DB_not_releasing_disk_space_after_deleting_data
We were getting alerts for disk space being at 85%.
We cleaned up several email's which brought the user quota down (deleted over 5GB of email's).
When you look at the disk space on the server, it does not show the 5GB being added back to the disk as free space.

Re: OPTIMIZE tables in MySQL

Posted: Fri Dec 16, 2016 10:59 pm
by JDunphy
On our servers, we don't see much of our storage costs associated with the db.

Given your question, I did it at the disk level to see if we had the problem you mentioned.

Code: Select all

cd /opt/zimbra/db/data/
find . -size +100M -exec ls -lh {} \;

For instance one of the largest accounts is about 50GB of storage but the meta data associated is only 170MB from the database.

Sometimes that 85% is wrong for your environment so adjusting it might make more sense depending on disk size and data patterns.

Code: Select all

zmlocalconfig |grep disk
zmlocalconfig -e zmdisklog_warn_threshold=90
zmlocalconfig -e zmdisklog_critical_threshold=95
zmstatctl restart

Short term, that should help reduce the number of scary messages ending up in your mailbox while you are resolving the problem.

Interesting link about freeing up disk space via optimize. Zimbra has a crontab entry that runs /opt/zimbra/libexec/zmdbintegrityreport every week and it does appear to have an optimize option.
It appears to just call mysqlcheck from a casual look. That might be a good start to study... I know in the past with some of our webservers, I have done: mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

I would be really nervous about running this on a zimbra production server unless I was forced to. :-)

Re: OPTIMIZE tables in MySQL

Posted: Tue Dec 20, 2016 8:59 pm
by vs2015sv
I also noticed that in zimbra 8.7.0_GA is not following the email retention that gets sepcified in the config.

zimbraDumpsterEnabled: TRUE
zimbraDumpsterPurgeEnabled: TRUE
zimbraDumpsterUserVisibleAge: 30d
zimbraMailDumpsterLifetime: 21d


I am finding users who have emails in the trash folder > recover deleted items from many years ago. These items are not getting removed.