估算大型MySQL表分区重组操作的耗时

First, let's break down why your linear small-sample estimate is way off: small datasets don't hit the same bottlenecks as 2 billion-row partitions—things like saturated IOPS, redo log throttling, index maintenance overhead, and background workload interference all drastically slow down large-scale operations. Here are practical, actionable ways to get a more accurate estimate without enabling Performance Schema or risking a restart:

1. Monitor Real-Time Progress of the Running Operation #

Since you can't use Performance Schema, rely on InnoDB's built-in metrics and Aurora's native monitoring tools:

• Check InnoDB Status for Operation Details
  Run SHOW ENGINE INNODB STATUS; and look for the ALTER TABLE section. You might find progress indicators like the number of rows copied so far, or log sequence number (LSN) growth. For example, if it shows something like copy to tmp table: 123456789 rows done, you can compare that to the total number of rows you need to move to p2022.

• Calculate Total Rows to Migrate First
  First, get an estimate of how many rows in pMAX belong to 2022:

  SELECT COUNT(*) FROM pIndexData PARTITION (pMAX) 
  WHERE `DateTime-UNIX` >= UNIX_TIMESTAMP('2022-01-01 00:00:00 UTC') 
    AND `DateTime-UNIX` < UNIX_TIMESTAMP('2023-01-01 00:00:00 UTC');
  

  If this is too slow, use the INFORMATION_SCHEMA for a rough estimate:

  SELECT TABLE_ROWS 
  FROM INFORMATION_SCHEMA.PARTITIONS 
  WHERE TABLE_NAME = 'pIndexData' 
    AND PARTITION_NAME = 'pMAX';
  

  Multiply this by the percentage of 2022 data in your sample (since your sample had 2022 as the largest portion) to get a rough total of rows to migrate.

• Track Partition Growth via Disk Space
  Each InnoDB partition maps to a separate .ibd file. Monitor the size of p2022.ibd (you can find this via Aurora's console or by querying INFORMATION_SCHEMA.FILES). Compare its current size to the expected size of the 2022 data:

  SELECT SUM(data_length + index_length)/1024/1024/1024 AS expected_GB
  FROM INFORMATION_SCHEMA.PARTITIONS 
  WHERE TABLE_NAME = 'pIndexData' 
    AND PARTITION_NAME = 'pMAX';
  

  Multiply this by the proportion of 2022 data to get the target size for p2022. Then, if p2022 has grown to 20% of that target, you can extrapolate based on how long it took to reach that point.

• Use Aurora CloudWatch Metrics
  Keep an eye on these metrics in CloudWatch:

  • AuroraVolumeWriteIOPs and AuroraVolumeWriteThroughput: Track the sustained write rate during the ALTER.
  • AuroraTransactionLogsGenerated: Measure how much redo log is being written (large ALTER operations generate massive amounts of redo).
    If you can capture the average write rate over the past day, divide the remaining data size by that rate to get a rough remaining time.

2. Adjust Your Sample Testing for Better Accuracy #

If you want to run a more reliable test, follow these rules to mimic production conditions:

• Use a Representative Sample Size
  Instead of 3 million rows, use a sample that's at least 0.5-1% of the total rows you're migrating (10-20 million rows). Extract this directly from the production pMAX partition (filtering for 2022 data) to ensure the data distribution, index fragmentation, and row size match production.

• Match Instance Configuration
  Spin up an Aurora instance with the same instance class, storage type, and parameter group as production. Pay close attention to parameters like innodb_buffer_pool_size, innodb_log_file_size, and innodb_flush_log_at_trx_commit—these have a huge impact on ALTER speed.

• Simulate Production Workload
  Run background read/write traffic against the test table (using tools like sysbench) to mimic real-world concurrency. The ALTER operation will slow down under load, which your initial small-sample test didn't account for.

3. Understand Key Bottlenecks to Interpret Progress #

Keep in mind why your initial estimate was wrong:

• Index Overhead: Your table has 5 secondary indexes plus the primary key. Each index needs to be rebuilt for the new p2022 partition—this is often the biggest time sink for large tables.
• IO Saturation: Aurora's SSD storage is fast, but moving 2 billion rows means continuous random reads from pMAX and sequential writes to p2022—this will likely saturate your instance's IOPS limit.
• Redo Log Throttling: InnoDB writes all changes to the redo log first. If your innodb_log_file_size is too small, the log will rotate frequently, causing waits for log file sync.

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