Very slow search in magento cloud

[iampolo87]

First time search for any term takes between 30 and 45 seconds. Subsequent searchs for that term are almost instant. Everything else (catalog, suggestions) seems to work just fine. This is an Adobe Commerce Cloud project (testing ESuite in staging environment). I've tried twitching shards and replicas, but it didn't seem to improve much (although i think it did have a lil performance boost). Reindex takes around 17 minutes in total (160k products in catalog). With livesearch, search results were fast and reindex took around 10 minutes.

Preconditions

Magento Version : 2.4.3-p1 (cloud)

ElasticSuite Version : Open Source ver. 2.10.11

Mysql Version: 15.1 Distrib 10.3.35-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

ElasticSearch version: 7.10.2

Environment : production mode

Third party modules : none that should affect catalog/search.

Steps to reproduce

1. just do a search for a term that was not used before

Expected result

1. search should take some seconds

Actual result

1. url: "catalogsearch/result/?q+query here" search result is taking around 30~45s

[romainruaud]

@iampolo87 can you ask Magento Cloud team to know how much memory they assigned to Elasticsearch ?

They probably gave it only 1gb as per their standard, which makes no sense.

You don't need to switch shards and replicas : ideal config is 1 shard / 2 replicas, but they won't allow it and will force you to use 3 shards / 2 replicas.

Regards

[romainruaud]

By the way, can you give it a try to 2.10.12 which contains performances improvements ?

Regards

[iampolo87]

I will update to 2.10.12 and ask the Cloud team how much memory they assigned to ES and report back to you. Thank you!

[iampolo87]

Hey @romainruaud apparently staging environment has 4gb allocated for ES. Also i did update the ESuite version to .12 but it didnt really make any difference.

[romainruaud]

Hello @iampolo87

If you enable the Magento profiler on your staging environment, what is the output of it ? Can you see in which function it's spending all this time ?

Regards

[iampolo87]

Hey, here is the result, these are the ones that take longer: ID | Time | Avg | Cnt | Emalloc | RealMem

CONTROLLER_ACTION:catalogsearch_result_index 31.773324 31.773324 1 13,856,024 25,165,824 · · · action_body 31.773308 31.773308 1 13,855,128 25,165,824 · · · · LAYOUT 31.718788 7.929697 4 12,332,096 23,068,672 TEMPLATE: .../magento/module-catalog-search/view/frontend/templates/result.phtml 20.879517 20.879517 1 3,047,104 16,777,216 EAV:load_collection 20.766174 20.766174 1 2,175,736 14,680,064 ES:Execute Search Query 20.475395 5.118849 4 578,312 8,388,608 TEMPLATE: .../smile/elasticsuite/src/module-elasticsuite-catalog/view/frontend/templates/layer/view.phtml 10.347667 10.347667 1 123,392 0 ES:Execute Search Query 10.295261 5.147630 2 15,216 0

[romainruaud]

Hello @iampolo87

I'm not sure how I should read this, I'm used to read the magento profiler actually, but this "stripped" version is a bit blurry for me.

I understand that we have a first "ES:Execute Search Query" that is taking 5 secs, and another that is taking 5 secs as well, correct ?

Could you enable the Elasticsuite debug log and paste here the queries that are sent to the server ? You can see them in the system.log

Regards

[iampolo87]

Im sorry @romainruaud , if you want i can paste the whole profiler data, i just pasted the longer ones. ES:Execute Search Query is taking 20.47... seconds, not 5, 5 would be the average, then comes the count of times executed (4), allocated memory and the used memory last.

[romainruaud]

Ok, 20 seconds is just enormous for running an Elasticsearch query.

My last question remains definitely valid, can you share the generated query ?

You can enable Elasticsuite Debug log in Store > Configuration > Elasticsuite > Enable Debug mode and see the queries in the system.log file

Regards

[iampolo87]

Here it is: [2022-10-24 13:17:00] report.INFO: Request Success: {"method":"HEAD","uri":"http://127.0.0.1/magento2_default_catalog_product","port":9200,"headers":{"Host":["127.0.0.1"],"Content-Type":["application/json"],"Accept":["application/json"],"User-Agent":["elasticsearch-php/7.11.0 (Linux 5.15.36-0psh1; PHP 7.4.30)"],"x-elastic-client-meta":["es=7.11.0,php=7.4.30,t=7.11.0,a=0,cu=7.52.1"]},"HTTP code":200,"duration":0.002098} [] [2022-10-24 13:17:00] report.INFO: curl -XHEAD 'http://127.0.0.1/magento2_default_catalog_product?pretty=true' [] [] [2022-10-24 13:17:00] report.INFO: Request Success: {"method":"POST","uri":"http://127.0.0.1/magento2_default_thesaurus/_analyze","port":9200,"headers":{"Host":["127.0.0.1"],"Content-Type":["application/json"],"Accept":["application/json"],"User-Agent":["elasticsearch-php/7.11.0 (Linux 5.15.36-0psh1; PHP 7.4.30)"],"x-elastic-client-meta":["es=7.11.0,php=7.4.30,t=7.11.0,a=0,cu=7.52.1"]},"HTTP code":200,"duration":0.00154} [] [2022-10-24 13:17:00] report.INFO: curl -XPOST 'http://127.0.0.1/magento2_default_thesaurus/_analyze?pretty=true' -d '{"text":"busued","analyzer":"synonym"}' [] [] [2022-10-24 13:17:00] report.INFO: Request Success: {"method":"POST","uri":"http://127.0.0.1/magento2_default_thesaurus/_analyze","port":9200,"headers":{"Host":["127.0.0.1"],"Content-Type":["application/json"],"Accept":["application/json"],"User-Agent":["elasticsearch-php/7.11.0 (Linux 5.15.36-0psh1; PHP 7.4.30)"],"x-elastic-client-meta":["es=7.11.0,php=7.4.30,t=7.11.0,a=0,cu=7.52.1"]},"HTTP code":200,"duration":0.000818} [] [2022-10-24 13:17:00] report.INFO: curl -XPOST 'http://127.0.0.1/magento2_default_thesaurus/_analyze?pretty=true' -d '{"text":"busued","analyzer":"expansion"}' [] [] [2022-10-24 13:17:00] report.INFO: Request Success: {"method":"GET","uri":"http://127.0.0.1/","port":9200,"headers":{"Host":["127.0.0.1"],"Content-Type":["application/json"],"Accept":["application/json"],"User-Agent":["elasticsearch-php/7.11.0 (Linux 5.15.36-0psh1; PHP 7.4.30)"],"x-elastic-client-meta":["es=7.11.0,php=7.4.30,t=7.11.0,a=0,cu=7.52.1"]},"HTTP code":200,"duration":0.000312} [] [2022-10-24 13:17:00] report.INFO: curl -XGET 'http://127.0.0.1/?pretty=true' [] [] [2022-10-24 13:17:00] report.INFO: Request Success: {"method":"GET","uri":"http://127.0.0.1/","port":9200,"headers":{"Host":["127.0.0.1"],"Content-Type":["application/json"],"Accept":["application/json"],"User-Agent":["elasticsearch-php/7.11.0 (Linux 5.15.36-0psh1; PHP 7.4.30)"],"x-elastic-client-meta":["es=7.11.0,php=7.4.30,t=7.11.0,a=0,cu=7.52.1"]},"HTTP code":200,"duration":0.000179} [] [2022-10-24 13:17:00] report.INFO: curl -XGET 'http://127.0.0.1/?pretty=true' [] [] [2022-10-24 13:17:00] report.WARNING: Deprecation ["299 Elasticsearch-7.10.2-c582914dd08ada68d7dafd8248b1db38afb0438d \"Deprecated field [cutoff_frequency] used, replaced by [you can omit this option, the [multi_match] query can skip block of documents efficiently if the total number of hits is not tracked]\""] [] [2022-10-24 13:17:00] report.INFO: Request Success: {"method":"POST","uri":"http://127.0.0.1/magento2_default_catalog_product/_search","port":9200,"headers":{"Host":["127.0.0.1"],"Content-Type":["application/json"],"Accept":["application/json"],"User-Agent":["elasticsearch-php/7.11.0 (Linux 5.15.36-0psh1; PHP 7.4.30)"],"x-elastic-client-meta":["es=7.11.0,php=7.4.30,t=7.11.0,a=0,cu=7.52.1"]},"HTTP code":200,"duration":0.017453} [] [2022-10-24 13:17:00] report.INFO: curl -XPOST 'http://127.0.0.1/magento2_default_catalog_product/_search?pretty=true' -d '{"size":0,"query":{"bool":{"filter":{"bool":{"must":[{"term":{"stock.is_in_stock":{"value":true,"boost":1}}},{"terms":{"visibility":[3,4],"boost":1}}],"must_not":[],"should":[],"boost":1}},"must":{"bool":{"must":[],"must_not":[],"should":[{"bool":{"filter":{"multi_match":{"query":"busued","fields":["search^1","sku^1"],"minimum_should_match":"100%","tie_breaker":1,"boost":1,"type":"best_fields","cutoff_frequency":0.15}},"must":{"multi_match":{"query":"busued","fields":["search^1","name.standard^5","sku.reference^6","option_text_condition.standard^7","option_text_gender.standard^5","search.whitespace^10","name.whitespace^50","sku.whitespace^60","option_text_condition.whitespace^70","option_text_gender.whitespace^50","name.sortable^100","sku.sortable^120"],"minimum_should_match":1,"tie_breaker":1.0,"boost":1,"type":"best_fields","cutoff_frequency":0.15}},"boost":1}}],"minimum_should_match":1,"boost":1}},"boost":1}},"aggregations":{"attribute_set_id":{"terms":{"field":"attribute_set_id","size":100000,"order":{"_count":"desc"}}},"indexed_attributes":{"terms":{"field":"indexed_attributes","size":100000,"order":{"_count":"desc"}}}},"track_total_hits":0}' [] [] [2022-10-24 13:17:00] report.WARNING: Deprecation ["299 Elasticsearch-7.10.2-c582914dd08ada68d7dafd8248b1db38afb0438d \"Deprecated field [cutoff_frequency] used, replaced by [you can omit this option, the [multi_match] query can skip block of documents efficiently if the total number of hits is not tracked]\""] [] [2022-10-24 13:17:00] report.INFO: Request Success: {"method":"POST","uri":"http://127.0.0.1/magento2_default_catalog_product/_search","port":9200,"headers":{"Host":["127.0.0.1"],"Content-Type":["application/json"],"Accept":["application/json"],"User-Agent":["elasticsearch-php/7.11.0 (Linux 5.15.36-0psh1; PHP 7.4.30)"],"x-elastic-client-meta":["es=7.11.0,php=7.4.30,t=7.11.0,a=0,cu=7.52.1"]},"HTTP code":200,"duration":0.002879} [] [2022-10-24 13:17:00] report.INFO: curl -XPOST 'http://127.0.0.1/magento2_default_catalog_product/_search?pretty=true' -d '{"size":10,"sort":[{"_score":{"order":"desc"}},{"entity_id":{"order":"desc","missing":"_first","unmapped_type":"keyword"}}],"from":0,"query":{"bool":{"filter":{"bool":{"must":[{"term":{"stock.is_in_stock":{"value":true,"boost":1}}},{"terms":{"visibility":[3,4],"boost":1}}],"must_not":[],"should":[],"boost":1}},"must":{"bool":{"must":[],"must_not":[],"should":[{"bool":{"filter":{"multi_match":{"query":"busued","fields":["search^1","sku^1"],"minimum_should_match":"100%","tie_breaker":1,"boost":1,"type":"best_fields","cutoff_frequency":0.15}},"must":{"multi_match":{"query":"busued","fields":["search^1","name.standard^5","sku.reference^6","option_text_condition.standard^7","option_text_gender.standard^5","search.whitespace^10","name.whitespace^50","sku.whitespace^60","option_text_condition.whitespace^70","option_text_gender.whitespace^50","name.sortable^100","sku.sortable^120"],"minimum_should_match":1,"tie_breaker":1.0,"boost":1,"type":"best_fields","cutoff_frequency":0.15}},"boost":1}}],"minimum_should_match":1,"boost":1}},"boost":1}},"track_total_hits":0}' [] [] [2022-10-24 13:17:00] report.INFO: Request Success: {"method":"HEAD","uri":"http://127.0.0.1/magento2_default_catalog_category","port":9200,"headers":{"Host":["127.0.0.1"],"Content-Type":["application/json"],"Accept":["application/json"],"User-Agent":["elasticsearch-php/7.11.0 (Linux 5.15.36-0psh1; PHP 7.4.30)"],"x-elastic-client-meta":["es=7.11.0,php=7.4.30,t=7.11.0,a=0,cu=7.52.1"]},"HTTP code":200,"duration":0.000852} [] [2022-10-24 13:17:00] report.INFO: curl -XHEAD 'http://127.0.0.1/magento2_default_catalog_category?pretty=true' [] [] [2022-10-24 13:17:00] report.WARNING: Deprecation ["299 Elasticsearch-7.10.2-c582914dd08ada68d7dafd8248b1db38afb0438d \"Deprecated field [cutoff_frequency] used, replaced by [you can omit this option, the [multi_match] query can skip block of documents efficiently if the total number of hits is not tracked]\""] [] [2022-10-24 13:17:00] report.INFO: Request Success: {"method":"POST","uri":"http://127.0.0.1/magento2_default_catalog_category/_search","port":9200,"headers":{"Host":["127.0.0.1"],"Content-Type":["application/json"],"Accept":["application/json"],"User-Agent":["elasticsearch-php/7.11.0 (Linux 5.15.36-0psh1; PHP 7.4.30)"],"x-elastic-client-meta":["es=7.11.0,php=7.4.30,t=7.11.0,a=0,cu=7.52.1"]},"HTTP code":200,"duration":0.002213} [] [2022-10-24 13:17:00] report.INFO: curl -XPOST 'http://127.0.0.1/magento2_default_catalog_category/_search?pretty=true' -d '{"size":5,"sort":[{"_score":{"order":"desc"}},{"entity_id":{"order":"desc","missing":"_first","unmapped_type":"keyword"}}],"from":0,"query":{"bool":{"filter":{"terms":{"is_displayed_in_autocomplete":[true],"boost":1}},"must":{"bool":{"must":[],"must_not":[],"should":[{"bool":{"filter":{"multi_match":{"query":"busued","fields":["search^1","sku^1"],"minimum_should_match":"100%","tie_breaker":1,"boost":1,"type":"best_fields","cutoff_frequency":0.15}},"must":{"multi_match":{"query":"busued","fields":["search^1","search.whitespace^10"],"minimum_should_match":1,"tie_breaker":1.0,"boost":1,"type":"best_fields","cutoff_frequency":0.15}},"boost":1}}],"minimum_should_match":1,"boost":1}},"boost":1}},"track_total_hits":100000}' [] []

[romainruaud]

ok so this is very basic queries... they should not take that long to complete, definitely ...

I'm afraid this is an infrastructure problem on Elasticsearch.

Can you give us the output of "curl localhost:9200/_cat/indices" as well ?

Regards

[romainruaud]

and also the output of : curl -sS "localhost:9200/_cat/nodes?h=heap*&v"

[iampolo87]

Of course! curl localhost:9200/_cat/indices:

green open magento2_default_tracking_log_event_202210        VP2z3_KtR7ibbOcCHQJBBA 3 2      1      0  29.1kb   9.7kb
green open magento2_stg_product_1_v5                         Rfi6z7EZQsWlJswsY1zwXw 3 2  56435 148246  71.8mb  23.9mb
green open magento2_default_catalog_category_20221019_021338 FIXY5p3nQA6FJG5Cs1ngSQ 3 2    354      0 923.5kb 307.8kb
green open magento2_default_thesaurus_20221019_021339        pxChUsKKTky2EDpWn_n12w 3 2      0      0   1.8kb    624b
green open magento2_default_catalog_product_20221019_020300  3B7KbUozQ3WJEe2g6_BeLg 3 2 433201      0 612.1mb 205.2mb
green open magento2_product_1_v40                            _2PkEDJLQ1eZxnkejcgfJA 3 2  56435  59590  93.9mb  31.3mb

*curl -sS "localhost:9200/_cat/nodes?h=heap&v"**

heap.current heap.percent heap.max
       1.8gb           47      4gb
       1.8gb           47      4gb
       2.1gb           53      4gb

[iampolo87]

The client mentioned this started happening after magento version update (2.3.x > 2.4.3) apparently, but i can't really confirm this since it wasn't my client at that time. Not sure if this means anything to you.

[romainruaud]

Hmm okay so maybe you need a bit more heap, can you check if you have files like this :

/var/log/opensearch/gc.log.1.current if you use Open Search

OR

/var/log/elasticsearch/gc*.log if you use Elasticsearch

And if the server is constantly logging inside those files, things like this :

2022-10-24T14:38:12.761+0000: 398588.239: Total time for which application threads were stopped: 0.0041667 seconds, Stopping threads took: 0.0000807 seconds
2022-10-24T14:38:14.105+0000: 398589.584: [GC (Allocation Failure) 2022-10-24T14:38:14.106+0000: 398589.584: [ParNew
Desired survivor size 35782656 bytes, new threshold 6 (max 6)
- age   1:   26210048 bytes,   26210048 total
- age   2:     152448 bytes,   26362496 total
- age   3:    1393232 bytes,   27755728 total
- age   4:      51840 bytes,   27807568 total
: 567510K->29867K(629120K), 0.0046396 secs] 1424208K->886565K(2027264K), 0.0047825 secs] [Times: user=0.09 sys=0.00, real=0.00 secs] 
2022-10-24T14:38:14.110+0000: 398589.589: Total time for which application threads were stopped: 0.0060975 seconds, Stopping threads took: 0.0001219 seconds
2022-10-24T14:38:15.013+0000: 398590.491: [GC (Allocation Failure) 2022-10-24T14:38:15.013+0000: 398590.491: [ParNew
Desired survivor size 35782656 bytes, new threshold 1 (max 6)
- age   1:   43879120 bytes,   43879120 total
- age   2:     127112 bytes,   44006232 total
- age   3:     152288 bytes,   44158520 total
- age   4:    1392848 bytes,   45551368 total
- age   5:      51280 bytes,   45602648 total
: 589099K->53791K(629120K), 0.0052474 secs] 1445797K->910490K(2027264K), 0.0053568 secs] [Times: user=0.10 sys=0.00, real=0.01 secs] 
2022-10-24T14:38:15.018+0000: 398590.497: Total time for which application threads were stopped: 0.0064892 seconds, Stopping threads took: 0.0000952 seconds

If that's the case, try to ask Magento Cloud team to increase the heap size to 8Gb and see if this improves.

In any case, we'll reach the maximum of help we can provide through Github...

Since this seems to be project/hosting specific, this is out-of-scope of our Open Source support policy here.

So if leveraging the heap does not help, you can write an email to elasticsuite@smile.fr and we'll try to provide you professional (paid) support.

Regards

[iampolo87]

I didn't see any allocation failure logs in the ES/gc.log Anyways, @romainruaud thanks a lot for the help! One last question tho, do you think a big amount of searchable attributes could be the reason?

[romainruaud]

What do you mean by "a big amount" ? We have customers with millions of products and hundreds of searchable attributes, and this runs smoothly, even some in Magento Cloud instances.

Regards

[iampolo87]

Oh nevermind then! Thanks a lot for your help!

[romainruaud]

Btw, you only have those attributes as searchable (as per your query) :

• name
• sku
• condition
• gender

Definitely not a big amount :)

[iampolo87]

Oh weird, i saw in the DB something around 25.

[mikew-westlund]

for me it's the /Catalog/view/frontend/templates/product/list/toolbar/amount.phtml template specifically the getTotalNum() method. I hope it helps

[iampolo87]

@mikew-westlund for me it wasnt, but thanks for the tip!

[romainruaud]

Did you find anything relevant on your environment @iampolo87 ?

Regards

[iampolo87]

Hey @romainruaud if found some mysql-slow logs which look like this, not sure if this is normal for big catalogs: INNER JOINcatalog_product_entityASeON e.row_id = t_d.row_id AND (e.created_in <= '1662008340' AND e.updated_in > '1662008340') LEFT JOINcatalog_product_entity_intASt_sON t_s.attribute_id = t_d.attribute_id AND t_s.row_id = t_d.row_id AND t_s.store_id = 1 WHERE (e.entity_id IN (162712, 162685, 162684, 162683, 162682, 162681, 162680, 162679, 162678, 162677, 162676, 162675, 162674, 162673, 162671, 162670, 162669, 162668, 162667, 162666, 162665, 162664, 133431, 133420, 133417, 133410, 133409, 133408, 133404, 116687, 133347, 133344, 133342, 133332, 133331, 133315, 133314, 133313, 133312, 133310, 133306, 133305, 133304, 133298, 133297, 133294, 133292, 133291, 133290, 133288, 133282, 133272, 133271, 133269, 133246, 133245, 133233, 133229, 125159, 125158, 125157, 125155, 125145, 125136, 125126, 125109, 125107, 125012, 124892, 124890, 124865, 124860, 124858, 124856, 124855, 122187, 124811, 124684, 124571, 124324, 122741, 94862, 91676, 106284, 112380, 111145, 111965, 110715, 107514, 102358, 90116, 102349, 107211, 90952, 94815, 97621, 93395, 111398, 101681, 106983, 104657, 102361, 104519, 105202, 91534, 98080, 104517, 105579, 106179, 106581, 107188, 89029, 101897, 92522, 92735, 101098, 93398, 93762, 105615, 89843, 93693, 96178, 98669, 101244, 102767, 104984, 103117, 103582, 103601, 102551, 102565, 103560, 106616, 85788, 84878, 68709, 86496, 104975, 55565, 100551, 78483, 83054, 64991, 68509, 101463, 107359, 61678, 84905, 99117, 91702, 111356, 112630, 112676, 113079, 111655, 110468, 76946, 78237, 75898, 102990, 99600, 78576, 81622, 82040, 82052, 84480, 84710, 89107, 93452, 103881, 77530, 78464, 84167, 87203, 90449, 91012, 96514, 45504, 54966, 73986, 87153, 93484, 95355, 97513, 97518, 60281, 122253, 117411, 115714, 115700, 115671, 115633, 115623, 115616, 115613, 115612, 115606, 115604, 115594, 115593, 115592, 115563, 115382, 112819, 105544, 104027, 102543, 101783, 101607, 100977, 100815, 100357, 97476, 96736, 93505, 89897, 88095, 87741, 85167, 82170, 82130, 81542, 81319, 81314, 81253, 81216, 77004, 73886, 72690, 70535, 69224, 68739, 68061, 68038, 67736, 58641, 58363, 55393, 45464, 45321, 21447)) AND (t_d.attribute_id IN (152, 142, 303, 322, 92, 251, 258, 259, 315, 312, 307, 311, 252, 306, 250, 262, 283, 145, 100, 146, 279, 148, 132, 129, 253, 270, 269, 296, 308, 89, 124, 127, 137, 155, 154, 128, 301, 302, 274, 284, 275, 276, 277, 285, 125, 313, 122, 153, 151, 147, 149, 102, 126, 255, //....GOES ON AND ON FOR A LOT OF IDS....//)) AND (t_d.store_id = IFNULL(t_s.store_id, 0)) UNION ALL SELECTt_d.attribute_id,e.entity_id,t_d.valueASdefault_value,t_s.valueASstore_value, IF(t_s.value_id IS NULL, t_d.value, t_s.value) ASvalueFROMcatalog_product_entity_varcharASt_d INNER JOINcatalog_product_entityASeON e.row_id = t_d.row_id AND (e.created_in <= '1662008340' AND e.updated_in > '1662008340') LEFT JOINcatalog_product_entity_varcharASt_sON t_s.attribute_id = t_d.attribute_id AND t_s.row_id = t_d.row_id AND t_s.store_id = 1 WHERE (e.entity_id IN (162712, 162685, 162684, 162683, 162682, 162681, 162680, 162679, 162678, 162677, 162676, 162675, 162674, 162673, 162671, 162670, 162669, 162668, 162667, 162666, 162665, 162664, 162663, 162662, 162661, 162660, 162659, 162658, 162657, 162656, 162655, 162654, 162653, 162652, 162651, 162650, 162649, 162648, 162647, 162646, 162645, 162644, 162643, 162642, 162640, 162639, 162638, 162637, 162636, 131573, 131567, 131566, 131563, 131561, 131530, 131524, 131523, 131522, 131513, 131511, 131507, 131506, 131491, 131488, 131368, 131373, 131351, 131366, 131375, 131376, 131381, 131382, 108047, 125693, 50511, 43850, 118999, 123441, 123967, 129515, 45533, 109493, 109782, 108377, 108742, 122069, 109542, 99213, 99215, 98934, 125277, 116970, 116910, 125746, 125738, 125737, 125728, 125689, 125686, 125685, 125682, 125657, 125652, 125648, 125632, 125621, 125609, 125591, 125558, 125544, 125527, 125506, 125492, 125491, 125490, 125388, 125384, 125375, 125374, 125367, 125366, 125325, 125324, 125269, 125258, 125181, 125179, 125178, 125177, 125176, 125159, 125158, 125157, 125155, 125145, 125136, 125126, 125109, 125107, 125012, 124892, 124890, 124865, 124860, 124858, 124856, 124855, 122187, 124811, 124684, 124571, 124324, 122741, 94862, 91676, 106284, 112380, 111145, 111965, 110715, 107514, 102358, 90116, 102349, 107211, 90952, 94815, 97621, 93395, 111398, 101681, 106983, 104657, 102361, 104519, 105202, 91534, 98080, 104517, 105579, 106179, 106581, 107188, 89029, 101897, 92522, 92735, 101098, 93398, 93762, 105615, 89843, 93693, 96178, 98669, 101244, 102767, 104984, 103117, 103582, 103601, 102551, 102565, 103560, 106616, 85788, 84878, 68709, 86496, 104975, 55565, 100551, 78483, 83054, 64991, 68509, 101463, 107359, 61678, 84905, 99117, 91702, 111356, 112630, 112676, 113079, 111655, 110468, 76946, 78237, 75898, 102990, 99600, 78576, 81622, 82040, 82052, 84480, 84710, 89107, 93452, 103881, 77530, 78464, 84167, 87203, 90449, 91012, 96514, 45504, 54966, 73986, 87153, 93484, 95355, 97513, 97518, 60281, 122253, 117411, 115714, 115700, 115671, 115633, 115623, 115616, 115613, 115612, 115606, 115604, 115594, 115593, 115592, 115563, 115382, 112819, 105544, 104027, 102543, 101783, 101607, 100977, 100815, 100357, 97476, 96736, 93505, 89897, 88095, 87741, 85167, 82170, 82130, 81542, 81319, 81314, 81253, 81216, 77004, 73886, 72690, 70535, 69224, 68739, 68061, 68038, 67736, 58641, 58363, 55393, 45464, 45321, 21447, //....GOES ON AND ON FOR A LOT OF IDS....//)) AND (t_d.attribute_id IN (304, 117, 103, 138, 139, 260, 261, 264, 267, 280, 150, 95, 123, 263, 314, 85, 112, 182, 131, 316, 256, 265, 266, 268, 273, 278, 84, 82, 281, 119, 71, 109, 107, 257, 271, 272, 130, 86, 113, 183, 87, 114, 300, 97, 98)) AND (t_d.store_id = IFNULL(t_s.store_id, 0)) UNION ALL SELECTt_d.attribute_id,e.entity_id,t_d.valueASdefault_value,t_s.valueASstore_value, IF(t_s.value_id IS NULL, t_d.value, t_s.value) ASvalueFROMcatalog_product_entity_decimalASt_d INNER JOINcatalog_product_entityASeON e.row_id = t_d.row_id AND (e.created_in <= '1662008340' AND e.updated_in > '1662008340') LEFT JOINcatalog_product_entity_decimalASt_sON t_s.attribute_id = t_d.attribute_id AND t_s.row_id = t_d.row_id AND t_s.store_id = 1 WHERE (e.entity_id IN (162712, 162685, 162684, 162683, 162682, 162681, 162680, 162679, 162678, 162677, 162676, 162675, 162674, 162673, 162671, 162670, 162669, 162668, 162667, 162666, 162665, 162664, 162663, 162662, 162661, 162660, 162659, 162658, 162657, 162656, 162655, 162654, 162653, 162652, 162651, 162650, 162649, 162648, 162647, 162646, 162645, 162644, 162643, 162642, 162640, 162639, 162638, 162637, 162636, 162635, 162634, 162633, 162632, 162631, 162630, 162629, 162628, 162627, 162626, 162625, 162624, 162623, 162622, 162621, 162620, 162619, 162618, 162617, 162616, 162615, 162614, 162613, 162612, 162611, 162610, 162609, 162608, 162607, 162606, 162605, 162604, 162603, 162602, 162601, 162600, 162599, 162598, 162595, 162594, 162593, 162592, 162591, 162590, 162589, 162588, 162587, 162586, 162585, 162584, 162583, 125652, 125648, 125632, 125621, 125609, 125591, 125558, 125544, 125527, 125506, 125492, 125491, 125490, 125388, 125384, 125375, 125374, 125367, 125366, 125325, 125324, 125269, 125258, 125181, 125179, 125178, 125177, 125176, 125159, 125158, 125157, 125155, 125145, 125136, 125126, 125109, 125107, 125012, 124892, 124890, 124865, 124860, 124858, 124856, 124855, 122187, 124811, 124684, 124571, 124324, 122741, 94862, 91676, 106284, 112380, 111145, 111965, 110715, 107514, 102358, 90116, 102349, 107211, 90952, 94815, 97621, 93395, 111398, 101681, 106983, 104657, 102361, 104519, 105202, 91534, 98080, 104517, 105579, 106179, 106581, 107188, 89029, 101897, 92522, 92735, 101098, 93398, 93762, 105615, 89843, 93693, 96178, 98669, 101244, 102767, 104984, 103117, 103582, 103601, 102551, 102565, 103560, 106616, 85788, 84878, 68709, 86496, 104975, 55565, 100551, 78483, 83054, 64991, 68509, 101463, 107359, 61678, 84905, 99117, 91702, 111356, 112630, 112676, 113079, 111655, 110468, 76946, 78237, 75898, 102990, 99600, 78576, 81622, 82040, 82052, 84480, 84710, 89107, 93452, 103881, 77530, 78464, 84167, 87203, 90449, 91012, 96514, 45504, 54966, 73986, 87153, 93484, 95355, 97513, 97518, 60281, 122253, 117411, 115714, 115700, 115671, 115633, 115623, 115616, 115613, 115612, 115606, 115604, 115594, 115593, 115592, 115563, 115382, 112819, 105544, 104027, 102543, 101783, 101607, 100977, 100815, 100357, 97476, 96736, 93505, 89897, 88095, 87741, 85167, 82170, 82130, 81542, 81319, 81314, 81253, 81216, 77004, 73886, 72690, 70535, 69224, 68739, 68061, 68038, 67736, 58641, 58363, 55393, 45464, 45321, 21447, //....GOES ON AND ON FOR A LOT OF IDS....//)) AND (t_d.attribute_id IN (79, 141, 99, 120, 144, 143, 75, 254, 76, 91, 80)) AND (t_d.store_id = IFNULL(t_s.store_id, 0)) UNION ALL SELECTt_d.attribute_id,e.entity_id,t_d.valueASdefault_value,t_s.valueASstore_value, IF(t_s.value_id IS NULL, t_d.value, t_s.value) ASvalueFROMcatalog_product_entity_datetimeASt_d INNER JOINcatalog_product_entityASeON e.row_id = t_d.row_id AND (e.created_in <= '1662008340' AND e.updated_in > '1662008340') LEFT JOINcatalog_product_entity_datetimeASt_sON t_s.attribute_id = t_d.attribute_id AND t_s.row_id = t_d.row_id AND t_s.store_id = 1 WHERE (e.entity_id IN (162712, 162685, 162684, 162683, 162682, 162681, 162680, 162679, 162678, 162677, 162676, 162675, 162674, 162673, 162671, 162670, 162669, 162668, 162667, 162666, 162665, 162664, 162663, 162662, 162661, 162660, 162659, 162658, 162657, 162656, 162655, 162654, 162653, 162652, 162651, 162650, 162649, 162648, 162647, 162646, 162645, 162644, 162643, 162642, 162640, 162639, 162638, 162637, 162636, 162635, 162634, 162633, 162632, 162631, 162630, 162629, 162628, 162627, 162626, 162625, 162624, 162623, 162622, 162621, 162620, 162619, 162618, 162617, 162616, 162615, 162614, 162613, 162612, 162611, 162610, 162609, 162608, 162607, 162606, 162605, 162604, 162603, 162602, 162601, 162600, 162599, 162598, 162595, 162594, 162593, 162592, 162591, 162590, 162589, 162588, 162587, 162586, 162585, 162584, 162583, 162582, 162581, 162580, 162579, 162578, 162577, 162576, 162575, 162574, 162573, 162572, 162571, 162569, 162568, 162567, 162566, 162565, 162564, 162563, 162562, 162561, 162560, 162559, 162558, 162557, 162556, 162555, 162554, 162553, 162552, 162551, 162550, 162549, 162548, 162547, 162546, 162545, 162544, 162543, 162542, 162541, 162540, 162539, 162538, 162537, 162536, 162535, 162534, 162532, 162531, 162530, 162529, 162528, 162526, 162525, 162524, 162523, 162522, 162521, 162519, 162518, 162517, 162516, 162515, 162514, 162513, 162512, 162511, 162510, 162509, 162508, 162506, 162505, 162504, 162503, 162502, 162501, 162500, 162499, 162498, 162497, 162496, 162495, 162493, 162492, 162491, 162490, 162489, 162488, 162487, 162486, 162485, 162484, 162483, 162482, 162481, 162480, 162479, 162478, 162477, 162476, 162475, 162474, 162473, 162472, 162471, 162470, 162469, 162468, 162467, 162466, 162465, 162464, 162463, 162462, 162460, 162459, 162458, 162457, 162456, 162455, 162454, 162453, 162452, 162451, 162450, 162449, 162448, 162447, 162446, 162445, 162444, 162443, 162442, 162441, 162440, 162439, 162438, 162437, 162436, 162435, 162434, 162433, 162432, 162431, 162430, 162429, 162428, 162427, 162426, 162423, //....GOES ON AND ON FOR A LOT OF IDS....//)) AND (t_d.attribute_id IN (106, 72, 83, 101, 73)) AND (t_d.store_id = IFNULL(t_s.store_id, 0));

When i say "goes on and on for a lot of ids" i mean hundrieds and hundrieds of lines of ids. The query is insanely long.

[romainruaud]

@iampolo87 any update ?

can you precise exactly which Magento version is using your client ? You were speaking about 2.4.1 then 2.4.3 ?

[iampolo87]

Its 2.4.3-p1 if i ever mentioned 2.4.1 it was a mistake. And yeah i think the problem is with the DB not the ENV. It seems the project was at first Magento 1.9.3, then migrated to 2.0, and then updated to 2.3.2, and at last to 2.4.3-p1, so the DB is a MESS. Adobe support team and even i encountered many inconsistencies, including several remains of eav_attributes that don't exist. Im guessing some ESuite queries are gettin fuck++ up because of this. So yeah, cleaning the db will take some time, after that i will try ESuite again, if you want you can close this, or leave it open until i try the module again.

[romainruaud]

@iampolo87 any news ?

[github-actions[bot]]

This issue was waiting update from the author for too long. Without any update, we are unfortunately not sure how to resolve this issue. We are therefore reluctantly going to close this bug for now. Please don't hesitate to comment on the bug if you have any more information for us; we will reopen it right away! Thanks for your contribution.

[iampolo87]

Hey @romainruaud i made a fresh instalation of the project in a new computer and after installing ES again, i found out that search isn't working because of this error: {"error":{"root_cause":[{"type":"too_many_clauses","reason":"maxClauseCount is set to 1024"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"magento2_troquer_default_catalog_product_20230207_210950","node":"NSH5xPCQTGyj_n5MYKmYew","reason":{"type":"too_many_clauses","reason":"maxClauseCount is set to 1024"}}],"caused_by":{"type":"too_many_clauses","reason":"maxClauseCount is set to 1024","caused_by":{"type":"too_many_clauses","reason":"maxClauseCount is set to 1024"}}},"status":500} [] []

So yeah there is something weird going on with the ES queries. Not sure if this helps you in any way tho.

[iampolo87]

Hey, well with that clue i found an older issue regarding virtual categories and found out my client is using a shitton of those (pardon my english). So yeah thats probably the thing making the query huge. Will test removing those locally tommorrow.

[romainruaud]

any update @iampolo87 ?

If this is project specific, I'll close this issue.

regards

[github-actions[bot]]

This issue was waiting update from the author for too long. Without any update, we are unfortunately not sure how to resolve this issue. We are therefore reluctantly going to close this bug for now. Please don't hesitate to comment on the bug if you have any more information for us; we will reopen it right away! Thanks for your contribution.