MySQL勉強会〜ロックについて(DDL編)〜を開催しました！

f:id:fkmy:20200811121843j:plain

こんにちは。最近リモートワーク用にマイクを買ったソフトウェアエンジニアの福間(fkmy)です。

先月、ANDPADのデータベースの技術顧問をして頂いてる三谷(mita2)さんによるロックの基礎編)〜について勉強会を開催しました。今月はロックのDDL編について8/4(月)に勉強会を実施しました。重要な箇所をピックアップします！

また今回も在宅勤務期間中のためオンライン開催となり当日は16名が参加していました。

内容

当日の資料はこちらになります。

DDLについて

DDLとはData Definition Languageの略称でデータ構造を定義するための言語のことです。SQLではCREATE文、DROP文、ALTER文、TRUNCATE文が該当します。

DDLの仕組みと改善の歩みについて

MySQLのALTER TABLEの初期実装
- 新しいテーブル定義のテーブルにデータコピーする
- 実行中は書き込みがブロックされる

バージョンアップごとに機能拡張されています

ver	サポート	概要
v5.5	・First Index Creationがサポート	インデックスのみの変更はテーブルをコピーせずインデックスを追加することにより高速化
v5.6	・オンラインDDLがサポート	更新中にブロックされず、テーブルのロック待ちが発生しなくなった
v5.7	・オンラインDDLのサポート範囲拡大	varcharのカラム長の変更もサポート
v8.0	・DDLがアトミック化・Instant Add Columnがサポート	・処理途中で止まってもデータが壊れなくなった・Add Columnの高速化

オンラインDDLについて

オンラインDDLの対応判別表

項目	対応	補足
インデックスの追加と削除	○	-
カラムの追加と削除	○	-
varcharカラム長の変更	○	v5.6以前は非対応
カラムの型変更	☓	-

手元での確認方法

ALTER TABLE 実行時に LOCK=NONE, ALGORITHM=INPLACEを指定して判別が可能です。オンラインDDL非対応の場合は以下のようにエラーが返ってきます。

-- コマンド例およびエラー
mysql> ALTER TABLE sbtest.sbtest1 MODIFY COLUMN v1 VARCHAR(110), LOCK=NONE, ALGORITHM=INPLACE;

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

metadata lock について

metadata lock：完了直前に行われるテーブル定義などのロックのことです。
問題となるケース
- 長いトランザクションがALTERの完了をブロックしmetadata lock待ちのALTERの完了を後続のSQLが待つことになる
対策案
- 長いトランザクションが流れていないタイミングを狙う
- lock_wait_time を短く設定する
  - ただし、過度に短いとmetadata lockが取れずALTERが失敗しやすくなります

ALTER関連のTips

複数の変更は1つのALTER 文で実行できます
- ただし、オンラインDDL非対応のものを混ぜるとロックが掛かります
Rolling Schema Upgrade
- オンラインでDDLを実行する運用手法です
- レプリケーション遅延をなくすことを目的としています
  Rolling Schema Upgrade
pt-online-schema-change
- オンラインでDDLを実行するツールです
- オンラインDDLに対応していないものをオンライン実行可能にします
  pt-online-schema-change