make commerce/upgrade migration re-startable

[Anubarak]

Currently a commerce/upgrade migration takes ~80h for us.
This is unacceptable for a client because it locks certain table so users cannot interact at certain points -> we would need to take down the entire site during that time.

It would be highly appreciated if the migration was re-tryable.
For example instead of using one huge transaction you could split the different sub-processes into different transactions

### before 
$db->transaction(function() {
   //.... run everything here
});

### after 
$db->transaction(function() {
    $this->stdout("Ensuring we have all the required custom fields…");
    $this->_migrateAddressCustomFields();
    $this->stdoutlast('Done.', Console::FG_GREEN);
});

$db->transaction(function() {
    $this->stdout("Updating the store location…");
    $this->_migrateStore();
    $this->stdoutlast('Done.', Console::FG_GREEN);
});

Furthermore it would be awesome if the address migration (takes longest) could be done in batches so we could start this migration via cron every night and stop it later. This would of course lead to several issues when certain addresses cannot be found but at least the shop would run and not kinda freeze for several days.

Address parsing issues could then be resolved on the fly as well #3249
At the moment we try to run this migration on our test system for 3 weeks straight now because there were many user input issues that caused exceptions.

[lukeholder]

Hi @Anubarak

Can you confirm which versions you are upgrading from and to? (Are you going straight to the latest 4.x release which has the performance improvements?)

How many orders, customers, and addresses do you have? We have some very large customer DBs, which we have tested with the latest fixes in place and did not see 80+ hours.

If you can send the pre-updatyed DB to [email protected] that would be appreciated.

[Anubarak]

Thank you for the fast reply, I have the latest version from this task
#3286

we migrated from Craft CMS 3.6 to Craft CMS 4.5 (and did the required commerce 3.7 [forgot the exact version] migration in between)

I will need to ask our client for permission to send the DB backup due to data policy here.
Currently we have
433601 customers
585733 addresses
485698 orders

not too much but saving so many elements takes it's time. I deactivated my custom save events as well.
Do you want the backup before or after the 3.7 migration? (with or without the v3userId columns)

Edit: Just updated to 4.3.2, still takes much time ~300.000s -> ~80h

[lukeholder]

@Anubarak, thanks for those numbers. For the record, we had a large site with over 500,000 addresses and after the performance fixes they reported:

The commerce migration completed in about 3 hours + 1 hr for the craft migration.

The latest 3.x backup would be good. We can migrate and update it.

[Anubarak]

Unfortunately the client does not allow it and instead refused the Craft 4 upgrade due to other reasons.
Since we don't have other migrations of that kind the discussion can be closed.
Thanks for your help anyway