Archived Emails load very slowly

[nmuleski]

Sugar Version: 6.2.2
Sugar Edition: Community
Category: Emails/GrinMark
Operating System: Ubuntu Server w/ Bitnami stack on vmWare ESX VM
Database: MySQL
Web Server: Apache

We recently started using GrinMark to archive emails from Outlook to Sugar. We've noticed that while most emails load quickly enough (2-5 seconds), some larger emails can take much, much longer. Longer emails usually take around 100 seconds to load in Sugar.These emails are usually larger, back and forth conversations composed in HTML. There is no difference in the amount of time it takes to archive the emails, its only in viewing them in Sugar.

It also doesn't seem to be affected by attachments. An short email with 10 attachments will load as quickly as short email with no attachments. A long email will take forever no matter what.

I have tried increasing the resources, both CPU and RAM (it's a VM), and I have increased the PHP memory limit to 512MB. Neither resulted in any noticeable difference.

Anybody else have this issue or know of a solution? Any suggestions are welcome!

[nmuleski]

Just an update. I've noticed that when we convert the email to plain text before archiving it makes a noticeable difference. Usually cuts the load time by 75%.

Does anybody know of a way to have Sugar only display or archive in plain text?

[plehry]

I'm having the same issue as well. Mine popped up after migrating the installation from MySQL and Apache to SQL Server 2008 R2 and IIS 7.5.

Any help would be greatly appreciated.

[plehry]

I think I found a possible solution.

When you open up the email message, look at the bottom of the message to see if all the sub-category sections are open. If they are, then that might be where the problem is. See, Sugar executes a query for every section that is open to get the results and display them. I closed all those sections and watched the execution time for showing the email message drop from 25 seconds to 0.83 seconds.

Give that a shot. There's no point in keeping those sections open if there is no data there. Just slows the entire process down.

[csinclair]

I have a very similar issue. However, mine never seem to load and it potentially brings the server to a standstill.

I also upgraded my SugarCRM Professional instance from 6.2.3 to 6.3.0 in hope to fix this issue to no avail.

Unfortunately I don't know of any SugarCRM settings to enable text-only mode (would be nice!).

I believe the fault resides in function clean_xss() found in utils.php. It is called in file ./modules/Emails/Email.php, function safeText().

Looks like it's hanging on the following code:

$jsMatches = xss_check_pattern($attribute_regex, $str);

I assume something isn't happy with the regex expression. Once expanded...

xss_check_pattern('#<.+(onblur|onfocus|oncontextmenu|onresize|onscroll|onunload|ondblclick|onclick|onmouseup|onmouseover|onmousedown|onmouseenter|onmouseleave|onmousemove|onload|onchange|onreset|onselect|onsubmit|onkeydown|onkeypress|onkeyup|onabort|onerror|ondragdrop)[^=>]*=[^>]*>#sim', $str);

I'm not familiar enough with regex to optimise. In utils.php there's a note saying:

// cn: bug 13079 - "on\w" matched too many non-events (cONTact, strONG, etc.)

I used "on\w" as a base of my "fix". Instead of using "on\w", I went effectively with "\Won\w".

Original code:

$attribute_regex        = "#<.+({$jsEvents})[^=>]*=[^>]*>#sim";

My replacement code:

$attribute_regex        = "#<.+[^A-Z]on[A-Z][^=>]*=[^>]*>#sim";

For a 55KB Outlook-generated email, loading still took considerable time (over 35 seconds on an idle server!) however it's better than not having it load at all.

SugarCRM may be able to use something like HTML Purifier to process and filter HTML content. It looks like that idea was suggested many years ago...

http://www.sugarcrm.com/forums/f22/s...ourself-16522/

(however, with my very limited testing, HTML Purifier stripped a lot of the MS Office code)

Or apply the XSS filters when not dealing with already archived emails (XSS should be removed prior to insertion into the database; if XSS filter updates are made, apply it once again during the software upgrade process to all currently stored emails).

Any suggestions to better optimise the regex or make different code changes, I'd be happy to hear!

@nmuleski, @plehry:
How large are the emails you're trying to load when the load times are long?

I performed the suggestion by plehry and wow, did it make a difference!!!

Before (with my code changes):

Server response time: 33.36 seconds.

After (with my code changes, all sections minimised):

Server response time: 5.47 seconds.

After (with original code, all sections minimised):

Server response time: 119.36 seconds.

You can see how bad it would perform if all tabs were expanded!!

The code definitely needs assessing. Not just the regex expression but the inner workings. I can't see any reason why the same bit of code (filtering the archived email) is run so many times. I think the email is still processed more than once with all tabs closed -- I would help but Sugar code drives me nuts; for the most part I don't understand what is going on.

[Tibovdk]

Hi,

Thank you a lot csinclair, I have found a workaround of this very annoying problem based on your statements.

I have commented into the file "modules/Emails/Email.php" line 1449. So now I have :

/var/www/modules/Emails/Email.php :
1448                 // Julian's XSS cleaner
1449                 //$potentials = clean_xss($str, false);

Since the function clean_xss is not called, it takes less than 1 second to Sugar to open an email, whereas it was taking 20 seconds to open it before !

I know that XSS protection is an important thing, but as it makes Sugar not user-friendly because of the very slow loading, I will not use anymore this feature until it is not improve.