Catalog Category Indexing on DISABLED Store View takes a long time. Magento 2.4.2. Related issue -> #25199

[neddy236]

Description

This is somewhat related to #25199 .
While magento tries to reindex indexer -> 'catalog_category_product' it runs a query for 'catalog_category_product_index_store{{store_id}}_tmp .

This works fine for the first 2 store views but for the third one which is also disabled, it can take up to 10-15 minutes to complete. The first 2 take like 30 seconds max. (Store3 is the disabled one https://prnt.sc/12cya22)

While this is happening, no transactions related to the product can be completed in the database, everything results in 504 timeout. (Api or change in the backend).

Preconditions (*)

Magento 2.4.2
PHP 7.4
MariaDB 10.4.17
22k Products (as of writing this issue)
1 website with 3 store view. Last store view is disabled.

Steps to reproduce (*)

bin/magento indexer:reindex catalog_category_product

or

let the cron run reindex_all_invalid job. (If its invalidated it will reindex it and the problem will show it self)

Expected result (*)

Not sure if the indexing needed to be done in the first place for a disabled store view,
but admins should never notice a hung like this in the backend while updating product.

Actual result (*)

Products could not be created/updated in the backend nor through the API (rest/v1/products/}

---

• [S1]Severity: Affects critical data or functionality and forces users to employ a workaround.

[m2-assistant]

Hi @neddy236. 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.

Please, add a comment to assign the issue: @magento I am working on this

---

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

⚠️ According to the Magento Contribution requirements, all issues must go through the Community Contributions Triage process. Community Contributions Triage is a public meeting.

🕙 You can find the schedule on the Magento Community Calendar page.

📞 The triage of issues happens in the queue order. If you want to speed up the delivery of your contribution, please join the Community Contributions Triage session to discuss the appropriate ticket.

🎥 You can find the recording of the previous Community Contributions Triage on the Magento Youtube Channel

✏️ Feel free to post questions/proposals/feedback related to the Community Contributions Triage process to the corresponding Slack Channel

[neddy236]

Please close this. We investigated a little bit more and we found out that the "optimizer_search_depth" on our mariaDB config was the issue here. After changing it to the value of 4 it got down to 27 seconds more or less.

[mackieee]

Please close this. We investigated a little bit more and we found out that the "optimizer_search_depth" on our mariaDB config was the issue here. After changing it to the value of 4 it got down to 27 seconds more or less.

If you don't mind me asking (I confess I've not researched this value) but what was the value before? Was this higher/lower than 4 once set?