search

magento / magento2

Prior to making any Submission(s), you must sign an Adobe Contributor License Agreement, available here at: https://opensource.adobe.com/cla.html. All Submissions you make to Adobe Inc. and its affiliates, assigns and subsidiaries (collectively “Adobe”) are subject to the terms of the Adobe Contributor License Agreement.
http://www.magento.com
Open Software License 3.0
11.54k stars 9.32k forks source link

setup:upgrade is changing indexer mode to `save` from `schedule` slowing it down immensely #33386

Open ioweb-gr opened 3 years ago

ioweb-gr commented 3 years ago

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 (*)

  1. 2.4.1

Steps to reproduce (*)

  1. A fairly large catalog with lots of categories (over 5000) and products (over 60k with over 200 attributes)
  2. The indexers set on schedule mode.
  3. Execute setup:upgrade

Expected result (*)

  1. 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.
  2. It should never change the mode from schedule to save. The user has put it for a reason there

Actual result (*)

  1. setup:upgrade is always recreating the triggers causing huge downtime during the command execution
  2. sometimes the indexers never revert back to the scheduled mode and stay on save mode

Additional Information

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