MySQLでカラム追加などのalter table中にクエリがブロックされるかなどについてのメモ

これは何

MySQLのカラム追加などのDDL, alter table時にそれぞれの操作がどれくらい危険なのか、負荷は？といった情報の見方を毎回忘れるのでメモしたもの

まとめ

表を見なくても ALTER TABLE の末尾に ALGORITHM=INPLACE,LOCK=NONE; をつけてしまえば、動作を決められる（不可能な場合はエラーになる）ので、それで実験してみるとよい
公式document に詳細に記載があるのでそれを見ると良い。
- ただし必ずしもその表通りになるわけではないため表の下に記載されている注意書きをよく読む必要がある。
例えば varcharのサイズ変更 = Extending VARCHAR column size は表内では INPLACE=yes と記載されているが length を 255 から 256 に拡張する場合は ALGORITHM=COPY しか利用できないといった制限が記載されているので見落とさないようにすべき。（MySQL8.0 公式doc）
余談だが上記の事象については MySQLでのVARCHARサイズ変更を行うALTER TABLEについて - だいたいよくわからないブログに理由を記載している。

Alter Tableの性能特性についての公式ドキュメントの見方

にある表からやりたい操作を探せば良い。

表にあるそれぞれの意味と解釈は

項目	説明
インプレース In Place	yesだと既存データを新規ファイルにコピーせずに、既存ファイル内でalter tableを実行する。Noだと新規データファイルにデータをコピーするのでディスク容量を消費する。またsharedロックが取得され書き込みがブロックされる。(yesだとalgorithm=INPLACE, noだとalgorithm=COPYとなる)
テーブルの再構築 Rebuilds Table	yesだと既存データの書き換えが走るのでテーブル行数に応じて時間がかかったりディスクIOが高まったりする
同時 DML の許可 Permits Concurrent DML	yesだとinsert/update/deleteなどの更新が行える(lock=none)
メタデータの変更のみ Only Modifies Metadata	yesだとメタデータ更新のみでalter tableがすぐ完了する

より詳細な解説は以下の記事を参照すると良い。

mita2db.hateblo.jp

まとめ欄にも記載したが、表だけではなく表の下の解説まで読むことをおすすめする。読み飛ばすとALGORITHM=INPLACEで実行されると思っていたのにALGORITHM=COPYが発動してしまう(ALGORITHMについては後述）といったことがありえる。

algorithmの違い

algorithm=COPY

空テーブルを作成
全データ変換しながらコピー
テーブルを新しいものに入れ替えという手順でのマイグレーション

基本的に古い時代の物だがいくつかのケースではCOPYが採用される。

使われるケースは例えば

主キーの削除（削除&追加を1行で行うのは問題ないが、削除だけを行う操作を行うと）
データ型の変更

などになる。

データをコピーするためデータ件数によってはdisk IO負荷が高くなり、ディスク容量も必要で、実行時間も長くなることがあるので注意

algorithm=INPLACE

テーブルを作り直す
secondary indexを作り直す
テーブル作り直し中の更新操作をログに記録
テーブル作り直し中の更新操作を作り直したテーブルへapplyする

という手順でのマイグレーション

MySQL 5.xでALGORITHMを指定せずにADD COLUMNをした場合は大抵の場合は inplace になる。

誕生の経緯などは第30回 InnoDBオンラインDDLについて | gihyo.jpを参照

裏でテーブルを作り直すのでデータ件数によってはdisk IO負荷が高くなり、実行時間も長くなることがあるので注意

algorithm=INSTANT

メタデータのみを書き換えて、テーブルデータを全く変更しないモード

対応する操作は以下

テーブル末尾へのカラム追加
MODIFY COLUMN
etc

基本的にはMySQL 8以降の機能。

MySQL 8.0.12からInstant ADD COLUMNという機能が追加されました。

MySQLのInstant ADD COLUMNをちゃんと調べてみる - y-asaba@hatenablog

auroraでは今までサポートされていなかったが 2021/11/18にMySQL 8.x系互換のaurora MySQL v3がリリースされたので現在は利用可能のよう。

速度感の違いとしては

COPY 21.70 秒
INPLACE 6.54 秒
INSTANT 0.05秒

といった参考値が挙げられている。（もちろん諸条件によって変化する） MySQL 8.0 の INSTANT DDL について – スマートスタイル技術ブログ

カラム追加をalgorithm=INSTANTで実行する際の条件については MySQL :: MySQL 8.0 Reference Manual :: 17.12.1 Online DDL Operationsから (Syntax and Usage Notes > Adding a column )を参照

aurora fast DDL

仕組みは不明だがテーブルコピーなしで瞬時にカラム追加できる。

条件は以下

NULL を許容する列 (デフォルト値を持たない) のテーブル末尾への追加
パーティション分割されてない
レコードサイズがページサイズの半分以下

公式には

Currently, Aurora lab mode must be enabled to use fast DDL for Aurora MySQL. We don't recommend using fast DDL for production DB clusters. For information about enabling Aurora lab mode, see Amazon Aurora MySQL lab mode.

との記載があり、さらに aurora MySQL v3からはInstantで行くぞと書いてあるためこちらについて詳しくなる必要はあまりなさそう。

Alter Table時のlockモードとalgorithmの指定

意図されていないlockモードやalgorithmでの動作を防ぎたい場合は

ALTER TABLE test MODIFY text varchar(20), ALGORITHM=INPLACE,LOCK=NONE;

のように明示的に指定してやればよい。

もし想定と異なるモードで実行される状態であればエラーで停止するようになっている。

第30回 InnoDBオンラインDDLについて | gihyo.jp

LOCKについて

ページ上部に記載した表を見て意図したものを選ぶのがよい。

オプション	説明
NONE	read ○, write○ (何もブロックしない)
SHARED	read ○, write × (writeがブロックされる）
EXCLUSIVE	read ×, write × (read/writeがブロックされる)
DEFAULT	使用可能なもっとも低いレベルのロックを使用
LOCK句省略	DEFAULTと同じ

algorithmについて

オプション	説明
COPY	テーブルコピー方式
INPLACE	インプレース方式
INSTANT	インスタント方式
DEFAULT	一番負荷が少ない方式を採用
ALGORITHM句省略	DEFAULTと同じ

マイグレーションの速度感

インスタンスサイズや設定によるので概算でしかないが以下が参考になる。

https://www.percona.com/live/e18/sites/default/files/slides/Deep%20Dive%20on%20Amazon%20Aurora%20-%20FileId%20-%20160328.pdf の p.31より r3.large ~ r3.8xlarge

mysql	テーブルサイズ	実行時間目安 sec	実行時間の大体オーダー感
5.6	10GB	900 ~ 3960	15 min ~ 70 min
5.6	50GB	4680 ~ 23400	1.3h ~ 6.5h
5.6	100GB	14400 ~ 53460	4h ~ 15h
5.7	10GB	1080 ~ 1600	15 min ~ 30 min
5.7	50GB	5040	1.4h
5.7	100GB	9720	2.7h

繰り返すがinstanceサイズやレコードサイズ、mysqlの設定などあらゆる要素で変動するので、この時間で終わると思ってはいけない。

テーブルサイズの調べ方

MySQLでDBとテーブルのサイズを確認するSQL #MySQL - Qiitaを参考にした。

SELECT  
    table_name, 
    table_rows,
    floor((data_length+index_length)/1024/1024)/1024 AS dataGB  #総容量(インデックス込み)
FROM 
    information_schema.tables  
WHERE
    table_schema=database()  
ORDER BY
    table_name;