Updated on 2023-08-21 GMT+08:00

DDL Progress Display

Introduction

DDL operations on large tables are time-consuming. However, MySQL Community Edition does not provide you with any information about the DDL execution phase and progress, which may cause great troubles to you.

To solve this problem, RDS for MySQL launches the DDL progress display feature. You can query the INFORMATION_SCHEMA.INNODB_ALTER_TABLE_PROGRESS table to view the execution phase and progress of DDL statements in real time.

Characteristics

Table 1 INNODB_ALTER_TABLE_PROGRESS table columns

Column

Description

THREAD_ID

Thread ID

QUERY

ALTER TABLE SQL statements

START_TIME

DDL start time

ELAPSED_TIME

Elapsed time (s)

ALTER_TABLE_STAGE

ALTER TABLE stage events

STAGE_COMPLETED

Completed work at the current stage

STAGE_ESTIMATED

Estimated work at the current stage

In order of occurrence, ALTER TABLE stage events include:

  • stage/innodb/alter table (read PK and internal sort): Read the primary key.
  • stage/innodb/alter table (merge sort): Sort by primary key. This process may take a long period of time because temporary files are generated.
  • stage/innodb/alter table (insert): Insert the sorted data into the table.
  • stage/innodb/alter table (log apply index): Apply DML logs generated during DDL execution to the created or modified index.
  • stage/innodb/alter table (flush): Flush data to the disk.
  • stage/innodb/alter table (log apply table): Apply DML logs generated during DDL execution to the created or modified table.
  • stage/innodb/alter table (end): Finish the remaining work.