はじめに
Galera Cluster（およびそのブランチ）には、「全テーブルにPrimary Keyが設定されている必要がある」という制約事項があります。これは以下のように公式リファレンスマニュアルでも明言されています。
DIFFERENCES FROM A STANDALONE MYSQL SERVER
Do not use tables without a primary key.
MariaDB Galera Cluster – Known Limitations
All tables should have a primary key (multi-column primary keys are supported).
特に Percona XtraDB Cluster（以下、PXC）の場合、PXC5.7で「PXC Strict Mode」という機能を実装し、PKがないテーブルが作られないよう厳密にチェックを行っています。
At runtime, any undesirable operation performed on a table without an explicit primary key is denied and an error is logged.
では、なぜGaleraでは必ずPKを付けなければいけないのでしょうか？本記事では、その理由について調べてみたいと思います。
マニュアルを参照
実は上記の疑問の答えは同じマニュアルの中にしっかりと書かれています。
以下がその答えとなりますが、後の章で解説いたします。
DIFFERENCES FROM A STANDALONE MYSQL SERVER
When tables lack a primary key, rows can appear in different order on different nodes in your cluster. As such, queries like SELECT…LIMIT… can return different results. Additionally, on such tables the DELETE statement is unsupported.
MariaDB Galera Cluster – Known Limitations
DELETE operations are unsupported on tables without a primary key. Also, rows in tables without a primary key may appear in a different order on different nodes.
結論
「Galera ClusterではPKが必須」という制限は、「Galera Clusterではbinlog_format = ROW の設定が必須」というもう一つの制限事項とセットになっています。
Galera Clusterに限らず、MySQLでも「ROW-based replication では、PKの設定が推奨される」というのは一般的に語られており、そのルールがGaleraレプリケーションにおいては明文化されているに過ぎないのです。
それでは、PK無し + ROW-based な環境だとどのような問題が発生するのでしょうか？それは、以下に述べるような事象です。
- レコードの順番が、各ノード間で異なってしまう
- レプリケーションされたイベントで不要なテーブルスキャンが実行され、大幅なレプリケーション遅延が発生することがある
検証
上に挙げた問題について、以前作成したPXC検証環境で確認してみましょう。
【検証1】レコード順の相違について
1. Node1にログインして、検証用のテーブル・データを作成します
|
1 2 3 4 5 6 |
mysql> CREATE DATABASE d1; mysql> use d1; mysql> CREATE TABLE test_PK (col1 char(5) primary key); mysql> CREATE TABLE test_no_PK (col1 char(5)); mysql> INSERT INTO test_PK VALUES ("aaa"), ("ccc"), ("bbb"); mysql> INSERT INTO test_no_PK VALUES ("aaa"), ("ccc"), ("bbb"); |
2. Node1 → Node2 の順番でトランザクションを開始します
|
1 2 3 4 5 6 7 8 |
node1> START TRANSACTION; node2> START TRANSACTION; node1> INSERT INTO test_PK VALUES ("111"); node1> INSERT INTO test_no_PK VALUES ("111"); node2> INSERT INTO test_PK VALUES ("222"); node2> INSERT INTO test_no_PK VALUES ("222"); |
3. Node2 → Node1 の順番でCOMMITします
|
1 2 3 |
node2> COMMIT; node1> COMMIT; |
4. PKテーブルでは Node1/Node2 のレコード順が同じになります
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
node1> SELECT * FROM test_PK; +------+ | col1 | +------+ | 111 | | 222 | | aaa | | bbb | | ccc | +------+ node2> SELECT * FROM test_PK; +------+ | col1 | +------+ | 111 | | 222 | | aaa | | bbb | | ccc | +------+ |
5. 非PKテーブルでは Node1/Node2 のレコード順が異なります
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
node1> SELECT * FROM test_no_PK; +------+ | col1 | +------+ | aaa | | ccc | | bbb | | 111 | | 222 | +------+ node2> SELECT * FROM test_no_PK; +------+ | col1 | +------+ | aaa | | ccc | | bbb | | 222 | | 111 | +------+ |
コミットのタイミングでレコード順が異なってしまうため、 ノード間でデータの整合性が保てなくなってしまいます。
【検証2】不要なテーブルスキャンについて
1. Node1でテストデータを作成します
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> use d1; mysql> CREATE TABLE t1_PK (id int auto_increment primary key, col1 varchar(10), col2 varchar(10), col3 varchar(10)); mysql> INSERT INTO t1_PK (col1, col2, col3) SELECT SUBSTRING(MD5(RAND()), 1, 10), SUBSTRING(MD5(RAND()), 1, 10), SUBSTRING(MD5(RAND()), 1, 10); mysql> INSERT INTO t1_PK (col1, col2, col3) SELECT SUBSTRING(MD5(RAND()), 1, 10), SUBSTRING(MD5(RAND()), 1, 10), SUBSTRING(MD5(RAND()), 1, 10) FROM t1_PK; ... （以下、同じクエリを繰り返し実行） ... mysql> INSERT INTO t1_PK (col1, col2, col3) SELECT SUBSTRING(MD5(RAND()), 1, 10), SUBSTRING(MD5(RAND()), 1, 10), SUBSTRING(MD5(RAND()), 1, 10) FROM t1_PK; Query OK, 524288 rows affected (39.99 sec) → 合計100万行ほどのランダムデータが生成されます |
2. 非PKテーブルを作成し、データをコピーします
|
1 2 |
mysql> CREATE TABLE t1_no_PK (id int, col1 varchar(10), col2 varchar(10), col3 varchar(10)); mysql> INSERT INTO t1_no_PK SELECT * FROM t1_PK; |
3. Node1のPKテーブルを更新しNode2にいつ反映されるか確認する
|
1 2 3 |
node1> UPDATE t1_PK SET col2="updated!" WHERE id between 10000 and 20000; node2> SELECT count(*) FROM t1_PK WHERE col2="updated!"; |
4. 非PKテーブルでも同じことを行う
|
1 2 3 |
node1> UPDATE t1_no_PK SET col2="updated!" WHERE id between 10000 and 20000; node2> SELECT count(*) FROM t1_no_PK WHERE col2="updated!"; |
t1_PKの場合に比べ、t1_no_PKの方は中々更新が反映されないことが確認できます
補足
ちなみに、Galera Cluster において binlog_format = ROW は必須ですが、log_bin（バイナリログ）の有効化は必須ではありません。つまり、Galera Cluster は Write-Set にROW形式のイベントを直接出力しており、それは内部的処理なので詳細は確認できません。
しかし、バイナリログは必須ではないものの、以下の記事で述べた通り基本的に有効にすることを推奨します。
Let’s Enjoy Galera Life !!
