MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

15.15.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:

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 26.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
    
  2. Enable the stage event consumer tables, which include events_stages_current, events_stages_history, and events_stages_history_long.

    mysql> UPDATE performance_schema.setup_consumers
           SET ENABLED = 'YES'
           WHERE NAME LIKE '%stages%';
    Query OK, 3 rows affected (0.00 sec)
    Rows matched: 3  Changed: 3  Warnings: 0
    
  3. Run an ALTER TABLE operation. In this example, a middle_name column is added to the employees table of the employees sample database.

    mysql> ALTER TABLE employees.employees ADD COLUMN middle_name varchar(14) AFTER first_name;
    Query OK, 0 rows affected (9.27 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
  4. Check the progress of the ALTER TABLE operation by querying the Performance Schema events_stages_current table. The stage event shown differs depending on which ALTER TABLE phase is currently in progress. The WORK_COMPLETED column shows the work completed. The WORK_ESTIMATED column provides an estimate of the remaining work.

    mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
           FROM performance_schema.events_stages_current;
    +------------------------------------------------------+----------------+----------------+
    | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
    +------------------------------------------------------+----------------+----------------+
    | stage/innodb/alter table (read PK and internal sort) |            280 |           1245 |
    +------------------------------------------------------+----------------+----------------+
    1 row in set (0.01 sec)
    

    The events_stages_current table returns an empty set if the ALTER TABLE operation has completed. In this case, you can check the events_stages_history table to view event data for the completed operation. For example:

    mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
           FROM performance_schema.events_stages_history;
    +------------------------------------------------------+----------------+----------------+
    | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
    +------------------------------------------------------+----------------+----------------+
    | stage/innodb/alter table (read PK and internal sort) |            886 |           1213 |
    | stage/innodb/alter table (flush)                     |           1213 |           1213 |
    | stage/innodb/alter table (log apply table)           |           1597 |           1597 |
    | stage/innodb/alter table (end)                       |           1597 |           1597 |
    | stage/innodb/alter table (log apply table)           |           1981 |           1981 |
    +------------------------------------------------------+----------------+----------------+
    5 rows in set (0.00 sec)
    

    As shown above, the WORK_ESTIMATED value was revised during ALTER TABLE processing. The estimated work after completion of the initial stage is 1213. When ALTER TABLE processing completed, WORK_ESTIMATED was set to the actual value, which is 1981.