バイナリログ転送ツール bingo

前回のエントリから相当間が空いてしまいました。
ネタがあればちゃんと書いていきたい所です。

で、タイトルの通り bingo というアプリケーションをリリースしました。

何をするアプリケーションかと言いますと、
MySQLのバイナリログ(更新差分)を簡単にリアルタイム転送するツール、です。

MySQL to MySQLであればMySQLのレプリケーションでいいじゃん、となるのですが
例えばBigQuery等にリアルタイムでデータ転送しようと思うと結構面倒くさいと思います。
(トリガーを使ったり、短時間毎に差分を取得したり。。)

bingo はMySQLのレプリケーション技術で更新データを読み取りますので、
非同期ではありますが、ほぼリアルタイムに更新データを取得できます。
現状はfluentdにhttp postする事しか出来ませんが、色々と応用できるとは思います。

使い方(詳しくはREADMEへ)

bingoは起動するとすぐにMySQLに接続します。

$ bingo
2016/09/02 01:19:10 connected to mysql(root@127.0.0.1:3306)
2016/09/02 01:19:10 start reading binlog
2016/09/02 01:19:10     Binlog Version:  4
2016/09/02 01:19:10     Server Version:  5.7.14-log

MySQLに何かしらデータを書き込みます。

$ mysql -u root -e 'create database testdb default character set utf8mb4'
$ mysql -u root -e 'create table testdb.testtable (id bigint, name varchar(32))'
$ mysql -u root -e 'insert into testdb.testtable (id, name) values (1, "hello world")'
$ mysql -u root -e 'insert into testdb.testtable (id, name) values (2, "はろーわーるど")'

localhost:8888 で待ち受けるfluentdにはこんな感じでデータがpostされます。

2016-09-02 01:23:28 -0400 bingo.data: {"database":"testdb","table":"testtable","columns":["1","hello world"]}
2016-09-02 01:23:36 -0400 bingo.data: {"database":"testdb","table":"testtable","columns":["2","はろーわーるど"]}

余力があればカラム名つきでpostするような実装を入れたいところですが、ひとまずはここまで。
テストも途中からほとんど書けていないのでこれもIssueですね。。

MySQL 5.7 お試し編

MySQL 5.7が正式にGAとしてリリースされたようです。
というわけでどこが変わったのか、触って見つつ調査してみようと思います。
ちなみにインストール先は AWS の r3.xlarge (Amazon Linux) です。

インストール/セットアップ

Redhat6向けのyumリポジトリをインストールし、mysql-community-serverをインストール。

sudo rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
sudo yum install -y mysql-community-server
sudo service mysqld start

# 原因が把握できていないのですが、mysqlのディレクトリに初期DBが構築されていない事があるようです。
# その場合は、以前のバージョンであれば mysql_install_db を実行して初期DBを構築しますが、
# mysql5.7からは mysqld --initialize に変更されたようです。
mysqld --initialize --datadir=/var/lib/mysql

rootでログインする際の初期パスワードは、下記の様にmysqlのエラーログに出力されるようです。
ノーヒントだったので困った。5.6は .mysql_secret というファイルに保存されていたのに・・

2015-10-22T10:32:43.950929Z 1 [Note] A temporary password is generated for root@localhost: xxxxxxxxxxx

ログインできることを確認したらインストール完了です。
適当にSQLを実行した際、このようなエラーが表示された場合は、 validate_password プラグインが有効になっています。
プラグインを無効化するか、適切なパスワードをrootユーザに設定してください。
(root以外のユーザも、強度の高いパスワードの設定を求められて面倒な為、今回はプラグインを無効化しています。本番環境では有効にしておきましょう)

> alter user root@localhost identified by "xxxxxxxxx";

or

> uninstall plugin validate_password;

とりあえずベンチマーク

http://mysqlserverteam.com/whats-new-in-mysql-5-7-generally-available/

5.6から3倍速くなったぜ!と豪語しているので、
別環境に mysql 5.6 をインストールし、sysbench でベンチマークを取ってみました。

sudo yum install -y sysbench --enablerepo=epel
echo "CREATE DATABASE sbtest; GRANT ALL PRIVILEGES ON sbtest.* TO sbtest@localhost IDENTIFIED BY 'sbtest';" | mysql -u root -p
sysbench --test=oltp --db-driver=mysql --mysql-password=sbtest prepare
sysbench --test=oltp --db-driver=mysql --mysql-password=sbtest run

ほどほどにメモリは割り当てています。

innodb_buffer_pool_size = 20G
innodb_log_file_size = 512M
innodb_flush_method = O_DIRECT
max_connections = 100
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

mysql 5.6の結果

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (234.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (4446.01 per sec.)
    other operations:                    20000  (468.00 per sec.)

Test execution summary:
    total time:                          42.7349s
    total number of events:              10000
    total time taken by event execution: 42.6917
    per-request statistics:
         min:                                  2.67ms
         avg:                                  4.27ms
         max:                                 17.84ms
         approx.  95 percentile:               5.02ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   42.6917/0.00

mysql 5.7

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (203.06 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (3858.12 per sec.)
    other operations:                    20000  (406.12 per sec.)

Test execution summary:
    total time:                          49.2467s
    total number of events:              10000
    total time taken by event execution: 49.2031
    per-request statistics:
         min:                                  2.97ms
         avg:                                  4.92ms
         max:                                 24.68ms
         approx.  95 percentile:               5.40ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   49.2031/0.00

んー、どちらかというと5.7の方がスループットが出ていないような・・
この程度の負荷だと誤差の範囲なのかもしれません。
あとはパラメータチューニングをもっと細かくやるべきなのかも。

JSONテーブルってどんな感じ?

個人的には結構大きいJSONの対応。
PostgreSQLでは既に似たような機能が実装されていたのですが、
RDBMSが複数あるのは微妙ですよね。というわけで採用したことはありませんでした。
そんな中、ついにMySQLに実装されたので、色々と試してみたいと思います。

mysql> create table json_test (id varchar(64) primary key, val json default null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into json_test values
    ->   ('key1', '{"id":1, "value":"hogehoge"}'),
    ->   ('key2', '{"id":2, "value":"fugafuga", "option":"optionvalue"}'),
    ->   ('key3', '{"id":3, "value":"foobar", "option":null}');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
[/bash]

これでテーブルとテストデータができあがりましたね。
色々とクエリを発行してみます。
一致検索、LIKE検索と、簡単な構文には対応している様子。


mysql> select * from json_test;
+------+---------------------------------------------------------+
| id   | val                                                     |
+------+---------------------------------------------------------+
| key1 | {"id": 1, "value": "hogehoge"}                          |
| key2 | {"id": 2, "value": "fugafuga", "option": "optionvalue"} |
| key3 | {"id": 3, "value": "foobar"}                            |
+------+---------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from json_test where json_extract(val, '$.id') = 1;
+------+--------------------------------+
| id   | val                            |
+------+--------------------------------+
| key1 | {"id": 1, "value": "hogehoge"} |
+------+--------------------------------+
1 row in set (0.00 sec)

mysql> select * from json_test where json_extract(val, '$.value') like '%hoge%';
+------+--------------------------------+
| id   | val                            |
+------+--------------------------------+
| key1 | {"id": 1, "value": "hogehoge"} |
+------+--------------------------------+
1 row in set (0.00 sec)

mysql> select * from json_test where json_extract(val, '$.option') is null;
+------+--------------------------------+
| id   | val                            |
+------+--------------------------------+
| key1 | {"id": 1, "value": "hogehoge"} |
+------+--------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract(val, '$.option') from json_test;
+-------------------------------+
| json_extract(val, '$.option') |
+-------------------------------+
| NULL                          |
| "optionvalue"                 |
| null                          |
+-------------------------------+
1 row in set (0.00 sec)

という感じで、なかなかそれっぽく動いているようです。
ただ、json内のフィールドとしてはnullとして保存されていても、SQLのnullとは評価されないようですね。。
ドキュメントを読んでもずばりな回答が得られなかったのですが、とりあえずjsonのnullにキャストするとnull値の検索が可能でした。

mysql> select * from json_test where val->"$.option" = cast('null' as json);
+------+----------------------------------------------+
| id   | val                                          |
+------+----------------------------------------------+
| key3 | {"id": 3, "value": "foobar", "option": null} |
+------+----------------------------------------------+
1 row in set (0.00 sec)

そしてjson用の独自構文。

mysql> select * from json_test where val->"$.id" = 2;
+------+---------------------------------------------------------+
| id   | val                                                     |
+------+---------------------------------------------------------+
| key2 | {"id": 2, "value": "fugafuga", "option": "optionvalue"} |
+------+---------------------------------------------------------+
1 row in set (0.00 sec)

sqlでアロー演算子は斬新すぎでは?
なんとか楽にフィールドにアクセスできないか、という事で生まれたのでしょうか・・・
まあ助かりますけど・・・

InnoDB周りの改善だったりとか、他にも色々とあるのですが、今日のエントリではこんなところで。

MySQL設定メモ2

MySQL設定メモ に続いて、今回はレプリケーションの設定項目について書きます。

log-bin

まず設定する項目であるバイナリログ有効化。
物理ディスクを分ける事ができる場合は、別のディスクに出力されるようパスを設定しておくのが良いです。

server_id

サーバーを区別するIDの設定。
これも最初に設定する項目ですね。
どのような値を設定するかいつも悩んでしまいます。
ロール毎に1000の位を連番で割り当てる等、構成管理のルールに合わせて決めましょう。

binlog_format

バイナリログのフォーマット設定。
デフォルトはMIXEDになっていますが、私はROWにします。
ROWの場合、バイナリログのサイズは増えますが、不整合が起きにくくなるのと、
更新された列のデータが完全な状態で残るので、何か問題が起きた際に、バイナリログを調査しやすくなる、という事があります。

expire_logs_days

自動削除する期限の設定(日数)。
大体7日〜14日を設定しています。
ディスクフルにならない程度に、少し長めにしておいた方が若干安心です。

slave_compressed_protocol

バイナリログの出力量が非常に多い場合、この設定を有効化すると、
master – slave間の通信が圧縮され、バイナリログ転送の高速化が期待できます。
master/slave両方で設定する必要があります。

slave_parallel_workers

slave側のバイナリログ適用を並列化する設定。
いわゆるSQLスレッドを複数に稼働させます。
一つ制限があり、1つのSQLスレッドは1つのDBしか処理できません。
DBが2つしか存在しないのに、slave_parallel_workers=4 と設定しても、2スレッドしか稼働しないということです。
この設定は一度導入したことがあるのですが、動作が不安定で定期的にInnoDBがクラッシュするようになったため、長期運用した経験はありません。

master_info_repository, relay_log_info_repository

master.infoやrelaylog.infoの管理をどこでするか、という設定。
master_info_repository=TABLE とすると、InnoDBで管理されるようになります。
ファイルベースでは無く、InnoDB化することで何がうれしいかというと、クラッシュセーフになるということ。
また副次的な作用として、master.infoの書き込みが高速化されます。
レプリケーションの遅延が気になる方は、一度変更してみても良いかもしれません。

このあたりでしょうか。後はshow slave statusを適宜監視するようにしておく等、
レプリケーションがクラッシュしたことを検知できるようにしておくことも必要ですね。

MySQL設定メモ

インストール後、いつも変更するMySQLの設定をメモ。
特にinnodb_file_per_tableと文字コード周りは運用後に変更しにくいので、最初に設定しておくこと!

参考

sql_mode

STRICT_TRANS_TABLESを追加して、
列制約に違反する形でデータを書き込むとエラーが発生するように指示します。
サイズオーバーだったり、Not Null列にNullを挿入しようとした場合にエラーハンドリングできるようになります。

innodb_file_per_table

InnoDBの実データが格納される.ibdファイルをテーブル毎に作成します。
これを設定しておかないと、ibdataXに全テーブルのデータが詰め込まれてしまいます。
テーブルを削除してもibdataXは縮小されることは無く、空き領域として再利用される事となります。
ディスクの空き容量が少なくなった場合に困るため、innodb_file_per_tableは必ず有効にしています。

innodb_buffer_pool_size

InnoDBの割り当てメモリ。基本的な設定ですね。
けちらずどかんと割り当てましょう。

innodb_log_file_size

REDOログのファイルサイズ。
InnoDBのクラッシュ時のリカバリにも使われます。
大きなトランザクションのパフォーマンスが向上するので、
バックアップのリストアにかかる時間も改善します。
場合によりけりですが、最初は128MB〜256MBに設定しています。
なお、この値を変更する際、MySQLの停止後、ib_logfileXを削除してからMySQLを起動する必要があります。

innodb_file_format

Barracudaにしておくことでメモリ効率が良くなる・・らしい。
5.6以降はデフォルトのmy.cnfに記述されているかも。
※5.6以降デフォルトかもというのは勘違いでした

character_set_client, character_set_connection, character_set_database

文字コードの設定。
ちょっと前まではutf8だったんですが、最近はサロゲートペアを含むutf8mb4にしておいた方が無難です。

collation_database, collation_connection, collation-server

照合順序の設定。
utf8mb4の場合は色々あるのですが、個人的にはutf8mb4_binに設定したい。
デフォルトはutf8mb4_general_ciになるのですが、こいつはアルファベットの大文字と小文字を区別しません。
たとえばユニーク制約を設定した際、”aaa”と”AAA”は同じ値となり、同時に挿入する事はできません。
また、”aaa”という値を検索した際、”AAA”が検索にヒットします。
便利ではあるのですが、ユーザIDの検索や、重複チェックの際に誤爆してしまうリスクが怖いので、経験上collateは完全一致になるようにしたいです。

slow_query_log, slow_query_log_file, long_query_time

スロークエリの設定。
long_query_timeは1ぐらいにしています。

max_connections

最大コネクション数。これも基本的な設定です。
コネクションあたりのメモリを計算して、
innodb_buffer_pool_sizeと足した値が物理メモリサイズを越えないよう気をつけつつ、適切な値にしておきましょう。

tmp_table_size, max_heap_table_size

一時テーブルのサイズとheapテーブルの最大サイズを指定します。
一時テーブルの場合は、この2つの設定両方の最小値を見るため、同じ値に設定しておきましょう。
物理メモリと相談ですが、なるべく大きい値にしたいところ。

細かいチューニングをしていくとまだ色々とあるのですが、
簡易的な初期設定はこんなところでしょうか。
また、レプリケーションの設定をする場合はさらに色々とチューニングが可能です。