技術めも

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したものとなっています。

以上

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/

以上

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を使っていく方針でよいのかな？という考察です。

以上

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

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

• 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対応も今後はあるのでは、と期待してます。

以上

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

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

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

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

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

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

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

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

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

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

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

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

以上

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

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

具体的には、

condition_pushdown_for_derived=on

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

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

・offのとき

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

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

・onのとき

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

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

というように、

condition_pushdown_for_derived

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

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

10.2から、

mrr=off
mrr_cost_based=off

に変更されている。

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

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

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

以上

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

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

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

動機としては、

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

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

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

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

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

（MySQL5.6）

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

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

（MySQL5.7）

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

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

（MySQL8）

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

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

（MariaDB10.1）

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

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

（MariaDB10.2）

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

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

（MariaDB10.3）

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

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

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

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

以上

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

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

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

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

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

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

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

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

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

（MySQL5.7）

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

（MySQL8）

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

一旦drop。

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

（MariaDB10.3）

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

一旦drop。

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

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

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

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

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

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

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

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

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

MySQL8の場合は以下。

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

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

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

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

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

以上

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

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

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

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

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

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

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

で、Redis4についてですが、

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

aof-use-rdb-preamble yes

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

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

auto-aof-rewrite-percentage

auto-aof-rewrite-min-size

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

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

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

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

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

（参考）

Redis Mixed RDB+AOF

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

以上

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

flush tables;

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

調査した結果、

spider_use_handler=1

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

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

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

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

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

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

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

以上