v11.0.1 Professional is very Slow populating recipient list in Compose Email

We finally upgraded to v11.0.1 Professional On Site this past Friday and today users complain that it is taking more than 10s to populate the list of recipients from the Compose Email window when typing in a name or email address.

Is anyone else finding the same issues?

I checked the DB, Elasticsearch and the Sugar application server and all have normal loads.

There are no errors or warnings in the Sugar, PHP or Console logs.

Where would you go from here to debug this problem?

Thanks,

FrancescaS

Parents

0 Francesca Shiekh over 3 years ago

Sugar support suggested looking at include/javascript/sugar7/plugins/EmailParticipants.js to see if I could pinpoint the delay

logging on the EmailParticipants lead me to determine that the delay is in the API (modules/Emails/clients/base/api/MailApi.php)

which in turn lead to examining modules/Emails/EmailRecipientsService.php and determining the delay to be because of a giant Union query that is composed in the findEmailFromBeanIds function of the modules/Emails/EmailUI.php

The resulting query is

(SELECT users.id, users.first_name, users.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Users' module FROM users JOIN email_addr_bean_rel eabr ON (users.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (users.deleted = 0 AND eabr.primary_address = 1) AND (first_name LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT users.id, users.first_name, users.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Users' module FROM users JOIN email_addr_bean_rel eabr ON (users.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (users.deleted = 0 AND eabr.primary_address = 1) AND (last_name LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT users.id, users.first_name, users.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Users' module FROM users JOIN email_addr_bean_rel eabr ON (users.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (users.deleted = 0 AND eabr.primary_address = 1) AND (LTRIM(RTRIM(CONCAT(IFNULL(users.first_name,''),' ',IFNULL(users.last_name,'')))) LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT users.id, users.first_name, users.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Users' module FROM users JOIN email_addr_bean_rel eabr ON (users.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (users.deleted = 0 AND eabr.primary_address = 1) AND (email_address LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT contacts.id, contacts.first_name, contacts.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Contacts' module FROM contacts JOIN email_addr_bean_rel eabr ON (contacts.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (contacts.deleted = 0 AND eabr.primary_address = 1) AND (first_name LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT contacts.id, contacts.first_name, contacts.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Contacts' module FROM contacts JOIN email_addr_bean_rel eabr ON (contacts.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (contacts.deleted = 0 AND eabr.primary_address = 1) AND (last_name LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT contacts.id, contacts.first_name, contacts.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Contacts' module FROM contacts JOIN email_addr_bean_rel eabr ON (contacts.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (contacts.deleted = 0 AND eabr.primary_address = 1) AND (LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT contacts.id, contacts.first_name, contacts.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Contacts' module FROM contacts JOIN email_addr_bean_rel eabr ON (contacts.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (contacts.deleted = 0 AND eabr.primary_address = 1) AND (email_address LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT leads.id, leads.first_name, leads.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Leads' module FROM leads JOIN email_addr_bean_rel eabr ON (leads.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (leads.deleted = 0 AND eabr.primary_address = 1) AND (first_name LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT leads.id, leads.first_name, leads.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Leads' module FROM leads JOIN email_addr_bean_rel eabr ON (leads.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (leads.deleted = 0 AND eabr.primary_address = 1) AND (last_name LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT leads.id, leads.first_name, leads.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Leads' module FROM leads JOIN email_addr_bean_rel eabr ON (leads.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (leads.deleted = 0 AND eabr.primary_address = 1) AND (LTRIM(RTRIM(CONCAT(IFNULL(leads.first_name,''),' ',IFNULL(leads.last_name,'')))) LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT leads.id, leads.first_name, leads.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Leads' module FROM leads JOIN email_addr_bean_rel eabr ON (leads.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (leads.deleted = 0 AND eabr.primary_address = 1) AND (email_address LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT prospects.id, prospects.first_name, prospects.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Prospects' module FROM prospects JOIN email_addr_bean_rel eabr ON (prospects.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (prospects.deleted = 0 AND eabr.primary_address = 1) AND (first_name LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT prospects.id, prospects.first_name, prospects.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Prospects' module FROM prospects JOIN email_addr_bean_rel eabr ON (prospects.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (prospects.deleted = 0 AND eabr.primary_address = 1) AND (last_name LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT prospects.id, prospects.first_name, prospects.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Prospects' module FROM prospects JOIN email_addr_bean_rel eabr ON (prospects.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (prospects.deleted = 0 AND eabr.primary_address = 1) AND (LTRIM(RTRIM(CONCAT(IFNULL(prospects.first_name,''),' ',IFNULL(prospects.last_name,'')))) LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT prospects.id, prospects.first_name, prospects.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Prospects' module FROM prospects JOIN email_addr_bean_rel eabr ON (prospects.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (prospects.deleted = 0 AND eabr.primary_address = 1) AND (email_address LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT accounts.id, '' first_name, accounts.name last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Accounts' module FROM accounts JOIN email_addr_bean_rel eabr ON (accounts.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (accounts.deleted = 0 AND eabr.primary_address = 1) AND (email_address LIKE 'fio%') AND ea.invalid_email = 0)
 UNION
(SELECT accounts.id, '' first_name, accounts.name last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Accounts' module FROM accounts JOIN email_addr_bean_rel eabr ON (accounts.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (accounts.deleted = 0 AND eabr.primary_address = 1) AND (name LIKE 'fio%') AND ea.invalid_email = 0) ORDER BY id DESC

Analyzing each component individually I found that the queries that search for the concatenated first and last name in the contacts or leads table are the most time consuming, an average of 6 seconds for the contacts query and 12 seconds for the leads query.

They each do full table scan on email_address_bean_rel.

These queries are different from the original v9 queries because Sugar removed the full_name column from the contacts and the leads tables.

For example the Leads v9 query would have looked like:

(SELECT leads.id, leads.first_name, leads.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Leads' module FROM leads JOIN email_addr_bean_rel eabr ON (leads.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id)  WHERE (leads.deleted = 0 AND eabr.primary_address = 1) AND (full_name LIKE 'fio%') AND ea.invalid_email = 0)

instead of the current v.11

(SELECT leads.id, leads.first_name, leads.last_name, eabr.primary_address, ea.id AS email_address_id, ea.email_address, ea.opt_out, 'Leads' module FROM leads JOIN email_addr_bean_rel eabr ON (leads.id = eabr.bean_id and eabr.deleted=0) JOIN email_addresses ea ON (eabr.email_address_id = ea.id) WHERE (leads.deleted = 0 AND eabr.primary_address = 1) AND (LTRIM(RTRIM(CONCAT(IFNULL(leads.first_name,''),' ',IFNULL(leads.last_name,'')))) LIKE 'fio%') AND ea.invalid_email = 0)

Having said this, I am not sure the culprit is the removal of the full_name column since querying for:

SELECT leads.id, leads.first_name, leads.last_name
FROM leads
WHERE leads.deleted = 0 
AND (LTRIM(RTRIM(CONCAT(IFNULL(leads.first_name,''),' ',IFNULL(leads.last_name,'')))) LIKE 'fio%')

takes just over 2seconds while the query with the joins to eabr and ea takes over 12 seconds

The bigger issue still appears to be on the full table scan of the email_addr_bean_rel table.

Sugar has filed a bug https://portal.sugarondemand.com/#supp_Bugs/87866 but if anyone has encountered this and has a solution I'm willing to put in NON-UPGRADE-SAFE changes to get my people working efficiently.

Right now our use of Sugar for Cases is severely impaired.

Thanks,
FrancescaS

+1 Cédric Mourizard over 3 years ago in reply to Francesca Shiekh

Maybe you can add an index for columns `primary_address`, `deleted`, `bean_id` on email_addr_bean_rel
Cancel
Vote Up +1 Vote Down

Sign in to reply

Reject Answer

Cancel
+1 Francesca Shiekh over 3 years ago in reply to Cédric Mourizard

Cédric Mourizard,

We added the index you suggested, and another:

alter table email_addr_bean_rel add index idx_pri_del_bean(primary_address,deleted,bean_id);
alter table leads add index idx_del_first_last(deleted,first_name,last_name);

The Contact search alone went from 6s yesterday to 0.74

Leads from 12s to 0.28s

The "feel" in the front end has much improved from ~ 26s to ~6s

Not brilliant, but much much more acceptable.

Thank you!
Cancel
Vote Up +1 Vote Down

Sign in to reply

Reject Answer

Cancel

Reply

+1 Francesca Shiekh over 3 years ago in reply to Cédric Mourizard

Cédric Mourizard,

We added the index you suggested, and another:

alter table email_addr_bean_rel add index idx_pri_del_bean(primary_address,deleted,bean_id);
alter table leads add index idx_del_first_last(deleted,first_name,last_name);

The Contact search alone went from 6s yesterday to 0.74

Leads from 12s to 0.28s

The "feel" in the front end has much improved from ~ 26s to ~6s

Not brilliant, but much much more acceptable.

Thank you!
Cancel
Vote Up +1 Vote Down

Sign in to reply

Reject Answer

Cancel

Children

0 Cédric Mourizard over 3 years ago in reply to Francesca Shiekh

That need to analyse every query about the union and maybe increase the innodb buffer pull size. Good that helps a little bit.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Francesca Shiekh over 3 years ago in reply to Cédric Mourizard

We did a detailed analysis of each of the queries of the Union, the Contacts query is the slowest one at this point in time, but that is largely due to the decision by Sugar to remove the full_name field.
It is now doing a full scan to achieve the same goal:

SELECT leads.id, leads.first_name, leads.last_name FROM leads WHERE leads.deleted = 0 AND (LTRIM(RTRIM(CONCAT(IFNULL(leads.first_name,''),' ',IFNULL(leads.last_name,'')))) LIKE 'fio%')

I will check with my dba wrt buffer size.

Thank you,

FrancescaS
Cancel
Vote Up +1 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Francesca Shiekh over 3 years ago in reply to Cédric Mourizard

As for the buffer we already have 100G configured.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel