Monitoring ALTER TABLE Progress in MySQL Using Performance Schema

After executing an ALTER TABLE statement, many MySQL users face the dilemma of long‑running operations that are hard to monitor or safely terminate. Being able to estimate the progress of such operations would be highly beneficial.

The official MySQL documentation (see monitor‑alter‑table‑performance‑schema ) describes how the performance_schema can be used to track DDL progress. This article follows that guidance with a hands‑on experiment.

First, a test database is created and several performance_schema variables are set to enable the necessary instrumentation. Then, using a technique from a previous experiment, a large amount of data is inserted into a table to ensure the subsequent ALTER TABLE will take noticeable time.

When the ALTER TABLE command is run in one session, another session repeatedly executes a monitoring SQL query. The query retrieves the currently running DDL statement, its progress, the elapsed time, and an estimated remaining time.

The monitoring output shows the DDL stage, start and end timestamps for the current stage, a parent event ID linking related stages, and a workload estimate based on completed and total task packages. The progress is calculated as the ratio of completed task packages to the estimated total.

Although the remaining‑time estimate is not perfectly precise, it provides sufficient accuracy for practical use.

The core monitoring SQL used is shown below:

select
    stmt.SQL_TEXT as sql_text,
    concat(WORK_COMPLETED, '/', WORK_ESTIMATED) as progress,
    (stage.TIMER_END - stmt.TIMER_START) / 1e12 as current_seconds,
    (stage.TIMER_END - stmt.TIMER_START) / 1e12 * (WORK_ESTIMATED-WORK_COMPLETED) / WORK_COMPLETED as remaining_seconds
from events_stages_current stage, events_statements_current stmt
where stage.THREAD_ID = stmt.THREAD_ID
  and stage.NESTING_EVENT_ID = stmt.EVENT_ID;

A short tip notes that enabling performance_schema typically adds only about 1% CPU overhead, which is negligible for most workloads.

Readers are invited to leave comments with further MySQL topics they would like to explore.