Magento2: Layered navigation huge perfomance hit when filtering with multiple attributes

[rickschippers]

Preconditions (*)

1. 2.2.8 / 2.3.1

Steps to reproduce (*)

1. Find a category with multiple attributes and a relatively large set of products including configurable products that have multiple simple products linked
2. Select values for multiple attributes in layered nav

Expected result (*)

1. The results to show up in a reasonable amount of time

Actual result (*)

1. The results show up extremely slow

Additional info

A change was made in the way layered nav queries results Specifcally a join was removed as can be seen here: https://github.com/magento/magento2/issues/12240#issuecomment-455086197

This change got added in magento 2.2.8 / 2.3.1. It seems to fix the issue mentioned https://github.com/magento/magento2/issues/12240, but it comes with a huge performance impact.

When you have a fairly large dataset and start filtering with multiple attributes, the resulting query has become extremely slow. Resulting in pages that loaded in less than a second, now loading in 30+ seconds.

When you add an extension like Amasty Improved Layered Navigation the issue gets magnified since that seems to run the query mulitple. But even with vanilla magento 2 the performance hit is extreme.

An example query FROM 2.2.7 (this is what is getting inserted in the temp search table)

SELECT DISTINCT search_index.entity_id, (((0) + (0) + (0) + (0)) * 1) AS score
FROM catalog_product_index_eav AS search_index
         INNER JOIN catalog_product_index_eav AS style_group_filter
                    ON search_index.entity_id = style_group_filter.entity_id
                       AND search_index.source_id = style_group_filter.source_id
                        AND style_group_filter.attribute_id = 228
                        AND style_group_filter.store_id = 1
         INNER JOIN catalog_product_index_eav AS quality_filter
                    ON search_index.entity_id = quality_filter.entity_id
                        AND search_index.source_id = quality_filter.source_id
                        AND quality_filter.attribute_id = 234
                        AND quality_filter.store_id = 1
         INNER JOIN cataloginventory_stock_status AS stock_index
                    ON stock_index.product_id = search_index.entity_id AND stock_index.website_id = 0 AND stock_index.stock_status = 1 AND
                       stock_index.stock_id = 1
         INNER JOIN cataloginventory_stock_status AS sub_products_stock_index
                    ON sub_products_stock_index.product_id = search_index.source_id AND sub_products_stock_index.website_id = 0 AND
                       sub_products_stock_index.stock_status = 1 AND sub_products_stock_index.stock_id = 1
         INNER JOIN catalog_product_index_eav AS visibility_filter
                    ON search_index.entity_id = visibility_filter.entity_id AND visibility_filter.attribute_id = 96 AND
                       visibility_filter.value in ('2', '4') AND visibility_filter.store_id = '1'
         INNER JOIN catalog_category_product_index_store1 AS category_ids_index
                    ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1'
WHERE (search_index.store_id = '1')
  AND (category_ids_index.category_id = 150)
  AND (style_group_filter.value IN ('331', '5130', '539', '938'))
  AND (quality_filter.value IN ('12055', '610', '337'))

And the same page in 2.2.8

SELECT DISTINCT search_index.entity_id, (((0) + (0) + (0) + (0)) * 1) AS score
FROM catalog_product_index_eav AS search_index
         INNER JOIN catalog_product_index_eav AS style_group_filter
                    ON search_index.entity_id = style_group_filter.entity_id
                        AND style_group_filter.attribute_id = 228
                        AND style_group_filter.store_id = 1
         INNER JOIN catalog_product_index_eav AS quality_filter
                    ON search_index.entity_id = quality_filter.entity_id
                        AND quality_filter.attribute_id = 234
                        AND quality_filter.store_id = 1
         INNER JOIN cataloginventory_stock_status AS stock_index
                    ON stock_index.product_id = search_index.entity_id AND stock_index.website_id = 0 AND stock_index.stock_status = 1 AND
                       stock_index.stock_id = 1
         INNER JOIN cataloginventory_stock_status AS sub_products_stock_index
                    ON sub_products_stock_index.product_id = search_index.source_id AND sub_products_stock_index.website_id = 0 AND
                       sub_products_stock_index.stock_status = 1 AND sub_products_stock_index.stock_id = 1
         INNER JOIN catalog_product_index_eav AS visibility_filter
                    ON search_index.entity_id = visibility_filter.entity_id AND visibility_filter.attribute_id = 96 AND
                       visibility_filter.value in ('2', '4') AND visibility_filter.store_id = '1'
         INNER JOIN catalog_category_product_index_store1 AS category_ids_index
                    ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1'
WHERE (search_index.store_id = '1')
  AND (category_ids_index.category_id = 150)
  AND (style_group_filter.value IN ('331', '5130', '539', '938'))
  AND (quality_filter.value IN ('12055', '610', '337'))

The first two inner joins have a condition removed
AND search_index.source_id = style_group_filter.source_id
AND search_index.source_id = quality_filter.source_id

If you remove the DISTINCT from the query to see the number of rows the db is working with. In this example with my dataset the 2.2.7 variant is working with ~36k rows while the 2.2.8 variant is working with ~47 million rows. So I'm not surprised it's that much slower.

The end results in my tests is identical - just that the new variant is much much slower.

[m2-assistant[bot]]

Hi @rickschippers. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• [ ] Summary of the issue
• [ ] Information on your environment
• [ ] Steps to reproduce
• [ ] Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento-engcom-team give me 2.3-develop instance - upcoming 2.3.x release

For more details, please, review the Magento Contributor Assistant documentation.

@rickschippers do you confirm that you was able to reproduce the issue on vanilla Magento instance following steps to reproduce?

• [ ] yes
• [ ] no

[m2-assistant[bot]]

Hi @surbhi-ranosys. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

• [ ] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• [ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• [ ] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• [ ] 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and _stop verification process here_!
  

• [ ] 5. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

[marcusirgens]

We ended up here after some thorough and painful hours of debugging. This issue has caused huge performance hits and downtime for several of our 2.3 clients due to completely locked databases after hits on catalogsearch endpoints.

As the OP mentions, the issue seems to be that the generated joins on the catalog_product_index_eav causes "recursion" (joins on catalog_product_index_eav expands the search result, and any subsequent join also joins on the tree that was expanded by previous catalog_product_index_eav joins)

For one of our clients with complex configurable products, a simple category restricted search can yield queries that iterate over literally hundreds of millions of rows (tested by extracting the inner select from the insert into tmp table queries and removing the DISTINCT clauses).

Example: A search with zero attributes: 12,771 rows evaluated
With one attribute (visibility, which is almost always included): 317,587 rows
Two attributes: 1,280,715 rows
Three attributes: 196.570.467 rows

These queries can take a _very_ long time.

[Luwdo]

In the last 2 weeks I have pushed out a 2.2.8 community and a 2.2.8 enterprise upgrades and both of these pushes resulted in about a day of down time because of this issue.

The community site I was able to solve by reducing the number of searchable attributes to just name and description as well as a pruning script that changed products that where visible in catalog that where not part of a category to be only visible in search only. This ended up reducing the join results of this search_tmp insert to be small enough to actually run.

I used the following to remove redundant visibility to reduce the result set of these joins.

This query to show the redundant data:

Select a.* from catalog_product_entity_int a 
JOIN catalog_product_entity e on e.entity_id=a.entity_id and e.type_id='simple' 
where a.attribute_id=[FIND THE ATTRIBUTE ID FOR PRODUCT VISIBILITY IN YOUR STORE] 
and (select count(*) from catalog_product_relation as r where r.child_id=a.entity_id) > 0
and (select count(*) from catalog_category_product as cat where cat.product_id=a.entity_id) = 0
and a.`value` in ('2', '4');

This query to fix:

Update catalog_product_entity_int a
JOIN catalog_product_entity e on e.entity_id=a.entity_id and e.type_id='simple'
SET a.`value` = 3
where a.attribute_id=[FIND THE ATTRIBUTE ID FOR PRODUCT VISIBILITY IN YOUR STORE] and (select count(*) from catalog_product_relation as r where r.child_id=a.entity_id) > 0
and (select count(*) from catalog_category_product as cat where cat.product_id=a.entity_id) = 0
and a.`value` in ('2', '4');

The enterprise site, not so easy, they have thousands of configurable products which lead me deeper into this issue and here is what I found:

In this commit:
https://github.com/magento/magento2/commit/c28d5dfa78fab6725e1dd077762fc445c0e75c38#diff-0889ae63e2dd93e531450bf853b09810L144

In the file: app/code/Magento/CatalogSearch/Model/Search/FilterMapper/CustomAttributeFilter.php:144

They removed a join condition that in an attempt to fix an issue causing certain combinations of layered nav filters resulting in no results.

The thing that was not addressed was that removing this join condition allows configurable children attribute records in catalog_product_index_eav to also be selected because they have the same entity_id as the parent product.

Also you will notice that at the bottom of this commit there is a comment that doing this will break sites with large volumes of configurable products.

Here is the original issue that is marked as closed:
https://github.com/magento/magento2/issues/12240

With that being said, I would rather have my sites database not deadlocking and crashing then have a specific layered nav filter combination miss-behaving sometimes.

I used the following patch
If you don't know how to apply patches there are some instructions here:
https://support.magento.com/hc/en-us/articles/360005484154-Create-a-patch-for-a-Magento-2-Composer-installation-from-a-GitHub-commit

File Name: patches/composer/search-tmp-population-configurable-attribute-deadlocks.patch

--- a/Model/Search/FilterMapper/CustomAttributeFilter.php
+++ b/Model/Search/FilterMapper/CustomAttributeFilter.php
@@ -140,6 +140,7 @@ class CustomAttributeFilter
     {
         return [
             sprintf('`%s`.`entity_id` = `%s`.`entity_id`', $mainTable, $joinTable),
+            sprintf('`%s`.`source_id` = `%s`.`source_id`', $mainTable, $joinTable),
             $this->conditionManager->generateCondition(
                 sprintf('%s.attribute_id', $joinTable),
                 '=',

composer.json patch reference:

    "extra": {
        "magento-force": "override",
        "composer-exit-on-patch-failure": true,
        "patches": {
            "magento/module-catalog-search": {
                "This is a fix for an issue introduced by an attempt to fix multi custom filter results that causes deadlocks on insert search_tmp tables": "patches/composer/search-tmp-population-configurable-attribute-deadlocks.patch"
            }
        }
    }

Remember to call

composer update --lock

To get the patch referenced in your composer.lock file

Here is a reference to another issue that is probably the same problem:
https://github.com/magento/magento2/issues/15545

[marcusirgens]

@luwdo that sounds very familiar to us. Unfortunately we are unable to reduce the amount of visible filters for many of our clients. Thanks for the patch, I’ll see how applying it affects performance.

I’ve been working on replicating this issue with sample data today, but it’s a slow process with the performance testing data due to the huge indexes.

We’ve really noticed these issues the last days as some of our clients have had search result pages indexed by crawlers. That’s brought dedicated MariaDB servers down repeatedly (8 physical cores at 100% until the entire site went unresponsive).

This issue desperately needs some TLC - it’s been a massive cause of frustration since we updated clients after the SQL injection vulnerability. However, the full text search seems to be deprecated, and I’m worried that it’s considered low priority.

Algolia Search uses this temporary storage method as the fallback solution, for instance, so we should be able to assume it will be used for a while longer.

edit: Those last paragraphs were directed at the eng team, not at the previous poster.

[Luwdo]

As an additional note. We have not seen this issue on any of our clients running elastic search.

@marcusirgens I second your frustration, I was looking at what they were trying to solve in 2.2.7 and its complicated. Part of me wants to say that issues like this can be caught by running the changes against a copy of anonymized live site data from partners with complicated catalogs. The patch I provided has solved the growing search tmp inserts for our sites, we had to kill any of these queries that were started prior to the patch. QA has not been able to find any side effects essentially undoing part of a bug fix. This could be because we have amasty layered nav which has its own logic for multi filter.

[marcusirgens]

@luwdo, looking at stack traces and some light IDE magic makes it look like the ElasticSearch search implementations aren’t using this search query builder, so that makes sense and is also what we are experiencing. We have been using Algolia for the bulk of our clients up until now, though, so I don’t have a lot of data points to lean on.

[rickschippers]

I have a patch available that reverts back to the previous behaviour by re-adding the removed join condition:

https://bitbucket.org/muntzamersfoort/magento2-patches/raw/0ac032f30a6ab8b3f56b63d06db4c1649923f499/layered-navigation-performance.patch

I use https://github.com/cweagans/composer-patches to apply it. To use it just composer require cweagans/composer-patches. And add the patch in the extra section of composer.json. It will then auto-apply on a composer install / update.

```
"extra": {
"magento-force": "override",
"patches": {
"magento/module-catalog-search": {
"Layered navigation performance": "https://bitbucket.org/muntzamersfoort/magento2-patches/raw/0ac032f30a6ab8b3f56b63d06db4c1649923f499/layered-navigation-performance.patch"
}
}
}

[longcreek]

Encountered same. M 2.3.2 CE - Shared Host. Configurable Products (90) with approx 2000 simple. 9 Attribute Filters displayed. Short term fix (site in development) reduced shown Filters down to 2. Finally getting things back to square one and stable.

Question: Does migrating to ElasticSearch solve this? If so, can anyone guide me through. I've reviewed dev.docs but still sketchy on exact steps to setup.

I expect to migrate the site to VPs or possible Dedicated as it grows. A2 hosting. May visit Nexcess.

[mustdobetter]

Thank you @rickschippers for the patch. This does indeed seem to bring back speeds to acceptable levels.

@magento-engcom-team, this seems quite a sizeable and fundamental issue which will be affecting all installations running on 2.2.8+ and 2.3.1+. Can we please get somebody assigned to verify and progress this?

[longcreek]

M 2.3.2. Shared Host (A2). Deployed patch yesterday using; composer require cweagans/composer-patches.

Added to composer.json:

"extra": {
"magento-force": "override",
"patches": {
"magento/module-catalog-search": {
"Layered navigation performance": "https://bitbucket.org/muntzamersfoort/magento2-patches/raw/0ac032f30a6ab8b3f56b63d06db4c1649923f499/layered-navigation-performance.patch"
}
}
}

Previously reduced number of attributes included in layered nav and search. Did not change those back yet. Noticed immediate performance with slow navigation.

Trying to isolate what triggered this hoping to have solved one problem.

Logged in viewed CPU Performance;

Any guidance?

[magento-engcom-team]

:white_check_mark: Confirmed by @sdzhepa
Thank you for verifying the issue. Based on the provided information internal tickets MC-19397 were created

Issue Available: @sdzhepa, _You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself._

[mertgokceimam]

This issue being open and not receiving a fix from Magento team in last 3 months 15 days is not acceptable.

Enterprise customers deserve better fix implementations from Magento team specially on issues that cost merchants income. It is not acceptable that merchants are forced to go on with issues that could effect their revenue.

Otherwise Magento will seriously see many merhants moving to other solutions that works better and have less maintenance costs.

[sdzhepa]

Hello @mertgokceimam

I am very sorry that you faced such an issue.

Current repository and issue tracker aimed at Magento Open Source version only and the main focus is community contribution/collaboration. It described in Issue reporting guidelines and it is a part of the issue report template:

Verify, that the issue you are about to report does not relate to the Magento Commerce. GitHub is intended for Magento Open Source users to report on issues related to Open Source only. There are no account management services associated with GitHub. You can report Commerce-related issues in one of two ways:

• You can use the Support portal associated with your account
• If you are a Partner reporting on behalf of a merchant, use the Partner portal.

So, I would recommend to report this issue through the support channel related to your enterprise account.
From my side, I will do everything that I can to confirm this issue and share this information with internal teams.

[mertgokceimam]

We received a patch from Magento support on this issue , we are pushing it to staging server and conduct tests

[TomSchlo]

Did you test the patch? Does it work? We have the same problem.

[mertgokceimam]

@TomSchlo yes the patch is on our live instance now and it fully resolved our issues. If you contact Magento support , they will surely pass the patch to you.

[Maddesto]

Did you test the patch? Does it work? We have the same problem.

Recently i have got this issue on one of our projects with a big amount of products. I have applied this patch, and after this query processed faster, but layered navigation result became not correct, number products in filter blocks is not equal to actual number products in listing. So patch solves with slow query but creates new issue with listing

[barryvdh]

Why isn't the patch publicly available? I need it also.

[euriskotech]

Is there any news on this issue being patched, I did a full M1 to M2 migration then hit the issue so stayed on M1 for the moment but under pressure to move the M2

[paradoxos]

Same issue right here. We migrated to M2 and postponing the live version for our client until we found a fix for this.

[facetimer]

@mertgokceimam do you have any bug with this patch?
@Maddesto do you resolve your issue?

Where we can find this patch?

[mertgokceimam]

@facetimer no it works accurate for us, if you are EE customer contact support and request following patch MDVA-20708_EE_2.2.9_v1.composer.patch

[ukguy]

Do we have a confirmed working patch for this yet? (CE edition) It keeps crashing the database

[ukguy]

It seems from early tests that this does not happen when using elasticsearch

[euriskotech]

@ukguy Still not found a working solution and no patch for CE issued yet, very frustrating

[rickschippers]

@euriskotech @ukguy see my comment from June 26th. It's not a perfect solution, but it'll keep your server from breaking. Or switch to elasticsearch, that also fixes it.

[PascalBrouwers]

The issue is that the query is doing an ‘infinite join’ during execution
And the culprit it the visibility_filter that is using catalog_product_index_eav instead of using the normal eav table for it.

I don't mean any disrespect but the implementation of the catalog_product_index_eav to fix the problem with row_id / entity_id could have been better.

I hope the Commerce patch will be put into Open Source soon.
For a quick fix you could try the patch mentioned by rickschippers: https://github.com/magento/magento2/issues/22587#issuecomment-505895318

[mertgokceimam]

@PascalBrouwers the patch you link is not solving the issue fully correctly. Even the initial patch we received from Magento team for this issue is not 100% accurate , there are other cases where this issue could still pop up , we supplied detailed report to Magento team and awaiting an updated patch

For the time being the best case we see is to automatically kill those queries. Here is the pt-kill command we use as we deamonize this so it constantly checks and kills hanging specific queries

pt-kill -udatabaseuser -hyourhost -Ddatabasename --ask-pass --busy-time 30 --match-state 'Sending data' --wait-after-kill 3 --kill --print --daemonize > /tmp/pt-kill.log

[PascalBrouwers]

What? Even the Magento Commerce patch is not 100% correct? pfffff.

[alexandrosk]

@mertgokceimam
Hello! Can I please have the patch for the commerce edition? you can use the email that is on my account page, thanks! :) really in need of that

[joaolisboa]

@alexandrosk you have a patch linked above - https://github.com/magento/magento2/issues/22587#issuecomment-516096993

[mustdobetter]

@joaolisboa, @alexandrosk Be aware that although the patch supplied in this thread solves the performance issues, it breaks functionality when filtering using composite products including grouped, bundled, and configurable. By applying this patch, not all results will be shown when filtering on these product types. So this is by no means a definitive solution.

[joaolisboa]

@mustdobetter Thanks for pointing out the issue. Fortunately this isn't an issue for me since I have no grouped/bundled/configurable products. The performance hit without the patch is also so bad that it was absolutely necessary to have this patch for us during this time of year.

[kunzi]

This is a huge issue for us in M2.3.3. We also have lots of bundle products, so I guess the mentioned patch is not going to be too helpful for us. Did not have this issue with M2.2.3. Right after updating to 2.3.3 this started happening.

[kunzi]

To continue the story, we fixed this problem by changing the catalog search to use elasticsearch6+ in Magento configurations. It resulted even complicated layered navigation combinations to happen very quickly.

[ukguy]

That's true, after many many hours (at great expense for our client) we also resolved this with Elasticsearch back in October. Magento 2.3.2 does not work well with layered navigation and catalog search set to mysql.
Also note not to use mariaDB 10.3 which also has issues, causing catalog search to never complete.

[insaurabh]

@rickschippers , Thanks for this patch.

Is this patch work for Magento 2.3.0

As i am getting

Gathering patches for dependencies. This might take a minute.
  - Installing magento/module-catalog-search (101.0.0): Downloading (100%)         
  - Applying patches for magento/module-catalog-search
    https://bitbucket.org/muntzamersfoort/magento2-patches/raw/0ac032f30a6ab8b3f56b63d06db4c1649923f499/layered-navigation-performance.patch (Layered navigation performance)
   Could not apply patch! Skipping. The error was: Cannot apply patch https://bitbucket.org/muntzamersfoort/magento2-patches/raw/0ac032f30a6ab8b3f56b63d06db4c1649923f499/layered-navigation-performance.patch

[akosglue]

such a fix can help as well: https://github.com/magento/magento2/pull/20971/commits/c5c5e418e0af9a685c12fe7800477038882ed7c8

additionally to the patch: https://gist.github.com/erikhansen/a813f8cf9fe8e9836eea467bdd205659

this patch gave me a mysql error: column not found
https://github.com/magento/magento2/issues/22587#issuecomment-505895318

[j-lloyd-slc]

Still a problem in 2.3.4. Dealt with days of random site crashes.

Installing Elastisearch seems to be the solution, but that creates new dependencies for certain extensions.

[duckchip]

What's the status of this ticket?

[rickschippers]

@duckchip my guess - it'll be as is, since the mysql search part has been removed in favor of elasticsearch in 2.4.

[gabrieldagama]

Closing this since Magento 2.4 doesn't support MySQL search anymore. If you believe a similar problem with ElasticSearch implementation, please, feel free to open another issue.

Thanks!