🦑
pt-online-schema-changeによる巨大テーブルのALTERでちゃんと失敗した話

zinkosuke
2025/04/02に公開
AWS
MySQL
tech
こんにちは、zinです🦑
先日、 pt-online-schema-change というツールを利用してシステム無停止で巨大テーブルのALTERを実行しました。ある程度枯れた技術のため記事は多く存在しますが、やらかした記事はあまりなかったので後悔公開しておこうと思います。

 まずネタバレロックに気をつけろ！

 前提・システム外観と対象テーブル弊社サービスの Liny には、LINEを介したメッセージ送受信やLINE友だちのメタデータ管理(タグづけなど)、予約などの機能があります。

ユーザーやLINE友だちからのアクセスは主に日中に集中しており、夜間はユーザー設定のバッチ処理が動いています。つまり、ほぼ1日中何かしらが動いているためシステム停止には痛みを伴います。
DBはAurora MySQL (MySQL8.0互換)、ワークロードはFargateタスクが中心です。コンテナは約700存在しており、ほとんどがDBにアクセスしています。つまり、DB停止には痛みを伴います。
ALTER対象のテーブルはLINE友だちとタグの中間テーブルです。過去のしがらみで主キーのidがUNSIGNED INTになっていましたが、この度AUTO_INCREMENTが21億を超えたためUNSIGNED BIGINTに移行することになりました。

最も読み書きが集中するテーブルのひとつで、移行時点でおよそ13億レコードありました。中間テーブルなのでカラムはid類とタイムスタンプ程度です。

 オンラインDDLの検討MySQLには オンラインDDL という機能があり、これを使えばテーブルのロックを最小限にALTERを流せるので、積極的に使って良いでしょう。

オンラインDDLでもロックが発生するタイミングがあるという点には注意が必要です。
今回やろうとしているカラムの型変更ではオンラインDDLが使えません。つまり、ALTERの実行中テーブルがロックされます。
-- ALGORITHMにはINSTANT, INPLACE, COPYが存在し、INSTANTとINPLACEがオンラインDDLにあたる
-- 使えないALGORITHMを指定すると落ちるので、どれが使えるかは試してみればわかる(ALGORITHMを省略するとなるべく高速なものが選択される)
mysql> ALTER TABLE my_name MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

mysql> ALTER TABLE my_name MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
本番DBのクローンで検証したところ、ALTERには約9時間かかりました。はじめはDBメンテ時間を設ける予定でしたが、システム停止~再開の手順やバッファ込みで12時間程度のシステム停止となります。無理です。

そこでオンラインでALTERを流せるpt-online-schema-changeを使うことになりました。

 pt-online-schema-changeの概要オプションやプラグインで挙動は制御できますが、基本の動作は以下のような流れで行われます。

※ テーブル名をmy_tableとする
create_new_table

CREATE TABLE LIKEでmy_tableと同じ構造のテーブルを作成する(_my_table_newとする)
alter_new_table

_my_table_newにALTERを実行する(空テーブルなので一瞬で終わる)
create_triggers

トリガー を作成し、my_tableへの書き込みを_my_table_newにも反映させる
copy_rows

my_tableのレコードを_my_table_newにコピーする
swap_tables

テーブルを入れ替える(_my_table_newをmy_tableに、my_tableを_old_my_tableにrename)

メタデータロックが発生するポイント
update_foreign_keys

(外部キー被参照がある場合)子テーブルの外部キー参照を新しいmy_tableに貼り替える

実施方法によるが、子テーブルの更新や制約違反のリスクがあるポイント
drop_old_table

_old_my_tableを削除する
drop_triggers

3で作成したトリガーを削除する
オンラインDDLと比べて以下のような利点があります。
データコピー時の負荷を調整できる

オンラインDDLは一度始めると調整が効きませんが、pt-online-schema-changeでは負荷(Threads_running)に応じてコピーの一時停止や全体のキャンセルが可能です。
テーブルの入れ替えタイミング(ロックの発生タイミング)を調整できる

デフォルトでは調整されないので改造が必要ですが、メタデータロックのタイミングを調整できます。

 pt-online-schema-changeを理解する
 unique indexの追加で使う場合は注意が必要これはドキュメントに記載がありますが、データのコピー時にエラーが無視されるため知らぬ間にデータロストが発生する可能性があります。

実施する場合はアプリケーション側で制約違反が起きないことを担保するか、データのロストがないか確認する手段を準備しておきましょう。
-- データコピーで使われるクエリ
INSERT LOW_PRIORITY IGNORE INTO _my_table_new ({columns})
SELECT {columns}
FROM my_table FORCE INDEX(`PRIMARY`)
WHERE ((`id` >= ?)) AND ((`id` <= ?))
LOCK IN SHARE MODE

 子テーブルのインデックス名が変わる可能性がある子テーブルは作り直されないため、my_tableを参照している外部キーがある場合は名前が変わります。

インデックス貼り替え時に_が1つ追加されます。
ALTER TABLE child_table
DROP FOREIGN KEY my_table_my_table_id_foreign,
ADD CONSTRAINT _my_table_my_table_id_foreign FOREIGN KEY (my_table_id)
    REFERENCES my_table (id) ON DELETE SET NULL ON UPDATE RESTRICT

 停止時の挙動pt-online-schema-changeの処理はECS RunTaskで実行しようと考えていましたが、処理を停止するとしたら以下の2パターンが考えられます。
critical-load設定への抵触

copy_rowsフェーズでは、チャンクの終了ごとにSHOW GLOBAL STATUSでThreads_runningの値を確認しています。

Threads_runningは稼働中のスレッド数を表しており、これがcritical-loadオプション(デフォルト50)を超えるとpt-online-schema-changeは処理を停止、ロールバックされます。

no-drop系のオプションを指定しなければ、終了時に一時リソースは削除されます。
シグナルによる停止

ECSタスクを停止すると、コンテナにはSIGTERMが送られます。

pt-online-schema-changeはSIGTERMによる停止では一時テーブルやトリガーの削除は行いません。

検証した限りだとSIGKILLリスクは低いと考えて良さそうです。
オプションや停止方法によって掃除が必要になります。

dry-runオプションを指定して実行することで実行される予定のクエリが確認できるので、掃除を手動で行う場合は事前にdry-runして掃除用クエリを控えておくと良いでしょう。

 そもそものトリガーって...？このツールに出会うまで存在すら知りませんでしたが、INSERT, UPDATE, DELETEにフックして追加処理をできる機能のようです。
https://dev.mysql.com/doc/refman/8.0/ja/triggers.html
トリガーによるデータコピーが失敗したら？という懸念がありましたが、トリガーの処理はトランザクションに含まれるのでデータコピーだけ失敗するという心配はなさそうです。

とはいえ、1回のINSERTで2行書き込むことに等しいので負荷の面で注意が必要です。

 ぼくがかんがえたさいきょうのアーキテクチャ本番で動かすまでは「なるほど完璧な作戦っすねーーーっ」って感じでした。

 オプション移行後何かあった時の保険としてno-drop-old-tableを指定していますが、基本的にはツールに乗っかる方針で行ってみようと考えました。

history-tableがあると途中で落ちた時に便利という噂を耳にしましたが、履歴の書き込み自体も負荷ではあるため今回は見送りました。
config/base.conf
### 共通
charset=utf8
new-table-name=pt_osc__%T
pause-file=${PAUSE_FILE}
print
statistics
plugin=./plugins/pt-osc-plugin.pl

### 負荷関連オプション
critical-load=${CRITICAL_LOAD}
max-load=${MAX_LOAD}
no-analyze-before-swap

### 安全対策オプション (pluginで対策している)
no-drop-old-table
# no-drop-new-table
# no-drop-triggers
# no-swap-tables

### 再実行関連オプション
# history-table
# resume

### ALTER
alter=${ALTER_STMT}
alter-foreign-keys-method=rebuild_constraints

### MySQL
host=${DB_HOST}
port=${DB_PORT}
database=${DB_DATABASE}
user=${DB_USERNAME}
password=${DB_PASSWORD}

 プラグインデフォルトではcopy_rowsが終わると直ちにswap_tablesに移ります。swap前にデータの確認をしつつ、swapタイミングを調整したかったので、プラグインを実装し一時停止できるようにしました。

今回は関係ありませんが、子テーブルがいる場合の外部キー貼り替えを高速化するプラグインも入れています。
plugins/pt-osc-plugin.pl
# https://github.com/winebarrel/pt-online-schema-change-fast-rebuild-constraints
package pt_online_schema_change_plugin;

use strict;

sub new {
  my ($class, %args) = @_;
  my $self = { %args };
  return bless $self, $class;
}

sub before_update_foreign_keys {
  my ($self, %args) = @_;
  my $dbh = $self->{cxn}->dbh;

  if ($self->{execute}) {
    print "Disable foreign key checks\n";
    $dbh->do("set foreign_key_checks=0");
  }
}

sub after_update_foreign_keys {
  my ($self, %args) = @_;
  my $dbh = $self->{cxn}->dbh;

  if ($self->{execute}) {
    print "Enable foreign key checks\n";
    $dbh->do("set foreign_key_checks=1");
  }
}

sub before_swap_tables {
  my ($self, %args) = @_;

  if ($self->{execute}) {
    my $command = "/work/plugins/before_swap_tables.sh $self->{pause_file}";
    system($command) == 0 or die "before_swap_tables failed: $?";
  }
}

1;
/work/plugins/before_swap_tables.sh
#!/bin/bash
set -euo pipefail

PAUSE_FILE=/tmp/pt-osc-pause
touch "${PAUSE_FILE}"
while [[ -f ${PAUSE_FILE} ]]; do
    echo "[INFO] Sleeping 60 seconds because ${PAUSE_FILE} exists..."
    sleep 60
done
perlが初見すぎてbashスクリプトに逃げましたが、before_swap_tablesという関数を定義しておくとswap_tables直前に実行されます。

この中で適当なファイルPAUSE_FILEを作成し、PAUSE_FILEが削除されるまではsleepして待機するという非常に簡易的な仕組みになっています。

 StepFunctionで動かす正直手元からRunTaskでよかった感はありますが、StepFunctionの検証も兼ねて以下のような簡易的なState Machineで動かしました。
explain: dry-runを動かす
execute: executeを動かす (swap_tables前の確認もちゃんとワークフローにしたかった)
Notify: 既存の通知用Lambdaに完了or失敗を通知
State Machineの定義を全て載せるとすごい量になってしまうので省略しますが、環境変数の調整とRunTaskをしているだけです。
実際はスクリプトで通知など色々やっていましたが、pt-online-schema-changeの実行に関しては基本的にconfigファイルから渡すようにしています。

executeの前にdry-runを実行し、流れる予定のクエリを出力させるようにしていました。
pt-online-schema-change \
    --config <(envsubst < config/base.conf) \
    --execute \
    "D=${DB_DATABASE},t=${TABLE_NAME}"

 リカバリープランエラーやコンテナkillなど、異常時の対応をステップごとに考えます。

基本的にはswap_tablesとupdate_foreign_keys以外でクリティカルな問題は起きないと見積りました。

今回はupdate_foreign_keysを実施しないため、負荷だけ気をつけておけば問題ない算段です。
create_new_table ~ copy_rowsで落ちた場合
システム影響は特になし
残った_my_table_newとtriggerを手で消す

copy_rowsの負荷が高すぎる場合
手動でタスクの停止
残った_my_table_newとtriggerを手で消す

swap_tablesで落ちた場合
renameが完了していない
システム影響は特になし
残った_my_table_newとtriggerを手で消す

rename完了している

システム影響あり / 子テーブルの外部キーがold_tableを指したまま
update_foreign_keys以降を手で実行する


update_foreign_keys

システム影響あり / 子テーブルの外部キーがold_tableを指したまま
update_foreign_keys以降(未完了分)を手で実行する
クエリはdry-runで事前に出しているので、コピペ

drop_old_table ~ drop_triggersで落ちた場合
システム影響は特になし、消えていない分を手でけす


 スケジュールテーブルの移行には15時間ほどかかる見込みでした。

DB負荷のピーク帯は日に何度かありどうやっても避けられないため、swap_tablesを日中の負荷が低めの時間に実施できるよう予定を組みました。

0時付近に大量のバッチ処理が起動するため、01:30移行開始 ~ 夕方頃完了の想定で実施します。

 そして障害へ移行当日。深夜にState Machineを起動し解散、データコピーには16時間半かかりました。データの確認をし、いよいよテーブルの入れ替えです。
そしてここで痛恨のミス！

ちょうどDBの書き込みが集中する時間帯(10分間程度)にswap_tablesをジャストミートさせました。
結果、swap_tablesの際発生するメタデータロックが大量のクエリをブロックし、約3分間システム全体の遅延や数々のエラーを生み出し、チームは障害対応に追われました。

 なぜこんなことに...？障害中に起きていたことを簡単にまとめると、以下のような具合になります。
スロークエリが動いている
swap_tablesを開始するが、スロークエリ完了まで待機
その後飛んでくるクエリはすべてswap_tables完了まで待機 (システム遅延ゾーン)
swap_tablesがタイムアウト、待機していたクエリが動く (一瞬復活する)
再びswap_tablesを開始、スロークエリ完了まで待機
再び飛んでくるクエリはすべてswap_tables完了まで待機 (システム遅延ゾーン)
スロークエリが終わるまで 5~6 をループ
スロークエリが完了、続いてswap_tablesも完了し事態は収束
障害発生の主な原因は
スロークエリが動いているタイミングにswap_tablesを被せてしまった
障害の規模が大きくなった主な要因は
swap_tablesの待機時間が長かった
ちょうど書き込み系のクエリが集中する時間帯だった
高負荷を避けるためにわざわざプラグインを実装しておきながらピーク帯にswapを被せるという凡ミスが今回の敗因でした。

16時間のコピーフェーズを無事終了したという気の緩みがDBの負荷を確認するという当たり前作業をスキップさせたのです。

 今後に向けた課題今後同じような障害を起こさないために何ができるか、次回以降に向けての改善案を考えました。

 lock_wait_timeoutを短くするデフォルトではswap_tablesは60秒待機します。つまり、swap_tables開始後に実行されたクエリは全て60秒待たされることになります。

lock_wait_timeoutを短く設定する事で、他のクエリが受ける被害を小さくします。
-- swap_tablesの実態はRENAME
RENAME TABLE my_table TO _my_table_old, _my_table_new TO my_table
高負荷帯を避けてswap_tablesを実施するのが基本姿勢ですが、lock_wait_timeoutを短く設定していれば甚大な被害は避けられるでしょう。

 swap_tablesとそれ以降の掃除を自前で行う今回は一連の処理をツールに任せましたが、テーブルロック以外にも大きな弱点がありました。

swap_tablesはデフォルトで10回までリトライされます。つまり、10回失敗するとロールバックされます (16時間の苦労が無駄に！！)
単にリトライ回数を増やすというのも手ですが、不必要なロールバックを心配しながら処理の完了を待つのは心臓に悪いので、それならば自前(自動)でswap_tables以降を実行する仕組みを作る方が健全だろうと思います。
no-swap-tablesとno-dropシリーズのオプションを指定し、StepFunctionからこれらを流せるようにしていく予定です。

 おわりに細かく検証・計画したつもりでも抜け漏れはあるものですね。非常に勉強になりました。

また、検証の詳細などは省略していますがAuroraが優秀ゆえに成り立っている部分も多くあり、感謝感謝です。
ソーシャルデータバンクテックブログ
ソーシャルデータバンク株式会社の開発チームです。インフラ、品質、UI/UX、DevOpsなど様々な活動に取り組んでいます。
まずネタバレ

前提・システム外観と対象テーブル

オンラインDDLの検討

pt-online-schema-changeの概要

pt-online-schema-changeを理解する

unique indexの追加で使う場合は注意が必要

子テーブルのインデックス名が変わる可能性がある

停止時の挙動

そもそものトリガーって...？

ぼくがかんがえたさいきょうのアーキテクチャ

オプション

プラグイン

StepFunctionで動かす

リカバリープラン

スケジュール

そして障害へ

なぜこんなことに...？

今後に向けた課題

lock_wait_timeoutを短くする

swap_tablesとそれ以降の掃除を自前で行う

おわりに

Discussion
