Go to Qiita Advent Calendar 2024 Top
LoginSignup
14
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

MySQLAdvent Calendar 2021

Day 20
@yy_harachi

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

Last updated at Posted at 2021-12-19

対象読者

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を検討してください）

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

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についてはこちらを参考にしてください。

オンライン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

pt-online-schema-change

ツールの注意点

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

さいごに

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

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

14
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
14
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?