Performance issue and scalability of popular search term cache

[mflott]

Upon upgrading our site to 2.3, we saw a sudden increase in database CPU load. By looking at the processlist we saw a frequent query that took a lot of time:

SELECT DISTINCT COUNT(*) FROM search_queryASmain_table WHERE (main_table.store_id = 1) AND (num_results > 0)

We found that this is part of the 2.3.0 update where they added Popular Search Term Cache (This pull request) The larger the search_query table is, the longer the query takes to complete.

Preconditions (*)

1. Magento Commerce 2.3.4
2. ElasticSearch 6.7.0

Steps to reproduce (*)

1. Make a query in the main search bar.

Expected result (*)

1. The query is expected to finish within a reasonable amount of time.

Actual result (*)

1. The query takes a long time to finish, depending on the size of the search_query table.

On our live site we have about 2,7 million search terms, so when doing queries this puts a lot of strain on the database and the queries are very slow. The main part that takes time is the num_results > 0 part - when we tested without this in our local testing environment we got a query duration of 0,7 sec., while if we included it it takes approx. 23 sec.

[m2-assistant]

Hi @mflott. 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 give me 2.4-develop instance - upcoming 2.4.x release

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

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

• yes
• no

---

• Join Magento Community Engineering Slack and ask your questions in #github channel.

[igorwulff]

Hi @mflott do you know what the impact is of removing the AND (num_results > 0) part? Or do you have any other suggestion to improve this? We are seeing the same issues here with larger datasets.

[mflott]

Hi @igorwulff . I'm not sure if removing the num_results will have any impact, would have to check that part a bit deeper.

Instead of a patch for reverting the PR, we decided to override the execute function in Magento_CatalogSearch/Controller/Result/Index instead and changed it to only use the getNotCacheableResult part, and removed code for the getCacheableResult part. This pretty much changes it back to how it performed before the PR.

[convenient]

Seeing this issue also

[mattijv]

Just got hit by this too on 2.3.5-p1 when a campaign caused a surge in traffic. Hotfixed by doing what @mflott suggested and forcing the controller code take the getNotCacheableResult path.

[mattijv]

Initial testing suggests that adding this index on the search_query table may resolve the issue. I did not observe much increase in write loads, but this is yet to be tested in production.

<index referenceId="SEARCH_QUERY_STORE_ID_NUM_RESULTS" indexType="btree">
    <column name="store_id"/>
    <column name="num_results"/>
</index>

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed after 14 days if no further activity occurs. Is this issue still relevant? If so, what is blocking it? Is there anything you can do to help move it forward? Thank you for your contributions!

[igorwulff]

This issue is still relevant.... a suggestion to fix this was added by @mattijv and should be checked by Magento.