スキーマ変更の運用
MySQL 8.4 リファレンスマニュアルのオンライン DDL の3ページ(操作一覧・性能・領域要件)を 取得して読み、理解した内容を自分の言葉で書いたもの。本文の引用はしていない。
動いているサービスのテーブル定義を変える話。
順序の話は ブルーグリーンにある。 新旧どちらのバージョンでも動くスキーマを先に出し、そこを切り戻し先にする。 ここは、その「先に出す」を実際にどうやるか。
ALGORITHM と LOCK
ALTER TABLE に付けられる指定が2つある。
ALGORITHM | 何が起きるか |
|---|---|
INSTANT | メタデータだけを書き換える。 データには触らない。ほぼ無料 |
INPLACE | 一時テーブルを作らずに、その場で作り替える |
COPY | 新しい定義の一時テーブルを作り、データを写し、名前を入れ替える。最も遅い |
LOCK | 並行して何ができるか |
|---|---|
NONE | 読み書きの両方ができる |
SHARED | 読みだけ。書き込みは止まる |
EXCLUSIVE | すべて止まる。代わりに最も速く終わる |
DEFAULT | 省略時。可能な範囲で最大の並行性を選ぶ |
明示的に指定する意味がある。 ALGORITHM=INSTANT と書いておけば、
その方法で実行できない変更はエラーになるので、
「無害なつもりで出したら全件コピーが始まった」を防げる。
操作ごとの挙動
| 操作 | INSTANT | 再構築 | 並行 DML |
|---|---|---|---|
| カラムの追加 | できる | しない | できる |
| カラムの削除 | できる | する | できる |
| カラム名の変更 | できる | しない | できる(型を変えない場合) |
| 既定値の変更 | できる | しない | できる |
| テーブル名の変更 | できる | しない | できる |
| インデックスの追加 | できない | する | できる |
| インデックスの削除 | できない | する | できる |
| 主キーの追加 | できない | する | できる |
| 型の変更 | できない | する | できない |
| 主キーの削除(単独) | できない | する | できない |
| AUTO_INCREMENT 付きのカラム追加 | できない | する | できない |
| SPATIAL インデックスの追加 | できない | する | できない |
「オンライン DDL」と呼ばれていても、並行 DML ができない操作がある。 上の太字の行は、実行中は書き込みが止まる。
型の変更だけは逃げ道がない
カラムの型を変える操作は ALGORITHM=COPY しかない。 全件コピーで、書き込みも止まる。
だから大きなテーブルでは、型を変える代わりに新しいカラムを足す手を採ることになる。
1. 新しい型の別カラムを INSTANT で追加する
2. アプリを両方書けるようにして出す
3. 既存行を分割して埋める(一度に全部やらない)
4. 読み取りを新カラムに切り替える
5. 古いカラムを落とす
これはブルーグリーンの 「新旧どちらでも動く状態を経由する」を、カラム単位でやったもの。
INSTANT には回数の上限がある
知らないと本番で詰まる。
INSTANT の追加・削除は、そのたびに行のバージョンを1つ増やす。
上限は 64(MySQL 9.1.0 以降は 255)。超えるとこうなる。
ERROR 4092 (HY000): Maximum row versions reached for table ...
No more columns can be added or dropped instantly.
テーブルを再構築するとバージョン数は 0 に戻る。 現在値は確認できる。
SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME LIKE 'test/t1';つまり**「INSTANT だから無料」を積み重ねると、いつか無料ではなくなる。** 長く運用するテーブルほど、この残高を意識する。
ROW_FORMAT=COMPRESSED のテーブル、FULLTEXT インデックスを持つテーブルでは
そもそも INSTANT が使えない。
メタデータロックの行列
ここが、オンライン DDL で最も事故になるところ。
オンライン DDL でも、開始時と終了時に短くメタデータロックを取る。 問題はそのロックが取れないときに起きる。
セッション1 長い SELECT が走っていて、共有メタデータロックを保持している
セッション2 ALTER TABLE が、排他メタデータロックを待って止まる
セッション3 その後の SELECT が、待機中の排他ロック要求の後ろで止まる
3が要点。 後から来た普通の読み取りが、
ALTER の後ろに並んで動けなくなる。
ALTER 自体は「オンライン」でも、テーブルが実質的に使えなくなる。
引き金は長時間のトランザクションなので、DDL の前に長いクエリがないか確認する。
SHOW FULL PROCESSLIST; -- "Waiting for table metadata lock" を探す
SELECT * FROM performance_schema.metadata_locks;トランザクションのロックの話と地続きだが、 行ロックではなくメタデータロックである点が違う。 インシデント対応で「まず止血する」なら、 止めるのは ALTER の方になることが多い。
領域と、終わりかけでの失敗
オンライン DDL は3種類の一時領域を使う。どれも枯れると失敗する。
| 何 | どこ | 上限 |
|---|---|---|
| 一時ログファイル(実行中の並行 DML を記録する) | — | innodb_online_alter_log_max_size |
| 一時ソートファイル(インデックス作成用) | tmpdir / innodb_tmpdir | ディスクの空き |
中間テーブルファイル(#sql-ib で始まる) | 元のテーブルと同じディレクトリ | ディスクの空き |
一時ログが上限を超えると DB_ONLINE_LOG_TOO_BIG で失敗し、
その時点までの並行 DML はロールバックされる。
つまり書き込みが多いテーブルほど、長い DDL は終わりかけで失敗しやすい。 時間をかけたあげく何も残らない、という失敗の仕方をする。
ここにトレードオフがある。
innodb_online_alter_log_max_size を大きくすると DDL 中により多くの DML を許せるが、
その分、最後に溜めた DML を適用するあいだのロック時間が伸びる。
「失敗しにくくする」と「最後に止まる時間を短くする」は逆を向いている。
ソートファイルはテーブルとインデックスの合計と同じくらいの領域を要求しうる。
データディレクトリの空きを食い潰さないよう、
innodb_tmpdir を別の場所に向けておく。
中間テーブルファイルには innodb_tmpdir が効かない(元のテーブルの隣に作られる)。
事前に測る
本番で流す前に、それがコピーを伴うかどうかを確かめられる。
Query OK, 0 rows affected (0.07 sec) # メタデータのみ
Query OK, 0 rows affected (21.42 sec) # その場で作り替え
Query OK, 1671168 rows affected (1 min 35.54 sec) # 全件コピー
rows affected が 0 でなければ、データを写している。
手順としては、テーブル定義を複製して少量のデータを入れ、そこで DDL を流して
rows affected を見る。これで本番での影響が事前に分かる。
原則の 「推測ではなく実際にどうなるか」を、DDL に当てたもの。