I have a self-hosted SuiteCRM 7.11.19 with Email module monitoring several our inbound emails, with one of them having over 14GB of emails (old email box).
SuiteCRM uses a cron job to download new email every 5 minutes, and it seems to be working fine.
However, earlier today I could not longer log into my SuiteCRM, it said my session has expired.
I logged into my system over SSH and found out the disk was full (100GB) and narrowed it down to the overgrown MySQL database with the folders_rel table taking over 70GB).
71G /var/lib/mysql/suitecrm/folders_rel.ibd
How can I safely clean up the folders_rel table?
Is this happening due to having Auto-Import function enabled on the inbound email boxes?
Iāll try to figure out why there are so many records. Other people have reported that each individual picture is stored as a note / an extra record in this table. Not sure if thatās at the root of this problem though. I think the issue is with Auto-Importing every single email into the database.
Maybe there is a way to delete all the attached pictures from the database (we certainly donāt need them under SuiteCRM).
Although I donāt use inbound email in SuiteCRM, and so I am not very knowledgeable on this, from what Iāve heard in these forums itās definitely a good idea to manage the size of your IMAP inboxes in ways that hide the large amounts of email from SuiteCRM, because SuiteCRM will hit performance issues much sooner than any Gmail or Outlook.com.
If you can get a policy of removing or archiving older stuff, or using IMAP labels (folders) to keep a lot of stuff only server-side, and clearing that from SuiteCRM, it should really help.
Try to ensure that only stuff youāre currently working on, or that is relevant to what youāre working on, is stored in SuiteCRM.
Iām currently investigating this issue, and it seems the problem is not with the amount of emails (or the total size of the emailbox), but rather thereās a bug/issue that fill in folders_rel table with junk.
The issue may be related to this one:
since my log file used to fill up with the mentioned error messages.
I am not sure about the purpose of this folders_rel table, but as far as I understand it should not be nearly as large as it is. I think its purpose is to maintain a relationship (a hierarchy) of emailbox folders, so it shouldnāt ever change except for when new inbound email accounts are added to SuiteCRM and/or those accounts get new folders/labels.
It appears that SuiteCRM fails to detect the right folder and attempts to re-create it every time it saves a new email.
Maybe the code is fixed now, and has been for a while, but it had previously filled up quite a lot, due to the old bug. Maybe now it just went over a certain threshold because of new emails and became noticeable.
If you try an aggregate query grouping the entries in that row by month or year of creation, you might get insights as the when this was most problematic.
I would also look into orphaned rows on that table. By āorphanedā I mean rows that reference items that have been deleted are no longer present in SuiteCRM. These should be the ones you can safely clear.
So you would need a SELECT joining folders_rel with Emails table, something like this (untested!)
SELECT id
FROM folders_rel
JOIN emails ON emails.id = folders_rel.polymorphic_id AND folders_rel.polymorphic_module = 'Emails'
WHERE emails.deleted = 1;
Then when youāre confident you have what you need (and full BACKUPS!) you can consider turning that into a DELETEā¦
