2017年2月20日月曜日

MariaDB 10.2.4の --flashback を触ってみる

ドキュメントはこちら。
Flashback - MariaDB Knowledge Base

"Common use case" をとっても雑に説明すると、
- `--flashback` をつけたmysqldが吐いたバイナリーログに対して
- `mysqlbinlog --flashback` でデコードすると、フラッシュバックっぽいことができる
という感じ。


まず、サーバー側の `--flashback` について。

https://github.com/MariaDB/server/blob/mariadb-10.2.4/sql/mysqld.cc#L9541-L9551

binlog_format= ROWにセットしてくれるだけぽい。 `--flashback` じゃなくても `--log-bin --binlog_format=ROW --binlog-row-format=FULL` で吐かせたバイナリーログでも大丈夫だった。ので、特に気にしなくて良さそう。


mysqlbinlog側の `--flashback` が肝。

https://github.com/MariaDB/server/blob/mariadb-10.2.4/client/mysqlbinlog.cc#L1455-L1468

↑で順番にイベントを読み込んでバッファに入れておいたものを(この時点でchange_to_flashback_eventを呼んでフラッシュバック用にクエリーを書き換えているぽい)
↓で後ろから順番に取り出す。

https://github.com/MariaDB/server/blob/mariadb-10.2.4/client/mysqlbinlog.cc#L3019-L3034

バイナリーログのイベントを逆転させてるのは↓のあたり。

https://github.com/MariaDB/server/blob/mariadb-10.2.4/sql/log_event.cc#L3417-L3460


というわけで、


MariaDB [(none)]> use d2;
MariaDB [d2]> create table t2 (num serial, val varchar(32));
MariaDB [d2]> INSERT INTO t2 VALUES (1, 'eins');
MariaDB [d2]> UPDATE t2 SET val = 'one' WHERE num = 1;

なんてことをやったbinlogを見ると、


C:\Users\yoku0825\Desktop\mariadb-10.2.4-winx64>bin\mysqlbinlog.exe -vv data\myhost-bin.000002
..
use `d2`/*!*/;
SET TIMESTAMP=1487580544/*!*/;
create table t2 (num serial, val varchar(32))
/*!*/;
# at 745
#170220 17:49:20 server id 1  end_log_pos 787 CRC32 0x3a401e2f  GTID 0-1-9 trans

/*!100001 SET @@session.gtid_seq_no=9*//*!*/;
BEGIN
/*!*/;
# at 787
# at 843
#170220 17:49:20 server id 1  end_log_pos 843 CRC32 0xee91dd13  Annotate_rows:
#Q> INSERT INTO t2 VALUES (1, 'eins')
#170220 17:49:20 server id 1  end_log_pos 889 CRC32 0xfac503f9  Table_map: `d2`.`t2` mapped to number 23
# at 889
#170220 17:49:20 server id 1  end_log_pos 936 CRC32 0x9cc43e21  Write_rows: table id 23 flags: STMT_END_F

BINLOG '
kK2qWBMBAAAALgAAAHkDAAAAABcAAAAAAAEAAmQyAAJ0MgACCA8CIAAC+QPF+g==
kK2qWBcBAAAALwAAAKgDAAAAABcAAAAAAAEAAv/8AQAAAAAAAAAEZWlucyE+xJw=
'/*!*/;
### INSERT INTO `d2`.`t2`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='eins' /* VARSTRING(32) meta=32 nullable=1 is_null=0 */
# at 936
#170220 17:49:20 server id 1  end_log_pos 967 CRC32 0x7e5f60b7  Xid = 9
COMMIT/*!*/;
# at 967
#170220 17:49:31 server id 1  end_log_pos 1009 CRC32 0x55d0a0bc         GTID 0-1-10 trans
/*!100001 SET @@session.gtid_seq_no=10*//*!*/;
BEGIN
/*!*/;
# at 1009
# at 1071
#170220 17:49:31 server id 1  end_log_pos 1071 CRC32 0xb32de27c         Annotate_rows:
#Q> UPDATE t2 SET val = 'one' WHERE num = 1
#170220 17:49:31 server id 1  end_log_pos 1117 CRC32 0x4ee231f4         Table_map: `d2`.`t2` mapped to number 23
# at 1117
#170220 17:49:31 server id 1  end_log_pos 1178 CRC32 0xe87e056f         Update_rows: table id 23 flags: STMT_END_F

BINLOG '
m62qWBMBAAAALgAAAF0EAAAAABcAAAAAAAEAAmQyAAJ0MgACCA8CIAAC9DHiTg==
m62qWBgBAAAAPQAAAJoEAAAAABcAAAAAAAEAAv///AEAAAAAAAAABGVpbnP8AQAAAAAAAAADb25l
bwV+6A==
'/*!*/;
### UPDATE `d2`.`t2`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='eins' /* VARSTRING(32) meta=32 nullable=1 is_null=0 */
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='one' /* VARSTRING(32) meta=32 nullable=1 is_null=0 */
# at 1178
#170220 17:49:31 server id 1  end_log_pos 1209 CRC32 0xdd417c80         Xid = 10

COMMIT/*!*/;
..

これが、 `--flashback` をつけるとこうなる。


C:\Users\yoku0825\Desktop\mariadb-10.2.4-winx64>bin\mysqlbinlog.exe --flashback -vv data\myhost-bin.000002
..

BEGIN/*!*/;
#170220 17:49:31 server id 1  end_log_pos 1178 CRC32 0xe87e056f         Update_rows: table id 23 flags: STMT_END_F

BINLOG '
m62qWBMBAAAALgAAAF0EAAAAABcAAAAAAAEAAmQyAAJ0MgACCA8CIAAC9DHiTg==
m62qWBgBAAAAPQAAAJoEAAAAABcAAAAAAAEAAv///AEAAAAAAAAAA29uZfwBAAAAAAAAAARlaW5z
bwV+6A==
'/*!*/;
### UPDATE `d2`.`t2`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='one' /* VARSTRING(32) meta=32 nullable=1 is_null=0 */
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='eins' /* VARSTRING(32) meta=32 nullable=1 is_null=0 */
COMMIT
/*!*/;
#170220 17:49:20 server id 1  end_log_pos 967 CRC32 0x7e5f60b7  Xid = 9
BEGIN/*!*/;
#170220 17:49:20 server id 1  end_log_pos 936 CRC32 0x9cc43e21  Delete_rows: table id 23 flags: STMT_END_F

BINLOG '
kK2qWBMBAAAALgAAAHkDAAAAABcAAAAAAAEAAmQyAAJ0MgACCA8CIAAC+QPF+g==
kK2qWBkBAAAALwAAAKgDAAAAABcAAAAAAAEAAv/8AQAAAAAAAAAEZWlucyE+xJw=
'/*!*/;
### DELETE FROM `d2`.`t2`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='eins' /* VARSTRING(32) meta=32 nullable=1 is_null=0 */
COMMIT
/*!*/;
..

`--flashback` の方は時間降順に並んでいるのがちょっと面白い。
これで、`--start-datetime` で時間を指定してやれば、それ以降のDMLをフラッシュバック(個人的にはリバートと呼びたい)するためのDMLが手に入って、それを適用すればフラッシュバック(個人的には略)完了。

RBRだからそれなりには時間がかかるはず。やっぱリバートで良いじゃん。。




めいじさんエスパー! :)



この辺( `--review` みたいなのがある)は後々なのかな(このマクロが有効化されてる箇所は見当たらなかった)

https://github.com/MariaDB/server/blob/mariadb-10.2.4/client/mysqlbinlog.cc#L1587-L1599

2017年2月13日月曜日

mysqlimportはトランザクションがきくのかどうか

TL;DR

- 1テキストファイル内でのトランザクションは利く
- 複数テキストファイル食わせた時のテキストファイル間のトランザクションは autocommit 依存 効かない
  - というか、 autocommit=0 だと mysqlimport さん使えないことが判明
  - ただし --use-threads を指定していない場合に限る(使ってる場合はそもそも別のトランザクションとしてパラレルで実行される)


今は英語化した MySQL CasualのSlack でそんな話題があったから調べてみた。

ざっと mysqlimportのソース を追ってみたけど、なんかどうもトランザクションをハンドルしている箇所はなさげ。ということはコマンドラインクライアントで LOAD DATA INFILE する時と同じになるのかな?

というわけでここからテスト。

まずは準備。t1.txtとt2.txtをそれぞれ datadir/d1 の下に作る。
中身は何でもいい。

$ cat t1.txt
1       one
2       two

$ cat t2.txt
1       one
2       two


main関数 を読む限り、 --use-threads の指定がない場合は左から順に引数を読んで LOAD DATA INFILE ステートメントに変換するので、t2の方をロックしてやればいいはず。

mysql57> SELECT @@session.autocommit, @@global.autocommit;
+----------------------+---------------------+
| @@session.autocommit | @@global.autocommit |
+----------------------+---------------------+
|                    1 |                   1 |
+----------------------+---------------------+
1 row in set (0.00 sec)

mysql57> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT * FROM t2 FOR UPDATE;
Empty set (0.00 sec)


これで t1にはロードできるけどt2にはロードできない 状態になったので、別のターミナルからmysqlimportを実行。

$ mysqlimport -S /usr/mysql/5.7.17/data/mysql.sock d1 t1.txt t2.txt
d1.t1: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0


ターミナルはここでハングする(t2の行ロック待ちで)
Ctrl + Cで終了して、さっきのターミナルに戻る。

mysql57> show processlist;
+----+------+-----------+------+---------+------+-----------+------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State     | Info                                                       |
+----+------+-----------+------+---------+------+-----------+------------------------------------------------------------+
| 77 | root | localhost | d1   | Query   |    0 | starting  | show processlist                                           |
| 86 | root | localhost | d1   | Query   |   11 | executing | LOAD DATA   INFILE 't2.txt' INTO TABLE `t2` IGNORE 0 LINES |
+----+------+-----------+------+---------+------+-----------+------------------------------------------------------------+
2 rows in set (0.00 sec)


ハマりどころその1。
mysqlimportのプロセスを終了しても、mysqldの中のスレッドは残ったままだったので、コイツをKILLする前にロックを解除すると

(゜∀。) あれなんでロードされてんの?

ってなる。

mysql57> KILL 86;
Query OK, 0 rows affected (0.00 sec)

mysql57> COMMIT; -- REPEATABLE-READをリフレッシュするためにコミット
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | two  |
+-----+------+
2 rows in set (0.00 sec)

mysql57> SELECT * FROM t2;
Empty set (0.00 sec)


予想通り、t1に対するLOAD DATA INFILE, オートコミット, t2に対するLOAD DATA INFILE, 行ロック待ちの間にKILL、でt1だけにデータがロードされる。
一度t1とt2をTRUNCATEして次。

mysql57> SELECT @@session.autocommit, @@global.autocommit;
+----------------------+---------------------+
| @@session.autocommit | @@global.autocommit |
+----------------------+---------------------+
|                    0 |                   0 |
+----------------------+---------------------+
1 row in set (0.00 sec)

mysql57> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT * FROM t2 FOR UPDATE;
Empty set (0.01 sec)


$ mysqlimport -S /usr/mysql/5.7.17/data/mysql.sock d1 t1.txt t2.txt
d1.t1: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0


mysql57> show processlist;
+----+------+-----------+------+---------+------+-----------+------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State     | Info                                                       |
+----+------+-----------+------+---------+------+-----------+------------------------------------------------------------+
| 87 | root | localhost | d1   | Query   |    0 | starting  | show processlist                                           |
| 88 | root | localhost | d1   | Query   |   16 | executing | LOAD DATA   INFILE 't2.txt' INTO TABLE `t2` IGNORE 0 LINES |
+----+------+-----------+------+---------+------+-----------+------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql57> kill 88;
Query OK, 0 rows affected (0.00 sec)

mysql57> commit;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT * FROM t1;
Empty set (0.00 sec)

mysql57> SELECT * FROM t2;
Empty set (0.00 sec)

autocommit= 0なので複数のLOAD DATA INFILEが全部1つのトランザクションとして扱われる。
なるほど。


…ここでなんか違和感を感じる。アレ?

ざっと mysqlimportのソース を追ってみたけど、なんかどうもトランザクションをハンドルしている箇所はなさげ。ということはコマンドラインクライアントで LOAD DATA INFILE する時と同じになるのかな?


ん? autocommit=0 でコマンドラインクライアントからLOAD DATA INFILE投げて、commitせずにquitしたらデータ残らなくね?


$ mysqlimport -S /usr/mysql/5.7.17/data/mysql.sock d1 t1.txt t2.txt
d1.t1: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
d1.t2: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

$ mysqlimport -S /usr/mysql/5.7.17/data/mysql.sock d1 t1.txt t2.txt
d1.t1: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
d1.t2: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

$ mysqlimport -S /usr/mysql/5.7.17/data/mysql.sock d1 t1.txt t2.txt
d1.t1: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
d1.t2: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

$ mysqlimport -S /usr/mysql/5.7.17/data/mysql.sock d1 t1.txt t2.txt
d1.t1: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
d1.t2: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

$ mysqlimport -S /usr/mysql/5.7.17/data/mysql.sock d1 t1.txt t2.txt
d1.t1: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
d1.t2: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

:(;゙゚'ω゚'): 残ってない…残ってたら

mysqlimport: Error: 1062, Duplicate entry '1' for key 'num', when using table: t1

って言われるから…autocommit=0だと本当に残ってない…

2017年2月2日木曜日

MySQL Casual Talks vol.10でトークしてきたMySQL的アンチパターン

昨夜の MySQL Casual Talks vol.10 は盛り上がりましたね!

総勢10名の登壇者ズがおよそ3時間にわたって繰り広げた **カジュアルな** トークの模様は↓にまとめました。

MySQL Casual Talks vol.10まとめ - Togetterまとめ


で、俺のヤーツは久々に(?) 愚痴っぽい「これをやるとMySQLは死ぬ」あるいは「これをやるとMySQLerが疲弊する」みたいなのをまとめたやつでした。

ちなみにこれ去年の新卒研修で使った資料を 90% くらいリライトしたやつで(リライト #とは)、もともとはもうちょっと本来のSQLアンチパターンっぽい項目が多かったはず。




ちょくちょく笑っていただけたようで何よりです。

MySQL Casual Talksに参加するような人自身はこんなことしないかも知れませんけど、油断していると足元から火が付いたりするので啓蒙活動にご活用ください。





ほんとだよ!!! _| ̄|○

2017年1月26日木曜日

MyNA(日本MySQLユーザ会)会 2017年1月でMySQLer 7つ道具の話をしてきた

2017/1/25の MyNA会 行ってきました。

赤井さん、やまさきさん、俺、かじやまさんでインプレスさんからMySQLの本が出た順にしゃべるヤーツ。





ネタ的にはこの時のリライトです(ただし結構リライトしてる…)

日々の覚書: 2年越しの #ChugokuDB in 中国地方


基本的にはくだんの本を書いた時より変わった運用とか、書かなかったポエミーなこととか、そんなものを紹介しています。

こちらもあわせてどうぞ!
MyNA(日本MySQLユーザ会)会 2017年1月ハッシュタグまとめ - Togetterまとめ

2017年1月19日木曜日

最近のPercona Serverで/usr/local/mysqlにシンボリックリンクを張ったら上手く起動しないでござる

バイナリー.tar.gz版のはなし。


こんな風に/usr/local/mysqlじゃないところにPercona Serverをダウンロードして、/usr/local/mysqlにシンボリックリンクを張る。

$ cd /usr/local
$ wget https://www.percona.com/downloads/Percona-Server-5.7/Percona-Server-5.7.16-10/binary/tarball/Percona-Server-5.7.16-10-Linux.x86_64.ssl101.tar.gz
$ tar xf Percona-Server-5.7.16-10-Linux.x86_64.ssl101.tar.gz
$ ln -s Percona-Server-5.7.16-10-Linux.x86_64.ssl101 mysql
$ ll
total 234348
drwxr-xr-x  2 root root        18 Jun  8  2016 bin
drwxr-xr-x  2 root root         6 Sep 23  2011 etc
drwxr-xr-x  2 root root         6 Sep 23  2011 games
drwxr-xr-x  2 root root         6 Sep 23  2011 include
drwxr-xr-x  2 root root         6 Sep 23  2011 lib
drwxr-xr-x  3 root root        18 Jun  8  2016 lib64
drwxr-xr-x  2 root root         6 Sep 23  2011 libexec
lrwxrwxrwx  1 root root        44 Jan 19 11:47 mysql -> Percona-Server-5.7.16-10-Linux.x86_64.ssl101
drwxr-xr-x 11 root root      4096 Jan 19 11:48 Percona-Server-5.7.16-10-Linux.x86_64.ssl101
-rw-r--r--  1 root root 239966730 Nov 28 05:07 Percona-Server-5.7.16-10-Linux.x86_64.ssl101.tar.gz
drwxr-xr-x  2 root root         6 Sep 23  2011 sbin
drwxr-xr-x  6 root root        58 Jun  8  2016 share
drwxr-xr-x  2 root root         6 Sep 23  2011 src


で、イニシャライズして起動しようとすると転ける。

$ useradd mysql
$ cd mysql
$ bin/mysqld --no-defaults --initialize --datadir=./data --user=mysql
$ bin/mysqld_safe --no-defaults --datadir=./data --user=mysql
 mysqld_safe Adding '/usr/local/Percona-Server-5.7.16-10-Linux.x86_64.ssl101/lib/mysql/libjemalloc.so.1' to LD_PRELOAD for mysqld
 mysqld_safe ld_preload libraries can only be loaded from system directories (/usr/lib64, /usr/lib, /usr/local/mysql/lib)


Percona Serverはもともと(特にインストールしてなければ) $MY_BASEDIR_VERSION/lib/libjemalloc.so.1 をロードして勝手にjemallocを使ってくれるようになっている けれど、CVE-2016-6662 対策で 変なところからLD_PRELOADできないようにmysqld_safeに修正 が入っている。

ちなみに本家5.7.16は オプションをパースする時にバリデーション してるだけだけど、Percona Serverは「デフォルトで勝手にjemallocをロードする」ために、 LD_PRELOADをセットする手前でもう一回バリデーション している。


 288 add_mysqld_ld_preload() {
 289   lib_to_add="$1"
 290   lib_to_add=$(readlink -f $lib_to_add)
 291   log_notice "Adding '$lib_to_add' to LD_PRELOAD for mysqld"
 292
 293   # Check if the library is in the reduced number of standard system directories
 294   case "$lib_to_add" in
 295     /usr/lib64/* | /usr/lib/* | ${MY_BASEDIR_VERSION}/lib/*)
 296       ;;
 297     *)
 298       log_error "ld_preload libraries can only be loaded from system directories (/usr/lib64, /usr/lib, ${MY_BASEDIR_VERSION}/lib)"
 299       exit 1
 300       ;;
 301   esac

290行目 のreadlinkが肝で、自動でロードしようとした /usr/local/mysql/lib/libjemalloc.so.1 をシンボリックリンク展開して /usr/local/Percona-Server-5.7.16-10-Linux.x86_64.ssl101/lib/mysql/libjemalloc.so.1 にしてしまい、結果 $MYSQL_BASEDIR_VERSION/lib ディレクトリーの外側だから不正、NG! となってる。

取り敢えずこの行をコメントアウトすれば動かせるようにはなる。
ただしその場合リアルにlibjemalloc.so.1をシンボリックリンクで変な共有ライブラリーに置き換えられると死ぬ。

…けど、そんなところ置き換えられるような権限取られたらもっとひどいことされてるだろうから大丈夫だとは思う。野良ビルドでなければ。


5.7.16でしか確認していないけど、CVE-2016-6662対応以降 のマイナーバージョンであれば5.5, 5.6も同じことが起こるかも知れない。

2017年1月6日金曜日

AWS上でMySQL5.7動作環境を最速で作る方法(MySQL::Sandbox使用)

@sakaik さんの連載(?)を読んでいたらやたら楽しそうで俺もなんかビルドしたくなってたまらなくなったので。

MySQLをビルドする on AWS Red Hat 7.3 - sakaikの日々雑感~(T)編
ふたつのバージョンのMySQLを同時に動かす on AWS Red Hat 7.3 - sakaikの日々雑感~(T)編
MySQL 5.7 のビルドにトライ ~ 5.6とは大違い - sakaikの日々雑感~(T)編
MySQL 5.7 を t2.micro でもビルドできた! - sakaikの日々雑感~(T)編
AWS上でMySQL5.7動作環境を最速で作る方法(Generic binaries使用) - sakaikの日々雑感~(T)編

タイトルからわかるように、最後のエントリーに対抗してみました。

先に書いておくと、MySQL::Sandboxのインストールまで終わっていれば 15秒くらい でMySQL 5.7.17の起動までたどり着けます(あとで書きますがこれはMySQL::Sandboxの **中の人** が上手くやってる)


TL;DR

$ sudo su -i
# yum install -y perl-ExtUtils-MakeMaker perl-Data-Dumper
# curl -L cpanmin.us | perl - App::cpanminus
# /usr/local/bin/cpanm MySQL::Sandbox
# useradd mysql
# su - mysql
$ make_sandbox_from_url 5.7.17
$ msb 5717


みんなだいすき MySQL::Sandbox ですが、make_sandbox と make_replication_sandbox だけで満足していませんか?

MySQL::Sandbox 3.1.10から(2017年1月6日現在、最新版は3.2.05)は make_sandbox_from_url というコマンドを備えています。
引数なしで叩くとusage吐いてくれるので取り敢えず叩く。


$ make_sandbox_from_url
    The MySQL Sandbox,  version 3.2.05
    (C) 2006-2016 Giuseppe Maxia
### version needed

This script tries to create a sandbox using binaries downloaded from the web.
The success of this scripts depends on good connectivity
and availability of downloading tools.
##########################
# IT ONLY WORKS ON LINUX
##########################
The repository of available binaries is at https://github.com/datacharmer/mysql-docker-minimal/blob/master/dbdata .
Supported:
# 5.0 -> [ 5.0.96 ]
# 5.1 -> [ 5.1.72 ]
# 5.5 -> [ 5.5.51 5.5.52 ]
# 5.6 -> [ 5.6.32 5.6.33 ]
# 5.7 -> [ 5.7.14 5.7.15 ]
# 8.0 -> [ 8.0.0 ]
The binaries are imported into /usr/mysql .

  make_sandbox_from_url X.X.XX [options]

    where X.X.XX is the version number.
    You can then pass any options accepted by make_sandbox.

make_sandbox_from_urlという名前ながら、引数はURLではなくてバージョン番号(make_sandboxと同じ)
これもともとは本当にURLだったけど、途中で変わったんじゃないかって気がする。確証はないし調べてもいない。


$ make_sandbox_from_url 5.7.17
2017-01-06 07:56:30 URL:https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata/available.txt [98/98] -> "/tmp/available.txt" [1]
wget -nv -O 5.7.17.tar.gz 'https://github.com/datacharmer/mysql-docker-minimal/blob/master/dbdata/5.7.17.tar.gz?raw=true'
2017-01-06 07:56:33 URL:https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata/5.7.17.tar.gz [40959490/40959490] -> "5.7.17.tar.gz" [1]
Executing low_level_make_sandbox --basedir=/home/mysql/opt/mysql/5.7.17 \
        --sandbox_directory=msb_5_7_17 \
        --install_version=5.7 \
        --sandbox_port=5717 \
        --no_ver_after_name \
        --my_clause=log-error=msandbox.err
..

do you agree? ([Y],n) Y
# Starting server
.. sandbox server started
# Loading grants
Your sandbox server was installed in $HOME/sandboxes/msb_5_7_17

$ msb 5717
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > 

…しかし…数百MBあるバイナリーを落としてるにしては…? と思ったら、公式バイナリーではなく、MySQL::Sandbox作者のGitHubから取ってきてる。
軽量版MySQLバイナリー.tar.gzは このスクリプト で作っている様子。

というわけでこれを使えば、5.0でも5.1でも5.5でも5.6でも5.7でも8.0でも好きなリリースシリーズが入れられますよ! ;)

ちなみにダウンロードされたバイナリー.tar.gzと展開後のファイルは$HOME/opt/mysql に置かれる。40MB………どんだけいろんなもん積んでるんだよ公式バイナリー。。


$ ll opt/mysql/
total 79712
drwxrwxr-x 7 mysql mysql     4096 Dec 14 18:46 5.7.17
-rw-rw-r-- 1 mysql mysql 40959490 Jan  6 07:56 5.7.17.tar.gz
drwxrwxr-x 7 mysql mysql     4096 Sep 12 15:22 8.0.0
-rw-rw-r-- 1 mysql mysql 40654528 Jan  6 07:57 8.0.0.tar.gz

2016年12月23日金曜日

MySQLのCOUNTを速くする(?)SQL1本ノック その2

この記事は MySQL Casual Advent Calendar 2016 の23日目の記事です。
そしてどうやら、 日々の覚書 の400本目の公開記事です。そんなに書いてたのか。。

前回 のあらすじ。


mysql57> SHOW CREATE TABLE game_score\G
*************************** 1. row ***************************
       Table: game_score
Create Table: CREATE TABLE `game_score` (
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `play_end_time` datetime NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`seq`),
  KEY `game_id` (`game_id`),
  KEY `play_end_time` (`play_end_time`)
) ENGINE=InnoDB AUTO_INCREMENT=300001 DEFAULT CHARSET=utf8mb4

mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/19' AND '2016/12/20' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     4610 |
|       1 |     4751 |
|       2 |     4532 |
|       3 |     4470 |
|       4 |     4581 |
|       5 |     4534 |
|       6 |     4523 |
|       7 |     4472 |
|       8 |     4583 |
|       9 |     4633 |
+---------+----------+
10 rows in set (0.37 sec)

COUNTが遅い! のでサマリーテーブルを作ってトリガーを仕掛けた! これで勝つる!
(前回とはPRIMARY KEYの順番を変えてあります。1個キーが追加されたのは、FKが勝手に作ったからです)


mysql57> SHOW CREATE TABLE game_score_summary\G
*************************** 1. row ***************************
       Table: game_score_summary
Create Table: CREATE TABLE `game_score_summary` (
  `game_id` int(11) NOT NULL,
  `play_date` date NOT NULL,
  `count_star` int(10) unsigned NOT NULL,
  PRIMARY KEY (`play_date`,`game_id`),
  KEY `game_score_summary_ibfk_1` (`game_id`),
  CONSTRAINT `game_score_summary_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `game_score` (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary (game_id, play_date, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-19 11:53:52.69
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/19';
+---------+------------+------------+
| game_id | play_date  | count_star |
+---------+------------+------------+
|       0 | 2016-12-19 |       4610 |
|       1 | 2016-12-19 |       4751 |
|       2 | 2016-12-19 |       4532 |
|       3 | 2016-12-19 |       4470 |
|       4 | 2016-12-19 |       4581 |
|       5 | 2016-12-19 |       4534 |
|       6 | 2016-12-19 |       4522 |
|       7 | 2016-12-19 |       4472 |
|       8 | 2016-12-19 |       4583 |
|       9 | 2016-12-19 |       4633 |
+---------+------------+------------+
10 rows in set (0.00 sec)

だが、この設計ではボトルネックを生むのであった_| ̄|○
ちなみにこの段階でボトルネックにならない場合、ボトルネックになるまではここから先に書いてあるようなことは考えなくて良いと思う。

さて、このテーブルのPRIMARY KEYは(play_date, game_id) で、play_date は game_score.play_end_time から派生してくる。game_score.play_end_timeはおそらく NOW() か、それに類するものから派生する。

ということはつまり、このサマリーテーブルはほぼ game_id 単位でしかロックを持たないことになる。
1分に数回しかプレイされないような環境ならまだしも、秒間数回(これはテキトー。 実際にはもっと叩かれていたし フィクションです)となってくるとこれでは詰まる。

パフォーマンスは失われないように、ロックの粒度は大きくなりすぎないように、単位を分割するのがここでのたしなみ。
もちろん、ロックを放棄して走り去るなどといった、はしたないDBAなど存在していようはずもない。

という訳でいくつかの分割案。


1. mysqldでのスレッド単位

コネクションプール環境ならこれが一番確実だと思う。MySQLには mysqld上のprocesslist_idを参照できる CONNECTION_ID関数と pseudo_thread_idセッション変数があるので、これをトリガーに埋め込むことができる。


mysql57> SHOW CREATE TABLE game_score_summary_by_thread\G
*************************** 1. row ***************************
       Table: game_score_summary_by_thread
Create Table: CREATE TABLE `game_score_summary_by_thread` (
  `game_id` int(11) NOT NULL,
  `play_date` date NOT NULL,
  `processlist_id` bigint(20) unsigned NOT NULL,
  `count_star` int(10) unsigned NOT NULL,
  PRIMARY KEY (`play_date`,`game_id`,`processlist_id`),
  KEY `game_id` (`game_id`),
  CONSTRAINT `game_score_summary_by_thread_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `game_score` (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_by_thread_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary_by_thread (game_id, play_date, processlist_id, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), @@pseudo_thread_id, 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-22 12:11:37.18
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

mysql57> SELECT game_id, SUM(count_star) AS count_star FROM game_score_summary_by_thread WHERE play_date = '2016/12/22' GROUP BY game_id;
+---------+------------+
| game_id | count_star |
+---------+------------+
|       0 |       4275 |
|       1 |       4279 |
|       2 |       4240 |
|       3 |       4084 |
|       4 |       4304 |
|       5 |       4231 |
|       6 |       4406 |
|       7 |       4254 |
|       8 |       4166 |
|       9 |       4253 |
+---------+------------+
10 rows in set (0.00 sec)

これで、ロックの粒度をスレッド単位で分割できる。当然ながら1スレッド単位で見れば完全に直列するので、ロックの競合は発生しない。フェッチする行数は game_id * コネクションプールから張られる総数で済むので、GROUP BYでSUMしても大した負荷ではない。

コネクションプール以外の環境(つまり、トランザクションの都度接続して切断するスタイル)の場合、pseudo_thread_idはどんどんインクリメントされるので、つまりgame_scoreからカウントしてるのと変わらなくなってしまってダメ。COUNTじゃなくてSUMな分悪くなると思う。

じゃあ…


2. 接続元ホスト単位

個人的にはイマイチ好きじゃないけど一つの案。
pseudo_thread_idとは違って、同じホストから複数のクエリーが同時に㌧で来るのは十分あり得るので、「スケールアウトよりもスケールアップ」で戦っているケースだとあんまりロックが分割されない。

それでも分割しないよりはマシであろうとは思うけれど、MySQLの中からクライアントの接続元を一発で引ける変数や関数はないので、クライアント側で「トリガーの中で使っているセッション変数に接続元をセットしてやる」か、「トリガーを諦めて同じトランザクションの中で一緒にサマリーの方も更新するか」のどちらかになる。

1ホスト1アカウントになるようにアカウントをIP決め打ちで設定していれば CURRENT_USER関数が使えそう。文字列型になるのでハッシュ化したとしてもちょっと容量かさむけれども。

サマリーテーブルやトリガーやSUMは省略。


3. 時間を使って分割

MySQL 5.6とそれ以降ではNOW関数がマイクロ秒まで出してくれるので、マイクロ秒単位の下2桁で分解する。具体的には RIGHT(NOW(6), 2) で分解してみた。

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_by_time_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary_by_time (game_id, play_date, fragment, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), RIGHT(NOW(6), 2), 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-22 19:08:12.68
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)


これが驚くくらい詰まらずにちゃんと分割された。こんなにうまくいくとは思ってなかった。びっくり。


というわけで、

- Connection Poolなら pseudo_thread_id
- それ以外で
  - MySQL 5.6とそれ以降なら RIGHT(NOW(6), 2)
  - MySQL 5.5とそれ以前なら CURRENT_USER() か アプリから文字列渡し

って感じで俺の中では落ち着きました。

明日はクリスマスイブ、カミポゥ さんです!