## 15.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema 

You can monitor ALTER TABLE  progress for InnoDB  tables using Performance Schema. 

There are seven stage events that represent different phases of ALTER TABLE . Each stage event reports a running total of WORK_COMPLETED  and WORK_ESTIMATED  for the overall ALTER TABLE  operation as it progresses through its different phases. WORK_ESTIMATED  is calculated using a formula that takes into account all of the work that ALTER TABLE  performs, and may be revised during ALTER TABLE processing. WORK_COMPLETED  and WORK_ESTIMATED  values are an abstract representation of all of the work performed by ALTER TABLE . 

In order of occurrence, ALTER TABLE  stage events include: 

• stage/innodb/alter table (read PK and internal sort) : This stage is active when ALTER TABLE  is in the reading-primary-key phase. It starts with WORK_COMPLETED=0  and WORK_ESTIMATED  set to the estimated number of pages in the primary key. When the stage is completed, WORK_ESTIMATED  is updated to the actual number of pages in the primary key. 

• stage/innodb/alter table (merge sort) : This stage is repeated for each index added by the ALTER TABLE  operation. 

• stage/innodb/alter table (insert) : This stage is repeated for each index added by the ALTER TABLE  operation. 

• stage/innodb/alter table (log apply index) : This stage includes the application of DML log generated while ALTER TABLE  was running. 

• stage/innodb/alter table (flush) : Before this stage begins, WORK_ESTIMATED  is updated with a more accurate estimate, based on the length of the flush list. 

• stage/innodb/alter table (log apply table) : This stage includes the application of concurrent DML log generated while ALTER TABLE  was running. The duration of this phase depends on the extent of table changes. This phase is instant if no concurrent DML was run on the table. 

• stage/innodb/alter table (end) : Includes any remaining work that appeared after the flush phase, such as reapplying DML that was executed on the table while ALTER TABLE  was running. 

Note 

InnoDB ALTER TABLE  stage events do not currently account for the addition of spatial indexes. 

## ALTER TABLE Monitoring Example Using Performance Schema 

The following example demonstrates how to enable the stage/innodb/alter table%  stage event instruments and related consumer tables to monitor ALTER TABLE  progress. For information about Performance Schema stage event instruments and related consumers, see Section 27.12.5, “Performance Schema Stage Event Tables” . 

1. Enable the stage/innodb/alter%  instruments: 

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = ’YES’ WHERE NAME LIKE ’stage/innodb/alter%’; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 

