Rechercher dans le manuel MySQL
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 whenALTER TABLE
is in the reading-primary-key phase. It starts withWORK_COMPLETED=0
andWORK_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 theALTER TABLE
operation.stage/innodb/alter table (insert)
: This stage is repeated for each index added by theALTER TABLE
operation.stage/innodb/alter table (log apply index)
: This stage includes the application of DML log generated whileALTER 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 whileALTER 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 whileALTER TABLE
was running.
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”.
Enable the
stage/innodb/alter%
instruments:Enable the stage event consumer tables, which include
events_stages_current
,events_stages_history
, andevents_stages_history_long
.Run an
ALTER TABLE
operation. In this example, amiddle_name
column is added to the employees table of the employees sample database.Check the progress of the
ALTER TABLE
operation by querying the Performance Schemaevents_stages_current
table. The stage event shown differs depending on whichALTER TABLE
phase is currently in progress. TheWORK_COMPLETED
column shows the work completed. TheWORK_ESTIMATED
column provides an estimate of the remaining work.- FROM performance_schema.events_stages_current;
- +------------------------------------------------------+----------------+----------------+
- | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
- +------------------------------------------------------+----------------+----------------+
- +------------------------------------------------------+----------------+----------------+
The
events_stages_current
table returns an empty set if theALTER TABLE
operation has completed. In this case, you can check theevents_stages_history
table to view event data for the completed operation. For example:- FROM performance_schema.events_stages_history;
- +------------------------------------------------------+----------------+----------------+
- | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
- +------------------------------------------------------+----------------+----------------+
- +------------------------------------------------------+----------------+----------------+
As shown above, the
WORK_ESTIMATED
value was revised duringALTER TABLE
processing. The estimated work after completion of the initial stage is 1213. WhenALTER TABLE
processing completed,WORK_ESTIMATED
was set to the actual value, which is 1981.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-monitor-alter-table-performance-schema.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.