RailsでALTER TABLE（擬似）の進捗状況を表示してみた

はじめに

こんにちは、『メルクストーリア – 癒術士と鐘の音色 –』で主にサーバーサイドを担当しているD.Yです。

MySQLのALTER TABLEを実行中に進捗を知りたいと思ったこと、ありませんか？
ユーザーデータを管理するテーブルはサービス開始から日が経つにつれて肥大化し、数十億レコード、数百GBといった規模になることもあります。
そのような巨大なテーブルに対するALTER TABLEには長時間を要し、数時間以上かかることもしばしばです。
移行時間は事前に試算すると思いますが、限られたメンテナンス時間の中、いつ終わるか分からないALTER TABLEを待つのは精神衛生上よろしくありません。

メンテナンス作業者の精神的負担を下げるべく、Railsのmigrate中にALTER TABLEの挙動を疑似的に再現し、進捗を表示してみたので紹介します。

ALTER TABLEの挙動

カラムの追加、削除、変更など、テーブルの構造変更を伴うALTER TABLEの挙動はざっくり以下の流れとなります。

① 変更後の構造で空のテンポラリテーブルをCREATE
② 変更前のテーブルのデータをテンポラリテーブルにINSERT
③ 変更前のテーブルとテンポラリテーブルの名前を入れ替え
④ 変更前のテーブルをDROP

上記の②の処理時間がデータのサイズに比例して長くなることが、巨大なテーブルのALTER TABLEに長時間を要す要因です。
①③④にはほとんど時間がかからないので、大体の進捗は②の進行状況で把握出来ます。

MySQL 8.0.12で実装されたInstant DDLはテーブルの最後尾への列追加等の条件付きですが、テーブルの再作成が走らないので高速です。Auroraの高速DDL（要Aurora ラボモード）も似たような仕組みで高速化しているものと思います。

ALTER TABLEを擬似的に再現して進捗表示

以下はbig_dataというテーブルにcol_6というカラムを追加する想定で実装したサンプルコードです。
実装では次の点を工夫しました。

• 主キーの範囲で1,000件ずつ区切ってINSERT
  • OFFSETを使うと移行が進むにつれてスキャンが増えて遅くなる
• INSERT INTO ... SELECTを使うことでmigrate実行環境にデータを展開しない
  • migrate実行環境のメモリとDBとのトラフィックの配慮
• ActiveRecord::Base.connection.updateが返すmysql_affected_rowsを処理済み件数として進捗計算に使用

class AddColToBigData < ActiveRecord::Migration[6.0]
  def up
    org_table_name = "big_data"
    tmp_table_name = org_table_name + "___tmp"
    old_table_name = org_table_name + "___old"
    batch_unit = 1_000

    #### ①変更後の構造で空のテンポラリテーブルをCREATE
    # 変更前と同じ構造のテンポラリテーブルを作成
    sql = "CREATE TABLE #{tmp_table_name} LIKE #{org_table_name}"
    ActiveRecord::Base.connection.execute(sql)

    # テンポラリテーブルに列追加
    change_table tmp_table_name do |t|
      t.column :col_6, :string, after: :col_5, comment: '追加カラム'
    end

    #### ②変更前のテーブルのデータをテンポラリテーブルにINSERT
    all_count = BigData.count
    max_id = BigData.maximum(:id)
    from_id = 1
    inserted_cnt = 0

    insert_sql = <<~SQL
      INSERT INTO #{tmp_table_name} (#{BigData.column_names.join(",")})
      SELECT *
      FROM #{org_table_name} 
      WHERE id BETWEEN ? AND ?
    SQL

    # max_idに到達するまでbatch_unit件ずつINSERT SELECT
    while from_id < max_id do      
      to_id = from_id + batch_unit - 1
      
      # サニタイズ
      sanitized_sql = ActiveRecord::Base.sanitize_sql_array([insert_sql, from_id, to_id])

      # ActiveRecord::Base.connection.updateはaffected_rows（更新件数）を返却するので進捗計算に利用
      inserted_cnt += ActiveRecord::Base.connection.update(sanitized_sql)

      # 進捗表示
      print_progress(inserted_cnt, all_count)

      from_id += batch_unit
    end

    # ③変更前のテーブルとテンポラリテーブルの名前を入れ替え
    sql = "RENAME TABLE #{org_table_name} TO #{old_table_name}, #{tmp_table_name} TO #{org_table_name}"
    ActiveRecord::Base.connection.execute(sql)

    # ④変更前のテーブルをDROP
    sql = "DROP TABLE #{old_table_name}"
    ActiveRecord::Base.connection.execute(sql)
    print "\n"
  end

  # 進捗表示
  def print_progress(done_count, all_count)
    @begin_sec ||= Time.current.to_i
    now_sec = Time.current.to_i
    rate = all_count > 0 ? done_count / all_count.to_f : 0              # 進捗率
    elapsed_sec = now_sec - @begin_sec
    estimated_sec = rate > 0 ?  elapsed_sec / rate : 0
    elapsed = Time.at(elapsed_sec).utc.strftime('%X')                   # 経過時間
    eta = Time.at(estimated_sec - elapsed_sec).utc.strftime('%X')       # 残り時間

    print "\rElapsed: #{elapsed} ETA: #{eta} Progress rate: #{(rate * 100).to_i}% #{done_count}/#{all_count}"
  end
end

upのみ実装してますが、downもほとんど同じロジックで実現できます。

実行結果

以下はテストデータを投入してサンプルコードを実行した際の動画です。
進捗として以下の情報を出力しています。

• Elapsed : 経過時間
• ETA : 終了予定時間
• Progress rate : 進捗率
• 処理済件数 / 全体件数

それっぽくなったんじゃないでしょうか？
終了予定時間をリアル時間にしたり、キャリッジリターン（\r）を改行（\n）にしても良さそうです。

一度に移行する件数（batch_unit）を増やせば速度は上がりますが、進捗表示の粒度が粗くなります。（増やしすぎるとtmp_table_sizeを溢れて遅くなるかもしれません。）

一度に移行する件数を減らせば、進捗表示の粒度は上がりますが、処理は遅くなります。

ALTER TABLEとの比較

メリット

進捗状況の把握

ALTER TABLEは実行中に何も応答がありません。
大規模なテーブルでは長時間を要するので、進捗状況を把握できることは移行作業中にとても心強いです。
予定した移行時間とのズレの検知は、メンテナンスの延長要否の判断に繋がります。

ALTER TABLEの進行状況はHandler_writeなどから知る方法もあります。

レプリケーション遅延の緩和

マスターでのALTER TABLE完了後にスレーブにバイナリログが伝搬し、ALTER TABLEが実行されるため、レプリケーションが完了するまでには単純計算で2倍の時間を要します。
上記のサンプルコードはマスターで完了したINSERTが逐次スレーブに伝搬し、並列で進行するため、レプリケーションが完了するまでの時間を大幅に短縮できます。
レプリケーションの遅延を許容できないサービスではメンテナンスの時間短縮に繋がります。

別のデータ移行とまとめて実行

テーブルの分割や統合、初期データ登録、データの削除、集約、変換など、同じテーブルに対する複数のデータ移行を一連の移行処理として、まとめて実装できます。
ALTER TABLE -> データ移行と順に実行するのに比べ、移行処理をまとめた場合はトータルで移行時間を短縮できるケースが多いかと思います。

ex)

• カラム追加のついでに別のテーブルから初期データを設定
• データ移行のついでに不要になった過去データを削除

デメリット

実装が手間

ALTER TABLEに比べて実装、動作確認の手間が増えるので、短時間で終わるデータ移行には向きません。

ALTER TABLEより遅い

通常のALTER TABLEと比べると遅いです。
どの程度遅くなるかは実行環境や移行内容が影響しますが、2倍以上遅くなるようであれば、レプリケーション完了までの所要時間がALTER TABLEより長くなるため、レプリケーション遅延を緩和するメリットは失われます。

バイナリログ（更新ログ）が巨大

移行対象となったデータは全てINSERTでバイナリログに出力されるため、ファイルサイズが大きくなります。
一時的に移行対象テーブルが2つ存在する状態になることもあり、バイナリログの出力先に指定するディスクの空き状況には注意が必要です。

おわりに

サービス運営中、新機能やイベントをユーザーさんにお届けするため、避けて通れないデータ移行。問題発生時には多くのユーザーさんにご迷惑を掛けてしまう場合もあります。
限られた時間で正確な作業を求められるメンテナンス作業者には、できるだけ心穏やかに作業にあたってもらいたいものですね。