Asynchronous indexing can break large servers

[gamort]

Large stores with extremely active product databases can suffer many related problems with indexers not being kept up to date. Products can disappear from the website, product data can be out of date on the website, excessive cache flushing can cause huge server load spikes.

This is due to the manner in which triggers are created by the core magento code in

magento2/lib/internal/Magento/Framework/Mview/View/Subscription.php

Line 201 in 9544fb2

switch ($event) {

INSERT INTO will insert a new record regardless of whether one exists or not.
REPLACE INTO will as the name says, replace a record - and in this case it will generate a new version_id since that is an auto_increment field.

Preconditions (*)

1. An extremely large database. Example 500,000 products
2. An active update schedule

Steps to reproduce (*)

1. Create a sample database with 500,000 products
2. Enable Asynchronous indexing
3. Reduce crons to every 10 minutes
4. Run a mass script to change the inventory for the products every 30 seconds. So within 10 minutes, you will have made 10 million updates

Expected result (*)

1. When the indexer runs, it will index the latest product information once for each index

Actual result (*)

It will actually try to index each product 20 times, once for each update

Additional information to reproduce the issue

1. Change the price of the product with SKU 24-MB01 20 times via API rest/all/V1/products/24-MB01
2. Doing the price change data inserted in the table catalog_product_price_cl
3. Adjust the DEFAULT_BATCH_SIZE in file lib/internal/Magento/Framework/Mview/View.php with 10 from 1000.
4. Enable the DB logs via the bin/magento dev:query-log:enable command.
5. grep the text to get the SQL query on table catalog_product_price_cl. Please find attached the screenshot of db.log:

The query always returns entity_id 1 each time.

In this case, it seems that entity_id 1 will process many times, instead of only 1 time, and should be on the latest one.

Please provide Severity assessment for the Issue as Reporter. This information will help during Confirmation and Issue triage processes.

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.

[m2-assistant]

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

[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. Thank you for your contributions.

[hostep]

Issue makes sense I think, removing stale issue label...

@sidolov: would be great if somebody could verify if this is still the case in 2.4-develop (since the referenced code was refactored a couple of months ago) and then assign priorities.

[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!

[hostep]

Sigh ...

@sidolov, @ihor-sviziev : can we please delete this stalebot crappy thing?
What's the use of people opening issues if nobody looks at them and then they get auto closed? That's just depressing and might scare people away from opening more issues in the future ...

(not saying that this issue is valid, but it feels like it should at least be investigated instead of auto closed)