2

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.

5
  • Was it 10.6.8 before the upgrade? Or after? What was the other version? Please provide SHOW CREATE TABLE for the table that required the rebuild. Complain to Amazon.
    – Rick James
    Apr 6, 2023 at 20:29
  • I see notes in earlier releases about temporal types and collations; do you think that either of these caused the Alter?
    – Rick James
    Apr 6, 2023 at 20:32
  • It was 10.6.8 before the upgrade. The version after was 10.6.12. I'm hesitant to post SHOW CREATE TABLE output, but these were all basic tables with no foreign keys, triggers, etc. Apr 7, 2023 at 13:30
  • We had some ugly inefficient queries being run at load for a few days (weeks before the upgrade) that led to us hard rebooting it a few times. My assumption is that's what flagged the tables to be rebuilt. Apr 7, 2023 at 13:32
  • What ENGINE is being used on the tables?
    – Rick James
    Apr 7, 2023 at 22:41

1 Answer 1

2

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.

3
  • Note: I used pt-online-schema-change with MySQL, not MariaDB. These are not the same product, and they are not fully compatible. It's possible that the tool works with MariaDB, but I can't say from experience. Mar 29, 2023 at 14:59
  • It's working for me (OP, using MariaDB). gh-ost would not, due to requiring more permissions than RDS would allow. I tried its docs for RDS, but they were more focused toward Aurora. Mar 29, 2023 at 20:53
  • RDS is for tests or toy projects, as far as I can tell. I would never use RDS for a project for which performance or limited downtime was important. Mar 29, 2023 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.