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

これは何

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

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

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

f:id:matsu_chara:20211128214831p:plain

表にあるそれぞれの意味と解釈は以下の記事が詳しい。

mita2db.hateblo.jp

algorithmの違い

algorithm=COPY

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

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

使われるケースは例えば

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

などになる。

  • exclusive lockを取ってデータを作り直すので、その間は対象のテーブルへの読み書きができなくなる
  • disk IO負荷が高いので注意

algorithm=INPLACE

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

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

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

誕生の経緯などは第30回 InnoDBオンラインDDLについて:MySQL道普請便り|gihyo.jp … 技術評論社を参照

  • exclusive lockを取らないので並列して参照や更新ができる
  • 裏でテーブルを作り直すので結構重い

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 について | スマートスタイル TECH BLOG

カラム追加をalgorithm=INSTANTで実行する際の条件については MySQL :: MySQL 8.0 Reference Manual :: 15.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について:MySQL道普請便り|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 - 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;