技術めも

pt-online-schema-changeをslaveに対して実行した場合に痛い目見たので、その備忘録。

そもそもの話、master側で不要なINDEXをslaveのみ張ってました。

OLTP側ではいらないINDEXだけど、集計とかであると嬉しいINDEXってやつですね。

そして、slaveに張っているINDEXをpt-oscでオンラインで張替えをしようとしたら、

リレーログからの取り込みが、旧テーブルに行ってしまい、

pt-oscで作成される新テーブルに入らなかったいうオチ。。。

その結果、レプリケーションの不整合が起きて、最初から構築するという目にあった。。

このような場合、素直にstop slaveを掛けてからDDLを実行すれば良かったです。。

とはいえ、リレーログからのデータ取り込みの際、pt-oscで実行しているとダメなことがわかったので、

もうちょっとこのあたりについて調べてみようかと思います。

あ、binlog_row_image=FULLだったし、MariaDB10.3だからflashback使えば、

もっと短時間で復旧できたかも、、って書いているときに思いました。。

以上

前回の記事で、unionの条件判定が10.2から変更になっていると記載しましたが、

どうやら、10.2から追加されたoptimizer_switchの動作によるものらしい。

具体的には、

condition_pushdown_for_derived=on

というパラメータに依存するようです。

実際に、上記パラメータを切り替えてテストしてみた。

・offのとき

MariaDB [test]> set optimizer_switch='condition_pushdown_for_derived=off';
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542;
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| 1    | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 1993522 | Using where |
| 2    | DERIVED     | users      | ALL  | NULL          | NULL | NULL    | NULL | 996761  |             |
| 3    | UNION       | users      | ALL  | NULL          | NULL | NULL    | NULL | 996761  |             |
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
3 rows in set (0.001 sec)

・onのとき

MariaDB [test]> set optimizer_switch='condition_pushdown_for_derived=on';
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542;
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1    | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    | Using where |
| 2    | DERIVED     | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
| 3    | UNION       | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.000 sec)

というように、

condition_pushdown_for_derived

のパラメータによって上記の差異がでる。

で、さらに気になったのが、

10.2から、

mrr=off
mrr_cost_based=off

に変更されている。

mrrはセカンダリーインデックスのIO負荷軽減のものであったかと思うが、

このあたりの絡みでパフォーマンスにどう影響があるのか、再度調べてみようと思う。

なお、MySQL8ではonのままであった。

以上

unionする前にwhereをするのと、

unionした後にwhereをする場合、

mysqlにおいて、差異はあるのかを確認してみた。

動機としては、

「unionする前にwhereをする」のと「unionした後にwhereをする」が同パフォーマンスであれば、

multisourceでviewを作っておいても十分高速に引ける可能性がある、と思ったところです。

いろいろなバージョンで試してみました。

予め以下のようなテーブルを作成しておき、100万件登録しておきます。

> desc users;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| email | varchar(128) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

（MySQL5.6）

mysql> explain select * from (select * from users union all select * from users) as t where id = 7542;
+------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+
| id   | select_type  | table      | type | possible_keys | key         | key_len | ref   | rows   | Extra           |
+------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+
| 1    | PRIMARY      | <derived2> | ref  | <auto_key0>   | <auto_key0> | 4       | const | 10     | NULL            |
| 2    | DERIVED      | users      | ALL  | NULL          | NULL        | NULL    | NULL  | 996473 | NULL            |
| 3    | UNION        | users      | ALL  | NULL          | NULL        | NULL    | NULL  | 996473 | NULL            |
| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL        | NULL    | NULL  | NULL   | Using temporary |
+------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+
4 rows in set (0.12 sec)

mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id   | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| 1    | PRIMARY      | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    | NULL            |
| 2    | DERIVED      | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    | NULL            |
| 3    | UNION        | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    | NULL            |
| NULL | UNION RESULT | <union2,3> | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary |
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
4 rows in set (0.00 sec)

（MySQL5.7）

mysql> explain select * from (select * from users union all select * from users) as t where id = 7542;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
| 1  | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | const | 10     | 100.00   | NULL  |
| 2  | DERIVED     | users      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 996473 | 100.00   | NULL  |
| 3  | UNION       | users      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 996473 | 100.00   | NULL  |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1  | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 2    | 100.00   | NULL  |
| 2  | DERIVED     | users      | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.00   | NULL  |
| 3  | UNION       | users      | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.00   | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

（MySQL8）

mysql> explain select * from (select * from users union all select * from users) as t where id = 7542;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
| 1  | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | const | 10     | 100.00   | NULL  |
| 2  | DERIVED     | users      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 996761 | 100.00   | NULL  |
| 3  | UNION       | users      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 996761 | 100.00   | NULL  |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
3 rows in set, 1 warning (0.05 sec)

mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1  | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 2    | 100.00   | NULL  |
| 2  | DERIVED     | users      | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.00   | NULL  |
| 3  | UNION       | users      | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.00   | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

（MariaDB10.1）

MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542;
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| 1    | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 1994176 | Using where |
| 2    | DERIVED     | users      | ALL  | NULL          | NULL | NULL    | NULL | 997088  |             |
| 3    | UNION       | users      | ALL  | NULL          | NULL | NULL    | NULL | 997088  |             |
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
3 rows in set (0.01 sec)

MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1    | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    |       |
| 2    | DERIVED     | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
| 3    | UNION       | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
3 rows in set (0.00 sec)

（MariaDB10.2）

MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542;
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1    | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    | Using where |
| 2    | DERIVED     | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
| 3    | UNION       | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.001 sec)

MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1    | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    |       |
| 2    | DERIVED     | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
| 3    | UNION       | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
3 rows in set (0.001 sec)

（MariaDB10.3）

MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542;
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1    | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    | Using where |
| 2    | DERIVED     | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
| 3    | UNION       | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.001 sec)

MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t;
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1    | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 2    |       |
| 2    | DERIVED     | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
| 3    | UNION       | users      | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
3 rows in set (0.000 sec)

これ、正直言って、動機を満たす結果を得るのは無理だと思ってたが、、、

MariaDB10.2と10.3は嬉しい結果を出してくれました。

以上

MariaDB10.3、MySQL8から、ALTER構文にinstantの機能が導入されました。

利用できる範囲は限られていますが、その中であれば超高速でALTERが完了します。

たぶん、メタデータだけ書き換えて、実際のデータは初回アクセス時に補完する形をとっているような感じです。

詳細は、公式マニュアルを参考指定だければと思います。

https://mariadb.com/kb/en/library/instant-add-column-for-innodb/

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

実際にデータを突っ込んでみて、どのくらいALTERが高速になるか試してみました。

以下のようなテーブルを用意して、100万件のデータを入れておきます。

> desc users;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(11)             | NO   | PRI | NULL    |       |
| email  | varchar(128)        | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+

（MySQL5.7）

mysql> alter table users add column money1 bigint unsigned not null;
Query OK, 0 rows affected (2.35 sec)
Records: 0 Duplicates: 0 Warnings: 0

（MySQL8）

mysql> alter table users add column money1 bigint unsigned not null;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

一旦drop。

mysql> alter table users add column money1 bigint unsigned not null, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

（MariaDB10.3）

MariaDB [test]> alter table users add column money1 bigint unsigned not null;
Query OK, 0 rows affected (0.006 sec)
Records: 0 Duplicates: 0 Warnings: 0

一旦drop。

MariaDB [test]> alter table users add column money1 bigint unsigned not null, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.007 sec)
Records: 0 Duplicates: 0 Warnings: 0

MySQL5.7と比較して、超高速ですね。。

なお、MariaDB10.3もMySQL8も、デフォルトで最後尾へのadd columnはINSTANTとして扱われるようです。

ちなみに、MySQL8のほうが、INSTANTが扱える範囲が広いようです。

以下のような条件でINSTANTが使えるようです。（※公式より引用）

• Adding a column. This feature is referred to as “Instant ADD COLUMN”. Limitations apply. See Section 15.12.1, “Online DDL Operations”.
• Adding or dropping a virtual column.
• Adding or dropping a column default value.
• Modifying the definition of an ENUM or SET column. The same restrictions apply as described above for ALGORITHM=INSTANT.
• Changing the index type.
• Renaming a table. The same restrictions apply as described above for ALGORITHM=INSTANT.

instantが発動したかどうかは、

MariaDB10.3の場合は以下のクエリーによって確認できます。

SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column';

MySQL8の場合は以下。

SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%{table_name}%';

条件はあるものの、MySQL8は仮想列に対するinstantにも対応しているので、結構使い勝手も広がる予感です。

とはいえ、これまでalterに非常に大きな時間・処理コストがかかっていたのが、一気に超高速になり、非常に素晴らしいと思います。

パッチを提供したTencentに感謝ですね。

あとは、instantで変更をかけて、その後のアクセスでどのくらいIO負荷を持っていかれるか、試してみたいな。。

以上

久しぶりにMariaDB以外のことを書いてみる。

Redis3.2のrdbファイルは、Redis4に持っていっても、なんの障害もなく移行できる。

リリースノートにも書いてあるし、実際に試してみたところ、問題なく移行でき、キー操作も問題ない。

移行先でAOFを無効化しているので、rdbファイルを移行しただけだが、

AOFを有効化している場合は、aofファイルから復元されるので、注意が必要。

https://raw.githubusercontent.com/antirez/redis/4.0/00-RELEASENOTES

※リリースノートの一番下に、移行についての説明が記載されています。

で、Redis4についてですが、

AOFが単なる追記型ファイルとしてでなく、一定時点のスナップショット＋追記、という形式（MIXED）に生まれ変わりました。

これにより、Redis3ではすごい速さで肥大化し続けるaofファイルが、

Redis4では肥大化速度が抑制され、さらにaofによる復元速度も早くなり、さらにさらにレプリケーションにも好影響があるということで、

Redis4からはAOFを積極的に使っていくべきだな、と感じております。

とはいえ、実際に移行を行う際、まるっとrdbファイルを移行しても、

移行先がAOFを有効化していると、aofファイルから復元が試みられる結果、

rdbファイルが読み込まれず、saveした際には、データがなくなってしまうという悲しい結果に終わる。

そこで、Redis3.2からRedis4への移行については、レプリケーションが好ましい。

以下のような形でサーバを組む。

--------------------
redis01 (version:3.2.x, aof:disable)
IP:192.168.35.11
--------------------
 |
 | (replication)
 |
--------------------
redis02 (version:4.0.x, aof:enable)
IP:192.168.35.12
--------------------

このように、移行元（redis01）ではAOFが無効化されており、

移行先（redis02）ではAOFを有効化したい場合、

移行先のAOFを有効化した状態で、レプリケーションを始める。

そうすると、移行先のデータディレクトリ（/var/lib/redisなど）に、rdbファイルとaofファイルの両方が作成される。

もし、MIX形式を使いたいなら、

aof-use-rdb-preamble yes

というように、「aof-use-rdb-preamble」を「yes」に設定する必要がある。

実際に、aofファイルにスナップショットが書かれるタイミングは、

auto-aof-rewrite-percentage

auto-aof-rewrite-min-size

という2つのパラメータに依存するため、なかなかaofファイルにスナップショットが書き込まれない場合、これらを小さくするとよい。

あくまで、aofにスナップショットが書かれたことを確認するためで、無理に小さくする必要はない。

ちなみに、aofファイルにスナップショットが含まれているかどうかは、lessコマンドで確認できる）

最後に、移行先（redis02）のaof関連パラメータを載せておく。

appendonly yes
appendfilename "appendonly.aof"
appendfsync everysec
no-appendfsync-on-rewrite no
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
aof-load-truncated yes
aof-use-rdb-preamble yes

（参考）

Redis Mixed RDB+AOF

https://qiita.com/devneko/items/67662e016e8cd0b7791a

以上

以前の投稿で、spider engineを使った際、

flush tables;

を使わないと、バックエンドに正常にクエリーが流れないことを記載した。

調査した結果、

spider_use_handler=1

を設定することで、正しくバックエンドにクエリーが流れることが確認できた。

上記パラメータは以下の公式にある。

https://mariadb.com/kb/en/library/spider-server-system-variables/#spider_use_handler

しかし、このパラメータはなんなのか。。

というわけで、spider関連の設定を備忘録として載せておく。

spider_bka_mode=1
spider_conn_recycle_mode=1
spider_connect_retry_count=2
spider_connect_retry_interval=1000
spider_connect_timeout=10
spider_crd_sync=0
spider_direct_dup_insert=1
spider_local_lock_table=0
spider_log_result_errors=2
spider_max_connections=0
spider_net_read_timeout=10
spider_net_write_timeout=10
spider_remote_access_charset=utf8mb4
spider_remote_autocommit=0
spider_remote_sql_log_off=0
spider_reset_sql_alloc=0
spider_sts_sync=0
spider_support_xa=0
spider_sync_autocommit=0
spider_sync_trx_isolation=0
spider_use_handler=1

別途クエリー挙動をまとめようと思います。

以上

spider engineを検証していたら、謎の挙動に出会った。

簡単に言うと、whereでpartition条件句を含めたクエリーを発行したのち、

whereがないクエリーを発行すると、partition条件句の向き先となったノードに発行先が固定されるというもの。

試したバージョンは、

MariaDB：10.3.8

Spider：3.3.13

である。

原因がよくわかっていないのだが、

flush tables;

コマンドを発行すると治る。。

セッションを終了しても治らない。。

高速化のため、spider側で何かしらキャッシュしていることが原因なのではないかと考えているが、

どうも気持ち悪く、バグなのか仕様なのかも現在はっきりしていない。

原因が判明したら、また記載しようと思います。

また、MariaDB10.1にバンドルされていた3.2.37とは動きが異なる部分が多かったので、

検証完了次第、詳細な動き（パターン）を合わせて記載出来たらと思います。

以上

MariaDBの以下のクラスター方式で、パフォーマンス比較をしてみた。

比較動機としては、galeraは本当に遅いか？ということを確認するためのものです。

1. standalone（比較用）
2. semi sync
3. galera

環境はvagrantの仮想マシンで、以下構成とバージョン。

CPU：1core、MEMORY：512MB

OS：CentOS7.5

MariaDB：10.3.8

sysbenchの実行スクリプトは以下の通りで、${thread}の部分は1と2で実施。

$ sudo sysbench /usr/share/sysbench/oltp_read_write.lua \
--db-driver=mysql \
--table-size=100000 \
--mysql-host=192.168.35.11 \
--mysql-user=admin \
--mysql-password=adminpassword \
--time=60 \
--db-ps-mode=disable \
--threads=${thread} run

テストにおけるインスタンス配置は以下の通り。

１．standalone

----------
sysbench
ip:192.168.35.10
----------
 |
 |
 |
----------
db01
ip:192.168.35.11
----------

２．semi sync

----------
sysbench
ip:192.168.35.10
----------
 |
 |
 |
-----------------------
db01
ip:192.168.35.11
-----------------------
  |               |
  |(replication)  |(replication)
  |               |
 ----------       ----------
 db02             db03
 ip:192.168.35.12 ip:192.168.35.13
 ----------       ----------

３．galera

----------
sysbench
ip:192.168.35.10
----------
 |
 |
 |
-----------------------
db01
ip:192.168.35.11
-----------------------
  |                |
  |(write set)     |(write set)
  |                |
 ----------       ----------
 db02             db03
 ip:192.168.35.12 ip:192.168.35.13
 ----------       ----------

ざっくりパラメータは以下の通りで、条件によって、変更していく。

# --------------------------------------------------
# base
# --------------------------------------------------
server_id=${server_id}
user=mysql
bind_address=0.0.0.0
pid_file=/var/run/mysql/mysqld.pid
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
sql_mode=TRADITIONAL
default_storage_engine=InnoDB
transaction_isolation=READ-COMMITTED
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
thread_pool_max_threads=100
thread_handling=pool-of-threads
extra_port=3307
extra_max_connections=10

# --------------------------------------------------
# network
# --------------------------------------------------
max_connections=50
max_connect_errors=999999999
connect_timeout=10
max_allowed_packet=16M
back_log=1024

# --------------------------------------------------
# logging
# --------------------------------------------------
log_output=FILE
log_error=/var/log/mysql/error.log
slow_query_log=1
long_query_time=1
log_queries_not_using_indexes=0
slow_query_log_file=/var/log/mysql/slow.log
general_log=0
general_log_file=/var/log/mysql/general.log

# --------------------------------------------------
# cache, memory
# --------------------------------------------------
query_cache_size=0
max_heap_table_size=32M
tmp_table_size=32M

# --------------------------------------------------
# session
# --------------------------------------------------
sort_buffer_size=4M
read_rnd_buffer_size=2M
read_buffer_size=512K
join_buffer_size=512K

# --------------------------------------------------
# innodb
# --------------------------------------------------
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_dump_pct=25
innodb_fast_shutdown=0
innodb_flush_log_at_trx_commit=1    // set globalで適宜変更
innodb_autoinc_lock_mode=2
innodb_doublewrite=ON
innodb_file_per_table=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=256M
innodb_flush_neighbors=0
innodb_read_ahead_threshold=0
innodb_log_file_size=64M
innodb_log_files_in_group=2
innodb_buffer_pool_instances=8
innodb_lru_scan_depth=1024
innodb_read_io_threads=1
innodb_write_io_threads=1
innodb_io_capacity=100
innodb_io_capacity_max=1000
innodb_open_files=1024
innodb_purge_threads=1
innodb_sync_array_size=2
innodb_flush_method=O_DIRECT

# --------------------------------------------------
# replication
# --------------------------------------------------
report_host=${ip_addr}
read_only=0
binlog_format=row
log_bin=mariadb-bin
max_binlog_size=128M
sync_binlog=1        // set globalで適宜変更
expire_logs_days=3
log_slave_updates=1
relay_log_recovery=1
slave_max_allowed_packet=1G
slave_net_timeout=3600
slave_parallel_threads=1
gtid_strict_mode=1

# --------------------------------------------------
# semisync
# --------------------------------------------------
{if (semi syncのときのみ)}
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1500
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_wait_point=AFTER_SYNC
{endif}

# --------------------------------------------------
# galera
# --------------------------------------------------
{if (galeraのときのみ)}
wsrep_on=1
wsrep_cluster_name=db-cluster
wsrep_cluster_address=gcomm://192.168.35.11,192.168.35.12,192.168.35.13
wsrep_node_address=${ip_addr}
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_sst_method=rsync
wsrep_slave_threads=1
wsrep_provider_options="gcache.recover=yes; gcache.size=1G; gcs.fc_factor=1.0; gcs.fc_limit=256; gcs.fc_master_slave=yes;"
{endif}

という前提でテストをした結果が以下の通り。

結果を見てみると、

semi sync3とgalera2は遜色がないことがわかる。

semi syncよりgaleraは遅いと思っていたが、そうとも言えないのかもしれない。

とはいえ、あくまでsysbenchの結果であり、トランザクションの量や、スレッドの量によって、

結果は変わるものなので、あくまで一つの指標として、「galeraは早くないけど、遅いとも言い切れない」ってことがわかった気がします。

以上

表題の通り、galeraにslaveくっつけて、slave側をmroongaとして使ってみる。

構成は以下の通り。

----------       ----------       ----------
galera01 <-----> galera02 <-----> galera03
IP:192.168.35.11 IP:192.168.35.12 IP:192.168.35.13
----------       ----------       ----------
|
|（replication）
|
----------
slave01
IP:192.168.35.14
----------

この構成の利点は以下の通り。

1. mroongaをストレージモード/ラッパーモードで使う際に発生する問題点を解決可能
2. galeraで行うことで、レプリケーションフェイルオーバ誤検知問題を回避できる。

1については、

・ストレージモードで扱うと、そもそもトランザクションに対応していない問題

・ラッパーモードで扱うと、rollback時にfulltextインデックスの再構築が必要になる問題

を解決できる。

2については、

・レプリケーションを多段にさせた場合、フェイルオーバを誤検知する可能性がある問題

※maxscaleのfailover toporogyを見ると、多段レプリケーションには対応していないようである。

※他のフェイルオーバツールとして、mrmは未検証、orchestoratorはMariaDBへ対応していない？ように見えた。

を解決できる。

さらに、galeraにしておくことで、masterをどこに向けても簡単に整合性が取れる。

現状では、slave01のmasterはgalera01になっているが、galera02をmasterとして向けてもよい。

これは、GTID（※ここではGlobal Transaction IDを指しています。Galera Transaction IDではありません。）が、

galeraクラスター間で同期が取られているためである。

簡単ながら、mroongaを構築する流れを記載します。

ここでは上記構成は構築済みとします。

・galera01

まずは、innoDBのテーブルを作成する。

[MariaDB]> create table news (
id integer not null,
search text not null,
primary key(id)
) ENGINE=InnoDB;

作成すると、galera02、galera02、slave01にも伝播する。

・slave01

以下のDDLでテーブル定義を修正する。

[MariaDB]> ALTER TABLE news ENGINE=Mroonga;
[MariaDB]> CREATE FULLTEXT INDEX news_fidx01 ON news(search);
[MariaDB]> show create table news;
+-------+----------------
| Table | Create Table |
+-------+----------------
| news | CREATE TABLE `news` (
`id` int(11) NOT NULL,
`search` text NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `news_fidx01` (`search`)
) ENGINE=Mroonga DEFAULT CHARSET=utf8mb4 |
+-------+----------------
1 row in set (0.000 sec)

これで、galera01でnewsテーブルにデータを登録すると、

slave01側では全文検索が可能になる。

※galera02、galera03に登録しても、slave01側に伝播します。

もし、「gtid_strict_mode」というMariaDB10.3から追加されたパラメータの値を1にしていたら、

slaveのDDL流し込み時に、slave01サーバで本パラメータを一時的に無効化しておきましょう。

[MariaDB]> SET GLOBAL gtid_strict_mode=0;

この「gtid_strict_mode」というのは、レプリケーションでの整合性を取るのに優れたパラメータである。

slaveサーバにおいて、

gtid_binlog_pos > gtid_slave_pos

を許さないためのパラメータであり、gtid_strict_mode=1のままだと、

slaveの独自DDL/DMLによって、レプリケーションが停止してしまうためです。

あとは、slave側で、

[MariaDB]> select * from news where match(search) against('+金額');

のような全文検索クエリーが利用できる。

以上

MariaDB Galera Clusterで、MariaDB10.3から導入されたsequenceを使うときは、2つの注意点がある。

１．wsrep_auto_increment_controlの設定

wsrep_auto_increment_control=1（default）にしておく必要がある。

これにより、auto_incrementの際、各ノードで飛び番でauto_incrementの値が払い出される。

sequenceについても、これを設定しておかないと、ノード間で値が重複してしまう。

２．create sequence時の設定

wsrep_auto_increment_control=1にした状態で、

以下のようにsequenceをcreateする。

[MariaDB]> CREATE SEQUENCE {シーケンス名} INCREMENT BY 0;

「increment by 0」が重要。

これを行わないと、nextvalの値が各ノードで重複してしまう。

実際にGalera３ノードで、wsrep_auto_increment_control=1の状態でnextvalしてみた。

・ノード1

[MariaDB]> CREATE SEQUENCE seq01;
[MariaDB]> CREATE SEQUENCE seq02 INCREMENT BY 0;
[MariaDB]> select nextval(seq01);
+----------------+
| nextval(seq01) |
+----------------+
| 1 |
+----------------+

[MariaDB]> select nextval(seq01);
+----------------+
| nextval(seq01) |
+----------------+
| 2 |
+----------------+

[MariaDB]> select nextval(seq02);
+----------------+
| nextval(seq02) |
+----------------+
| 1 |
+----------------+

[MariaDB]> select nextval(seq02);
+----------------+
| nextval(seq02) |
+----------------+
| 4 |
+----------------+

・ノード2

[MariaDB]> select nextval(seq01);
+----------------+
| nextval(seq01) |
+----------------+
| 1 |
+----------------+

[MariaDB]> select nextval(seq01);
+----------------+
| nextval(seq01) |
+----------------+
| 2 |
+----------------+

[MariaDB]> select nextval(seq02);
+----------------+
| nextval(seq02) |
+----------------+
| 2 |
+----------------+

[MariaDB]> select nextval(seq02);
+----------------+
| nextval(seq02) |
+----------------+
| 5 |
+----------------+

・ノード3

[MariaDB]> select nextval(seq01);
+----------------+
| nextval(seq01) |
+----------------+
| 1 |
+----------------+

[MariaDB]> select nextval(seq01);
+----------------+
| nextval(seq01) |
+----------------+
| 2 |
+----------------+

[MariaDB]> select nextval(seq02);
+----------------+
| nextval(seq02) |
+----------------+
| 3 |
+----------------+

[MariaDB]> select nextval(seq02);
+----------------+
| nextval(seq02) |
+----------------+
| 6 |
+----------------+

という結果である。

sequenceはauto_incrementより便利な機能であるが、

galeraで使う場合は注意が必要。

一応、公式に書いてあるが、ちょっと理解するのに手間取った。

https://mariadb.com/kb/en/library/sequence-overview/#replication

なお、通常のmaster-slaveレプリケーションでsequenceを利用する場合は、

「auto_increment_increment」「auto_increment_offset」の値によらず、

sequenceのnext_not_cached_valueの値によって、飛び番で発番されるようです。

つまり、slaveの昇格をした場合などは、sequenceの番号が大きく飛ぶ可能性がある。

以上