技術めも

表題の通り、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の番号が大きく飛ぶ可能性がある。

以上

maxscaleにはbinlogrouterなる機能がある。

なお、maxscaleのバージョンは2.2、mariadbのバージョンは10.3で確認しています。

これは以下のようなイメージで、バイナリログを蓄積し、他slaveへ伝播させる中継機能を持つ。

----------
mariadb master
IP：192.168.35.11
----------
|
| （replication）
|
----------
maxscale binlogrouter
IP：192.168.35.12
----------
|
| （replication）
|
----------
mariadb slave
IP：192.168.35.13
----------

これはmysqlbinlogのremote機能にも似ているが、

通常のレプリケーションと同じように扱える利点があり、

さらにCDC（Change Data Capture）という機能を利用することで、

Kafkaなんかと連携することも可能らしい。

面白いアプローチだなと思いつつ、使いどころが難しい。。。

とりあえず、構築手順だけ残しておく。

・master（192.168.35.11）

$ sudo mysql_secure_installation
$ sudo mysql -u root -p
[MariaDB]> CREATE USER 'admin' IDENTIFIED BY 'admin1234';
[MariaDB]> GRANT ALL PRIVILEGES ON *.* TO 'admin';

・binlogrouter（192.168.35.12）

$ sudo mkdir /var/lib/maxscale/binlog
$ chown maxscale:maxscale /var/lib/maxscale/binlog

/etc/maxscale.cnf

[Replication]
type=service
router=binlogrouter
user=admin
passwd=admin1234
server_id=99
binlogdir=/var/lib/maxscale/binlog
mariadb10-compatibility=1
mariadb10_master_gtid=1

[Replication Listener]
type=listener
service=Replication
protocol=MariaDBClient
port=13306

$ mysql -h 127.0.0.1 -P 13306 -u admin -padmin1234
[MariaDB]> CHANGE MASTER TO MASTER_HOST='192.168.35.11', MASTER_PORT=3306, MASTER_USER='admin', MASTER_PASSWORD='admin1234', master_use_gtid=slave_pos;
[MariaDB]> start slave;

※slave_posは最初は空なので、場合によっては、「SET GLOBAL gtid_slave_pos=’XXX’;」が必要。

ここまでで、masterとbinlogrouterのレプリケーション関係が構築された。

binlogrouterはバイナリログしか持っていないので、slave_posは「欲しい状態」を指定すればよい。

・slave（192.168.35.13）

$ mysqldump -h 192.168.35.11 -u admin -padmin1234 --master-data=2 --single-transaction --routines --all-databases > master.sql
$ mysql -u admin -padmin2612 < master.sql
$ mysql -u admin -padmin2612
[MariaDB]> SET GLOBAL gtid_slave_pos='{master.sqlファイルに書いてあるGTIDポジション}';
[MariaDB]> CHANGE MASTER TO MASTER_HOST='192.168.35.12', MASTER_PORT=13306, MASTER_USER='admin', MASTER_PASSWORD='admin1234', master_use_gtid=slave_pos;
[MariaDB]> start slave;

という手順にて、binlogrouterを介したレプリケーションができる。

あとは、有効な使い所を探すべし。。

以上

MariaDBでMultisource Replicationをやるときは、

• GTIDベース
• 通常ベース（ファイル／ポジション）

によって、制約が異なる。

GTIDベースでは、

レプリケーション元のgtid_domain_idが異なっていなければならない。

通常ベースでは、

レプリケーション元のgtid_domain_idが同じでも良い。

つまり、GTIDベースでは、以下のようなケースにおいては、

db01とdb02のgtid_domain_idは異る必要がある。

+++++          +++++
db01           db02
+++++          +++++
|              |
|              |
++++++++++++++++++++
multisource
++++++++++++++++++++

しかし、通常ベースでは、gtid_domain_idが同じでもよい。

これは、gtid_domain_idを指定できないAWSのRDS環境のおいても、

Multisource Replicationが使えることを意味している。

つまり、db01とdb02がRDSで、multisourceがEC2という位置付けになる。

※RDS上にmultisourceは組めません。

さらに、MariaDBの嬉しい機能として、

コネクションごとに、replicate_do_dbやらreplicate_igonore_dbが指定できる。

たとえば、db01へのコネクション名を「con-db01」、db02へのコネクション名を「con-db02」とすると、

以下のような設定をmultisource側のmy.cnfに記載できる。

con-db01.replicate_do_db=hoge01
con-db02.replicate_do_db=hoge02

この設定の意味するところは、「con-db01」ではhoge01データベースをレプリケーションの対象とする、

「con-db02」ではhoge02データベースをレプリケーションの対象とする、ということである。

このような機能を使うことで、

分割されたDBからデータをかき集め、

様々な集計／分析を行うことが容易となる。

さらに、MariaDB10.2からはwindow関数も導入されているため、

Multisource Replicationによる恩恵もおおきくなるだろう。

※MariaDB10.2と10.3では使えるwindow関数が異なるので注意。

なお、GTIDを使う場合、以下のように、2つのGTIDを指定する。

gtid_domain_idが10はdb01、20はdb02を指している。

> stop all slaves;
> SET GLOBAL gtid_slave_pos='10-11-10,20-21-10';
> CHANGE MASTER 'con-db01' TO  MASTER_HOST='XXX', MASTER_USER='rpl', MASTER_PASSWORD='rplpassword', master_use_gtid=slave_pos;
> CHANGE MASTER 'con-db02' TO  MASTER_HOST='YYY', MASTER_USER='rpl', MASTER_PASSWORD='rplpassword', master_use_gtid=slave_pos;
> start all slaves;

以上

表題のとおり、MariaDB10.3とOracleをconnectエンジンで繋いでみた。

つなぎ方は、odbcかjdbcを使うことで実現できる。

Oracleのクライアント設定とかも必要なので、

ここでは詳細な設定について割愛。

なお、Oracleとの互換性を最大限たかめるため、

SQL_MODE=ORACLE

という、MariaDB10.3で導入されたモードを設定している。

おおよそのデータについては、問題なくconnectエンジン経由で扱えるのだが、

最大の難関は日付けである。

Oracleはtimestamp型を拡張しているため、

MariaDB(MySQL)のtimestamp型とカバー範囲が異なる。

そのため、以下の条件のいずれかの場合において、

connectで持ってきた時に「0000-00-00 00:00:00」という日時になってしまう。

• Oracleのtimestamp型データがMySQLのtimestamp型の範囲外のとき
• Oracleのtimestamp型データがNULLのとき

前者はその通りの話ではあるが、後者のパターンも有り得るのがやっかいである。

しかも、条件句で指定した時は、NULLと認識するくせに、結果セットが変るのである。

これはconnectで自動作成させるテーブル定義上仕方ないことなのであるが。。

その他、BLOB、CLOBなどのバイナリ型が、

connect上はvarcharになるので、このあたりも場合によっては、注意が必要。

以上

MySQL8のJSON部分更新を確認してみた。

最初こんなテーブルを作り、データを更新する。

> create database hoge;
> use hoge;
> create table fuga (id integer not null, body json not null, primary key(id));
> insert into fuga values (1, '{"x":10, "y":20}');
> update fuga set body = json_replace(body, '$.y', 50);

この状態でバイナリログを見ると、以下のようになっている。

### UPDATE `hoge`.`fuga`
### WHERE
### @1=1
### @2='{"x": 10, "y": 20}'
### SET
### @1=1
### @2='{"x": 10, "y": 50}'

部分更新じゃなくね？と思ったが、

my.cnfに以下を記載する。

binlog_row_image=minimal
binlog_row_value_options=partial_json

再起動完了後、以下のようなSQLを実行する。

> update fuga set body = json_replace(body, '$.y', 12);

この時、バイナリログは、

### UPDATE `hoge`.`fuga`
### WHERE
### @1=1
### SET
### @2=JSON_REPLACE(@2, '$.y', 12)

となる。

もともと、binlog_row_imageはバイナリログに変更分しか記載しないもので、
5.6から導入されていたが、8ではbinlog_row_value_optionsというパラメータが追加された。
これにより、JSONの部分更新がバイナリログ上でも明示される。

とはいえ、JSONの部分更新を成立させるには、
以下のJSON関数を使う必要がある。

• JSON_SET()
• JSON_REPLACE()
• JSON_REMOVE()

MariaDB10.3でも、binlog_row_value_optionsがないため、

JSONの部分更新はできない。

おまけに、JSONはlongtextのエイリアスのため、

「JSONの正しさ」は保証しない。

JSONを使うなら、virtual columnもあるMSQL8が現時点では良いと思う。

（２０１８年１１月１日追記）

＞　おまけに、JSONはlongtextのエイリアスのため、

＞　「JSONの正しさ」は保証しない。

大変申し訳ありません。

上記は間違っていました。

https://mariadb.com/kb/en/library/json-data-type/

にcheck制約を使うことで、「JSONの正しさ」を保証することが可能でした。

以上

10.3のパラメータ（innodb関連中心）を確認してみたところ、

10.2でなされたような大きな変更は少なく、

10.2でdepricatedになったものが、removedになったりしている感じであった。

10.3の目玉としては、

やはりPL/SQLへの対応なのであろうが、

個人的には、以下が嬉しい。

• sequenceの導入 → nextvalができる
• Instant ADD COLUMN → alter tableの時間抑制に繋がりそう

その他、spider enginにおいて、条件句のpush downも入ったようだ。

以上を踏まえると、10.3への移行する場合、

既に10.2を使っているのであれば、比較的低リスクで移行できる？

と感じています。

10.4の計画も見てみると、

innodb以外のストレージエンジンの機能拡張が候補になっていたりと、

MySQLとの乖離がどんどん大きくなるような気がするが。。

以上

先月のMySQL8のGAにつづき、

MariaDB10.3がGAされました。

https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-103/

MySQL5.7もそんなにいじってないうちに8が出たので、

これから8を検証しようとしているうちに、10.3が出たのですが、

10.3では気になっていたJSONの改善はなさそうです。

そもそもMySQLでは5.7で導入されたJSONがNativeJSONのため、

登録時のvalidationが効くという特性がありました。

MariaDB10.2で導入されたJSONはText型のaliasのようで、

validationは効かないような感じです。（未検証です。すいません。）

ただ、MariaDBのドキュメント読む限り、Textのパースは高速と記載されています。

MySQL8ではJSONの部分更新機能が導入されました。

これにより、巨大なJSONの一部を更新しても、更新負荷を下げることが可能となり、

もちろん、バイナリログにもそのように記載されるとのことです。

このようなJSONの部分更新がMariaDB10.3でも入るかと期待してましたが、

そのような記載が見あたらず。。

そもそもdynamic columnsに部分更新があるのかどうかにさえ、今気付いた。。

というわけで、

• MySQL8
• MariaDB10.3

の2つのGAについて、まずはJSONまわりから挙動確認していこうと考えてます。

以上

centos7でstart-stop-daemonが使いたいなと思い、

調べていると、epelレポジトリにdpkgというパッケージがあった。

https://apps.fedoraproject.org/packages/dpkg/

ってわけで、

# yum install dpkg --enablerepo=epel

で、start-stop-daemonコマンド入りのdpkgがインストール可能である。

ソースからビルドする必要あると思っていたのに。。

以上

Google Computer Cloud（以下、GCP）で、

グローバルロードバランサの背後にnginxを置く場合は、

keepaliveをしておこう。

（参考）

https://blog.percy.io/tuning-nginx-behind-google-cloud-platform-http-s-load-balancer-305982ddb340

これやっておかないと、結構502がでる。

ちなみに、650秒をkeepalive_timeoutにするなら、

ロードバランサのタイムアウトは600秒程度、つまり少し短めがよいとのこと。

実は、AWSのELBも同様。

とはいえ、結構な高負荷にならないと、本事象は目立ってこないので、

なかなか難しいところである。

以上