MysqlのAlter Tableの終了時間を確認するSQL

最近扱うデータ量が増えて、1億件を超えてきたため、AlterTableを待つのが辛い。
それでいつ終わるかどのくらいの速度で実行してるか、調べるクエリを作ってみた。

データ量があまりに多いと、処理時間過ぎても終わらないときがあるけど、目安という事で。
※1億件とか処理すると数時間過ぎても終わらなかったことあり。

performance_schemaを有効にする

[mysqld]
performance_schema=on

Alter Tableのモニタリングを有効にする

UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';

Alter Table実行

Alter Table ○○○○○○○○○○○○○○

Alter Table実行中に以下のクエリを実行

SELECT
    EVENT_NAME as "実行中プロセス",
    WORK_COMPLETED as "完了",
    WORK_ESTIMATED as "全体",
    CONCAT(@PROGRESS := WORK_COMPLETED/WORK_ESTIMATED * 100 , "%" ) as "進捗率",
    @ELAPSED :=  ( TIMER_END - TIMER_START ) / POWER(10, 12)  as "実行時間",
    WORK_COMPLETED / @ELAPSED as "処理量/秒",
    @REMAIN := FLOOR( @ELAPSED * ( 100 / @PROGRESS ) - @ELAPSED ) AS "残時間",
    DATE_FORMAT( NOW() + INTERVAL @REMAIN SECOND , "%m月%d日 %H:%i:%s" ) AS "終了予測時刻"
FROM
    `performance_schema`.events_stages_current;

サンプル

予想時間がわかると、後はそれまで寝とけばいいので便利！

参考

14.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema
https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html
MySQL 5.7.6のPerformance SchemaでInnoDBのALTER TABLE進捗どうですか
https://yoku0825.blogspot.jp/2015/03/mysql-576performance-schemainnodbalter.html