Email slow loading or timeout. v8.0.3

[alexvam1]

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

I had the same problem.
Problem with:

USE INDEX (`IDX_DATE_SENT`)

You need to remove this from the query.

And here is the fix (added by default at Espo CRM 9):

https://github.com/espocrm/espocrm/commit/2c83edf69184c6857720ee9444aee6cfe6520f13?diff=split&w=0

[esforim]

I cant seem to track down why mine is slow. I'm already on MariaDB. I'll wait for v8.2 and try again

[mwo300]

This helped me too very much.
I had the problem that i imap my e-mail back from 2013 to now. Once i tried opening an e-mail from 2022 or earlier i got a timeout. I was working with MariaDB already and the latest 8.1.4 Espo. Now i changed that and this did the job. E-Mails even from 2013 loading instantly.

[alexisc]

This solution helped me. Thank you!

https://github.com/espocrm/espocrm/commit/2c83edf69184c6857720ee9444aee6cfe6520f13?diff=split&w=0

[jflores]

Yes, i was skeptical of it too, but I also didn't want to have to dig deeper into selectmanagers or queries than I wanted to. I did want to keep the note, though, in case I had to go to that route.

In the end, migrating to MariaDB solved the problem. MySQL 8, for whatever reason, was garbage for us. Which is odd b/c MySQL 5.7 was working just fine.

At this point, I'm considering moving the entire DB to Amazon RDS in order to use their performance, backups, replicability, etc. Databases drive me crazy.

[jflores]

alexisc sorry for the confusion; I had 2 separate issues (at first I thought they were related):

1. Slow email/timeouts -> I switched from mysql to mariadb and that fixed the problem.
2. Couldn't send -> That turned out to be a problem with my entityDefs file.

Hope that clarifies things!

[yuri]

This ChatGPT recommendations is nonsense for our particular case. For information, the same query is running super fast with 250,000 records in the table.

[alexisc]

This solution didn't help me. I don't have any custom settings in entityDefs for email.

[jflores]

I figured out the send issue. This post helped: https://forum.espocrm.com/forum/gene...-does-not-work

there was a default status of “New” in my custom entitydefs- probably from v5 (I migrated 5.8 to 8).

I updated the entitydef properties more like the current build, rebuilt the code, and it worked!

[jflores]

alexisc - I took yuri advice and switched to Mariadb. The performance got much much better. We still can't send emails since upgrading, but that's a separate issue, I assume. My initial hypothesis was that the server was timing out while executing some sql query and preventing emails from going out. Looks not to be the case. So, we aren't able to send, but at least we can see inbound again - without waiting 30 seconds, timing out or crashing server.

[jflores]

alexisc - where did you grab this code from? I saw the raw SQL in my server (I log slow queries) and it looks very similar.

[jflores]

Updates:

- I set up the app on a local environment (docker-compose, but with the custom && client/custom fields from production) and imported the same database. Performance on local is just as slow as performance on production (30+ second queries, emails not sending).
- I went into mysql and ran optimize table command on email && email related tables. No improvement.
- I turned on slow query logging and clicked on one of the emails in the system. This is the query:

# Query_time: 14.541477  Lock_time: 0.000011 Rows_sent: 1  Rows_examined: 221855
use swp_espocrm;
SET timestamp=1697506170;
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 = '65187eb0cfacda24f' AND email.deleted = 0 ORDER BY email.date_sent DESC, email.id DESC LIMIT 0, 1;

When run from both the app and mysql itself, it takes about 14-20 seconds.

My database is ~10GB

I also:

1. Updated my settings in mysqld.conf

[mysqld]
innodb_buffer_pool_size = 6G
innodb_log_file_size = 256M

2. Analyzed related tables for query planner

ANALYZE TABLE email, account, group_email_folder, email_address, user, email_user;

What ChatGPT noticed about the query:

I've noticed the query has a few potential performance bottlenecks:

1. **Joins**: The query joins multiple tables, which increases the complexity and time taken, especially if the tables have a large number of rows.

2. **Functions**: The query uses functions like `TRIM` and `CONCAT`, which, when applied to a lot of rows, can be expensive.

3. **Order By**: `ORDER BY email.date_sent DESC, email.id DESC` – This sorts the entire result set. Depending on the number of records in the email table, this could be costly.

Let's tackle each point and consider potential optimizations:

1. **Optimize Joins**:
- Ensure that fields used in the JOIN conditions are indexed. For instance, `email.account_id = account.id` suggests that `account.id` and `email.account_id` should be indexed. Based on the indexes you showed earlier, they seem to be indexed, but it's good to double-check for all join conditions.

2. **Limit the use of Functions**:
- If possible, move functions like `TRIM` and `CONCAT` to the application side rather than the database. This might not be feasible depending on the application, but it's a general optimization technique.

3. **Reconsider the `ORDER BY` clause**:
- The query uses an index on `date_sent`, which is good. However, since the `WHERE` clause is filtering by `email.id`, consider whether sorting by `date_sent` and then `id` is necessary. If you're only fetching one record based on a unique `id`, the sorting might be superfluous.

[jflores]

Another few updates, in case it helps with debugging for anyone else (I don't have a solution, just some observations that I'm seeing as I try to figure it out).

Problem: Sending email
We aren't able to send emails from the interface (autoresponders and system-generated emails (e.g., mentions) are working fine). I'm attaching a few screenshots:

The payload is fine - and so is the response.

Payload:


Response:
However the "Sending" notification stays glued to the stop and it never seems to send.

The "Timing" tab shows only a 400ms response to server, so it seems that somewhere along the line it's not making it's way to the smtp transport.


The Response Object
The response object _does_ eventually make it into the database and is (after about 30 seconds) available to the client, inside the application. At no point does the email actually send.

[jflores]

Sending emails using the default editor is also very slow and ultimately times out. System-generated autoresponders and the 'test' emails from the SMTP screens are sending fine. It's just the outbound emails from the client that aren't sending.

[jflores]

I had been using Espo for a long time and was delaying upgrading since version 6 because there were so many changes.

I ultimately decided to make the move this past weekend and I'm running espocrm through docker, connected to my MySQL database, which is on the same server, but outside the container.

My operating system is Ubuntu 22 and my MySQL version is 8 on a digital ocean server with a dedicated CPU, 8GB RAM, and 50GB storage (with all files stored externally in a digital ocean storage block)

I'm also experiencing painfully slow email performance. I'm not getting a timeout error, but it's taking several seconds for emails to load. And I just discovered inbound emails aren't coming in at all (all connection tests are ok).

yuri - When you say 'tweak the database' - do you have something specific in mind? Would you suggest I migrate everything to mariadb? I'm not seeing any errors in the logs that suggest anything is wrong with email - especially the inbound email not making it in. Where would you suggest I dig first?

( alexisc - FYI - I'm on mysql 8)

In case it helps, this is the performance graph -> it seems to be waiting for server response. Other Entities are performing just fine, though.