技術めも

MariaDB10.4.7から、text/blobについても、桁数を指定しないでINDEXが作成できるようになった。

以下のリンクの下のほうに書いてあった。

https://jira.mariadb.org/browse/MDEV-20918

しかし、UNIQUE KEYの場合、これによる弊害が現時点では発生する。

10.3まではエラーで抑止してくれたものの、10.4からはスルーして、HASHインデックスとして作成しまうのだ。

つまり、InnoDBにおいては、現状有効でないHASHインデックスが形だけ定義されるということである。

実際に例を挙げてみます。

MariaDB [test]> create table dummy (
    ->   c_tinytext tinytext,
    ->   c_text text,
    ->   c_mediumtext mediumtext,
    ->   c_longtext longtext,
    ->   cu_tinytext tinytext,
    ->   cu_text text,
    ->   cu_mediumtext mediumtext,
    ->   cu_longtext longtext,
    ->   key k1 (c_tinytext),
    ->   key k2 (c_text),
    ->   key k3 (c_mediumtext),
    ->   key k4 (c_longtext),
    ->   unique key u1 (cu_tinytext),
    ->   unique key u2 (cu_text),
    ->   unique key u3 (cu_mediumtext),
    ->   unique key u4 (cu_longtext)
    -> ) Engine=InnoDB;
Query OK, 0 rows affected, 3 warnings (0.016 sec)

waringがあるものの、問題なくテーブルが作成できてしまった。

念のためwarningを見てみる。

MariaDB [test]> show warnings;
+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Note  | 1071 | Specified key was too long; max key length is 3072 bytes |
| Note  | 1071 | Specified key was too long; max key length is 3072 bytes |
| Note  | 1071 | Specified key was too long; max key length is 3072 bytes |
+-------+------+----------------------------------------------------------+
3 rows in set (0.000 sec)

３つしかない。次にshow create tableを見てみる。

MariaDB [test]> show create table dummy;
+-------+-----------------------------------------+
| Table | Create Table                            |
+-------+-----------------------------------------+
| dummy | CREATE TABLE dummy (
  c_tinytext tinytext DEFAULT NULL,
  c_text text DEFAULT NULL,
  c_mediumtext mediumtext DEFAULT NULL,
  c_longtext longtext DEFAULT NULL,
  cu_tinytext tinytext DEFAULT NULL,
  cu_text text DEFAULT NULL,
  cu_mediumtext mediumtext DEFAULT NULL,
  cu_longtext longtext DEFAULT NULL,
  UNIQUE KEY u1 (cu_tinytext) USING HASH,
  UNIQUE KEY u2 (cu_text) USING HASH,
  UNIQUE KEY u3 (cu_mediumtext) USING HASH,
  UNIQUE KEY u4 (cu_longtext) USING HASH,
  KEY k1 (c_tinytext(255)),
  KEY k2 (c_text(768)),
  KEY k3 (c_mediumtext(768)),
  KEY k4 (c_longtext(768))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------+
1 row in set (0.001 sec)

なんと、UNIQUE KEYが、自動的にHASH INDEXに変更されてしまった。

念のため、INDEXの状態を見てみる。

MariaDB [test]> show index from dummy;
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dummy |          0 | u1       |            1 | cu_tinytext   | A         |        NULL |     NULL | NULL   | YES  | HASH       |         |               |
| dummy |          0 | u2       |            1 | cu_text       | A         |        NULL |     NULL | NULL   | YES  | HASH       |         |               |
| dummy |          0 | u3       |            1 | cu_mediumtext | A         |        NULL |     NULL | NULL   | YES  | HASH       |         |               |
| dummy |          0 | u4       |            1 | cu_longtext   | A         |        NULL |     NULL | NULL   | YES  | HASH       |         |               |
| dummy |          1 | k1       |            1 | c_tinytext    | A         |           0 |      255 | NULL   | YES  | BTREE      |         |               |
| dummy |          1 | k2       |            1 | c_text        | A         |           0 |      768 | NULL   | YES  | BTREE      |         |               |
| dummy |          1 | k3       |            1 | c_mediumtext  | A         |           0 |      768 | NULL   | YES  | BTREE      |         |               |
| dummy |          1 | k4       |            1 | c_longtext    | A         |           0 |      768 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.002 sec)

やはり、UNIQUE KEYだけHASH INDEXになっている。

この状態において、残念ながらUNIQUE KEYの検索時のインデックスは効きません。HASH INDEXだから等価なら効くのかと思いきや、InnDBなんで、フルスキャンになってしまいます。ご注意ください。

これ、見落とすと思う。少なくとも今までMySQLに触ってきた人なら。

ポジティブに考えるなら、InnoDBでもHASH INDEXを近いうちにサポートするということなのだろうか？とも考えられなくはない。10.5で導入されるのだろうか？

UNIQUE制約を張る以上、等価検索が往々にして行われるので、InnoDBのHASH INDEXサポートは個人的には歓迎かなと。

以上

MariaDB10.3から導入されたsystem versioned table（正確にはTemporal Data Tablesというらしい）ですが、一見するととても便利な機能ですが、ちょっと注意が必要です。

（ひとつめ）

CREATE時にSQLモードの影響を受ける。

SQLモードに「NO_ZERO_DATE」（もしくはTRADITIONAL）が入っていて、「0000-00-00」などの無効な日時を弾く定義を行っているとCREATEできない。（当然ALTER時も影響を受けます）

MariaDB [test]> select @@sql_mode;
 +------------------------------------------------------------------------------------------------------------------------------------------------------+
 | @@sql_mode                                                                                                                                           |
 +------------------------------------------------------------------------------------------------------------------------------------------------------+
 | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
 +------------------------------------------------------------------------------------------------------------------------------------------------------+

MariaDB [test]> create table t_sv (
     ->   user_id int not null,
     ->   name varchar(32) not null,
     ->   primary key (user_id)
     -> ) with system versioning;
 ERROR 1067 (42000): Invalid default value for 'row_start'

とまあ、こんな感じです。

当方の環境では、「NO_ZERO_DATE」と「TRADITIONAL」を落とすことで、CREATE可能になりました。

（ふたつめ）

row_endというカラム（レコードが切り替えられた日時であり、上のDDLだと勝手にそのような名前で作成される）は実はPRIMARY KEYである。

show create table の結果だと、

MariaDB [test]> show create table t_sv;
 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table | Create Table                                                                                                                                                                |
 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | t_sv  | CREATE TABLE t_sv (
   user_id int(11) NOT NULL,
   name varchar(32) NOT NULL,
   PRIMARY KEY (user_id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING |
 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

となるのですが、show index の結果をみると、

MariaDB [test]> show index from t_sv;
 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | t_sv  |          0 | PRIMARY  |            1 | user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 | t_sv  |          0 | PRIMARY  |            2 | row_end     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

というように、user_idとrow_endでPRIMARY KEY となっている。

show create table 上は出てこないのに、不思議。

ちまみに、information_schema.statistics のテーブルにも出てきているので、アプリケーション上で自動的にPRIMARY KEY の判定を行って、何かをしている場合は注意が必要です。

しかし、実は例外もあって、以下のようにそもそもPRIMARY KEY がないテーブルだとrow_endにPRIMARY KEY が設定されない。

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
   PARTITION BY SYSTEM_TIME INTERVAL 1 WEEK (
     PARTITION p0 HISTORY,
     PARTITION p1 HISTORY,
     PARTITION p2 HISTORY,
     PARTITION pcur CURRENT
   );

これまた超絶不思議。おそらくはclustered indexに自動的に決定されることが起因しているのかもしれない。

試しにxをPRIMARY KEY に変更したら以下のようなエラーがでた。

MariaDB [test]> alter table t add primary key (x);
 ERROR 4119 (HY000): Not allowed for system-versioned test.t. Change @@system_versioning_alter_history to proceed with ALTER.

どうやら変更できないみたい。

（みっつめ）

公式にも書いてありますが、mysqldumpを使った時に、現在のレコードのみが出力されます。

つまり、mysqldumpではsystem versioned table のバックアップはできません。

そのほか、

• 同じデータであっても隠しカラムの値のため、CHECKSUM の値が同じにならない
• データを変更してなくても、ON UPDATEが走る（row_startとかが変わるから）

といった特徴もあります。

安易に使うと、意外なところでハマりそうな気がしますが、社内システムなど限られた環境で扱う分は、威力を発揮しそうな感じがします。

system versioned table はトランザクションIDの履歴対応やら、10.4でアプリケーションタイムをサポートするなど、今後も改良が加えられていくと思いますので、個人的には期待してます。

以上

MariaDB10.3で導入されたSequenceですが、

便利な反面、注意しなければいけないことが2つありましたので、

記載しておきます。

（ひとつめ）

テーブルのdefault値にすると、名前次第でmysqldumpから復元できない。

これは以前書いた記事の話です。

たとえば、以下のようなテーブルシーケンスを作成します。

MariaDB [test]> create sequence t_user_seq increment by 0;

MariaDB [test]> create table t_user (
    ->   user_id int not null,
    ->   name varchar(32) not null,
    ->   seq_no bigint not null unique default nextval(t_user_seq),
    ->   primary key (user_id)
    -> );

この状態で、mysqldumpすると、以下のようにテーブルの定義が先に来てしまう関係上、リストアで失敗します。

--
-- Table structure for table `t_user`
--
:
:
:
--
-- Table structure for table `t_user_seq`
--

これは名前順で出力されてしまう仕様上、t_user_seqが後に定義されてしまうため、リストアに失敗するという現象をおこします。

（ふたつめ）

show create table 中に、シーケンスの定義にスキーマ（データベース）名が含まれてしまう。

MariaDB [test]> show create table t_user;
+--------+----------------------+
| Table  | Create Table         |
+--------+----------------------+
| t_user | CREATE TABLE `t_user` (
  `user_id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  `seq_no` bigint(20) NOT NULL DEFAULT nextval(`test`.`t_user_seq`),
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `seq_no` (`seq_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------+----------------------+

test というスキーマ名が入ってきてしまっています。

これにより、別スキーマに、

create table ~ like test.t_user;

とかしてしまうと、シーケンスの参照がコピー元となってしまいます。

うっかりやりそうなミスなので、ご注意ください。

以上

MariaDB10.4でhistgramの収集がデフォルトでONになりました。

（もちろんANALYZE TABLEを実行したときですけど）

10.3では、

select @@use_stat_tables;
+-------------------+
| @@use_stat_tables |
+-------------------+
| NEVER             |
+-------------------+

でしたが、

10.4では、

select @@use_stat_tables;
+------------------------+
| @@use_stat_tables      |
+------------------------+
| PREFERABLY_FOR_QUERIES |
+------------------------+

となり、ANALYZE TABLE 時に、特定のクエリーを加えると、histgramの収集が行われます。

ANALYZE TABLE 時にhistgramを収集するための書き方としては、

analyze table ${tableName} persistent for all;

のようになります。詳しくは公式を参考ください。

https://mariadb.com/kb/en/library/engine-independent-table-statistics/#examples-of-statistics-collection

で、実際にやってみました。

まずは、以下のようなテーブルに大量にデータを突っ込んでおきます。

MariaDB [test]> create table user (
   id         integer not null auto_increment,
   name       varchar(64),
   money_free integer,
   money_paid integer,
   primary key (id)
 );

MariaDB [test]>  select count() from user;
+----------+
| count() |
+----------+
| 16777214 |
+----------+

最小値と最大値をとっておきます。

MariaDB [test]> select min(money_free), max(money_free)  from user;
+-----------------+-----------------+
| min(money_free) | max(money_free) |
+-----------------+-----------------+
|               1 |             100 |
+-----------------+-----------------+

histgramを収集していない状態で、money_freeを条件として、フィルターされる行数を見てみます。

MariaDB [test]> explain extended select * from user where money_free between 30 and 31;
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
|    1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 16305730 |   100.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+

わかりにくいですが、filteredの値が100となっており、全くフィルターされない状態となっています。

次に、ANALYZE TABLE を実行してみます。

MariaDB [test]> analyze table user persistent for all;
+-----------+---------+----------+-----------------------------------------+
| Table     | Op      | Msg_type | Msg_text                                |
+-----------+---------+----------+-----------------------------------------+
| test.user | analyze | status   | Engine-independent statistics collected |
| test.user | analyze | status   | OK                                      |
+-----------+---------+----------+-----------------------------------------+
2 rows in set (36.498 sec)

結構時間かかりましたね。フルスキャンなので、公式にも記載があるように、slaveのみで実施するなどにして、サービスに影響が無いよう配慮する必要があります。

なお、バイナリログに記載されるようなので、masterで実施すると、slaveにも伝播してしまいますので、お気をつけ下さい。（flush localで回避できる模様）

そして、再度同じクエリーで、explainを実行してみます。

MariaDB [test]> explain extended select * from user where money_free between 30 and 31;
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
|    1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 16777214 |     1.56 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+

おおー、filteredが激減しましたね。

なお、ヒストグラムの情報は以下のテーブルに書かれるようです。

• mysql.table_stats
• mysql.column_stats
• mysql.index_stats

MySQL8のように、ヒストグラムを削除するコマンドはないようなので、直接DELETEしちまってもいいような。。

削除したら、flush tablesを打っておくとよいのかな。。。（削除に限らず作成／更新したら？）

このようにヒストグラムを更新することで、INDEXがないカラムに対しても、JOIN時に評価対象行が減るので、非常にパフォーマンスがアップすることが期待されます。

もちろん、join_bufferを増やしたり、optimizer_switchを調整したりといったことも合わせて必要ですが、OLAP向けにもInnoDBでもこれまで以上に頑張れそうですね。

以上

久し振りの投稿。

MariaDBを10.3から10.4にupgradeして、色々検証中。

まずは、INSTANTの拡張について記載します。

検証したバージョンは、10.4.8-communityです。

10.3で導入されたINSTANT機能ですが、10.4になって以下のように拡張されています。

• ALTER TABLE … ADD COLUMN（末尾への追加、10.3で導入）
• ALTER TABLE … DROP COLUMN（カラム削除、ただしINDEXなし）
• ALTER TABLE … MODIFY COLUMN（カラム変更）
  • Reordering Columns（順序変更、つまりafterが使える）
  • Changing the Data Type of a Column（varcharは最大まで、varbinaryは255まで、text型はダメ）
  • Changing a Column to NULL（NULL許可へ変更）
  • Adding a New ENUM Option（ENUM値の追加）
  • Adding a New SET Option（SET値の追加）
  • Removing System Versioning from a Column（system versionedの外し）
• ALTER TABLE … ALTER COLUMN（カラム修正）
  • Setting a Column’s Default Value（デフォルト値の設定）
  • Removing a Column’s Default Value（デフォルト値の削除）
  • ALTER TABLE … CHANGE COLUMN（カラム名変更）
• ALTER TABLE … DROP INDEX and DROP INDEX（Secodary INDEX削除）
• ALTER TABLE … DROP FOREIGN KEY（外部キー削除）
• ALTER TABLE … AUTO_INCREMENT=…（AUTOインクリメント値再設定）
• ALTER TABLE … PAGE_COMPRESSED=1 and ALTER TABLE … PAGE_COMPRESSION_LEVEL=…（1のみ）
• ALTER TABLE … DROP CONSTRAINT（制約の削除）
• ALTER TABLE … RENAME TO and RENAME TABLE …（テーブル名変更）

（参考）

https://mariadb.com/kb/en/library/innodb-online-ddl-operations-with-the-instant-alter-algorithm/

※varcharの拡張は、utf8mb4環境で、最大まで一瞬でした。

※varbinaryについては公式に記載ありませんが、255までなら一瞬でした。256からはCOPYとなるようです。

いやー数が多いですね。

ここには記載がありませんが、virtualの生成列も一瞬で生成可能なので、これもINSTANTとしていいのかなと。

で、INSTANTを発動させるためには、DDLを発行する前に、

SET SESSION alter_algorithm='INSTANT';

をやると、INSTANT以外のものはエラーとなるのですが、

MariaDB [test]> select @@alter_algorithm;
+-------------------+
| @@alter_algorithm |
+-------------------+
| DEFAULT           |
+-------------------+

の状態であれば、INSTANT可能なDDLであれば、INSTANTが選択されるようです。

ただ、これ明示的な記載がないのが気になるところですが。。

（参考）

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

いくつか実験してみました。

以下のようなテーブルに大量にデータ入れてみます。

MariaDB [test]> create table user (
   id         integer not null auto_increment,
   name       varchar(64),
   money_free integer,
   money_paid integer,
   primary key (id)
 );

MariaDB [test]>  select count() from user;
+----------+
| count() |
+----------+
| 16777214 |
+----------+

これまでは、INSTANTが機能されると、

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

の結果がインクリメントされるのですが、

MariaDB [test]> alter table user modify column name varchar(8192);
Query OK, 0 rows affected (0.017 sec)

などのvarchar拡張やら、enum追加などは、innodb_instant_alter_columnの値が増えませんでした。

これは、どうやら内部的には10.2でサポートされた拡張については、innodb_instant_alter_columnをインクリメントしないという意図的な動きのようです。

https://jira.mariadb.org/browse/MDEV-20801

ここまで広い範囲をカバーしてもらえると、DDL変更の運用負荷がとても下がりますね。

残っている箇所が、INDEX関係なので、相当難しいと思いますが、今後も期待ですね。

以上

MariaDBに特化した、データベースの差分検出／反映ツールを作ってみました。

https://github.com/shigenobu/magentadesk

こちらのツールは、同一筐体内に存在する2つのデータベース（スキーマ）の差分を検出し、検出した差分結果を使って、反映対象先データベースにデータをコピーするものとなります。

以下のMariaDB特有機能をふんだんに使ってしまっているため、MySQLでは動きません。

• Dynamic Column
• Except syntax
• CTE
• Sequence

使いどころとしては、webサービスなどで、staging環境で確認が取れたデータを、production環境に反映するといったシチュエーションとなるかと。

staging環境とproduction環境の筐体を同一にしておき、productionはそこからレプリケーションでproduction専用のDBサーバに配るような形がよいかなと思います。

日本語のマニュアルは以下となります。

https://github.com/shigenobu/magentadesk/blob/master/README.ja.md

JAVAの単体実行バイナリをreleaseページからDLしてもられば使えます。

依存を極小化したので、わずか1.5MBのバイナリとなり、取り回しも楽かと。

そのほか、過去に作成したものですが、JAVAのTCPサーバ／クライアント、UDPサーバ／クライアントのMAVENライブラリも紹介させてください。

（redchest）

https://github.com/shigenobu/redchest

websocketライクなインタフェースで設計しており、NIO2のTCP実装を使っています。Linuxで動かせば、epollのシステムコールで処理します。特徴としては、断線などにも対応すべく、最終受信からN秒で接続を破棄するように作り込んでいます。注意点として、広域（日本⇔ヨーロッパなど）で使う場合、epollのシステムコールの制約なのか、ごく稀にMTUより小さい受信バッファサイズであっても、さらに分割される場合があります。（C/C++でも同様の現象確認済）その際は、次回受信で不足分が取得できますので、前回メッセージを一時的にセッションに蓄積するなどの工夫が必要です。

（blueshelf）

https://github.com/shigenobu/blueshelf

こちらもwebsocketライクなインターフェースで設計した、NIOのUDP実装を使っています。Linuxならselectのシステムコールですね。特徴としては、複数ポートでlistenすることで、多重のIOを実現しています。そもそも、UDPには多重IOの技術はあまり不要かと思いますが、複数ポートを使うことで、複数のIOをうまく使い、メニーcore環境におけるパフォーマンス向上を目指しました。こちらも、最終受信からN秒で、「切断」のような扱いにしています。

以上

MariaDB10.4.6がGAとしてリリースされました。（2019年6月18日）

10.3からやや遅れたそうですが、ほぼ1年、おめでとうございます。

今回は、10.3と比べると、MariaDB Server関連の機能的な追加はすくないように見えます。

そのかわり、Galeraがバージョン4になっており、こちらが今回の目玉のように見えます。

Percona XtraDB Cluster 5.7のGalera3より一歩先行した感じでしょうか。

変更点の概要は公式ページを参照ください。

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

これから諸々検証していければと思います。

以上

githubが開発しているOrchestoratorについて、

以前の記事で、MariaDBに対応していない？と記載してしまったが、

実際に試してみたところ、MariaDBのGTIDレプリケーションにも対応していたので、

その備忘録を記載します。

なお、検証したバージョンは以下となります。

• CentOS7.5
• MariaDB10.3.11
• Orchestorator3.0.13

１．MariaDBのセットアップ

master、slave×2の準同期レプリケーションを構築します。

構成は以下の通り。

----------------------------------------
master
IP:192.168.35.11
----------------------------------------
     |                       |
     |(semi sync)            |(semi sync)
     |                       |
    --------------------    --------------------
    slave                   slave
    IP:192.168.35.12        IP:192.168.35.13
    --------------------    --------------------

設定は以下の通り。

# マルチソースをやる際に必要かもしれないので、レプリケーションクラスターで共通の数字
gtid_domain_id=11

# レプリケーションクラスター間で一意の数字（IPアドレスの下2桁など）
server_id={{ server_id }}

# 名前解決ができない環境向けに、自身のノードのIPアドレスを設定しておく
report_host={{ ip_addr }}

# とりあえず0だが、動的に変更する
read_only=0

# 安定のrow
binlog_format=row

# 毎コミットごとにバイナリログに書き出す
sync_binlog=1

# フェイルオーバー時にslaveが昇格しても大丈夫なように
log_slave_updates=1

# レプリケーション安全設定
relay_log_recovery=1

# 大きなリレーログへの対応
slave_max_allowed_packet=1G

# タイムアウト長めに
slave_net_timeout=3600

# slaveのSQLスレッドの並列化
slave_parallel_threads={{ cpu_num }}

# 厳格モード
gtid_strict_mode=1

# 以下、準同期設定（MariaDB10.3では、plugin_load_addは不要）
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

そして、レプリケーションを開始しておく。

２．Orchestoratorの設定

IPアドレス192.168.35.10のOrchestorator専用ノードを立てる。

orchestorator.conf.jsonの内容は以下の通り。

{
  "Debug": false,
  "EnableSyslog": false,
  "ListenAddress": ":3000",
  "MySQLTopologyUser": "adminuser",
  "MySQLTopologyPassword": "adminpassword",
  "BackendDB": "sqlite",
  "SQLite3DataFile": "/usr/local/orchestrator/orchestrator.sqlite3",
  "DiscoverByShowSlaveHosts": true,
  "InstancePollSeconds": 5,
  "UnseenInstanceForgetHours": 240,
  "SnapshotTopologiesIntervalHours": 0,
  "InstanceBulkOperationsWaitTimeoutSeconds": 10,
  "HostnameResolveMethod": "default",
  "MySQLHostnameResolveMethod": "@@report_host",
  "SkipBinlogServerUnresolveCheck": true,
  "ExpiryHostnameResolvesMinutes": 60,
  "RejectHostnameResolvePattern": "",
  "ReasonableReplicationLagSeconds": 10,
  "ProblemIgnoreHostnameFilters": [],
  "VerifyReplicationFilters": false,
  "ReasonableMaintenanceReplicationLagSeconds": 20,
  "CandidateInstanceExpireMinutes": 60,
  "DetectClusterAliasQuery": "SELECT @@report_host",
  "PromotionIgnoreHostnameFilters": [],
  "DetectSemiSyncEnforcedQuery": "",
  "URLPrefix": "",
  "StatusEndpoint": "/api/status",
  "StatusSimpleHealth": true,
  "StatusOUVerify": false,
  "AgentPollMinutes": 60,
  "UnseenAgentForgetHours": 6,
  "StaleSeedFailMinutes": 60,
  "SeedAcceptableBytesDiff": 8192,
  "PseudoGTIDPattern": "",
  "PseudoGTIDPatternIsFixedSubstring": false,
  "PseudoGTIDMonotonicHint": "asc:",
  "DetectPseudoGTIDQuery": "",
  "BinlogEventsChunkSize": 10000,
  "SkipBinlogEventsContaining": [],
  "ReduceReplicationAnalysisCount": true,
  "FailureDetectionPeriodBlockMinutes": 60,
  "RecoveryPeriodBlockSeconds": 3600,
  "RecoveryIgnoreHostnameFilters": [],
  "RecoverMasterClusterFilters": [
    "*"
  ],
  "RecoverIntermediateMasterClusterFilters": [
    "*"
  ],
  "OnFailureDetectionProcesses": [],
  "PreFailoverProcesses": [],
  "PostFailoverProcesses": [
    "echo -e 'Failed server is {failedHost}:{failedPort}.\nSuccessed server is {successorHost}:{successorPort}.' | mail -s 'Complete Orchestrator Failover' yourname@sample.com"
  ],
  "PostUnsuccessfulFailoverProcesses": [
    "echo -e 'Failed server is {failedHost}:{failedPort}.' | mail -s 'Failed Orchestrator Failover' yourname@sample.com"
  ],
  "PostMasterFailoverProcesses": [
    "/usr/local/bin/detect_orchestrator_failover.py -u adminuser -p adminpassword -H {successorHost} -P {successorPort}"
  ],
  "PostIntermediateMasterFailoverProcesses": [],
  "CoMasterRecoveryMustPromoteOtherCoMaster": true,
  "DetachLostSlavesAfterMasterFailover": true,
  "ApplyMySQLPromotionAfterMasterFailover": true,
  "MasterFailoverDetachSlaveMasterHost": false,
  "MasterFailoverLostInstancesDowntimeMinutes": 0,
  "PostponeSlaveRecoveryOnLagMinutes": 0
}

※adminuserは、レプリケーションクラスターに対してアクセス可能なsuperユーザとする。

ここでのポイントは、

"MySQLHostnameResolveMethod": "@@report_host"
"DetectClusterAliasQuery": "SELECT @@report_host"

である。

これにより、Orchestoratorはレプリケーションクラスターの状態取得をIPアドレスで行うことができるようになる。

この後、ひとまずOrechstoratorにレプリケーションクラスターのmasterノードを登録する。masterを登録しておけば、slaveも自動的に発見してくれる。

なお、登録方法は、WebGUIやらAPIなどであるが、ここでは割愛。

３．フェイルオーバー時のMariaDBパラメータの動的設定処理の配備

上記の設定の

"PostMasterFailoverProcesses"

の部分の

/usr/local/bin/detect_orchestrator_failover.py

を以下の感じで実装する。

なお、以下の処理は、masterのフェイルオーバー完了時に呼び出される。

#!/usr/bin/python
# -*- coding: utf-8 -*-
import os
import sys
import traceback
import datetime
import re
import argparse
import MySQLdb

parser = argparse.ArgumentParser(description = "detect orchestrator failover.")
parser.add_argument("-u", type=str, help = "(required) mysql user", required=True)
parser.add_argument("-p", type=str, help = "(required) mysql pass", required=True)
parser.add_argument("-H", type=str, help = "(required) master host", required=True)
parser.add_argument("-P", type=str, help = "(required) master port", required=True)
args = parser.parse_args()

mysql_user = args.u
mysql_pass = args.p
master_host = args.H
master_port = args.P

fp = None
master_conn = None
master_c = None
try:
  # log
  log_path = '/path_to_your_dir/detect_orchestrator_failover.log'
  fp = open(log_path, 'a')

  # connect master
  master_conn = MySQLdb.connect(
    user=mysql_user,
    passwd=mysql_pass,
    host=master_host,
    port=int(master_port),
    db='mysql'
  )
  master_c = master_conn.cursor(MySQLdb.cursors.DictCursor)
  fp.write('[{}]new master is {}:{}.\n'.format(datetime.datetime.now(), master_host, master_port))

  # set master
  master_queries = []
  master_queries.append('set global read_only = 0')
  for sql in master_queries:
    master_c.execute(sql)
    fp.write('[{}]new master query is {}.\n'.format(datetime.datetime.now(), sql))

  # get slaves
  sql = "show slave hosts"
  master_c.execute(sql)
  rows = master_c.fetchall()
  if isinstance(rows, tuple):
    for row in rows:
      slave_conn = None
      slave_c = None
      try:
        slave_host = row['Host']
        slave_port = row['Port']

        # connect slave
        slave_conn = MySQLdb.connect(
          user=mysql_user,
          passwd=mysql_pass,
          host=slave_host,
          port=int(slave_port),
          db='mysql'
        )
        slave_c = slave_conn.cursor(MySQLdb.cursors.DictCursor)
        fp.write('[{}]new slave is {}:{}.\n'.format(datetime.datetime.now(), slave_host, slave_port))

        # set slave
        slave_queries = []
        slave_queries.append('set global read_only = 1')
        for sql in slave_queries:
          slave_c.execute(sql)
          fp.write('[{}]new slave query is {}.\n'.format(datetime.datetime.now(), sql))

      except:
        fp.write('[{}]{}\n'.format(datetime.datetime.now(), traceback.format_exc()))
      finally:
          if slave_c is not None:
            slave_c.close()
          if slave_conn is not None:
            slave_conn.close()

except:
  fp.write('[{}]{}\n'.format(datetime.datetime.now(), traceback.format_exc()))
finally:
  if master_c is not None:
    master_c.close()
  if master_conn is not None:
    master_conn.close()
  if fp is not None:
    fp.close()

ここまでやっておくことにより、

• IPアドレスベースでOrchestorator上からレプリケーションクラスターの認識／操作が行われる
• フェイルオーバー時にslaveのread_onlyを1に変更できる

ということができる。

ちなみに、Orchestoratorでは、

masterのダウンに対する振舞のみがhookとして設定できるようなので、

slaveを追加した場合に自動的にread_onlyを設定するといったことは出来無いように見えます。（たぶん）

マニュアルが細かいので、利用の際はがんばってマニュアルを読んでみてください。

https://github.com/github/orchestrator/tree/master/docs

（参考）

https://qiita.com/rerorero/items/1f06cc8db9c469191289

以上

MariaDBにてudfを作成したのですが、

生成カラムで利用する際の制限が存在しているようなので記載します。

なお、MariaDBのバージョンは、10.3.13で、

以下のようなudfを作成したとします。

> create function myfunc returns string soname 'myfunc.so';

１．virtualタイプの生成カラム

virtualタイプの生成カラムでは、条件式として使えます。

以下のようなalterをすることができます。

> alter table t1 add column c2 varchar(64) as (myfunc(c1)) virtual;

しかし、c2に対するINDEXを作成することはできません。

２．persistent(stored)タイプの生成カラム

persistentタイプの生成カラムでは、udfを使えません。

当然INDEXも作成できません。

以下のマニュアルにもその旨が記載されていますが、udfを用いたvirtualタイプの生成列にINDEXを作成することができないとは記載されていないような。。

https://mariadb.com/kb/en/library/generated-columns/

実は、この制約は結構痛いなーと感じてます。

udfは、C/C++やらgolangで実装できるので、アプリケーションの処理を通さなければ判定できなかった状態を、データベース側で判定できるようになる、、にも拘わらずINDEXが使えない、つまり高速アクセスできない。。。残念。

また、persistentタイプにも使えないことで、文字列を全文検索用に整形するようなudfを作っても、すごく便利には使えないなどのことが発生してしまう。

（FTSに対応するのは、persistentタイプのみなので）

将来のバージョンアップに期待。

以上

MariaDBにMroongaはバンドルされていますが、

TokenizerとしてMecabを使う際は、少し注意が必要です。

公式には、以下のように書かれていますが、手順を残しておきます。

https://mariadb.com/kb/en/library/mroonga-overview/

「Tokenise using MeCab. Required Groonga to be buillt with MeCab support.」

なお、OSはCentOS7、MariaDBのバージョンは、10.3.8です。

まずは、バンドルされているMroongaのインストールを見ていきます。

公式（https://mariadb.com/kb/en/library/about-mroonga/）を参考にしています。

１．プラグインのインストール

MariaDB [(none)]> INSTALL SONAME 'ha_mroonga';

２．Mroongaのインストール確認

MariaDB [(none)]> show engines;
+--------------------+---------+-----------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                 | Transactions | XA   | Savepoints |
+--------------------+---------+-----------------------------------------+--------------+------+------------+
| CSV                | YES     | Stores tables as CSV files              | NO           | NO   | NO         |
:（省略）
| Mroonga            | YES     | CJK-ready fulltext search, column store | NO           | NO   | NO         |
:（省略）
+--------------------+---------+-----------------------------------------+--------------+------+------------+

３．UDFの作成

公式（https://mariadb.com/kb/en/library/creating-mroonga-user-defined-functions/）より引用。

４．プラグインの確認

MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+---------------+---------+
| Name                          | Status   | Type               | Library       | License |
+-------------------------------+----------+--------------------+---------------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL          | GPL     |
:（省略）
| Mroonga                       | ACTIVE   | STORAGE ENGINE     | ha_mroonga.so | GPL     |
| Mroonga_stats                 | ACTIVE   | INFORMATION SCHEMA | ha_mroonga.so | GPL     |
+-------------------------------+----------+--------------------+---------------+---------+

５．Tokenizerの確認

MariaDB [(none)]> select json_detailed(mroonga_command("tokenize TokenBigram '東京都'"));
+----------------------------------------------------------------+
| json_detailed(mroonga_command("tokenize TokenBigram '東京都'")) |
+----------------------------------------------------------------+
| [
    {
        "value": "東京",
        "position": 0,
        "force_prefix": false
    },
    {
        "value": "京都",
        "position": 1,
        "force_prefix": false
    },
    {
        "value": "都",
        "position": 2,
        "force_prefix": false
    }
]      |
+----------------------------------------------------------------+

MariaDB [(none)]> select json_detailed(mroonga_command("tokenize TokenMecab '東京都'"));
ERROR 1026 (HY000): [tokenize] nonexistent tokenizer 

TokenBigramは使えるが、TokenMecabは使えない。。。

このように、公式にバンドルされているMroongaではMecabが使えないので、

Groongaレポジトリから取得するMroongaを使うには、

一旦削除しておく必要があります。

MariaDB [(none)]> UNINSTALL SONAME 'ha_mroonga';

ここからGroongaレポジトリからもろもろ取得し、設定していきます。

主な設定方法は、以下の公式ページに記載されています。

http://mroonga.org/ja/docs/install/centos.html#centos-7-with-mariadb-10-3-package

１．レポジトリのインストール

# yum install -y https://packages.groonga.org/centos/groonga-release-latest.noarch.rpm

２．Mroonga関連インストール

# yum install -y --enablerepo=epel mecab mecab-devel groonga groonga-tokenizer-mecab mariadb-10.3-mroonga

※依存関係により、この時点でMariaDB-Serverのバージョンが上がる可能性あるため、mysql_upgradeを適宜実行する。

３．Mecabの確認

$ mecab -D
filename:    /usr/lib64/mecab/dic/ipadic/sys.dic
version:    102
charset:    utf8
type:    0
size:    392126
left size:    1316
right size:    131

$ echo '東京都' | mecab
東京    名詞,固有名詞,地域,一般,,,東京,トウキョウ,トーキョー
都    名詞,接尾,地域,,,*,都,ト,ト
EOS

４．Groongaの確認

$ groonga --version | head -1
Groonga 8.0.9 [linux-gnu,x86_64,utf8,match-escalation-threshold=0,nfkc,mecab,msgpack,mruby,onigmo,zlib,lz4,zstd,epoll]

ここまでで、Mecabが無事動作し、Groongaからも認識されていればOK。

５．Mroongaプラグインをインストール

MariaDB [(none)]> source /usr/share/mroonga/install.sql;

上記コマンドで、Groonga提供のMroongaライブラリがインストールされ、UDFも上書きされます。

６．プラグインの状態確認

MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+------------------------+---------+
| Name                          | Status   | Type               | Library                | License |
+-------------------------------+----------+--------------------+------------------------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
:
| Mroonga                       | ACTIVE   | STORAGE ENGINE     | ha_mroonga_official.so | GPL     |
+-------------------------------+----------+--------------------+------------------------+---------+

お、共有ライブラリの名称が「ha_mroonga.so」から「ha_mroonga_official.so」に変わっていますね。

７．Mecabの実行確認

MariaDB [(none)]> select json_detailed(mroonga_command("tokenize TokenMecab '東京都'"));
+---------------------------------------------------------------+
| json_detailed(mroonga_command("tokenize TokenMecab '東京都'")) |
+---------------------------------------------------------------+
| [
    {
        "value": "東京",
        "position": 0,
        "force_prefix": false,
        "force_prefix_search": false
    },
    {
        "value": "都",
        "position": 1,
        "force_prefix": false,
        "force_prefix_search": false
    }
]    |
+---------------------------------------------------------------+

おおー、無事にMariaDBからMecabを使えるようになりました。

長くなりましたが、以上です。