Emails module slow

đź’¬ General Discussion
1

Hi all,

I’m using SuiteCRM 7.10.9 and I’m finding the emails module to be particularly slow. My mailbox had roughly 2500 emails in it and it took ~14 seconds to load it. I went into my mailbox on Office365 and archived up until the start of this month with only about 750 emails left. I went back to SuiteCRM and it only shaved one second off the loading time. All the other modules in SuiteCRM seem to have a snappy loading time, it’s only the Emails module that I’m having trouble with.

All of the email accounts in SuiteCRM are Office365 accounts and they all have the following inbound account settings:
Mail Server Address: outlook.office365.com
Mail Server Protocol: IMAP
Mail Server Port: 993
Use SSL: Yes
Monitored Folders: INBOX,Junk Email
Trash Folder: Deleted Items
Sent Folder: Sent Items

I know this issue has been raised around the 7.9.X versions but they have all been closed since.

Does anyone have any tips?

EDIT:
Looking at the network log, would it be possible that outlook.office365.com is the culprit?

2

You might want to run the query at the top of this post to check for any overgrown tables in the database:

https://pgorod.github.io/Database-tables-size/

If you don’t have the “Prune database” job enabled, you might be getting a large number of records with “deleted=1” which cram up your database and make it slower.

But if the delays are accessing Outlook 365 it could be their fault, or it could be the way SuiteCRM is making the query to the IMAP mail server, maybe it’s asking for too much information all at once. I remember a problem like this a few months ago, but I am convinced that it was already solved in 7.10.9…

3

I’ve attached a picture of the results of the query.

For those sizes, is it expected to take that long?

Our current CRM system (version 7.6.4) has the same data and it’s able to load the emails module in a reasonable time.

4

I would say your database would probably benefit enormously from maintenance.

Do you have the Prune database Scheduler job enabled? I’m just asking, I’m not saying to enable it - I think you should do manual maintenance first.

You could try (with proper backups first, of course) clearing records with “deleted=1”, and clearing orphaned relationships, which I would say you probably have a lot of them in there.

I can provide you a few DELETE queries for orphaned records if you need them.

5

No I don’t have the prune job enabled. Is there anyway to trigger it manually instead of it being on a schedule?

If you could provide me with the delete queries I would really appreciate that, thank you!

6

I’ve been doing some experimenting with the database, and there seems to be something weird going on with the emails_text table. I ran the following query:

SELECT * FROM emails_text WHERE deleted = 1

and it took ~27 seconds to execute (I timed it myself) despite Sequel Pro reporting it only took 700-800ms.

Looking at this issue it seems to indicate that the query executed speedily, but the actual transfer of data took the longest amount of time:

However, I decided to execute the following query as well:

SELECT email_id FROM emails_text WHERE deleted = 1

and Sequel Pro is now reporting it took ~24 seconds to execute.

I ran the above two queries on the email_addresses table which has slightly more rows than the emails_text table, and theres no hang up. I also had another look at the results from my previous comment, and I noticed that the space taken by the index is quite low for the emails_text table, despite it being one of the largest tables in the database. Could that have something to do with it? I’ve attached a picture of the structure of the emails_text table to see if anything doesn’t look right.

Would it be possible for you (or someone else) to run these queries to see if the result is similar?

My database is being hosted on AWS as a MySQL RDS instance. Is it possible that there’s some setting that is interfering?

7

If I executed the queries here they would be quick because I don’t have much data on my test systems :slight_smile:

If you have a ton of deleted=1 emails, consider deleting them from the table. But you need to understand the relationships because these also need to be deleted, otherwise you’ll be left with a bunch of orphaned records in the relationships tables.

This is an example query (a SELECT, which you can turn into a DELETE once you’re sure you want to do it) to delete orphaned relationships:

Delete orphaned records from contacts_cstm:

SELECT * 
FROM contacts_cstm ChildTable    
LEFT JOIN contacts ParentTable 
ON ChildTable.id_c = ParentTable.id
WHERE ParentTable.id IS NULL

To adapt to another pair of tables you only need to change where it says “contacts” and “contacts_cstm”, nothing else.

To turn it into a DELETE, change the first line to “DELETE ChildTable”. You do have backups, don’t you?

8

I’ve got backups, that’s not an issue. However the emails table only has ~150 rows which are marked as deleted, and out of the ~350,000 rows in that table, I don’t think pruning is going to make much of an impact.

9

I apologize, I should have checked the logs when I submitted this issue. I’ve just discovered these error messages in /var/log/apache2/error.log:

[Thu Oct 11 00:44:36.035388 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:36.225071 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:36.513987 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:36.703670 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:36.992346 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:37.181934 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:37.470283 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:37.660127 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:37.948861 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:38.141337 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:38.429817 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:38.666291 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:38.955278 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:39.144848 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:39.433445 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:39.623125 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:39.911899 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:40.125484 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:40.414320 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:40.604033 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:40.896980 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:41.086311 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:41.413640 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:41.603735 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:41.893451 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:42.085013 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:42.374002 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:42.563920 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:42.853984 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:43.045437 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:43.334480 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:43.524042 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:43.813116 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:44.005359 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:44.294777 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:44.484617 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:44.773705 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:44.963479 2018] [:error] [pid 889] PHP Notice:  Undefined index: has_attachment in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmails.php on line 584, referer: https://<my crm address>/index.php?module=Home&action=index
[Thu Oct 11 00:44:45.267333 2018] [:error] [pid 889] PHP Notice:  Undefined property: Email::$assigned_user_name in /var/www/html/suitecrm/modules/Emails/include/ListView/ListViewDataEmailsSearchOnIMap.php on line 380, referer: https://<my crm address>/index.php?module=Home&action=index

Could this be impacting the performance?

EDIT: Found this one too

[Thu Oct 11 00:54:46.390522 2018] [:error] [pid 889] PHP Warning:  Invalid argument supplied for foreach() in /var/www/html/suitecrm/modules/InboundEmail/InboundEmail.php on line 4178, referer: https://<my crm address>/index.php?
module=Emails&action=index&parentTab=Activities
10

The PHP Notices are usually safe to ignore. You might want to investigate that warning, though.

In Admin / system settings you can set your Log level to “DEBUG” and turn on “Log slow queries”. Then you can get a better idea from your suitecrm.log of what is running slow. I still suspect your database size. If it’s not “deleted=1” items, it could be orphaned related records.