mita2 database life

主にMySQLに関するメモです

MySQL Shell 8.0 を CentOS7 上でソースからビルドする

MySQL

前回のエントリーでMySQL Shellのバグについて書きました。バグの原因を調査するために、MySQL Shellをビルドしたんですが、ビルドに苦労しました、、、 MySQL Shell をビルドするために乗り越えた障害と対応を方法をメモしておきます。

まず、MySQL Shell をビルドするには、事前にMySQL Server もビルドしておく必要があります。

mita2db.hateblo.jp

2つのcmakeが必要

MySQL Server 本体は cmake 3系 が必要なのですが、MySQL Shell は cmake 2 系でないと通りません。。。いつものノリでcmake3を使うとコケます。

$ cmake3 .. -DMYSQL_SOURCE_DIR=/home/user1/mysql-8.0.22/ -DMYSQL_BUILD_DIR=/home/user1/mysql-8.0.22/bld -DHAVE_PYTHON=1
<snip>
CMake Error at /usr/share/cmake3/Modules/FindPackageHandleStandardArgs.cmake:164 (message):
  Could NOT find Protobuf: Found unsuitable version "ERROR.ERROR.ERROR", but
  required is at least "3.11.4" (found
  /home/user1/mysql-8.0.22/bld/extra/protobuf/protobuf-3.11.4/cmake/libprotobuf.a)
Call Stack (most recent call first):
  /usr/share/cmake3/Modules/FindPackageHandleStandardArgs.cmake:443 (_FPHSA_FAILURE_MESSAGE)
  /usr/share/cmake3/Modules/FindProtobuf.cmake:626 (FIND_PACKAGE_HANDLE_STANDARD_ARGS)
  cmake/protobuf.cmake:117 (FIND_PACKAGE)
  CMakeLists.txt:414 (INCLUDE)

CentOS7の標準のcmakeは2系なので、普通にyumで入れれば大丈夫。

$ sudo yum install -y cmake
<snip>
Installed:
  cmake.x86_64 0:2.8.12.2-2.el7

Dependency Installed:
  libarchive.x86_64 0:3.1.2-14.el7_7
# ビルドに必要なヘッダファイルたちもインストール
$ sudo yum install python3-devel libcurl-devel
<snip>

python が見つからないと言われる

このバージョン（2.8.12）のcmake は、python-3.6 に対応していないようです。そのままだと「pythonが見つからん！」とエラーが出ます。

$ cd mysql-shell-8.0.22-src
$ mkdir bld
$ cd bld

$ scl enable devtoolset-7 bash
$ cmake .. -DMYSQL_SOURCE_DIR=/home/user1/mysql-8.0.22/ -DMYSQL_BUILD_DIR=/home/user1/mysql-8.0.22/bld -DHAVE_PYTHON=1
<snip>
CMake Error at /usr/share/cmake/Modules/FindPackageHandleStandardArgs.cmake:108 (message):
  Could NOT find PythonLibs (missing: PYTHON_LIBRARIES PYTHON_INCLUDE_DIRS)
  (Required is at least version "3.4.3")
Call Stack (most recent call first):
  /usr/share/cmake/Modules/FindPackageHandleStandardArgs.cmake:315 (_FPHSA_FAILURE_MESSAGE)
  /usr/share/cmake/Modules/FindPythonLibs.cmake:197 (FIND_PACKAGE_HANDLE_STANDARD_ARGS)
  CMakeLists.txt:277 (FIND_PACKAGE)

FindPythonLibs.cmake の_PYTHON3_VERSIONS に、python 3.6 を追加して回避します（もしかしたら、python を 3.4以下にダウングレードするほうが正しいのかもしれませんが、とりあえず、これで通ったので）。

$ sudo vim /usr/share/cmake/Modules/FindPythonLibs.cmake
set(_PYTHON3_VERSIONS 3.6 3.4 3.3 3.2 3.1 3.0)

MySQL Server のビルドオプションも調整が必要

cmake が終わって、make を走らせていくと、libprotobuf.a が見つからないためエラーになりました。

$ make -j 4
<snip>
Scanning dependencies of target mysqlshrec
Scanning dependencies of target mysqlsh
make[2]: *** No rule to make target `/home/user1/mysql-8.0.22/bld/extra/protobuf/protobuf-3.11.4/cmake/libprotobuf.a', needed by `bin/mysqlsh'.  Stop.
<snip>

protobuf のソースは MySQL Server 本体のほうにあります。libprotobuf を static ライブラリとしてビルドするよう、MySQL Server のビルドオプション protobuf_BUILD_SHARED_LIBS=OFF を足してビルドしなおす必要がありました。

$ cmake3 .. -DWITH_BOOST=/home/user1/mysql-8.0.22/boost  -Dprotobuf_BUILD_SHARED_LIBS=OFF
-- Running cmake version 3.17.3
-- Could NOT find Git (missing: GIT_EXECUTABLE)
-- MySQL 8.0.22

$ make -j 4

MySQL Shell dumpInstance が一貫性の壊れたバックアップを生成するバグの件（修正済み）

MySQL

このバグは MySQL Shell 8.0.23 で修正済みです。

前回のエントリーで MySQL Shell の dumpInstance にはバグがあると書きました。 今回は、そのバグについてです。

どんなバグか

バックアップの一貫性が失われるバグです。一貫性が失われるている状態とは、バックアップデータの時間軸がずれていることを指します。 あるレコードは10:00:00の状態だが、別のレコードは10:00:01の状態であるといったケースです。

MySQL Shell 8.0.22 でバグがあることを確認しています（それ以前も同じバグがあるかもしれない、未確認です）

Workaround

このバグには簡単なワークアラウンド（回避策）があります。 プロシージャとファンクションをバックアップ対象外にします。 MySQL Shell でバックアップを取っている人はこのオプションを設定しましょう。

util.dumpInstance("mydump", {routines:false});

このバグはMySQL Shellのバグです。mysqldumpやxtrabackupやら他の方法のバックアップには影響ありません（ご安心ください）。

再現方法

簡単に再現します。このような2つのテーブルにINSERTするトランザクションを連続して実行しながら dumpInstance でバックアップを取ります。

BEGIN
  INSERT INTO db1.t1 (c) VALUES()
  INSERT INTO db2.t1 (c) VALUES()
COMMIT
# 運が良いと再現しないので何度もバックアップします
mysqlsh> \py
mysqlsh> for i in range(10): util.dump_instance('/tmp/bk%s' % i, compression='none')

一貫性が正しく保たれていれば、生成されたバックアップの db1.t1 と db2.t1 の件数が一致するはずです。 しかし、ズレたバックアップが生成されてしまいます。。。

$ tail -n3 bk5/db*@t1*tsv | less
==> bk5/db1@t1@@0.tsv <==
2222 
2223
2224

==> bk5/db2@t1@@0.tsv <==
2221 
2222
2223

バグの原因

MySQL Shellの dumpInstance は、FLUSH TABLES WITH READ LOCK か LOCK TABLES で静止点を設け、その間に各Dumpスレッドが、トランザクションを開始します。 各ダンプスレッドが静止点から開始したトランザクションを使って、データをSELECTすることで一貫性を保ったバックアップを生成する仕組みです。

f:id:mita2db:20201104230031p:plain

よくよく調べると、一部のダンプスレッドではトランザクションを開始したあと、プロシージャをバックアップするため、mysql.proc テーブルのロックをとりに行ってました。 LOCK TABLES は暗黙的にトランザクションをコミットします。これによって特定のダンプスレッドだけ静止点からずれたデータをダンプしてしまう状態になってしまっていました。

f:id:mita2db:20201104230857p:plain

ということで、バグレポを上げています。気になる人は Subscribe しておくと良いでしょう。

MySQL Bugs: #101410: mysqlsh dumpInstance backup consistency is broken

MySQL Shell dumpInstance の仕組みと8.0.22 わいわい会

MySQL

MySQL Release note でわいわい言う勉強会 8.0.22 でLTしてきました。

MySQL Shell の dumpInstance の仕組み

MySQL Shell のバックアップ機能 dumpInstance がどのようにして、一貫性のあるバックアップを実現しているか説明しました。 8.0.21 までは FLUSH TALBES WITH READ LOCK を利用してロックをかける方式のみでしたが、 8.0.22 以降では、FTWRLが使えない場合は、LOCK TABLES を代替として利用するようになっています。

これで、Amazon RDSなどFTWRLが使えない環境でも、一貫性のあるバックアップが取れるようになりました。 このあたりは、@atsuizo さんも解説されています。

atsuizo.hatenadiary.jp

実は、MySQL Shell 8.0.22 時点ではバグがあって、一貫性のあるバックアップが取れないときがあります（追記：このバグは MySQL Shell 8.0.23 で修正済みです） この件はバグレポートを上げています。詳細は以下のエントリーに書いています。

mita2db.hateblo.jp

サーバサイド Prepared Statement の仕組み

8.0.22 会では、Derived Condition Pushdown Optimization、SRVレコードを使った接続の話、などなど。。。いろいろな話題が出ました。 中でも、Prepared Statement の話が印象深かったので、書き残しておきたいと思います。

みなさんは、サーバサイド Prepared Statement は使っていますか？自分は使ってません。。。 経験則ですが、アプリとDBサーバを一往復余計にする分、むしろ使うと遅くなることが多いように思います。言い換えると、MySQLのサーバサイドプリペアドステートメントではPREPARE時に生成しておける情報が少ないとも言えると思います *1

8.0.22 でこの辺りに多少改善が入ったようです（サーバサイドプリペアドステートメントを積極利用するほどの改善ではないですが、、、）

Historically, only the parse stage has been reused for each execution. With this work both the parse stage and the resolve stage will be prepared once and executed many times when prepared statements are executed. The planning stage will still be repeated for each prepared statement execution. The motivation for this work is to improve performance by repeated executions and also to simplify the code base.

https://mysqlserverteam.com/the-mysql-8-0-22-maintenance-release-is-generally-available/

プリペア時にはSQLの parse だけをやっていて、resolve と planning は EXECUTE 時に毎回やっていました。 これが、8.0.22 では parse に加えて、resolve ステージもPREPARE時に１回だけやるように改善したと。 resolve stage って何かっていうと、「オブジェクト（テーブルやら、カラムやら）が存在している確認したりするフェーズ」らしいです。

プリペアドステートメントの実装の一旦が知れて面白かったです。

8.022 のプリペアドステートメントまわりの挙動の変化は tom__bo さんや tmtms さんも解説してます。

tombo2.hatenablog.com

tmtms.hatenablog.com

*1:Oracleだと逆にプリペアドステートメントを使わないと、ハードパースになり、遅くなった記憶があります

MySQL キャラクタセット（文字コード）の変換方法おさらい

MySQL

ALTER TABLE xxx MODIFY 〜 でキャラクタセットを変換するのと、ALTER TABLE xxx CONVERT TO〜 で変換するのは、どう違うんだっけ？ ってなったので、おさらい。

データベースのデフォルトキャラクタセット

MySQL はカラム単位でキャラクタセット（文字コード）を指定することが出来ます。

データベース（スキーマ）のキャラクタセットは、「テーブル作成時にテーブルのキャラクタセットを指定しなかったときに採用されるキャラクタセット」の指定です。 CREATE DATABASE db1 CHARACTER SET ujis と DEFAULT を省略して書くことが出来ますが、省略せずに書くと、DEFAULT CHARACTER SET です。 デフォルトのキャラクタセットの指定であることが、SHOW CREATE TABLEの結果からもわかります。

# データベースのデフォルトキャラクタセットを ujis にする
mysql> CREATE DATABASE db1 CHARACTER SET ujis;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE DATABASE db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET ujis */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

テーブルの DEFAULT CHARACTER SET を指定しない場合）

DEFAULT CHARSET=ujis と、データベースのデフォルトキャラクタセットを引き継いでいることがわかる。

mysql> CREATE TABLE tbl_no_char (pk SERIAL, c1 VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_no_char \G
*************************** 1. row ***************************
       Table: tbl_no_char
Create Table: CREATE TABLE `tbl_no_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=ujis
1 row in set (0.00 sec)

テーブルの DEFAULT CHARACTER SET を明示的に utf8mb4 に指定）

mysql> CREATE TABLE tbl_with_char (pk SERIAL c1 VARCHAR(255) NOT NULL) CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_with_char \G
*************************** 1. row ***************************
       Table: tbl_with_char
Create Table: CREATE TABLE `tbl_with_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

テーブルのデフォルトキャラクタセット

テーブルのデフォルトキャラクタセットも同様です。CREATE TABLE時にカラムのキャラクタセットを明示的に指定しなかった場合、テーブルのデフォルトキャラクタセットが採用されます。

mysql> CREATE TABLE tbl_mix_char (
    pk SERIAL,
    ujis_col VARCHAR(255) CHARACTER SET ujis NOT NULL,
    utf8mb4_col VARCHAR(255) NOT NULL) 
    CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_mix_char \G
*************************** 1. row ***************************
       Table: tbl_mix_char
Create Table: CREATE TABLE `tbl_mix_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ujis_col` varchar(255) CHARACTER SET ujis NOT NULL,
  `utf8mb4_col` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

念の為、ujisには存在しない絵文字🍣をINSERTして、utb8mb4_col が utf8mb4 であることを確認してみます。

mysql> INSERT INTO tbl_mix_char (ujis_col, utf8mb4_col) VALUES('', '🍣たべたい');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tbl_mix_char (ujis_col, utf8mb4_col) VALUES('🍣たべたい' '');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'ujis_col' at row 1

テーブルのデフォルトキャラクタセットを ujis にしてみます。

すると、SHOW CRETE TABLEの結果のujis_col varchar(255) CHARACTER SET ujis NOT NULL が ujis_col varchar(255) NOT NULL に変わりました。 テーブルのデフォルトキャラクタセットとキャラクタセットが一致するカラムは CHARACTER SET 〜 の表示が省略される作りのようです。

mysql> ALTER TABLE tbl_mix_char CHARACTER SET ujis;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_mix_char \G
*************************** 1. row ***************************
       Table: tbl_mix_char
Create Table: CREATE TABLE `tbl_mix_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ujis_col` varchar(255) NOT NULL,
  `utf8mb4_col` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
<snip>
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=ujis
1 row in set (0.00 sec)

データを変換する

データベースとテーブルのデフォルトキャラクタセットを変更しても、実際に保存されているデータは変換されません。 これらはCREATE時に明示的にキャラクタセットを指定しなかったときに採用されるキャラクタセットを示しているだけですから。

実際にデータのキャラクタセットを変換方法は２つ。

1. カラムごとに変更する

utf8 → utf8mb4 に ALTER TABLE xxx MODIFY 〜 で変換します。この方法では、テーブルのデフォルトキャラクタセットは変更されません（別途、必要に応じて変更）。

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` text,
  `c1` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 MODIFY t1 TEXT CHARACTER SET utf8mb4, MODIFY c1 VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` text CHARACTER SET utf8mb4,
  `c1` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
<snip>
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2. テーブル全体を変換する

カラム単位での変換は、カラム数が多いと面倒です。ALTER TABLE xxx CONVERT TO CHARACTER SET 〜でテーブルをまるごと変換できます。

TEXT型の扱いに注意が必要です。 １文字あたりのバイト数が増える場合、TEXT型が自動的にMEDIUMTEXT型に変更されます。

データ型が VARCHAR か、またはいずれかの TEXT 型であるカラムに対して、CONVERT TO CHARACTER SET は、新しいカラムが確実に元のカラムと同じ数の文字を格納できる十分な長さになるように、必要に応じてデータ型を変更します。 たとえば、TEXT カラムには、そのカラム内の値のバイト長 (最大 65,535) を格納するための 2 バイト長があります。 latin1 TEXT カラムの場合は、各文字に 1 バイトが必要なため、このカラムには最大 65,535 文字を格納できます。このカラムが utf8 に変換された場合は、各文字に最大 3 バイトが必要になる可能性があるため、可能性のある最大の長さは 3 × 65,535 = 196,605 バイトになります。 その長さは TEXT カラムのバイト長には収まらないため、MySQL はそのデータ型を、バイト長に 196,605 の値を記録できる最小の文字列型である MEDIUMTEXT に変換します。同様に、VARCHAR カラムは MEDIUMTEXT に変換される可能性があります。

https://dev.mysql.com/doc/refman/5.6/ja/alter-table.html

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` text,
  `c1` varchar(100) DEFAULT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4;

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` mediumtext,
  `c1` varchar(100) DEFAULT NULL,
<snip>
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

変換できない文字があるときは？

変換できない文字があればエラーになります。

# utf8mb4 から ujis に変換する、絵文字はujisにないのでエラーに
mysql> ALTER TABLE tbl_mix_char MODIFY ujis_col VARCHAR(255) CHARACTER SET ujis NOT NULL;
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'ujis_col' at row 2

デフォルトキャラクタセットの変更も忘れずに

特別な事情がない限りは、データベースとテーブルのデフォルトキャラクタセットと、カラムのキャラクタセットはすべて揃える運用でしょう。 データを変更したあとは、忘れずに、データベースとテーブルのデフォルトキャラクタセットも変換が必要です。

mysql> ALTER TABLE t1 CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER  DATABASE db1 CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL Group Replication vs Percona XtraDB Cluster 〜DDLのKILL 編〜

MySQL

MySQL のマルチマスターのソリューションである、PXC と Group Replication の比較記事が続きます・・・

PXC では DDL は KILL できない

Percona XtraDB Cluster (Galera) では DDL実行中は、すべてのノードで更新が止まると、以前のエントリーで書きました。

mita2db.hateblo.jp

うっかりDDLを流してしまって、更新が止まってしまい、DDLをKILLしようとしても、PXCでは、このように、KILLさせてくれません。

mysql> KILL 18;
ERROR 1095 (HY000): You are not owner of thread 18

PXCでは更新を一旦ブロックし、全てのノードで同時にDDLを実行します（この仕組みを TOI (Total Order Isolation) と呼びます）。ここで、あるノードだけDDLがKILLされてしまうと、ノード間でテーブル定義が一致しない状態になってしまいます。不一致を回避するため「一度流したDDLは途中ではキャンセルできない」仕様になっていると推測しています。

これは、MySQLにはない制約です。PXCは、（Group Replicationの Primary/Secondaryのような）ノードに役割を持たせない設計となっている一方で、その代償として、MySQLにはない制約があります。

Group Replication は大丈夫

Group Replication (Single Primary 運用) では、普通にKILLできます。 Group Replication は、 Single Primary 運用を前提とすることで、特に追加の制約が少なく運用できるように工夫されているように感じました。

> ALTER TABLE sbtest2  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
^C-- query aborted
Query OK, 0 rows affected (2.6369 sec)

※ Single Primary を off にすると、どうなるかは検証してない

MySQL Group Replication vs Percona XtraDB Cluster 〜フルデータコピー中のDDLブロック 編〜

MySQL

フルデータコピーが行われるタイミング

PXC や MySQL Group Replication では、新規にノードを追加したタイミングや、障害発生後、既存ノードからデータをコピーし、復旧する機能があります。

f:id:mita2db:20200906133955p:plain

データコピー中のDDLの挙動の比較

フルデータコピーには非常に時間がかかることが予想されます。 DDLはあまり日常的に流れるものではないと思いますが、万が一、コピー中にDDLが走ってしまったときにどうなるか確認してみます。

検証内容：

  1. CREATE / DROP PROCEDURE を流しつつ、フルデータコピーを走らせる
  2. 並行して、DDLを流しているDBへ更新、参照を行う

PXC は キツイ挙動をする

Percona XtraDB Cluster では、このフルデータコピープロセスは State Snapshot Transfers (SST) と呼ばれています。 なんだかカッコイイ名前がついていますが、実態は、xtrabackup によるリカバリです。

コピー元となるノードで、xtrabackup による物理バックアップを取得し、コピー先に転送します。

PXC では、フルデータコピー中（xtrabackup 実行中）はDDLがブロックされます。DDL実行が待機させられます。

f:id:mita2db:20200906135445g:plain

DDLが単品で待機させられるだけなら、まぁ、良いのですが、、、「DDL実行中は、クラスタ全体の更新がブロックされる」という仕様に基づき、すべてのINSERTやUPDATEが巻き込まれて止まります😱

mita2db.hateblo.jp

xtrabackup でバックアップ中にDDLが実行されるとバックアップが失敗するため、DDLをブロックしているのでしょう。SSTを行うスクリプトで、xtrabackup の引数に明示的に --block-ddl が指定されていることが、確認できます。

/usr/bin/wsrep_sst_xtrabackup-v2

INNOBACKUP="${pxb_bin_path} --defaults-file=${WSREP_SST_OPT_CONF} \
 --defaults-group=mysqld${WSREP_SST_OPT_CONF_SUFFIX} $disver $iopts \
 \$INNOEXTRA \$keyringbackupopt --lock-ddl --backup --galera-info \
 --binlog-info=ON \$encrypt_backup_options --stream=\$sfmt \
 --xtrabackup-plugin-dir="$pxb_plugin_dir" \
 --target-dir=\$itmpdir 2>\${DATA}/innobackup.backup.log"

定期的にバッチでDDLを流しているようなケース（パーティション追加など）では、バッチとフルデータコピーが重ならないように運用しなければなりません。つらい。。。

Group Replication は好ましい挙動

Group Replication のノードリカバリは Clone プラグインを使って実装されています。 データファイルを物理的にコピー先のノードへコピーします。

Group Replication でも、フルデータコピー中はDDLの実行が待機させられています。addInstance でノードを追加したタイミングで、CREATE PROCEDURE がストップしていることがわかります。

f:id:mita2db:20200906134638g:plain

Clone プラグインのマニュアルにもドナー（コピー元）で、DDLが ブロックされると明記されています。

On the donor, the clone user requires the BACKUP_ADMIN privilege for accessing and transferring data from the donor, and for blocking DDL during the cloning operation. https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-remote.html

ただし、Group Replication は PXCとは異なり、DDL実行中に更新クエリをブロックすることはありません。したがって、フルデータコピー中であっても、INSERTやUPDATEが影響を受けることはありません。 Groiup Replication のほうが好ましい挙動です。

余談ですが、Clone プラグインには、データコピーに利用するネットワーク帯域幅に制限をかける clone_max_network_bandwidth パラメータがあります。 このパラメータを活用することで、意図的にフルデータコピーに時間のかかる状況を簡単に作り出せました。検証がしやすかったです。

まとめ

  • Percona XtraDB Cluster
    • コピー中、DDLがブロックされ、それに巻き込まれて、更新クエリもブロックされる
  • Group Replication
    • コピー中、DDLだけがブロックされる

MySQL Group Replication vs Percona XtraDB Cluster 〜DDLのロック編〜

MySQL

Gelera Cluster / Percona XtraDB Cluster

Galera Cluster for MySQL は フィンランドの会社が開発している、MySQL の fork （派生製品）の１つです。wsrep API と呼ばれる独自のレプリケーション機構を使った、マルチマスター型のHA構成を組むことができます。Galera Cluster は 2007年にリリースされ、MySQL の高可用性構成を実現するための手段として一定の支持を得てきました。

galeracluster.com

wsrep API ベースのマルチマスター機能を MySQL の forkである、Percona Server に実装したものが、Percona XtraDB Cluster。 同じく、MariaDB に実装したものが、MariaDB Galera Cluster になります。

MySQL Group Replication

2016 年に登場した、Vanilla MySQL (forkでなくオリジナル）のマルチマスター機能です。実装は全く異なりますが、目的としているところは、Galera Cluster と同じです。

MySQL 5.7 の終盤でいきなり登場したのですが、機能が充実し、十分使えるようになったのは、MySQL 8.0 になってからです。 MySQL 8.0の新機能と捉えても良いでしょう。

MySQL Group Replication は、HA機能のみで、アプリケーションにデータベースの構成情報を伝える機能は持ちません（例えば、PRIMARYサーバが変わったときに、アプリケーションの接続先を切り替えるなど）。 アプリケーションをデータベースの構成変更に追従させるためのツールである MySQL Router と組み合わせて、InnoDB Cluster いうソリューション名が付けられています。

相互レプリケーションと何が違うのか

Galera Cluster や Group Replication を使わなくても、従来、相互にマスターサーバ同士をレプリケーションさせることで、「マルチマスター」構成とすることが出来ました。しかし、単純な相互レプリケーションでは、スプリットブレインのリスクを排除できません。運が悪いと障害時にデータロストやデータ不整合が発生してしまう可能性があります。

（Paxos などの）分散合意アルゴリズムを利用して、堅牢なマルチマスター構成を実現したものが、Galera Cluster や Group Replication になります。

PXC vs MySQL Group Replication

Group Replication は Galera Cluster や Percona XtraDB Cluster (PXC) と同じようなHAを実現するための機能ですが、実装は全く異なります。 PXCと Group Replicaion でどのような違いがあるのか比較していきたいと思います。

バージョンは、PXC 8.0 と MySQL 8.0 で比較します。

※ 比較相手に Galera Cluster for MySQL や MariaDB Galera Cluster でなく、PXCを選択したのは、単なる自分の趣味です。

DDLのロックの挙動

以下の３点に注目して比較してみます。いずれの検証も、書き込み先のノード（サーバ）は１台に固定して行います（Single Primary 運用）

  1. ALTER 中に対象テーブルに対して書き込みができるか
  2. ALTER 中に対象テーブル以外に対して書き込みができるか
  3. ALTER がレプリケーション遅延（ノード間のデータ同期の遅延）を引き起こすかどうか

Percona XtraDB Cluster 8.0

PXC 8.0 では、例えオンラインDDLであっても、DDL実行中はすべての更新がブロックされます。 ALTER TABLE の対象外のテーブルであってもです。

すべてのノードで同じタイミングで、DDLの実行が開始され、ほぼ同時に終わります。

これはこれで、シンプルでわかりやすいですし、予期しないトラブルを避けるのに有効な挙動だと思います。

f:id:mita2db:20200903163419g:plain

MySQL 8.0 - Group Replication

MySQL Group Replication では、ALTER TABLE中もテーブルに対して書き込みを行えます（オンラインDDLに限る）。 プライマリノードで実行が終わったら、DDLはセカンダリノードで実行されます。セカンダリノードでのDDL実行中、セカンダリノードへの更新の反映が待機します（レプリケーション遅延が発生）。

つまり、普通のマスター・スレーブ構成と同じです。これまでマスター・スレーブで運用してきた人であれば、違和感なく運用できますね。

f:id:mita2db:20200903162025g:plain

まとめ

製品 対象テーブルへの更新 それ以外のテーブルの更新 レプリ遅延
PXC 8.0 できない できない N/A
MySQL GR 8.0 できる できる 発生する