Rechercher dans le manuel MySQL

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:

  • 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

Table des matières

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:

    1. mysql> UPDATE performance_schema.setup_instruments
    2.        SET ENABLED = 'YES'
    3.        WHERE NAME LIKE 'stage/innodb/alter%';
    4. Query OK, 7 rows affected (0.00 sec)
    5. 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.

    1. mysql> UPDATE performance_schema.setup_consumers
    2.        SET ENABLED = 'YES'
    3.        WHERE NAME LIKE '%stages%';
    4. Query OK, 3 rows affected (0.00 sec)
    5. 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.

    1. mysql> ALTER TABLE employees.employees ADD COLUMN middle_name varchar(14) AFTER first_name;
    2. Query OK, 0 rows affected (9.27 sec)
    3. 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.

    1. mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
    2.        FROM performance_schema.events_stages_current;
    3. +------------------------------------------------------+----------------+----------------+
    4. | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
    5. +------------------------------------------------------+----------------+----------------+
    6. | stage/innodb/alter table (read PK and internal sort) |            280 |           1245 |
    7. +------------------------------------------------------+----------------+----------------+
    8. 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:

    1. mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
    2.        FROM performance_schema.events_stages_history;
    3. +------------------------------------------------------+----------------+----------------+
    4. | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
    5. +------------------------------------------------------+----------------+----------------+
    6. | stage/innodb/alter table (read PK and internal sort) |            886 |           1213 |
    7. | stage/innodb/alter table (flush)                     |           1213 |           1213 |
    8. | stage/innodb/alter table (log apply table)           |           1597 |           1597 |
    9. | stage/innodb/alter table (end)                       |           1597 |           1597 |
    10. | stage/innodb/alter table (log apply table)           |           1981 |           1981 |
    11. +------------------------------------------------------+----------------+----------------+
    12. 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.


Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-monitor-alter-table-performance-schema.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut