MySQL :: MySQL 8.0 Reference Manual :: 15.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema

10bet网址

version 8.0

5.7

8.0 Japanese

MySQL 8.0 Reference Manual/.../ Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema

15.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema

You can monitorALTER TABLEprogress forInnoDBtables usingPerformance Schema.

There are seven stage events that represent different phases ofALTER TABLE. Each stage event reports a running total ofWORK_COMPLETEDandWORK_ESTIMATEDfor the overallALTER TABLEoperation as it progresses through its different phases.WORK_ESTIMATEDis calculated using a formula that takes into account all of the work thatALTER TABLEperforms, and may be revised duringALTER TABLEprocessing.WORK_COMPLETEDandWORK_ESTIMATEDvalues are an abstract representation of all of the work performed byALTER TABLE.

In order of occurrence,ALTER TABLEstage events include:

stage/innodb/alter table (read PK and internal sort): This stage is active whenALTER TABLEis in the reading-primary-key phase. It starts withWORK_COMPLETED=0andWORK_ESTIMATEDset to the estimated number of pages in the primary key. When the stage is completed,WORK_ESTIMATEDis 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 theALTER TABLEoperation.
stage/innodb/alter table (insert): This stage is repeated for each index added by theALTER TABLEoperation.
stage/innodb/alter table (log apply index): This stage includes the application of DML log generated whileALTER TABLEwas running.
stage/innodb/alter table (flush): Before this stage begins,WORK_ESTIMATEDis 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 whileALTER TABLEwas 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 whileALTER TABLEwas running.

Note

InnoDBALTER TABLEstage 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 thestage/innodb/alter table%stage event instruments and related consumer tables to monitorALTER TABLEprogress. For information about Performance Schema stage event instruments and related consumers, seeSection 27.12.5, “Performance Schema Stage Event Tables”.

Enable thestage/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

Enable the stage event consumer tables, which includeevents_stages_current,events_stages_history, andevents_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

Run anALTER TABLEoperation. In this example, amiddle_namecolumn 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

Check the progress of theALTER TABLEoperation by querying the Performance Schemaevents_stages_currenttable. The stage event shown differs depending on whichALTER TABLEphase is currently in progress. TheWORK_COMPLETEDcolumn shows the work completed. TheWORK_ESTIMATEDcolumn provides an estimate of the remaining work.

mysql >选择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)

Theevents_stages_currenttable returns an empty set if theALTER TABLEoperation has completed. In this case, you can check theevents_stages_historytable to view event data for the completed operation. For example:

mysql >选择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, theWORK_ESTIMATEDvalue was revised duringALTER TABLEprocessing. The estimated work after completion of the initial stage is 1213. WhenALTER TABLEprocessing completed,WORK_ESTIMATEDwas set to the actual value, which is 1981.

PREV HOME UP NEXT