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

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

# はじめに
この記事は [MySQL Advent Calendar 2021](https://qiita.com/advent-calendar/2021/mysql) 20日目のものです。
19日目 [pt-online-schema-changeによる負荷を好きなメトリクスでコントロールする](https://shallow1729.hatenablog.com/entry/modify-pt-osc-for-rds) に続き、DDL関連のお話です。


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

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

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

```sql
-- もしこれが Source で1時間かかるなら、Replica でも1時間かかる
ALTER TABLE large_table XXX;
```
本記事ではオンラインDDLでレプリケーション遅延を回避する方法について記述します。

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


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

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

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

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

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

- `INSTANTアルゴリズムが使えるDDL処理に限られる`
 - カラム追加 `（末尾のみ可能）`
 - Virtualカラムの追加/削除
 - カラムのデフォルト値の追加/削除
 - ENUM/SET型の定数の変更
 - Indexの型の変更
 - テーブルのリネーム 
 - カラムのリネーム ([MySQL 8.0.28~](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html#mysqld-8-0-28-feature))
 - カラムの削除 ([MySQL 8.0.29~](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-29.html#mysqld-8-0-29-feature))
- `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です。

```sql:MySQL8.0.12~
-- 実行したいDDL
ALTER TABLE large_table ADD any_flag TINYINT UNSIGNED;
```

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

```sql:MySQL8.0.12~
-- 末尾に , ALGORITHM=INSTANT を追記
ALTER TABLE large_table ADD any_flag TINYINT UNSIGNED, ALGORITHM=INSTANT;
```

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

```sql: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 が圧倒的に速いぞ (∩´∀｀)∩ﾜｰｲ

```sql: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 では公式用語のようです。](https://galeracluster.com/library/documentation/schema-upgrades.html)

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

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

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

## RSU の仕組み
1. 対象の DDL を binlog に書かないようにして 
1. **Replica で実行してから**
1. 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について](https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0030)

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

```sql:お手元の環境で試しましょう
-- 実行したい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 | 末尾以外も追加可能だが、非決定的な更新のリスクがあるので <br>STATEMENT 自体が非推奨 | **必要** |

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

```sql: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 で、末尾以外にカラム追加してもレプリケーションが止まらないパターンがあります。

```sql:余談です
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台ずつ実行するのがより安全です。

```sql:ここは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 で実行します。

```sql:ここは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 の値が非決定的なパターン
このようなパターンは気をつけてください。

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

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

```sql
-- 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
https://github.com/github/gh-ost

- [MySQL道普請便り:第138回 オンラインスキーママイグレーションツール gh-ostを使ってみよう［その1］](https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0138)
- [MySQL道普請便り:第139回 オンラインスキーママイグレーションツール gh-ostを使ってみよう［その2］](https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0139)
- [MySQL道普請便り:第140回 オンラインスキーママイグレーションツール gh-ostを使ってみよう［その3］](https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0140)

## pt-online-schema-change
https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

## ツールの注意点
予期せぬ挙動をする場合があるため、検証してからご利用ください。

- [pt-online-schema-changeとgh-ostの比較(データが損失するかもしれないAlterTable編)](https://kenken0807.hatenablog.com/entry/2021/12/14/000622)


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

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