IT系のめもを蓄積していこうかと
spider engineを検証していたら、謎の挙動に出会った。
簡単に言うと、whereでpartition条件句を含めたクエリーを発行したのち、
whereがないクエリーを発行すると、partition条件句の向き先となったノードに発行先が固定されるというもの。
試したバージョンは、
MariaDB：10.3.8
Spider：3.3.13
である。
原因がよくわかっていないのだが、
flush tables;
コマンドを発行すると治る。。
セッションを終了しても治らない。。
高速化のため、spider側で何かしらキャッシュしていることが原因なのではないかと考えているが、
どうも気持ち悪く、バグなのか仕様なのかも現在はっきりしていない。
原因が判明したら、また記載しようと思います。
また、MariaDB10.1にバンドルされていた3.2.37とは動きが異なる部分が多かったので、
検証完了次第、詳細な動き（パターン）を合わせて記載出来たらと思います。
以上
MariaDBの以下のクラスター方式で、パフォーマンス比較をしてみた。
比較動機としては、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については、
・ストレージモードで扱うと、そもそもトランザクションに対応していない問題
・ラッパーモードで扱うと、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_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」という日時になってしまう。
前者はその通りの話ではあるが、後者のパターンも有り得るのがやっかいである。
しかも、条件句で指定した時は、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関数を使う必要がある。
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への対応なのであろうが、
個人的には、以下が嬉しい。
その他、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に部分更新があるのかどうかにさえ、今気付いた。。
というわけで、
の2つのGAについて、まずはJSONまわりから挙動確認していこうと考えてます。
以上