magento2: setup:upgrade is changing indexer mode to `save` from `schedule` slowing it down immensely
I have a fairly large catalog on a website and I’ve noticed that sometimes setup:upgrade takes a very very long time to complete. More than 30 minutes. It’s not always occuring which makes it hard to track but by carefully examining what is happening I noticed that the command is dropping and recreating the database triggers when the indexers are set on scheduled mode. However with the indexers on save mode, the backend takes a very long time to save products and especially categories.
So our only option on big catalogs is to use the indexers on schedule mode.
By examining further I went and forced a check on the mview_state table to see what script is changing the indexer mode during setup:upgrade and so the command threw an exception as expected when executing the following query
UPDATE `mview_state` SET `view_id` = ?, `mode` = ?, `status` = ?, `updated` = '2021-06-30 05:57:46', `version_id` = ? WHERE (state_id=5)
View.php:225, Magento\Framework\Mview\View->unsubscribe()
TriggerCleaner.php:63, Magento\Framework\Mview\TriggerCleaner->removeTriggers()
Installer.php:1726, Magento\Setup\Model\Installer->removeUnusedTriggers()
UpgradeCommand.php:145, Magento\Setup\Console\Command\UpgradeCommand->execute()
Command.php:255, Magento\Setup\Console\Command\UpgradeCommand->run()
Application.php:1009, Magento\Framework\Console\Cli->doRunCommand()
Application.php:273, Magento\Framework\Console\Cli->doRun()
Cli.php:115, Magento\Framework\Console\Cli->doRun()
Application.php:149, Magento\Framework\Console\Cli->run()
magento:36, {main}()
Then I was able to see that the functions \Magento\Framework\Mview\View::unsubscribe \Magento\Framework\Mview\View::subscribe
Are executed for all indexers regardless, which will trigger dropping and re-adding the database triggers.
However dropping a trigger and recreating in a fairly large database is taking a long time to complete causing significant downtime.
It seems I’m not the only one facing this issue https://magento.stackexchange.com/questions/330245/magento-2-indexers-sometimes-switchon-their-own-to-update-on-save-when-config
Preconditions (*)
- 2.4.1
Steps to reproduce (*)
- A fairly large catalog with lots of categories (over 5000) and products (over 60k with over 200 attributes)
- The indexers set on schedule mode.
- Execute setup:upgrade
Expected result (*)
- I expect that setup:upgrade finishes fast and doesn’t recreate the triggers if it’s not needed. If the trigger exists, it shouldn’t drop and recreate it. Only If it does not exist should it create it.
- It should never change the mode from schedule to save. The user has put it for a reason there
Actual result (*)
- setup:upgrade is always recreating the triggers causing huge downtime during the command execution
- sometimes the indexers never revert back to the scheduled mode and stay on save mode
Additional Information
- Confirmation note, AC, and suggested approach https://github.com/magento/magento2/issues/33386#issuecomment-1043290448
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.
- Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
- Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
- Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
- Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.
About this issue
- Original URL
- State: open
- Created 3 years ago
- Reactions: 7
- Comments: 48 (18 by maintainers)
Hi, that only means that you cannot simulate real conditions this way.
On our client’s store we have around 60k products, 500 orders daily, 50-150 concurrent customers browsing the website at all times, 5 people doing data entry, multiple catalog price rules, 5 sources, 11 stocks etc etc.
The indexers are constantly having items on queue to reindex and while it’s not always happening that setup:upgrade is extremely slow I have seen cases where it will take a long time (even 15minutes) contrary to the normal ones when it needs a few seconds.
Usually the queries running in the backend as showcased by mytop are related to MSI indexers running for stock or catalog rule pricing ones in our specific case.
The best way I found to replicate this is to actually have a reindexing process running on an amount of data, that will lock the tables and then try to do a setup:upgrade. Because the lock exists, the process will hang for an indefinite period of time until the lock is released.
But in spite of this side-effect, the underlying issue is still puzzling me.
If I’ve decided to set my indexers to
schedulemode. Why would they need to change to work onsaveand then back toscheduleduring setup:upgrade? There’s nothing to gain by that, it only adds time to the setup:upgrade process.Hello
Due to questions in slack channels about the status of this issue, I think it is better to provide answers here. This post is based on information that I found in several Jira tickets related to this issue and reflects the current status.
Development/fix for this is going on in
ACP2E-963this internal ticket. So after merge related commits can be found by this ticket id. like https://github.com/magento/magento2/search?q=ACP2E-963&type=commitsAs I can see initial development(fix) and testing were already done. Ticket in the preparation for delivery stage. I think it will be merged in
2.4-developsoonThe target release version for this fix is 2.4.6
cc: @coderimus
Hello @ioweb-gr,
We have tried to reproduce the issue in Magento 2.4-develop branch with the following data:
But for us the
setup:upgradenot taking that much time. We have recorded a video related to that. Please have a look and let us know if we have missed anything:https://user-images.githubusercontent.com/51681618/147472638-931ed215-9988-4b1a-b1a9-0ded96009612.mp4
Thanks
The issue has been fixed by the Adobe team in the scope of
ACP2E-963Jira ticket Related commits: https://github.com/magento/magento2/search?q=ACP2E-963&type=commitsAfter an internal discussion and based on multiple complaints we confirmed this issue. For such type of issue is not trivial to describe simple(or end-user) steps to reproduce.
Also, it is hard to predict final performance results. Theoretically, numbers should be better So, as Acceptance Criteria for this issue should be performance measurement before and after
Suggested approach
cc: @ioweb-gr @hostep cc2: @engcom-Hotel @kandy @sidolov @akaplya
@magento I am working on this
We are still experiencing this issue on 2.4.6.
see also https://github.com/magento/magento2/issues/19220
Hi guys, thanks for this fix!
We were struggling with this bug.
Any chance to be released in a Quality Patch before 2.4.6 release?
It would help a lot of merchants!
@kandy: maybe it helps if a senior developer could have a look here? The engcom squad seems to not understand this problem because I think they miss some deeper knowledge about this sort of complicated functionality of Magento.
Hello @ioweb-gr,
We have rechecked this issue and it seems the triggers are still dropping and creating on each
bin/magento setup:upgradecommand. In order to reproduce the issue, we have followed the below steps:Enable the db.log via the following command:
bin/magento dev:query-log:enableDebug the
subscribeandunsubscribemethods from the below class: https://github.com/magento/magento2/blob/adc4105fcfbeee29d534482d8c6d9c5c1a193a0c/lib/internal/Magento/Framework/Mview/View.php#L1Run the
bin/magento setup:upgradecommandWe can see in the below screenshot of db.log triggers are dropping and creating always: Dropping Triggers
Creating Triggers
In the case of a huge catalog and categories, it might happen that the
bin/magento setup:upgradecommand will take time. Hence confirming the issue.Thanks
We are reopening this issue for further analysis.
@lauraseidler
According to Jira ticket,
ACP2E-963Target release is 2.4.6Hi, what do you mean it’s working fine?
Let’s take it one step at a time.
Did you verify that during setup upgrade triggers are dropped and recreated?