[4.x]: CRaft 4 upgrade

[wmdhosting]

What happened?

Description

I’ve tried and some other devs ~10 times to upgrade a large Craft 4 multi-site to Craft 5. During the upgrade Craft triggers a resave of all entries. Our install reports ~30M entries across sites, and the process keeps dying (connection gets dropped). The longest run made it ~16 hours before failing.

Is there a supported way to defer the upgrade-triggered resave to the queue instead of doing it inline or other you have advice ?

Craft CMS version

4.x

PHP version

No response

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

[linear[bot]]

CMS-1647

[brandonkelly]

Are you running the upgrade locally, or on a remote server?

For large installs, you’ll have much better luck running it locally, and then uploading a pre-upgraded database backup to your remote server.

[wmdhosting]

we tryed only localy..

We done entrification on Craft 4..

[brandonkelly]

Any chance you can share a database dump and Composer files with us – or access to a server? [email protected]

[FlorisDerks]

I've recently upgraded a Craft installation from v4 to v5 with ~9 million entries. Without any changes, the resave-job took about 12 hours locally, and even more on our production environment (with AWS Aurora RDS MySQL database). I believe it was around 18 hours on a db.r6g.large (which was only using ~30% of it's cpu).

The bottleneck turned out to be all separate update-commands that are executed one at a time. We've fixed that by batching the update-queries 250 at a time, which sped up the resave process to ~1 hour instead of 12+.

These are the changes I made:

--- a/src/migrations/BaseContentRefactorMigration.php
+++ b/src/migrations/BaseContentRefactorMigration.php
@@ -107,6 +107,8 @@
         $totalLen = strlen($total);
         $i = 0;
 
+        $updateQueries = [];
+
         foreach (Db::each($query) as $element) {
             $i++;
             echo sprintf(
@@ -154,15 +156,29 @@
                 $content[$layoutElementUid] = $value;
             }
 
-            // don't call $this->update() so it doesn't mess with the CLI output
-            Db::update(Table::ELEMENTS_SITES, [
-                'title' => $element['title'] ?? null,
-                'content' => $content ?: null,
-            ], ['id' => $element['id']], updateTimestamp: false, db: $this->db);
+            $updateQueries[] = $this->db->createCommand()->update(
+                Table::ELEMENTS_SITES,
+                [
+                    'title' => $element['title'] ?? null,
+                    'content' => $content ?: null,
+                ],
+                ['id' => $element['id']],
+                [],
+                false
+            )->getRawSql();
 
+            if (count($updateQueries) >= 250) {
+                $this->db->pdo->exec(implode(';', $updateQueries));
+                $updateQueries = [];
+            }
+
             echo " done\n";
         }
 
+        if (count($updateQueries) > 0) {
+            $this->db->pdo->exec(implode(';', $updateQueries));
+        }
+
         // make sure the elements’ fieldLayoutId values are accurate
         if ($fieldLayout) {
             $this->update(Table::ELEMENTS, [

This is probably not the most clean solution, as it uses the direct PDO object to execute the queries, but it worked for us.

[brandonkelly]

@FlorisDerks Mind creating a PR for that?