MySQLのオンラインDDLについて

2021.9.112022.5.1Writer: ideComment: 0

技術開発ブログ

お世話になっております。井出です。

開発する上でデータベースのテーブル構成を変更する事は
それなりの頻度で発生するかと思いますが
テーブルロックなどの知識が無いなかDDLを流すのは
運用する上で危険な行為になるため、整理も兼ねてまとめてみました。

オンラインDDLのお話をする前に、簡単にですが用語の説明を

DDLとは

DDLとは「Data Definition Language」の略で
データ定義言語という意味になります
※ definitionは「定義」

SQLでいうと以下がそれにあたります。
・CREATE
・DROP
・ALTER
・TRUNCATE

DMLとは

DMLとは「Data Manipulation Language」の略で
データ操作言語という意味になります。
※ manipulationは「操作」

SQLでいうと以下がそれにあたります。
・SELECT
・INSERT
・UPDATE
・DELETE

テーブルロックとは

テーブルをロックします。
コマンドでいうとこちら

LOCK TABLES

1	LOCK TABLES

解放はこちら

UNLOCK TABLES

1	UNLOCK TABLES

以下の状態でロックされるため、ロック解除されるまでDML操作が待ちの状態になります。

ロックの種類

ロック種別	ロックを行ったプロセス	ロック状態で実行されたプロセス
READ	READのみ可能 WRITEするとエラーになる	READのみ可能
WRITE	READ/WRITE可能	READ/WRITE不可

（本題）オンラインDDLとは？

前置きが長くなりましたが、本題のオンラインDDLについて

テーブルのロックを取らないのでDDLをDB実行時に
DBに対して変更系のSQLを行ってもロックが掛からずサービスを停止することがなくなる。

※ ただしMySQLのバージョンによって動作が異なります。

もうサポートが切れているバージョンもありますが
5.5系以前から8系まででの動作をまとめています。

5.5系以前

オンラインDDLはMySQL5.6から実装されました。
そのためMySQL5.5以前のバージョンでは
ALTER TABLEを実行中は、テーブルがロックされるため、
DML実行するとロックが解除されるまで待ちとなります。

運用中のサービスがロック中、DB操作できなくなるため
サービスをメンテナンス画面にするなど対応が大変です。

5.6系

5.6からオンラインDDLが実装されました。
DDL実行中もテーブルロックされず、待ちが発生しなくなったので
サービス稼働中でも実施しやすくなりました。

ただ一切問題無いのかといわれるとそうでもなく
オンラインDDLの開始前と完了前にDDLの対象テーブルに
実行中のトランザクションが存在した場合、
対象のトランザクションがコミットまたはロールバックするまで待機します。

この待機状態になっているDDL、DML が Waiting for table metadata lock
という状態になり、
トランザクションが完了するまで待ちのキューが増え続けご臨終状態となります。

詳細は公式ドキュメントに
https://dev.mysql.com/doc/refman/5.6/ja/metadata-locking.html

もしWaiting for Table metadata lockとなった場合

select * from information_schema.innodb_trx

1	select * from information_schema.innodb_trx

を叩き、TRX_STARTED(トランザクション開始時刻）が古いものからKillするのが良いとの事
https://dev.mysql.com/doc/refman/5.6/ja/information-schema-innodb-trx-table.html

Alter Table実行時にオンラインDDL操作を制御するための
オプション（LOCKとALGORITHM句）も追加されています。

LOCK句

オプション名	説明
NONE	クエリーと並列DML操作を許可
SHARED	クエリーを許可・並列DML操作をブロック
EXCLUSIVE	クエリーと並列DML操作をブロック
DEFAULT	使用可能なもっとも低いレベルのロックを使用

※ LOCK句を省略した場合はDEFAULTが設定される

ALGORITHM句

オプション名	説明
INPLACE	インプレース方式
COPY	テーブルコピー方式
DEFAULT	インプレース方式を優先

※ ALGORITHM句を省略した場合はDEFAULTが設定される

ただすべてのDDLに対して有効では無いので、
この辺りは公式ドキュメントを参考にするのがベストです。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.html#innodb-online-ddl-summary-grid

※カラムのデータ型を変更については5.6では非対応のため注意が必要です

公式ドキュメントを見て有効、無効を判断するとともに
オプションを付けることにより、無効のものであればエラーになるので、
明示的にLOCK句とALGORITHM句を記述する設計にすれば大丈夫かもしれません。
カラムのデータ型を変更を行いたい場合の解決にはなっていませんが。。

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

1	ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

5.7系

・varcharのカラム長の変更をサポート
※内容うす

MySQL8.0.12系以降

・Instant Add Columnがサポート

https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/
制限は色々とありますがadd columnの処理がかなり高速化されました。
※ 内容うっす！

補足：RailsのMigrationでオプションをつける方法

普段Railsメインで開発しているため、
Migrationで「ALGORITHM」「LOCK」のオプションをつける方法を

executeでSQL文実行しつつオプションをという感じで。分かりやすくてナイスですね。
rails love

class Test < ActiveRecord::Migration
  def up
    execute "ALTER TABLE `users` COMMENT 'test', ALGORITHM=INPLACE, LOCK=NONE"
  end
  def down
    execute "ALTER TABLE `users` COMMENT ''    , ALGORITHM=INPLACE, LOCK=NONE"
  end
end

# migrate実行時
== 20210911091017 : migrating ==============================
-- execute("ALTER TABLE `users` COMMENT 'test', ALGORITHM=INPLACE, LOCK=NONE") 
   -> 0.0108s
== 20210911091017 : migrated (0.0109s) =====================

# rollback実行時
== 20210911091017 : reverting ==============================
-- execute("ALTER TABLE `users` COMMENT ''    , ALGORITHM=INPLACE, LOCK=NONE")
   -> 0.0159s
== 20210911091017 : reverted (0.0160s) =====================

class Test < ActiveRecord::Migration

def up

execute "ALTER TABLE `users` COMMENT 'test', ALGORITHM=INPLACE, LOCK=NONE"

end

def down

execute "ALTER TABLE `users` COMMENT '' , ALGORITHM=INPLACE, LOCK=NONE"

end

# migrate実行時

== 20210911091017 : migrating ==============================

-- execute("ALTER TABLE `users` COMMENT 'test', ALGORITHM=INPLACE, LOCK=NONE")

-> 0.0108s

== 20210911091017 : migrated (0.0109s) =====================

# rollback実行時

== 20210911091017 : reverting ==============================

-- execute("ALTER TABLE `users` COMMENT '' , ALGORITHM=INPLACE, LOCK=NONE")

-> 0.0159s

== 20210911091017 : reverted (0.0160s) =====================

公式ドキュメントを参照ばかりの記事になりましたが。。
ありがとうございました！