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を実行する運用手法です
    • レプリケーション遅延をなくすことを目的としています
      f:id:fkmy:20200811113838p:plain
      Rolling Schema Upgrade
  • pt-online-schema-change

    • オンラインでDDLを実行するツールです
    • オンラインDDLに対応していないものをオンライン実行可能にします
      f:id:fkmy:20200811113945p:plain
      pt-online-schema-change

Amazon Auroraについて

  • DDLによるレプリケーション遅延がほとんど発生しない
  • レプリカではmetadata lock待ちは発生しませんがマスターではmetadata lock待ちが発生します
    • 検証結果が三谷さんのブログに記載されています。 mita2db.hateblo.jp

さいごに

DDLによるレプリケーション遅延が素のMySQLでは発生してしまいますが、Auroraではほとんど発生しないらしくAurora最高!!というのが1番の感想です。AndpadでもAuroraを使用して開発を行っているので、オンラインDDL対応のものは積極的に日中リリースしていきたいと思います。

勉強会を通してMySQLと徐々に仲良くなれてる気がするので来月のデータベース勉強会も楽しみです!

ANDPADではエンジニアを募集しています

データベース勉強会に限らず定期的に勉強会を開催していますので一緒に成長したい!と思った方は以下の採用サイトから詳しい職場環境などもご覧になってみてください!

engineer.andpad.co.jp