3334 ALTER TABLE Monitoring Example Using Performance Schema 15.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema 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 . • 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. In order of occurrence,  ALTER TABLE  stage events include: • 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. InnoDB ALTER TABLE  stage events do not currently account for the addition of spatial indexes. Note 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