Email slow loading or timeout. v8.0.3

[ThomasB]

Always make a backup first, in case it does not work. Or try first with a test server.

[alexisc]

Thank you!
I'll try to upgrade to MySQL 8.0
according to the result, I will write here

[ThomasB]

As a side note: MySQL 5.7 support seems to end October 2023.

This is another incentive to change or upgrade the DB sooner or later. I'm just not sure if MySQL 8.x works with EspoCRM (docs state 5.7 or later) or would improve the situation for you.

[yuri]

If you would like just to fix the problem but not to figure out what is the cause, I suggest migrating to MariaDB. Anyway, if it won't fix the problem, Espo works better on MariaDB.

[alexisc]

you are right, the problem is not related to the update or the number of records

[alexisc]

SELECT
email.id AS `id`,
email.name AS `name`,
email.deleted AS `deleted`,
email.from_string AS `fromString`,
email.reply_to_string AS `replyToString`,
email.address_name_map AS `addressNameMap`,
email.is_replied AS `isReplied`,
email.message_id AS `messageId`,
email.message_id_internal AS `messageIdInternal`,
email.body_plain AS `bodyPlain`,
email.body AS `body`,
email.is_html AS `isHtml`,
email.status AS `status`,
email.has_attachment AS `hasAttachment`,
email.date_sent AS `dateSent`,
email.delivery_date AS `deliveryDate`,
email.created_at AS `createdAt`,
email.modified_at AS `modifiedAt`,
email.is_system AS `isSystem`,
email.ics_contents AS `icsContents`,
email.ics_event_uid AS `icsEventUid`,
email.from_email_address_id AS `fromEmailAddressId`,
fromEmailAddress.name AS `fromEmailAddressName`,
email.parent_id AS `parentId`,
email.parent_type AS `parentType`,
email.created_by_id AS `createdById`,
NULLIF(
TRIM(
CONCAT(
IFNULL(createdBy.first_name, ''),
' ',
IFNULL(createdBy.last_name, '')
)
),
''
) AS `createdByName`,
email.sent_by_id AS `sentById`,
NULLIF(
TRIM(
CONCAT(
IFNULL(sentBy.first_name, ''),
' ',
IFNULL(sentBy.last_name, '')
)
),
''
) AS `sentByName`,
email.modified_by_id AS `modifiedById`,
NULLIF(
TRIM(
CONCAT(
IFNULL(modifiedBy.first_name, ''),
' ',
IFNULL(modifiedBy.last_name, '')
)
),
''
) AS `modifiedByName`,
email.assigned_user_id AS `assignedUserId`,
NULLIF(
TRIM(
CONCAT(
IFNULL(assignedUser.first_name, ''),
' ',
IFNULL(assignedUser.last_name, '')
)
),
''
) AS `assignedUserName`,
email.replied_id AS `repliedId`,
replied.name AS `repliedName`,
email.created_event_id AS `createdEventId`,
email.created_event_type AS `createdEventType`,
email.group_folder_id AS `groupFolderId`,
groupFolder.name AS `groupFolderName`,
email.account_id AS `accountId`,
account.name AS `accountName`,
emailUser.is_read AS `isRead`,
emailUser.is_important AS `isImportant`,
emailUser.in_trash AS `inTrash`,
emailUser.folder_id AS `folderId`
FROM
`email` AS `email` USE INDEX (`IDX_DATE_SENT`)
LEFT JOIN `account` AS `account` ON email.account_id = account.id
LEFT JOIN `group_email_folder` AS `groupFolder` ON email.group_folder_id = groupFolder.id
LEFT JOIN `email_address` AS `fromEmailAddress` ON email.from_email_address_id = fromEmailAddress.id
LEFT JOIN `email` AS `replied` ON email.replied_id = replied.id
LEFT JOIN `user` AS `sentBy` ON email.sent_by_id = sentBy.id
LEFT JOIN `user` AS `assignedUser` ON email.assigned_user_id = assignedUser.id
LEFT JOIN `user` AS `modifiedBy` ON email.modified_by_id = modifiedBy.id
LEFT JOIN `user` AS `createdBy` ON email.created_by_id = createdBy.id
LEFT JOIN `email_user` AS `emailUser` ON emailUser.email_id = email.id
AND emailUser.deleted = 0
AND emailUser.user_id = '1'
WHERE
email.id = '65211b495413eb143'
AND email.deleted = 0
ORDER BY
email.date_sent DESC,
email.id DESC
LIMIT
0, 1​ 

such a request runs on average from 73 to 122 seconds and ends with a timeout

[yuri]

Could you find out which exactly SQL queries causes the issue?

[yuri]

Just for information. On the instance with 250,000 emails the list view and detail view load instantaneously. v8.0.3, MariaDB 11.

[alexisc]

there are no problems with server loading, but there are a large number of slow requests related to email

Sample type	Possible keys	Key	Link	Strings	Additional info
SIMPLE : email ALL				32436	Using where; Using temporary; Using filesort

each email request scans all 45091 records and returns 32436 Strings

[alexisc]

free 22 GB out of 32 GB
the rebuild command from the administration menu also ends with a timeout

I'll try to set up the database, I don't understand why there were no problems with the previous version...

[yuri]

Check active processes on the server. Maybe Mysql takes up all free resources.

[alexisc]

free 22 GB out of 32 GB
the rebuild command from the administration menu also ends with a timeout

I'll try to set up the database, I don't understand why there were no problems with the previous version...

[yuri]

I recommend to try to tweak your database. Maybe your have not enough disk space on your server?

[alexisc]

updated from Version 7.5.6
I don't think this is related to CRM, but the interface is slow in getting information from the database

[yuri]

I don't know of any change in v8.0 that could affect emails performance. From which version did you upgrade?