技術めも

つい忘れがちなので、foregroundからbackgroundへジョブを移すメモです。

centos7で確認済みです。

まず、こんな処理があったとします。

$ cat ./hogehoge.sh 
#!/bin/sh
while true; do echo "hogehoge" >> ./fugafuga.txt; sleep 1; done

これをssh経由でforegroundで実行してしまったが、background実行にして、sshを切りたい場合、

（実行）
$ ./hogehoge.sh 

（中断）
^Z       ※ここでCtr+z
[1]+  Stopped                 ./hogehoge.sh

（確認）
$ jobs
[1]+  Stopped                 ./hogehoge.sh

（バックグラウンドに切り替え）
$ bg 1
[1]+ ./hogehoge.sh &

（TTYから切り離す）
$ disown -h %1

とすることで、background実行になり、sshを切ってもジョブは残り続けます。

予め時間がかかるときは、以下のようにnohup（debian系ならstart-stop-daemonでもよし）を使って実行しておきましょう。

（命令が1つの場合）
$ nohup ./hogehoge.sh 1> ./success.txt 2> ./error.txt &

（命令が連結する場合）
$ nohup sh -c 'while true; do echo "hogehoge" >> ./fugafuga.txt; sleep 1; done' 1> ./success.txt 2> ./error.txt &

以上

なにをいっているのか不明なタイトルですが、UDPクライアントでは、受信をするためにbindまたはconnectのシステムコールが必要です、ということです。

TCPクライアントでは、主にconnectのシステムコールが使われることで、一時的に受信ポートが設定されます。そのため、受信が可能となっています。

UDPクライアントでは、bindまたはconnectのシステムコールが内部的に使われている実装と、そうでない実装があります。

javaのNIOのudpにおいては、bindまたはconnectのシステムコールが使われていません。そのため、受信をするためには、上記のシステムコールを使う必要があります。

私が以前作成した、

https://github.com/shigenobu/blueshelf

というUDPのNIOのwrapperでは、クライアントでもbindし、bindしたポート（ファイルディスクリプタ）を使って、送信を行い、そのまま受信してます。

クライアントでbindするメリットは、受信ポートを事前に決めることができる点です。これにより、1対多の通信の実装が行いやすくなります。

もちろん、connectでも1対多は可能ですが、受信ポートを外側から知る手段が必要となります。（外部のサーバを利用するなど）

もちろん、NAT環境下では、外部のサーバを利用しないとはいけませんが、ローカルネットワーク通信においては、受信ポートを予めわかっているというのはクラスタリングをするときに有用かなと考えています（UDPでクラスタリングって微妙ですが）

たぶんあっていると思うのですが、もし間違っていたらごめんさない。

以上

10.2からバイナリログを逆さにするflashbackという機能が追加されたのですが、中々実践でためす機会もなかったので、改めて動きを確認してみます。

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

（前提）

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

MariaDB [test]> show create table fb;
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| fb | CREATE TABLE fb (
no int(11) NOT NULL,
name text NOT NULL,
PRIMARY KEY (no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [test]> insert into fb value (1, 'あああ');
Query OK, 1 row affected (0.003 sec)

MariaDB [test]> select * from fb;
+----+-----------+
| no | name |
+----+-----------+
| 1 | あああ |
+----+-----------+
1 row in set (0.001 sec)

GTIDとバイナリログを確認します。

MariaDB [test]> show variables like 'gtid_binlog_pos';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| gtid_binlog_pos | 0-1-284795 |
+-------------------------+------------+
1 rows in set (0.001 sec)

MariaDB [test]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000066 | 54405979 |
| mysql-bin.000067 | 9650 |
| mysql-bin.000068 | 2667 |
+------------------+-----------+
3 rows in set (0.000 sec)

わかりやすくするために、一度バイナリログをflushします。

MariaDB [test]> flush binary logs;
Query OK, 0 rows affected (0.005 sec)

MariaDB [test]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000066 | 54405979 |
| mysql-bin.000067 | 9650 |
| mysql-bin.000068 | 2714 |
| mysql-bin.000069 | 385 |
+------------------+-----------+
4 rows in set (0.000 sec)

一応、GTID確認します。

MariaDB [test]> show variables like 'gtid_binlog_pos';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| gtid_binlog_pos | 0-1-284795 |
+-------------------------+------------+
1 rows in set (0.001 sec)

GTIDは「0-1-284795」のままですね。

（データ追加）

データを追加し、GTIDを確認します。

MariaDB [test]> insert into fb value (2, 'いいい');
Query OK, 1 row affected (0.003 sec)

MariaDB [test]> select * from fb;
+----+-----------+
| no | name |
+----+-----------+
| 1 | あああ |
| 2 | いいい |
+----+-----------+
2 rows in set (0.000 sec)

MariaDB [test]> show variables like 'gtid_binlog_pos';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| gtid_binlog_pos | 0-1-284796 |
+-------------------------+------------+
1 rows in set (0.001 sec)

GTIDが「0-1-284796」に変わりました。ここで、バイナリログを見てみます。長いので、一部だけにします。

# mysqlbinlog --no-defaults -vvv /var/lib/mysql/mysql-bin.000069
:
:
# at 256
#210224 11:08:36 server id 1  end_log_pos 299 CRC32 0xacff8998 	Gtid list [0-1-284795]
# at 299
#210224 11:08:36 server id 1  end_log_pos 342 CRC32 0x8bf7cf8a 	Binlog checkpoint mysql-bin.000068
# at 342
#210224 11:08:36 server id 1  end_log_pos 385 CRC32 0x187e7066 	Binlog checkpoint mysql-bin.000069
# at 385
#210224 11:09:30 server id 1  end_log_pos 427 CRC32 0x5386bf8e 	GTID 0-1-284796 trans
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=284796*//*!*/;
START TRANSACTION
/*!*/;
# at 427
# at 487
#210224 11:09:30 server id 1  end_log_pos 487 CRC32 0x685176b0 	Annotate_rows:
#Q> insert into fb value (2, 'いいい')
#210224 11:09:30 server id 1  end_log_pos 534 CRC32 0x02e81a93 	Table_map: `test`.`fb` mapped to number 64
# at 534
#210224 11:09:30 server id 1  end_log_pos 583 CRC32 0x9a15a3b4 	Write_rows: table id 64 flags: STMT_END_F

BINLOG '
WrU1YBMBAAAALwAAABYCAAAAAEAAAAAAAAEABHRlc3QAAmZiAAID/AECAJMa6AI=
WrU1YBcBAAAAMQAAAEcCAAAAAEAAAAAAAAEAAv/8AgAAAAkA44GE44GE44GEtKMVmg==
'/*!*/;
### INSERT INTO `test`.`fb`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='いいい' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */
# Number of rows: 1
# at 583
#210224 11:09:30 server id 1  end_log_pos 614 CRC32 0x2ae1c8a4 	Xid = 4087358
COMMIT/*!*/;
DELIMITER ;
:
:

まずは、「# at 256」を見てみます。

# at 256
#210224 11:08:36 server id 1  end_log_pos 299 CRC32 0xacff8998 	Gtid list [0-1-284795]

listされたGTIDが「0-1-284795」になっているので、ここが直前のGTIDであり、

insert into fb value (1, 'あああ');

の直後となっています。

念の為、確認します。

MariaDB [test]> select binlog_gtid_pos('mysql-bin.000069', 256);
+------------------------------------------+
| binlog_gtid_pos('mysql-bin.000069', 256) |
+------------------------------------------+
| 0-1-284795 |
+------------------------------------------+
1 row in set (0.001 sec)

次に、「# at 385」を見てみます。

# at 385
#210224 11:09:30 server id 1 end_log_pos 427 CRC32 0x5386bf8e GTID 0-1-284796 trans

385からが、次のGTIDの開始点となります。そこから直近のCOMMITまでを追っていくと、

# at 583
#210224 11:09:30 server id 1  end_log_pos 614 CRC32 0x2ae1c8a4 	Xid = 4087358
COMMIT/*!*/;

583に辿り着きます。583のGTIDを確認します。

MariaDB [test]> select binlog_gtid_pos('mysql-bin.000069', 583);
+------------------------------------------+
| binlog_gtid_pos('mysql-bin.000069', 583) |
+------------------------------------------+
| 0-1-284796 |
+------------------------------------------+
1 row in set (0.001 sec)

ここまでを整理すると、

GTID：「0-1-284795」（ポジション：「256」）は「(1, ‘あああ’)」の直後、

GTID：「0-1-284796」（ポジション：「583」）は「(2, ‘いいい’)」の直後、

となります。

※正確には、256を示すend_log_posのatが、1つ前のポジションです。

（操作取消）

では、GTID：「0-1-284796」の操作を取り消します。

# mysqlbinlog --no-defaults -vvv /var/lib/mysql/mysql-bin.000069 --start-position=256 --stop-position=583 --flashback 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
#210224 11:08:36 server id 1  end_log_pos 256 CRC32 0x9b36958f 	Start: binlog v 4, server v 10.5.5-MariaDB-log created 210224 11:08:36
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
JLU1YA8BAAAA/AAAAAABAAABAAQAMTAuNS41LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgGPlTab
'/*!*/;
#210224 11:08:36 server id 1  end_log_pos 299 CRC32 0xacff8998 	Gtid list [0-1-284795]
#210224 11:08:36 server id 1  end_log_pos 342 CRC32 0x8bf7cf8a 	Binlog checkpoint mysql-bin.000068
#210224 11:08:36 server id 1  end_log_pos 385 CRC32 0x187e7066 	Binlog checkpoint mysql-bin.000069
#210224 11:09:30 server id 1  end_log_pos 487 CRC32 0x685176b0 	Annotate_rows:
#Q> insert into fb value (2, 'いいい')
#210224 11:09:30 server id 1  end_log_pos 534 CRC32 0x02e81a93 	Table_map: `test`.`fb` mapped to number 64
# Number of rows: 1
#210224 11:09:30 server id 1  end_log_pos 583 CRC32 0x9a15a3b4 	Delete_rows: table id 64 flags: STMT_END_F

BINLOG '
WrU1YBMBAAAALwAAABYCAAAAAEAAAAAAAAEABHRlc3QAAmZiAAID/AECAJMa6AI=
WrU1YBkBAAAAMQAAAEcCAAAAAEAAAAAAAAEAAv/8AgAAAAkA44GE44GE44GEtKMVmg==
'/*!*/;
### DELETE FROM `test`.`fb`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='いいい' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */
COMMIT
/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

これをファイルに落として、実行することで、「(2, ‘いいい’)」のINSERTが取り消されます。ただし、バイナリログには、別途GTIDが切り出されます。

長くなりましたが、

start-positionの部分には、”戻したい場所”にしましょう。

今回では、

Gtid list [0-1-284795]

の箇所が直前のGTID払い出し位置となるので、ここに戻すようにしています。

MariaDBだと、GTIDで状態を確認することが多いので、flashbackでもGTIDの状態をみるように注意して対応してみました。

以上

MariaDB10.5がリリースされたのは、2020年6月でそれから大分たってしまいました。。

当ブログも10.5に移行完了済みなので、10.5での変更点などをのせておきます。

（公式）

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

個人的に気になったは、

• innodb_buffer_pool_instances
• innodb_log_files_in_group

がDeprecatedになったということでしょうか。

…なぜこのような変更になったのか、暫く調査していたのですが、ちょっとはっきりとした理由が不明な状態です。。

これは誰か教えてほしい。。

その他、レプリケーション系での権限が細分化されており、「slave」ではなく「replica」に変更になっていたり（※「slave」という表現は10.5では廃止されたわけではないのですが、将来的にはなくなるかと）、といったところでしょうか。

以上

またしてもcgoで作りました。

https://github.com/shigenobu/mysql_ws_split

前回作成した、ws_parse_url関数との違いは、

引数が2つあることですが、意外とこれがはまりました。

MySQLって、splitする関数がなくて（まあ、あったとしてもどうするんだという話）、

splitして、JSON配列に展開しちゃえば、そこから加工しやすくなるだろうという算段です。

以上

PHPのparse_url関数のような動作をするMySQLのUDFをcgoで作成しました。

https://github.com/shigenobu/mysql_ws_parse_url

（参考）PHPのparse_url関数

https://www.php.net/manual/ja/function.parse-url.php

この関数のポイントは、クエリー形式のパラメータをキー名でとれるところです。

最近はアクセスログをDBに保存して、そのまま分析に使うなどをすることもあり、MySQL側で一気に加工できれば楽だなと思いつくりました。

これ以外にも作成したものがいくつかあるので、そのうち公開しようと思います。

なお、動作確認したのはMariaDB10.4のみなので、もしお使いになる場合は自身の環境での動作確認をお願いします。

以上

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でもこれまで以上に頑張れそうですね。

以上