Stream performance optimization

[dimyy]

Stream is the heaviest request in Espo. For example original.txt

Optimization suggestions:
1. In the first 4 union parts remove USE INDEX (`UNIQ_NUMBER`)
2. In other 4 unions add use index (`related`, parent)

it's about 10* times faster. ( 29s against 4m 33s) in my case

[yuri]

It could be that with other date-set result will be opposite.

[yuri]

When I configured indexing I remember trying different databases and different datasets. The current one I found optimal by that time. It worked pretty fast.

How fast the stream is loaded when logged under an admin user?

[dimyy]

About 1m.10 on one DB (note: 5990720 records (with type 'Post' :579226), subscriptions: 432964)
About 40s on second DB (417416 (21008) , 58241)

execution time after optimization
On the first DB: 7s
On the second: 3s

Analyze (query) results in the screens

[yuri]

Is it for admin user?

What database is used?

It would be helpful to run all union queries separately. Usually there are 1 or 2 that have most performance impact.

The current indexing is working very fast in our CRM, that is weird.

[dimyy]

All requests under admin user

DB's
10.6.8-MariaDB-1:10.6.8+maria~focal-log
10.6.9-MariaDB-log

I try each union query separate. Results are similar except last union



can it matter that Both bases continue from version 5?

[yuri]

> can it matter that Both bases continue from version 5?

I don't think so.

I see a performance boost when removed the index. It's on MariaDB (don't have MySQL with data at hand). I remember when I added that index it improved performance significantly. Might be on MySQL it works differently. Or the optimizer improved since then. We need to do more testing on different databases.

[yuri]

How much of performance increase do adding related, parent indexes add?

I believe that the proper way to add these indexes is USE INDEX (`IDX_PARENT`, `IDX_RELATED`). Could be that indexes where not renamed since v5.

[dimyy]

Original query: 29 rows retrieved starting from 1 in 4 m 17 s 678 ms (execution: 4 m 17 s 572 ms, fetching: 106 ms)

After removing USE INDEX (`UNIQ_NUMBER`) (first 4 union parts), adding index (parent_id, parent_type, type)

and USE INDEX (`note_parent_id_parent_type_type_index`) on the last 4 union parts: 29 rows retrieved starting from 1 in 20 s 760 ms (execution: 20 s 619 ms, fetching: 141 ms)

Increasing performance: 12 times


Without adding index and using (`IDX_PARENT`) in the last 4 union parts : 29 rows retrieved starting from 1 in 38 s 817 ms (execution: 38 s 701 ms, fetching: 116 ms)
Increasing performance from start point: 6 times

[yuri]

In your upgraded CRM, how the index was named, IDX_PARENT or parent ? If it's not renamed after upgrades we have a problem to apply it from code as we don't know the name for sure.

[dimyy]

Originally it named 'parent' and I have some other "old named" indexes. It's not my problem - I can delete all of them and rebuild ESPO. But it can be problem for other older ESPO installations.

[yuri]

It would be a problem. How long did it take to rename indexes for your large note table?

[dimyy]

For example:

create index note_parent_id_parent_type_type_index
on note (parent_id, parent_type, type)

completed in 42 s 417 ms

[yuri]

It seems that after removing the UNIQUE forced index the stream works even slower on our CRM that uses MySQL. Not yet sure, but definitely no 10 fold increase like in MariaDB.

[dimyy]

We will try to deploy MySQL 8 and conduct similar tests on the same database.

I guess it will take about a week. I will post the results