MySQL 5.7.6で追加された新しいp_sのステージ情報から、
合法的に InnoDBに進捗どうですか？ を聞けるようになったらしい。
MySQL :: MySQL 5.7 Reference Manual :: 14.13.11.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema
setup_instrumentsでalter table関連のやつ(デフォルトOFF)と
mysql> SELECT * FROM setup_instruments WHERE name LIKE 'stage/innodb/alter%';
+------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+------------------------------------------------------+---------+-------+
| stage/innodb/alter table (end) | NO | NO |
| stage/innodb/alter table (flush) | NO | NO |
| stage/innodb/alter table (insert) | NO | NO |
| stage/innodb/alter table (log apply index) | NO | NO |
| stage/innodb/alter table (log apply table) | NO | NO |
| stage/innodb/alter table (merge sort) | NO | NO |
| stage/innodb/alter table (read PK and internal sort) | NO | NO |
+------------------------------------------------------+---------+-------+
7 rows in set (0.11 sec)
setup_consumersのevents_stages_*もデフォルトはOFF
mysql> SELECT * FROM setup_consumers WHERE name LIKE '%stages%';
+----------------------------+---------+
| NAME | ENABLED |
+----------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
+----------------------------+---------+
3 rows in set (0.08 sec)
これを
mysql> UPDATE setup_instruments SET enabled= 'YES' WHERE name LIKE 'stage/innodb/alter%';
Query OK, 7 rows affected (0.03 sec)
Rows matched: 7 Changed: 7 Warnings: 0
mysql> UPDATE setup_consumers SET enabled= 'YES' WHERE name LIKE '%stages%';
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3 Changed: 3 Warnings: 0
こうじゃ！
mysql> ALTER TABLE order_line ADD KEY (ol_dist_info);
..
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) | 12084 | 268542 |
+------------------------------------------------------+----------------+----------------+
1 row in set (0.01 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) | 28060 | 268542 |
+------------------------------------------------------+----------------+----------------+
1 row in set (0.00 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) | 47972 | 268542 |
+------------------------------------------------------+----------------+----------------+
1 row in set (0.00 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
+---------------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+---------------------------------------+----------------+----------------+
| stage/innodb/alter table (merge sort) | 138643 | 287346 |
+---------------------------------------+----------------+----------------+
1 row in set (0.01 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
+-----------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+-----------------------------------+----------------+----------------+
| stage/innodb/alter table (insert) | 243821 | 287346 |
+-----------------------------------+----------------+----------------+
1 row in set (0.00 sec)
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
Empty set (0.00 sec)
終わるとevents_stages_currentからは消える。
The WORK_COMPLETED column shows the number of pages processed. The WORK_ESTIMATED column provides an estimate of the remaining work, in numbers of pages.
MySQL :: MySQL 5.7 Reference Manual :: 14.13.11.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema
ということなので、work_completedとwork_estimatedはページ数らしい。
ちなみに、↑だとあまりにコピペでしかないのでちょっと気を利かせて終了予想時刻を計算するクエリー↓も書いたんですが
mysql> SELECT thread_id, event_name, sql_text, @progress:= (work_completed / work_estimated) * 100 AS progress, @elapsed:= (timer_current - timer_start) / power(10, 12) AS elapsed, @elapsed * (100 / @progress) - @elapsed AS estimated FROM (SELECT stage.thread_id, stage.event_name, work_completed, work_estimated, (SELECT timer_start FROM events_statements_current WHERE sql_text LIKE 'SELECT thread_id, event_name,%') AS timer_current, statement.timer_start, sql_text FROM events_stages_current AS stage JOIN events_statements_current AS statement USING(thread_id)) AS dummy;
+-----------+------------------------------------------------------+-------------------------------------+-------------+-------------+--------------------+
| thread_id | event_name | sql_text | progress | elapsed | estimated |
+-----------+------------------------------------------------------+-------------------------------------+-------------+-------------+--------------------+
| 28 | stage/innodb/alter table (read PK and internal sort) | ALTER TABLE t1 ADD UNIQUE KEY (val) | 7.330386000 | 1.877416142 | 23.734006530694177 |
+-----------+------------------------------------------------------+-------------------------------------+-------------+-------------+--------------------+
1 row in set (0.15 sec)
mysql> SELECT thread_id, event_name, sql_text, @progress:= (work_completed / work_estimated) * 100 AS progress, @elapsed:= (timer_current - timer_start) / power(10, 12) AS elapsed, @elapsed * (100 / @progress) - @elapsed AS estimated FROM (SELECT stage.thread_id, stage.event_name, work_completed, work_estimated, (SELECT timer_start FROM events_statements_current WHERE sql_text LIKE 'SELECT thread_id, event_name,%') AS timer_current, statement.timer_start, sql_text FROM events_stages_current AS stage JOIN events_statements_current AS statement USING(thread_id)) AS dummy;
+-----------+------------------------------------------------------+-------------------------------------+--------------+--------------+------------------+
| thread_id | event_name | sql_text | progress | elapsed | estimated |
+-----------+------------------------------------------------------+-------------------------------------+--------------+--------------+------------------+
| 28 | stage/innodb/alter table (read PK and internal sort) | ALTER TABLE t1 ADD UNIQUE KEY (val) | 46.969643300 | 33.385053874 | 37.6928839778295 |
+-----------+------------------------------------------------------+-------------------------------------+--------------+--------------+------------------+
1 row in set (0.01 sec)
mysql> SELECT thread_id, event_name, sql_text, @progress:= (work_completed / work_estimated) * 100 AS progress, @elapsed:= (timer_current - timer_start) / power(10, 12) AS elapsed, @elapsed * (100 / @progress) - @elapsed AS estimated FROM (SELECT stage.thread_id, stage.event_name, work_completed, work_estimated, (SELECT timer_start FROM events_statements_current WHERE sql_text LIKE 'SELECT thread_id, event_name,%') AS timer_current, statement.timer_start, sql_text FROM events_stages_current AS stage JOIN events_statements_current AS statement USING(thread_id)) AS dummy;
+-----------+---------------------------------------+-------------------------------------+--------------+--------------+--------------------+
| thread_id | event_name | sql_text | progress | elapsed | estimated |
+-----------+---------------------------------------+-------------------------------------+--------------+--------------+--------------------+
| 28 | stage/innodb/alter table (merge sort) | ALTER TABLE t1 ADD UNIQUE KEY (val) | 50.831565800 | 40.169081343 | 38.854810033960106 |
+-----------+---------------------------------------+-------------------------------------+--------------+--------------+--------------------+
1 row in set (0.00 sec)
mysql> SELECT thread_id, event_name, sql_text, @progress:= (work_completed / work_estimated) * 100 AS progress, @elapsed:= (timer_current - timer_start) / power(10, 12) AS elapsed, @elapsed * (100 / @progress) - @elapsed AS estimated FROM (SELECT stage.thread_id, stage.event_name, work_completed, work_estimated, (SELECT timer_start FROM events_statements_current WHERE sql_text LIKE 'SELECT thread_id, event_name,%') AS timer_current, statement.timer_start, sql_text FROM events_stages_current AS stage JOIN events_statements_current AS statement USING(thread_id)) AS dummy;
+-----------+-----------------------------------+-------------------------------------+--------------+--------------+--------------------+
| thread_id | event_name | sql_text | progress | elapsed | estimated |
+-----------+-----------------------------------+-------------------------------------+--------------+--------------+--------------------+
| 28 | stage/innodb/alter table (insert) | ALTER TABLE t1 ADD UNIQUE KEY (val) | 83.429283200 | 61.092267798 | 12.134140789914134 |
+-----------+-----------------------------------+-------------------------------------+--------------+--------------+--------------------+
1 row in set (0.00 sec)
だがしかしこれ、work_completedとwork_estimatedの値はALTER TABLE全体を通して全てのページ数を表現している(ので、途中でステージが変わってもprogressとして算出している値は常に進む)んだけど、各ステージごとの処理のスピードは違うので、そこまでアテにはならないかも知れない（が、それを言ったらSHOW ENGINE INNODB STATUSで見るのも似たようなもので。。）
体感ではステージがread PK and internal sort > merge sort >> insert >> その他 くらいの順で時間を占めているので、そのあたりを加味すればありかな。。
【2017/01/24 19:41】
これから2年、今では俺も
@@pseudo_thread_id のことを知りました。
今ならこう書く。
SELECT
thread_id,
event_name,
sql_text,
@progress:= (work_completed / work_estimated) * 100 AS progress,
@elapsed:= (timer_current - timer_start) / power(10, 12) AS elapsed,
@elapsed * (100 / @progress) - @elapsed AS estimated
FROM
(SELECT
stage.thread_id,
stage.event_name,
work_completed,
work_estimated,
(SELECT timer_start
FROM events_statements_current JOIN threads USING(thread_id)
WHERE processlist_id = @@pseudo_thread_id) AS timer_current,
statement.timer_start,
sql_text
FROM
events_stages_current AS stage JOIN events_statements_current AS statement USING(thread_id)
) AS dummy;
…あんま変わらんか。変わらんな。
