MySQL 5.7升级至8.0：生产RDS实例修改行格式相关问题咨询

Great question—let’s break this down step by step since you’re dealing with production RDS MySQL instances and large tables, which requires careful consideration. I’ve worked through similar scenarios with RDS, so here’s what you need to know:

First, a quick recap of the row formats: COMPACT stores partial large column data directly in the row (with the rest in overflow pages), while DYNAMIC only stores pointers to overflow pages for large columns—this is far more efficient for tables with lots of text/blob fields.

When you run that ALTER statement on an InnoDB table in MySQL 5.7, here’s the behind-the-scenes:

• By default, this triggers a table rebuild (since row format is a physical storage attribute). But with the right DDL options (more on that later), it can be done in-place without copying the entire table to a temporary table.
• InnoDB will rewrite every row in the table to conform to the DYNAMIC format, updating the table’s metadata to reflect the new row format.
• On RDS, this operation is managed by the RDS service, so it won’t consume your local application resources, but it will use the instance’s CPU, IO, and memory—critical to monitor in production.

Short answer: Yes, if you take the right precautions. Here’s why and how:

• RDS supports native online DDL: MySQL 5.7 introduced robust in-place DDL operations, which means you can run this ALTER without taking the table offline for long periods.
• Key precautions to take:
  • Run during low-traffic windows: Even with online DDL, the rebuild will consume IO/CPU, which can slow down query performance on large tables. Pick a time when your application has the least load.
  • Monitor replica lag: Your read replicas will replicate the ALTER operation, so make sure they have enough resources to handle the rebuild. Keep an eye on CloudWatch metrics for replica lag to avoid outages.
  • Take a snapshot first: Before making any schema changes, create a manual RDS snapshot. If something goes wrong, you can restore quickly.
  • Leverage your staging test: You already validated this in testing, which is critical—staging should mirror production as closely as possible (same table sizes, traffic patterns) to catch any unexpected issues.
• Avoid concurrent large operations: Don’t run multiple ALTERs on large tables at the same time, and avoid heavy batch jobs during the ALTER window.

It depends on the DDL algorithm you use. Here’s the breakdown:

• Default behavior: If you don’t specify an algorithm, MySQL might use the COPY algorithm, which locks the table for the entire duration of the rebuild (bad for production).
• Optimal setup: Use ALGORITHM=INPLACE and LOCK=NONE (or LOCK=LOW) to minimize locking:

  ALTER TABLE `tablename` ROW_FORMAT=DYNAMIC ALGORITHM=INPLACE LOCK=NONE;
  

  • ALGORITHM=INPLACE: Rebuilds the table in-place without creating a temporary copy, reducing resource usage and lock time.
  • LOCK=NONE: Allows read and write operations on the table during the ALTER (except for a tiny lock at the start and end of the operation to update metadata).
• Edge cases: If your table has FULLTEXT indexes, ALGORITHM=INPLACE might not be supported—check with SHOW CREATE TABLE and adjust if needed. But for most standard tables with large rows, this works perfectly.

• Batch your ALTERs: Don’t run this on 10 large tables at once—do one at a time, monitoring resource usage between each.
• Check table fragmentation: After the ALTER, you’ll likely see reduced fragmentation since the table is rebuilt. Verify with SHOW TABLE STATUS LIKE 'tablename'\G.
• Prep for RDS 8.0 upgrade: Once all tables are in DYNAMIC format, you’re in a better position for the 8.0 upgrade—MySQL 8.0 recommends DYNAMIC (or COMPRESSED) for InnoDB tables, and COMPACT is deprecated in some contexts.

内容的提问来源于stack exchange，提问作者Mayb2Moro