MariaDB upgrade requires table rebuild. Rebuild takes too long. Options?

Question

I've got a MariaDB instance (10.6.8) hosted on Amazon RDS (db.m6g.4xlarge). It had automatic minor upgrades enabled, which unfortunately caused hours of downtime last time it triggered.

In reviewing the logs, it seems the issue was that a few of our biggest tables needed to be rebuilt before the upgrade could be done.

error : Table rebuild required. Please do "ALTER TABLE 'my_table' FORCE" or dump/reload to fix it!

Each was taking multiple hours. The biggest has ~170 million rows and 20 columns.

In the worst case, we can schedule downtime, I guess. But if there's some way to do things differently so that this won't be an issue going forward, that'd be preferable.

So far, I've investigated:

• Add pruning to avoid keeping old, unneeded data in some of those tables. This makes them small enough that rebuilding doesn't cause hours of downtime
• In the future, do and schedule my own minor upgrades, run mysqlcheck ahead of time, and at least know beforehand what tables might need to be rebuilt. This still isn't ideal because the ALTER TABLE 'my_table' FORCE command locks the table, which of course also causes downtime for our application
• Presume the required table rebuild is unlikely to happen again. Implement an automated rebuild that selects chunks of the old table and inserts them into a new table until their contents match. Then delete the old table and rename the new table to the old one's name. This would work--but takes time, and would need to be done each time a table needs to be rebuilt
• Split the big table(s) logically into different tables with the same schema. If data access patterns don't often require querying from more than one at a time, I could then limit the locking done for a rebuild to one (smaller) table at a time, instead of locking to rebuild the big table all at once

Ideally, I'd like to be able to turn on automatic minor version upgrades again, and it "just work" with minimal downtime in a pre-selected window.

Answer 1

You can use an online schema change tool such as pt-online-schema-change or gh-ost to perform a table rebuild without incurring downtime.

At my last job, we ran hundreds of ALTER TABLE commands every week on production MySQL instances during peak traffic, with no downtime.

These tools work very similarly to your third bullet in your question above. They create a copy table, and copy chunks of rows from the original table to the new table, which effectively upgrades data types, and rebuilds indexes. Meanwhile, it also monitors for any new changes in the table, and executes those changes on the copy table as well. This takes longer than the ALTER TABLE method, but since it doesn't block ongoing query traffic and doesn't cause any downtime, it isn't a problem.

Note I said "no downtime" but in fact there is a brief moment at the start and at the end where the online schema change tool needs exclusive access to the table. If you have an uninterruptible stream of transactions at every moment, then the tool cannot get its work done.

Another alternative is to create a replica, do the upgrade on the replica. This takes hours, but it doesn't matter if no applications rely on the replica. Then make the upgraded replica the new source instance, and decommission the former source instance.