MySQLのオンラインDDLでレプリケーション遅延を回避する方法

対象読者

MySQLのカラム追加やインデックス追加でレプリケーション遅延が出て困っている人。

はじめに

この記事は MySQL Advent Calendar 2021 20日目のものです。
19日目 pt-online-schema-changeによる負荷を好きなメトリクスでコントロールする に続き、DDL関連のお話です。

この記事でお話すること

下記のような構成で MySQL の Replication が組まれており
Replica ではサービスから参照系のクエリが実行されているものとします。

Source
├── Replica#1 <- Read Query
└── Replica#2 <- Read Query

この状況でサイズの大きいテーブルに対してDDLを行うと、Replica でのレプリケーション遅延を招いてしまう恐れがあります。
遅延が発生すると Replica を参照しているサービスで Source に書き込まれた最新のレコードが取得できない状況になってしまい、サービスに影響が出ます。

-- もしこれが Source で1時間かかるなら、Replica でも1時間かかる
ALTER TABLE large_table XXX;

本記事ではオンラインDDLでレプリケーション遅延を回避する方法について記述します。

※ Source/Replica = Master/Slave です。慣れましょう

方法

MySQLのバージョンに応じて 2種類。そしてツールを使う方法があります。

1. INSTANT DDL (MySQL 8.0.12 ~)
   特定のDDLに限る

2. Rolling Schema Upgrade (RSU) (MySQL 5.6 ~)
   オンラインDDLに限る

3. ツールを利用する
   今回は紹介に留めて割愛します

1. INSTANT DDL (MySQL 8.0.12 ~)

MySQL 8.0.12 以降で使えるアルゴリズムです。
従来の ALGORITHM=COPY や INPLACE とは異なり、メタデータのみを更新するため高速です。
ただし、いくつか制約があります。

• INSTANTアルゴリズムが使えるDDL処理に限られる
• カラム追加 （末尾のみ可能）
• Virtualカラムの追加/削除
• カラムのデフォルト値の追加/削除
• ENUM/SET型の定数の変更
• Indexの型の変更
• テーブルのリネーム
• カラムのリネーム (MySQL 8.0.28~)
• カラムの削除 (MySQL 8.0.29~)
• Index追加には非対応です。（方法2のRSUを検討してください）

詳細はこちらを参考にしてください。

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-12.html#mysqld-8-0-12-feature
• https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html

ALGORITM=INSTANT に対応しているDDLならそのまま実行すればOKです。

MySQL8.0.12~

-- 実行したいDDL
ALTER TABLE large_table ADD any_flag TINYINT UNSIGNED;

INSTANT DDL ができるかどうか不明な場合は末尾に , ALGORITHM=INSTANT を追記して明示してください。

MySQL8.0.12~

-- 末尾に , ALGORITHM=INSTANT を追記
ALTER TABLE large_table ADD any_flag TINYINT UNSIGNED, ALGORITHM=INSTANT;

検証

sysbench で作った 2GBのテーブルにカラム追加します。

MySQL8.0.12~

-- ALGORITHM=INPLACE
ALTER TABLE sbtest1 ADD any_flag TINYINT UNSIGNED, ALGORITHM=INPLACE;
Query OK, 0 rows affected, 1 warning (1 min 41.57 sec)

-- ALGORITHM=COPY
ALTER TABLE sbtest1 ADD any_flag TINYINT UNSIGNED, ALGORITHM=COPY;
Query OK, 10000000 rows affected, 1 warning (3 min 44.57 sec)

-- ALGORITHM=INSTANT
ALTER TABLE sbtest1 ADD any_flag TINYINT UNSIGNED, ALGORITHM=INSTANT;
Query OK, 0 rows affected, 1 warning (0.03 sec)

-- 明示しない場合。 ちゃんと ALGORITHM=INSTANT が選択されたようですね
ALTER TABLE sbtest1 ADD any_flag TINYINT UNSIGNED;
Query OK, 0 rows affected, 1 warning (0.04 sec)

INSTANT が圧倒的に速いぞ (∩´∀｀)∩ﾜｰｲ

MySQL8.0.12~

-- 末尾以外に追加しようとした場合
ALTER TABLE sbtest1 ADD any_flag TINYINT UNSIGNED AFTER c, ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

なお、末尾以外のカラム追加には使えないです。COPY または INPLACE を使うように促されます。

2. Rolling Schema Upgrade (RSU)

次に INSTANT DDL が利用できないバージョン（MySQL 5.6 ~）での方法です。
また、 MySQL 8.0.12 ~ の環境でも ADD INDEX の場合は INSTANT DDL を利用できないのでこちらを選択することになります。

Rolling Schema Upgrade (RSU)というのはオペレーションに対する呼称なので、
公式には MySQL のドキュメントに記載がありません。（私の周りでは Rolling Update とも呼称されています。）
どうやら Galera Cluster では公式用語のようです。

下記の場合に実行可能です。

• オンラインDDLが可能であること
• オンラインDDLでないと、テーブルの書き込みをブロックしてしまいます。
• Replica から先に適用するので Sourceからのbinlogを受け付けられるDDLであること
• カラム追加の場合は末尾にのみ追加可能
  • binlog_format=MIXED/STATEMENT の場合は更に INSERT 文の確認が必要です。

※ 今回はカラム追加に絞って記述します

RSU の仕組み

1. 対象の DDL を binlog に書かないようにして
2. Replica で実行してから
3. Source で実行

という順序でレプリケーション遅延を回避します。
「レプリケーションしないから、レプリケーション遅延もしない」ということですね。

• SET SESSION sql_log_bin = OFF; をすることで対象のDDLをレプリケーションさせないで、1台ずつ作業します。
• Replica が全台終わってから Source をやりましょう。

※ MySQLのバージョンや実行するDDLによりけりなので、必ずお手元の環境で検証をしてください。

RSU の手順

1. オンラインDDLが可能か確認

ALGORITHM=INPLACE, LOCK=NONE で実行できるDDLのことを指します。
オンラインDDLができない場合、テーブルの書き込みをブロックしてしまいます。
対象のテーブルに書き込みができなくなってしまうため、オンラインDDLが可能であることが前提です。
オンラインDDLについてはこちらを参考にしてください。

• https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl.html
• MySQL道普請便り: 第30回 InnoDBオンラインDDLについて

オンラインDDLができるか不明な場合はお手元の環境で実行したいDDLの末尾に
, ALGORITHM=INPLACE,LOCK=NONE; を付けて実行してください。

お手元の環境で試しましょう

-- 実行したいDDL
ALTER TABLE large_table ADD any_flag TINYINT UNSIGNED;
-- これで通るかどうか。通らなければ不可です。メンテの予定を立てましょう
ALTER TABLE large_table ADD any_flag TINYINT UNSIGNED, ALGORITHM=INPLACE, LOCK=NONE;

2. カラム追加の場合

2-1. カラム追加は末尾にのみ追加可能

Replica から実行するため、作業が完了するまでテーブル定義が異なる状態でレプリケーションは動き続けます。
binlog_format = ROW な環境が一般的だと思いますので、SQL Thread が停止することを回避するために末尾に追加します。

binlog_format	カラム追加の位置	INSERT文の確認
ROW	末尾のみ可能	不要
MIXED	ROW が選ばれる可能性があるので、末尾のみ可能	必要
STATEMENT	末尾以外も追加可能だが、非決定的な更新のリスクがあるので STATEMENT 自体が非推奨	必要

2-2. INSERT 文の確認

binlog_format = MIXED または STATEMENT の場合、INSERT文の確認が必要です。

binlog_format=MIXED/STATEMENTで注意

-- NGパターン。カラム未指定のINSERT
INSERT INTO table (1, 'one');
-- 対象のテーブルの INSERT がカラム指定ならOK
INSERT INTO table (col1, col2) VALUES (1, 'one');

DDL対象のテーブルに対して
カラム指定が無いINSERTがある場合、Replica でカラム数が合わなくなり Replication が停止する原因になります。
カラム指定でINSERTが実行されていることを確認してください。

2-3. 余談

binlog_format=ROW で、末尾以外にカラム追加してもレプリケーションが止まらないパターンがあります。

余談です

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Replica のみ
-- `pad` の手前に同じ定義の `dummy_pad` を入れると...
ALTER TABLE sbtest1 ADD dummy_pad char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '' AFTER c;

この場合、レプリケーションは停止しませんが、本来 pad に入るはずの値が dummy_pad に入ってしまいます。
もちろん意図している動作と異なるので、真似しないでください。

3. Replica から実行

複数のReplicaがある場合、1台ずつ実行するのがより安全です。

ここはReplicaです

-- binlog に記録しない & GTID を発行しない
SET SESSION sql_log_bin = OFF;
-- binlog_format = row または mixed の場合
SET GLOBAL slave_type_conversions = ALL_NON_LOSSY;
-- オンラインDDLで実行
ALTER TABLE large_table ADD any_flag TINYINT UNSIGNED, ALGORITHM=INPLACE, LOCK=NONE;

3-1. slave_type_conversions

binlog_format が row の場合、あるいは mixed で binlog が row になった場合に、Source, Replica でテーブルの型が一致している必要がある。
対策として、Replica 側で SET GLOBAL slave_type_conversions = ALL_NON_LOSSY; を行う。
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0040

4. 最後に Source から実行

Replica で完了したら、最後に Source で実行します。

ここはSourceです

-- binlog に記録しない & GTID を発行しない
SET SESSION sql_log_bin = OFF;
-- オンラインDDLで実行
ALTER TABLE large_table ADD any_flag TINYINT UNSIGNED, ALGORITHM=INPLACE, LOCK=NONE;

RSU 注意点

RSU が可能な DDL

Replica から先に適用するので Source からの binlog を受け付けられる DDL でしか利用できません。
例えば

• ADD col AFTER {末尾以外のカラム}
• CHAR(20) -> CHAR(10) への MODIFY

は不可です。

Source/Replica を同時に作業しない

Replica 全台で作業が終わってから Source をやりましょう。
カラム追加の場合、Source で先に DDL が終わってしまうと、Replica でカラム数が合わなくなり、レプリケーションが停止する原因になります。
例外として INDEX 追加であれば同時でも可能です。

binlog に残らない

実行した DDL が binlog に残らないため、リカバリができません。
実行後はバックアップを取ることをおすすめします。

DEFAULT の値が非決定的なパターン

このようなパターンは気をつけてください。

-- DEFAULT CURRENT_TIMESTAMP で非決定的
ALTER TABLE large_table ADD updated_at DATETIME DEFAULT CURRENT_TIMESTAMP;

これを Replica -> Source の順序で実行してしまうと Source/Replica で値がずれてしまいます。
回避策としては、RSU では固定値を指定して、最後に通常のレプリケーションで MODIFY する方法です。

-- Replica -> Source の順序で実行
SET SESSION sql_log_bin = OFF;
ALTER TABLE large_table ADD updated_at DATETIME DEFAULT '2021-12-20';

-- 最後に Source で実行。
-- レプリケーションで反映したいので ON に戻します
SET SESSION sql_log_bin = ON;
ALTER TABLE large_table MODIFY updated_at DATETIME DEFAULT CURRENT_TIMESTAMP;

カラムのデフォルト値の変更はメタデータのみの変更なのでレプリケーション遅延は発生しません。

3. ツールを利用する

前述の2つの方法は純粋にMySQLだけでやりくりするものですが、オンラインDDLができない場合でも、レプリケーション遅延を最小限に抑えてDDLを実行できるツールもあります。
記事が長くなってしまったので割愛しますが、こちらも選択肢として紹介します。

gh-ost

• MySQL道普請便り:第138回 オンラインスキーママイグレーションツール gh-ostを使ってみよう［その1］
• MySQL道普請便り:第139回 オンラインスキーママイグレーションツール gh-ostを使ってみよう［その2］
• MySQL道普請便り:第140回 オンラインスキーママイグレーションツール gh-ostを使ってみよう［その3］

pt-online-schema-change

ツールの注意点

予期せぬ挙動をする場合があるため、検証してからご利用ください。

• pt-online-schema-changeとgh-ostの比較(データが損失するかもしれないAlterTable編)

さいごに

Rolling Schema Upgrade よりも INSTANT DDL の方が速くて安全で圧倒的に楽ですね。
本記事が皆さんの現場の MySQL を 8.0 へバージョンアップさせるモチベーションにつながればと思います。

※ 他にも RSU の注意点があったらコメントいただきたいです。
※ 記事をレビューしてくれた同僚に感謝を！主キー！だいしゅきー！