OPTIMIZE tables in MySQL

Discuss your pilot or production implementation with other Zimbra admins or our engineers.
vs2015sv
Posts: 10
Joined: Wed Oct 19, 2016 8:12 pm

OPTIMIZE tables in MySQL

Postby vs2015sv » Fri Dec 16, 2016 7:12 pm

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


phoenix
Ambassador
Ambassador
Posts: 26341
Joined: Fri Sep 12, 2014 9:56 pm
Location: Liverpool, England

Re: OPTIMIZE tables in MySQL

Postby phoenix » Fri Dec 16, 2016 7:27 pm

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)?
Regards

Bill

Rspamd: A high performance spamassassin replacement

If you'd like to see this implemented in a future version of ZCS then please vote on Bugzilla entries 97706 & 108168
vs2015sv
Posts: 10
Joined: Wed Oct 19, 2016 8:12 pm

Re: OPTIMIZE tables in MySQL

Postby vs2015sv » Fri Dec 16, 2016 8:20 pm

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.
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 482
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: 8.7.11_P14 RHEL6 Network Edition
Contact:

Re: OPTIMIZE tables in MySQL

Postby JDunphy » Fri Dec 16, 2016 10:59 pm

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. :-)
vs2015sv
Posts: 10
Joined: Wed Oct 19, 2016 8:12 pm

Re: OPTIMIZE tables in MySQL

Postby vs2015sv » Tue Dec 20, 2016 8:59 pm

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.

Return to “Administrators”

Who is online

Users browsing this forum: No registered users and 13 guests