MySQLのオンラインDDLについて
お世話になっております。井出です。
開発する上でデータベースのテーブル構成を変更する事は
それなりの頻度で発生するかと思いますが
テーブルロックなどの知識が無いなかDDLを流すのは
運用する上で危険な行為になるため、整理も兼ねてまとめてみました。
オンラインDDLのお話をする前に、簡単にですが用語の説明を
DDLとは
DDLとは「Data Definition Language」の略で
データ定義言語という意味になります
※ definitionは「定義」
SQLでいうと以下がそれにあたります。
・CREATE
・DROP
・ALTER
・TRUNCATE
DMLとは
DMLとは「Data Manipulation Language」の略で
データ操作言語という意味になります。
※ manipulationは「操作」
SQLでいうと以下がそれにあたります。
・SELECT
・INSERT
・UPDATE
・DELETE
テーブルロックとは
テーブルをロックします。
コマンドでいうとこちら
|
1 |
LOCK 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となった場合
|
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句を記述する設計にすれば大丈夫かもしれません。
カラムのデータ型を変更を行いたい場合の解決にはなっていませんが。。
|
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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) ===================== |
公式ドキュメントを参照ばかりの記事になりましたが。。
ありがとうございました！