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/)を参考にしています。

1.プラグインのインストール

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

2.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 |
:(省略)
+--------------------+---------+-----------------------------------------+--------------+------+------------+

3.UDFの作成

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

4.プラグインの確認

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 |
+-------------------------------+----------+--------------------+---------------+---------+

5.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

1.レポジトリのインストール

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

2.Mroonga関連インストール

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

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

3.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

4.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。

5.Mroongaプラグインをインストール

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

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

6.プラグインの状態確認

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」に変わっていますね。

7.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を使えるようになりました。

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


投稿日時:2018年12月14日 16:28   カテゴリー:mariadb  

MariaDB10.3系で導入されたsequence(nextvalの方)において、

ほかテーブルのdefault値として設定できるようです。

確認したバージョンは、10.3.8です。


最初にsequenceを作ります。

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

このとき、galeraなど使う際、問題ないように「increment by 0」をつけておきます。

ちなみに、このときのsequence関連のパラメータは以下の通りなので、

「increment by 0」をつけても、1ずつインクリメントされます。

MariaDB [test]> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------

次に以下のようなテーブルを作成します。

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

上記テーブルにおいて、seq_noカラムのdefault値にsequenceの結果を設定したので、以下のようなinsertにて、nextvalの結果が格納されます。

MariaDB [test]> insert into t_user (user_id, name) values (1, 'name01');
MariaDB [test]> insert into t_user (user_id, name) values (2, 'name02');
MariaDB [test]> insert into t_user (user_id, name) values (3, 'name03');
MariaDB [test]> insert into t_user (user_id, name) values (11, 'name11');
MariaDB [test]> insert into t_user (user_id, name) values (12, 'name12');
MariaDB [test]> insert into t_user (user_id, name) values (13, 'name13');

MariaDB [test]> select * from t_user;
+---------+--------+--------+
| user_id | name | seq_no |
+---------+--------+--------+
| 1 | name01 | 1 |
| 2 | name02 | 2 |
| 3 | name03 | 3 |
| 11 | name11 | 4 |
| 12 | name12 | 5 |
| 13 | name13 | 6 |
+---------+--------+--------+


sequenceが導入されてから、見落としていたのですが、なにげにこれは嬉しい。

公式にもこっそり書いてありました。

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

PERSISTENTタイプの生成カラムで設定できたら、変更不能な連番を作り出せたので、それがあっても良かったのかなと少し思います。(とはいえ変更不能も困るか。。)

なお、sequenceの上限は、

9223372036854775806

で、javaなどのlong型の限界値

9223372036854775807

より-1したものとなっています。

以上


投稿日時:2018年12月14日 14:42   カテゴリー:mariadb  

MariaDB10.2で導入されたCHECK CONSTRAINTについて挙動を確認してみました。

使用したバージョンは、10.3.8です。


まず、create table時に一緒に制約を入れてみる。

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(32) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `reg_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `check_status` CHECK (`status` between 1 and 10)
)

これで、statusカラムは1以上10以下しか入らないはずで、以下のSQLを流してみる。

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u1', 1, now());
-> OK

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u2', 10, now());
-> OK

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u3', 0, now());
ERROR 4025 (23000): CONSTRAINT `check_status` failed for `test`.`users` 

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u4', 11, now()); 
ERROR 4025 (23000): CONSTRAINT `check_status` failed for `test`.`users` 

見事に、0と11が弾かれている。
もちろん、0と11を登録可能なようにするためには、一度制約を解除する必要がある。

 

次に、ALTERのときの動きを見てみる。

前回作って800万件入っている以下のテーブルを利用する。

 
create table user ( 
  id integer not null auto_increment, 
  name varchar(64), 
  money_total_virtual integer as (money_free + money_paid) virtual
  money_free integer, 
  money_paid integer, 
  primary key (id) 
); 

・通常カラムへの制約

MariaDB [test]> alter table user add constraint check_money_free check (money_free <= 100);
Query OK, 8388608 rows affected (59.516 sec)           
Records: 8388608  Duplicates: 0  Warnings: 0

・virtualカラムへの制約

MariaDB [test]> alter table user add constraint check_money_total_virtual check (money_total_virtual <= 200);
Query OK, 8388608 rows affected (1 min 0.316 sec)      
Records: 8388608  Duplicates: 0  Warnings: 0

やはり、大量のレコードがある状態では、制約を掛けるのも時間がかかることがわかる。

 

最後にCHECK制約の仕様をまとめておきます。

  • すでに入っているデータが制約違反の場合、制約自体が掛からない
  • 制約の発動はINSERT/UPDATE時
  • primary keyに対しても制約は掛けられる
  • auto_incrementに対しては制約は掛けることができない

合わせて公式のマニュアルも参照ください。

https://mariadb.com/kb/en/library/constraint/

 

以上

投稿日時:2018年11月13日 17:44   カテゴリー:mariadb  

MariaDBの生成カラムをALTERするときの速度検証してみました。

確認バージョンは、10.3.8です。

 

準備として、以下のようなテーブルを作成し、データを投入しておきます。

create table user (
  id         integer not null auto_increment,
  name       varchar(64),
  money_free integer,
  money_paid integer,
  primary key (id)
);
insert into user () values ();
insert into user (id) select 0 from user;
: 上を20回くらい実行

update user set name = concat('name-', lpad(id, 10, '0')), money_free = ceil(rand() * 100), money_paid = ceil(rand() * 100);

MariaDB [test]> select count(*) from user;
+----------+
| count(*) |
+----------+
| 8388608  |
+----------+
1 row in set (1.579 sec)

下準備として、約800万件のデータを投入。


この状態で、通常・PERSITENT・VIRTUALのカラムをALTERで追加してみます。

通常カラムは、最後尾に追加すると、INSTANTが発動してしまうので、すべてAFTERで途中に差し込んでみます。

 

・通常

# カラム追加
MariaDB [test]> alter table user add column money_total_nornaml integer after name;
Query OK, 0 rows affected (17.338 sec)
Records: 0 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx01 on user (money_total_nornaml);
Query OK, 0 rows affected (20.096 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_nornaml;
Query OK, 0 rows affected (16.986 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加:17秒

インデックス追加:20秒

カラム削除:16秒

という結果でした。

 

・PERSISTENT

# カラム追加
MariaDB [test]> alter table user add column money_total_persitent integer as (money_free + money_paid) persistent after name;
Query OK, 8388608 rows affected (36.700 sec)
Records: 8388608 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx02 on user (money_total_persitent);
Query OK, 0 rows affected (19.660 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_persitent;
Query OK, 0 rows affected (16.810 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加:36秒

インデックス追加:19秒

カラム削除:16秒

という結果でした。

 

・VIRTUAL

# カラム追加
MariaDB [test]> alter table user add column money_total_virtual integer as (money_free + money_paid) virtual after name;
Query OK, 0 rows affected (0.012 sec)
Records: 0 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx03 on user (money_total_virtual);
Query OK, 0 rows affected (20.224 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_virtual;
Query OK, 0 rows affected (0.011 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加:0.01秒

インデックス追加:20秒

カラム削除:0.01秒

という結果でした。

 

PERSISTENTは、実データの生成も一緒に行われるので、

通常に比べて、ADDも遅くなるという結果でした。

VIRTUALは、実データの生成が無いので、

通常に比べて、ADD/DROPも圧倒的に高速ですね。

インデックスの作成速度は変わらないですね。

VIRTUALはPERISITENTに比べて、演算コストがかかるため、

そのあたりは注意が必要であると思いますが、

基本的には、VIRTUALを使っていく方針でよいのかな?という考察です。

 

以上

投稿日時:2018年11月09日 11:47   カテゴリー:mariadb  

まず自分自身がMariaDBの生成カラム周りおよびJSONについて、

以下2点勘違いをしていた。

  • MariaDBでもMySQLと同様に、VIRTUAL COLUMNにインデックスを張ることができる(すいません、できないと思ってました。。)
  • MariaDBでもCHECK制約を使うことで、「JSONの正しさ」を保証できる(すいません、できないと思ってました。。)

以前書いた記事にも訂正を追記しておきました。

 

というわけで、MariaDB10.2.17、10.3.10のバージョンで生成カラムについて見ていきます。


ではまず、以下のようなテーブルを作ってみます。

CREATE TABLE `mail` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`to` varchar(255) NOT NULL,
`cc` varchar(255),
`bcc` varchar(255),
`subject` varchar(255) NOT NULL,
`body` json NOT NULL,
`addrs` text AS (CONCAT_WS(',', `to`, `cc`, `bcc`)) PERSISTENT,
`sign` varchar(255) AS (JSON_VALUE(`body`, '$.sign')) VIRTUAL,
CHECK (JSON_VALID(`body`)),
PRIMARY KEY (`id`),
FULLTEXT KEY `mail_fidx01` (`addrs`),
KEY `mail_idx01` (`sign`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ちょっと長いのですが、ポイントは以下の通り。

  1. addrsカラムに対して、PERSISTENTタイプの生成カラムを利用して、全文検索INDEXを張る
  2. bodyカラムに対して、JSONの型チェックを行う
  3. signカラムに対して、VIRTUALタイプの生成カラムを利用して、JSONの部分抽出を行い、セカンダリーINDEXを張る

1はカラムを結合した状態を全文検索にしたい場合、

2はJSONの正しさを保証したい場合、

3はJSONの中の特定のキーを条件に高速にデータアクセスしたい場合、

に用いられるような想定です。

この状態のテーブルを見てみます。

MariaDB [test]> desc mail;
+---------+--------------+------+-----+---------+-------------------+
| Field   | Type         | Null | Key | Default | Extra             |
+---------+--------------+------+-----+---------+-------------------+
| id      | int(10)      | NO   | PRI | NULL    | auto_increment    |
| to      | varchar(255) | NO   |     | NULL    |                   |
| cc      | varchar(255) | YES  |     | NULL    |                   |
| bcc     | varchar(255) | YES  |     | NULL    |                   |
| subject | varchar(255) | NO   |     | NULL    |                   |
| body    | longtext     | NO   |     | NULL    |                   |
| addrs   | text         | YES  | MUL | NULL    | STORED GENERATED  |
| sign    | varchar(255) | YES  | MUL | NULL    | VIRTUAL GENERATED |
+---------+--------------+------+-----+---------+-------------------+
8 rows in set (0.002 sec)

MariaDB [test]> show index from mail;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mail  | 0          | PRIMARY     | 1            | id          | A         | 0           | NULL     | NULL   |      | BTREE      |         |               |
| mail  | 1          | mail_idx01  | 1            | sign        | A         | 0           | NULL     | NULL   | YES  | BTREE      |         |               |
| mail  | 1          | mail_fidx01 | 1            | addrs       | NULL      | NULL        | NULL     | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.000 sec)

生成カラムの情報がdescのExtraにも表現されていますね。

ちなみに、生成カラムは「NOT NULL DEFAULT ・・・」は付けられないようで、生成カラムの評価ができない(失敗?)の場合は、NULLが入る仕様のようです。

 

次に以下のようなデータを流し込んで、結果を見てみます。

INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00001@example.com', 'cc-00001@example.com', 'bcc-00001@example.com', 'subject-00001', '{"sign": "sign-00001", "message": "message-00001"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00002@example.com', 'cc-00002@example.com', null , 'subject-00002', '{"sign": "sign-00002", "message": "message-00002"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00003@example.com', null , null , 'subject-00003', '{"sign": "sign-00003", "message": "message-00003"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00004@example.com', null , null , 'subject-00004', '{"message": "message-00004"}');
MariaDB [test]> select * from mail\G;
*************************** 1. row ***************************
     id: 1
     to: to-00001@example.com
     cc: cc-00001@example.com
    bcc: bcc-00001@example.com
subject: subject-00001
   body: {"sign": "sign-00001", "message": "message-00001"}
  addrs: to-00001@example.com,cc-00001@example.com,bcc-00001@example.com
   sign: sign-00001
*************************** 2. row ***************************
     id: 2
     to: to-00002@example.com
     cc: cc-00002@example.com
    bcc: NULL
subject: subject-00002
   body: {"sign": "sign-00002", "message": "message-00002"}
  addrs: to-00002@example.com,cc-00002@example.com
   sign: sign-00002
*************************** 3. row ***************************
     id: 3
     to: to-00003@example.com
     cc: NULL
    bcc: NULL
subject: subject-00003
   body: {"sign": "sign-00003", "message": "message-00003"}
  addrs: to-00003@example.com
   sign: sign-00003
*************************** 4. row ***************************
     id: 4
     to: to-00004@example.com
     cc: NULL
    bcc: NULL
subject: subject-00004
   body: {"message": "message-00004"}
  addrs: to-00004@example.com
   sign: NULL
4 rows in set (0.000 sec)

ちゃんとデータが入っていますので、
PERSISTENTタイプのaddrsへの全文検索と、
VIRTUALタイプのsignへの検索を行ってみます。

 

・PERSISTENTタイプのaddrsへの全文検索

MariaDB [test]> select * from mail where match(addrs) against('cc-00002')\G;
*************************** 1. row ***************************
     id: 2
     to: to-00002@example.com
     cc: cc-00002@example.com
    bcc: NULL
subject: subject-00002
   body: {"sign": "sign-00002", "message": "message-00002"}
  addrs: to-00002@example.com,cc-00002@example.com
   sign: NULL
1 row in set (0.001 sec)

MariaDB [test]> explain select * from mail where match(addrs) against('cc-00002')\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mail
         type: fulltext
possible_keys: mail_fidx01
          key: mail_fidx01
      key_len: 0
          ref:
         rows: 1
        Extra: Using where
1 row in set (0.000 sec)

・VIRTUALタイプのsignへの検索

MariaDB [test]> select * from mail where sign = 'sign-00003'\G;
*************************** 1. row ***************************
     id: 3
     to: to-00003@example.com
     cc: NULL
    bcc: NULL
subject: subject-00003
   body: {"sign": "sign-00003", "message": "message-00003"}
  addrs: to-00003@example.com
   sign: sign-00003
1 row in set (0.001 sec)

MariaDB [test]> explain select * from mail where sign = 'sign-00003'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mail
         type: ref
possible_keys: mail_idx01
          key: mail_idx01
      key_len: 1023
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.001 sec)

 

ちょっと件数少ないですが、両方共INDEXが効いていることがわかります。

 

生成カラムって実際どのくらい使われているのかわかりませんが、うまく使うことで、機能追加にも柔軟な対応ができそうな予感です。

生成カラムの進化はこれからも期待しています。

おっと、INSTANT ADDは、生成カラムには効かないので、ご注意ください。INSTANT対応も今後はあるのでは、と期待してます。

 

以上

投稿日時:2018年11月01日 16:38   カテゴリー:mariadb  

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使えば、

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

 

以上

投稿日時:2018年10月22日 12:48   カテゴリー:mariadb, mysql  

前回の記事で、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のままであった。

 

以上

投稿日時:2018年10月02日 17:45   カテゴリー:mariadb  

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は嬉しい結果を出してくれました。

 

以上

投稿日時:2018年09月11日 20:08   カテゴリー:mariadb, mysql  

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負荷を持っていかれるか、試してみたいな。。

 

以上

 

投稿日時:2018年09月11日 18:48   カテゴリー:mariadb, mysql  

久しぶりに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

 

以上

投稿日時:2018年09月06日 16:45   カテゴリー:redis