500 Error on show more filter results 1.6M skus

[stb]

Preconditions

Single instance (Apache2, Percona, Elasticsearch), 14 CPU's | 64GB RAM | SSD
Magento Version : 2.3.2 CE
PHP: 7.2
Elasticsearch min_heapsize: 16 GB
PHP memory_limit: 2048 MB
1.6M simple products

ElasticSuite Version : 2.8.2

Environment : Developer

We have around 1.6 M sku's with 4-6 active layered navigation filters with each between 100 - 600 entries. When your result set is large > ~ 50.000 skus then the show more button (and search) which does the ajax request to get the additional filters get's a 500 error.

First error was Result window is too large, from + size must be less than or equal to: [100000] but was [1621521].
So we increased the shards to 20 as per #599 & reindexed.

Then we got a PHP Fatal error:
Allowed memory size of 792723456 bytes exhausted (tried to allocate 1319170176 bytes) in /var/www/html/magento2/vendor/elasticsearch/elasticsearch/src/Elasticsearch/Connections/Connection.php on line 289

So we increased the php memory_limit to 2048 MB.

Now we have the exhaustion of the database:
Allowed memory size of 2147483648 bytes exhausted (tried to allocate 20480 bytes) in /var/www/html/magento2/vendor/magento/framework/DB/Statement/Pdo/Mysql.php on line 91

It is obviously the size of the result set combined with the amount of navigation filters. On our 1.9.4 setup we limited the resultset of the filters to 200 for performance reasons but we would obviously prefer not to do that. Any ideas on what might cause the exhaustion? I figured as you guys have experience with larger magento 2 instances you might know the pitfalls.

Thanks!

[rbayet]

Hello @stb,

Any ideas on what might cause the exhaustion? I figured as you guys have experience with larger magento 2 instances you might know the pitfalls.

On a hunch, given the second error, I would blame \Smile\ElasticsuiteCatalog\Model\Layer\Filter\Attribute::addOptionsData where, if the all the attribute options are loaded if the facet sort orders is "Admin sort"...
But this method is reached whether or not in the ajax/Show more context, so that 500 error should pop sooner.

We'll try to look into it.

Regards,

[stb]

Thanks I just checked the sort order for all fields, which is set to default (result count). But you are damn right, it's the sort that makes the problem! I just changed it to admin sort and it loads the filters instantly. The question now is how can it be solved so that it works with sort. I checked with sort by name and relevance enabled - also the same issue. Should I open a new issue for that?

[rbayet]

Thanks I just checked the sort order for all fields, which is set to default (result count). But you are damn right, it's the sort that makes the problem! I just changed it to admin sort and it loads the filters instantly.

I was totally not shooting in that direction, but glad I hit something nonetheless...

The question now is how can it be solved so that it works with sort. I checked with sort by name and relevance enabled - also the same issue. Should I open a new issue for that?

If it's entirely related, there is no need.
So, to be clear, the "show more/search" ajax calls generate 500 errors even when limiting the buckets max size to 200

• when the facet sort order is Result count, Relevance, Name
• but not when the facet sort order is Admin sort

Are your positive about that ?

Regards,

[stb]

If it's entirely related, there is no need.
So, to be clear, the "show more/search" ajax calls generate 500 errors even when limiting the buckets max size to 200

No I have not changed the bucket size. I have just increased the number of shards to 20.
If the facet sort order is set to Admin sort I do not actually see any additional ajax request when pressing the "show more/search" only when the sort order is set to Result count, Relevance or Name do I see an additional ajax request which returns a 500 error.

[rbayet]

Could it be somehow linked to #1581 ie also impacted by 30a8d87 ?

[rbayet]

Managed to reproduce it with both a virtual and physical category containing all of a 42K catalog organised with :

mysql> select type_id, count(*) from catalog_product_entity group by type_id;
+--------------+----------+
| type_id      | count(*) |
+--------------+----------+
| bundle       |        1 |
| configurable |      787 |
| downloadable |        6 |
| grouped      |        1 |
| simple       |    41251 |
+--------------+----------+
5 rows in set (0.06 sec))

Same symptoms as #1581 : an Elasticsearch request is sent with a pagination size of 24827, and the fulltext collection tries to load all the products.

[stb]

Just tested the fix and can also confirm that it is now working. Thank you!