2014年5月29日木曜日

InfiniDB 4.5をざっくりインストール…する前に色々困ったこと

今ふっと2年くらい前にInfobright調べてたことを思い出しましたが気にしない。

Infobrightが今どうなったのかは知りませんが、InfiniDBは去年くらい(4.0)から「商用版とオープンソース版のコードベースが統合され、機能制限がなくなった(同じ機能が使える)」と前々から聞いていたので、GPL版だと変にコア数の制限を受けたりするInfobright使うぐらいならInfiniDBかなと思って。

昔は www.calpont.com (Calpontって会社がInfiniDBを作ってた) だったものの、今はInfiniDB社になったっぽく、URLは http://infinidb.co/ にリダイレクトされる。

ダウンロードページにいってプラットフォームを選ぶと、「Access denied. You must log in to view this page.」と言われて、アカウントを登録しろと言われる。ちなみにここまで一切合切ライセンスの話(GPLv2だよ、という話)は無し。MySQLのように「No thanks」のリンクは無く、メールアドレスを差し出さない限りダウンロードはできない(2014/05/26現在) メールアドレスを差し出すと何が起こるのかも何の説明も無い。

2013/12/13時点の情報だけど、メールアドレス登録したら
<日本のお客様へ>
Calpont Corporation(以下:Calpont)は、日本におけるInfiniDBの販売及びサポートに関して株式会社アシスト(以下:アシスト社)と提携しており、
サイト上から登録頂いた情報はアシスト社と共有しています。
そのため、アシスト社から直接コンタクトをする場合がありますので、あらかじめご了承下さい。
とかいうメールが来た(今もそうなのかどうかはわからない) 今見返して気付くものの、当時はまだCalpont社だったのか。

これはなんだか非常に嫌だなぁ(´・ω・`) < せめて同意とってー


で、登録後にダウンロードできるのはWindows, Linux(バイナリーの.tar.gz), .rpm, .deb の4種類。

ソースコードマダァ-? (・∀・ )っ/凵⌒☆チンチン

バイナリーの.tar.gzの中にもライセンス書いてないし、ソースコード取得先も書いてないからちょっと不安になったものの、ソースコードはGithubから取ってくるらしい。

InfiniDB本体のリポジトリーMySQLインターフェイスのリポジトリー があって、なんかmakeしようと色々がんばってみたりしたけどダメだったので、既にメールアドレスも登録しちゃってたので諦めてバイナリーをDLしてきた。


という訳で、今のところメールアドレスを登録しないとさっくり試すにはつらそうな気配がするものの、いっこだけ簡単にやる方法として、InfiniDBが提供しているEC2のAMIを使ってインスタンスを立ち上げると、インストールが終わった状態(かつ、/rootに.rpmが置いてあるままの状態)で上がってくるので、このrpmファイルをscpで持ってきて、好きなところにインストールする。

rpmファイルのライセンスがGPLv2でなくてちょっとあせったけれど、ただの間違いらしいのでそのうち直るだろうし、↑の方法ももちろん問題ない。



InfiniDBのデータノード的なプロセスのベースディレクトリが/usr/local/Calpont 、mysqldも /usr/local/Calpont/mysql にある想定で色々設定してあって、後者はまだmy.cnf書き換える気にならなくもないけど、前者は色々面倒そうなので、パッケージファイルで突っ込んじゃうか、.tar.gzバイナリーを配置するにしても/usr/local/Calpont 決め打ちでいった方が面倒はなさげ。

ざっくり使ってみた感想はまたいずれ。

2014年5月21日水曜日

Tritonnのsenna_log_levelの取りうる値

senna_log_levelでぐぐったけど、まさかの何も見つからなかったので。


const char *senna_log_level_type_names[] = { "NONE", "EMERG", "ALERT",
                                             "CRIT", "ERROR", "WARNING",
                                             "NOTICE", "INFO", "DEBUG",
                                             "DUMP", NullS };
TYPELIB senna_log_level_typelib=
{
  array_elements(senna_log_level_type_names)-1, "",
  senna_log_level_type_names, NULL
};

tritonn-1.0.12-mysql-5.0.87/sql/set_var.cc


それだけ。

2014年5月20日火曜日

MySQLのバイナリーログ、999999の次は?

1000000です。
↓は"log-bin= bin"を設定した状態。

# ll
total 537160
-rw-rw---- 1 mysql mysql        56 May 20 14:32 auto.cnf
-rw-rw---- 1 mysql mysql       139 May 20 15:42 bin.000001
-rw-rw---- 1 mysql mysql        13 May 20 15:42 bin.index
-rw-r----- 1 mysql root      11916 May 20 15:42 error.log
-rw-rw---- 1 mysql mysql       884 May 20 15:42 ib_buffer_pool
-rw-rw---- 1 mysql mysql 268435456 May 20 15:42 ib_logfile0
-rw-rw---- 1 mysql mysql 268435456 May 20 15:42 ib_logfile1
-rw-rw---- 1 mysql mysql  12582912 May 20 15:42 ibdata1
-rw-rw---- 1 mysql mysql         6 May 20 15:42 mysql.pid
drwx------ 2 mysql mysql      4096 May 20 14:32 mysql
drwx------ 2 mysql mysql      4096 May 20 14:32 performance_schema
-rw-rw---- 1 mysql mysql       322 May 20 15:42 slow.log
drwx------ 2 mysql mysql      4096 May 20 14:32 test

# mv -i bin.000001 bin.999999
# echo bin.999999 > bin.index

# /etc/init.d/mysql restart

# ll
total 537172
-rw-rw---- 1 mysql mysql        56 May 20 14:32 auto.cnf
-rw-rw---- 1 mysql mysql       120 May 20 15:42 bin.1000000
-rw-rw---- 1 mysql mysql       139 May 20 15:42 bin.999999
-rw-rw---- 1 mysql mysql        27 May 20 15:42 bin.index
-rw-r----- 1 mysql root      13820 May 20 15:42 error.log
-rw-rw---- 1 mysql mysql       884 May 20 15:42 ib_buffer_pool
-rw-rw---- 1 mysql mysql 268435456 May 20 15:42 ib_logfile0
-rw-rw---- 1 mysql mysql 268435456 May 20 15:42 ib_logfile1
-rw-rw---- 1 mysql mysql  12582912 May 20 15:42 ibdata1
-rw-rw---- 1 mysql mysql         6 May 20 15:42 mysql.pid
drwx------ 2 mysql mysql      4096 May 20 14:32 mysql
srwxrwxrwx 1 mysql mysql         0 May 20 15:42 mysql.sock
drwx------ 2 mysql mysql      4096 May 20 14:32 performance_schema
-rw-rw---- 1 mysql mysql       483 May 20 15:42 slow.log
drwx------ 2 mysql mysql      4096 May 20 14:32 test


どこまでいけるのか試してみましたが、2^ 63までのようです。

# mv -i bin.1000000 bin.9223372036854775808
# echo bin.9223372036854775808> bin.index

# /etc/init.d/mysql restart

# ll
total 537192
-rw-rw---- 1 mysql mysql        56 May 20 14:32 auto.cnf
-rw-rw---- 1 mysql mysql       170 May 20 16:21 bin.9223372036854775808
-rw-rw---- 1 mysql mysql        50 May 20 16:21 bin.index
-rw-r----- 1 mysql root      39612 May 20 16:21 error.log
-rw-rw---- 1 mysql mysql       884 May 20 16:21 ib_buffer_pool
-rw-rw---- 1 mysql mysql 268435456 May 20 16:21 ib_logfile0
-rw-rw---- 1 mysql mysql 268435456 May 20 15:42 ib_logfile1
-rw-rw---- 1 mysql mysql  12582912 May 20 16:21 ibdata1
-rw-rw---- 1 mysql mysql         6 May 20 16:21 mysql.pid
drwx------ 2 mysql mysql      4096 May 20 14:32 mysql
srwxrwxrwx 1 mysql mysql         0 May 20 16:21 mysql.sock
drwx------ 2 mysql mysql      4096 May 20 14:32 performance_schema
-rw-rw---- 1 mysql mysql      1127 May 20 16:21 slow.log
drwx------ 2 mysql mysql      4096 May 20 14:32 test

# less error.log
..
2014-05-20 16:19:01 9560 [Warning] Next log extension: 9223372036854775808. Remaining log filename extensions: 9223372039002259455. Please consider archiving some logs.
2014-05-20 16:19:01 9560 [Warning] Next log extension: 9223372036854775808. Remaining log filename extensions: 9223372039002259455. Please consider archiving some logs.
..

mysql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.00 sec)

# ll
total 537192
-rw-rw---- 1 mysql mysql        56 May 20 14:32 auto.cnf
-rw-rw---- 1 mysql mysql       170 May 20 16:22 bin.9223372036854775808
-rw-rw---- 1 mysql mysql        76 May 20 16:22 bin.index
-rw-r----- 1 mysql root      39782 May 20 16:22 error.log
-rw-rw---- 1 mysql mysql       884 May 20 16:21 ib_buffer_pool
-rw-rw---- 1 mysql mysql 268435456 May 20 16:21 ib_logfile0
-rw-rw---- 1 mysql mysql 268435456 May 20 15:42 ib_logfile1
-rw-rw---- 1 mysql mysql  12582912 May 20 16:21 ibdata1
-rw-rw---- 1 mysql mysql         6 May 20 16:21 mysql.pid
drwx------ 2 mysql mysql      4096 May 20 14:32 mysql
srwxrwxrwx 1 mysql mysql         0 May 20 16:21 mysql.sock
drwx------ 2 mysql mysql      4096 May 20 14:32 performance_schema
-rw-rw---- 1 mysql mysql      1127 May 20 16:21 slow.log
drwx------ 2 mysql mysql      4096 May 20 14:32 test

# less error.log
..
2014-05-20 16:22:08 11194 [Warning] Next log extension: 9223372036854775808. Remaining log filename extensions: 9223372039002259455. Please consider archiving some logs.

おい、Query OK, じゃないだろ。。

InnoDBオンラインALTER TABLEではIndex_lengthが更新されない

そのままなんですが。


インデックス張ってからロードしたとき。

mysql> CREATE TABLE t1 (num int unsigned, val varchar(32), upd datetime default current_timestamp);
mysql> ALTER TABLE t1 ADD KEY (val, upd), ADD KEY (upd);
mysql> LOAD DATA INFILE '/data/tmp/md5.tsv' INTO TABLE t1(num, val);

$ ls -ls /data/tmp/mysql/d1/t1.ibd
1754804 -rw-rw---- 1 mysql mysql 1795162112 May 20 14:41 /data/tmp/mysql/d1/t1.ibd

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9705549
 Avg_row_length: 74
    Data_length: 727711744
Max_data_length: 0
   Index_length: 1027342336
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2014-05-20 14:38:32
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)


ALTER TABLE .. ALGORITHM= COPY
mysql> CREATE TABLE t1 (num int unsigned, val varchar(32), upd datetime default current_timestamp);
mysql> LOAD DATA INFILE '/data/tmp/md5.tsv' INTO TABLE t1(num, val);
mysql> ALTER TABLE t1 ADD KEY (val, upd), ADD KEY (upd), ALGORITHM= COPY;

$ ll -s /data/tmp/mysql/d1/t1.ibd
1754804 -rw-rw---- 1 mysql mysql 1795162112 May 20 15:37 /data/tmp/mysql/d1/t1.ibd

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9188260
 Avg_row_length: 75
    Data_length: 691011584
Max_data_length: 0
   Index_length: 965476352
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2014-05-20 15:37:16
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)


ALTER TABLE .. ALGORITHM= INPLACE 暗黙のデフォルト、いわゆるオンラインALTER TABLE

mysql> CREATE TABLE t1 (num int unsigned, val varchar(32), upd datetime default current_timestamp);
mysql> LOAD DATA INFILE '/data/tmp/md5.tsv' INTO TABLE t1(num, val);
mysql> ALTER TABLE t1 ADD KEY (val, upd), ADD KEY (upd);

$ ll -s /data/tmp/mysql/d1/t1.ibd
1394004 -rw-rw---- 1 mysql mysql 1426063360 May 20 14:45 /data/tmp/mysql/d1/t1.ibd

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9704565
 Avg_row_length: 75
    Data_length: 729808896
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-05-20 14:45:40
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> ANALYZE TABLE t1;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| d1.t1 | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9704565
 Avg_row_length: 75
    Data_length: 729808896
Max_data_length: 0
   Index_length: 690749440
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-05-20 15:42:26
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)


information_schema.tablesやSHOW TABLE STATUSを見張っている場合は要注意…:(;゙゚'ω゚'):

MySQL 5.6.4で実装されたinnodb-sort-buffer-sizeの値

InnoDBのオンラインALTER TABLEの時に使われるパラメーター。
セッション変数のsort_buffer_sizeのように使われて、これをあふれたぶんだけsort_merge_passes相当の処理が走るので重くなる。

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_sort_buffer_size

最大値が6.7GBに見えたけど全くの空目で、最大値は64Mと小さめ。暗黙のデフォルトは1M。

実際どれくらい違うのか。ざっくりテスト。


$ perl -e 'use Digest::MD5 qw/md5_hex/; open($fh, ">/data/tmp/md5.tsv"); for ($n= 1; $n<= 10000000; $n++) {printf($fh "%d\t%s\n", $n, md5_hex($n));}'

$ cat /data/tmp/md5.sql
SELECT @@innodb_sort_buffer_size;

use d1

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (num int unsigned, val varchar(32), upd datetime default current_timestamp);

LOAD DATA INFILE '/data/tmp/md5.tsv' INTO TABLE t1(num, val);

ALTER TABLE t1 ADD KEY (val, upd), ADD KEY (upd);

DROP TABLE t1;


mysql> source /data/tmp/md5.sql
+---------------------------+
| @@innodb_sort_buffer_size |
+---------------------------+
|                   1048576 |
+---------------------------+
1 row in set (0.00 sec)

Database changed
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 10000000 rows affected (1 min 3.48 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 0 rows affected (2 min 8.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.70 sec)


mysql> source /data/tmp/md5.sql
+---------------------------+
| @@innodb_sort_buffer_size |
+---------------------------+
|                  16777216 |
+---------------------------+
1 row in set (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 10000000 rows affected (59.01 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 0 rows affected (2 min 8.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.62 sec)


mysql> source /data/tmp/md5.sql
+---------------------------+
| @@innodb_sort_buffer_size |
+---------------------------+
|                  67108864 |
+---------------------------+
1 row in set (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 10000000 rows affected (1 min 0.29 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 0 rows affected (1 min 48.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.71 sec)

オンラインALTER TABLE用のパラメーターなので、ALTER TABLE .., ALGORITHM= COPYの場合はもちろん効かなかった。これを約10秒/GBの減少とみるか(ロード後で.ibdファイルは1.7GBくらい)、15%の減少とみるか。

グローバルで64Mなら、最初から最大値にしておいてもいいかな。


【2014/07/03 12:15】
実際には1つのADD KEYに対してinnodb-sort-buffer-sizeの4倍のメモリーを使うので注意。。

日々の覚書: MySQL 5.6のオンラインALTER TABLEとinnodb-sort-buffer-sizeに関する考察 

2014年5月19日月曜日

Percona XtraBackupの圧縮メモ

innobackupexのオプションごとにどれくらいかメモ。
主にファイルサイズと処理時間を比べたいだけなので、MySQLは起動しておれどトラフィックはなし。tpcc-mysqlのWH= 100をロードしただけ。


$ du -sh /data/mysql
14G     /data/mysql

データファイル意外と小さかった。。RESET MASTERしたのでバイナリーログは当然含まず。


tarボールストリーム圧縮なし

$ time innobackupex /data/mysql --stream=tar | ssh mysql@backup-server "cat - > /data/tmp/xtrabackup.tar"
..
real    4m53.213s
user    4m13.456s
sys     0m37.721s

$ ls -lh xtrabackup*
-rw-rw-r--  1 mysql mysql 8.5G May 19 16:35 xtrabackup.tar

$ mkdir xtrabackup

$ time tar ixf xtrabackup.tar -C xtrabackup

real    0m16.243s
user    0m0.163s
sys     0m16.073s

$ time innobackupex --apply-log xtrabackup
..
real    0m45.953s
user    0m0.297s
sys     0m5.908s


tarボールgzip圧縮

$ time innobackupex /data/mysql --stream=tar | gzip -c | ssh mysql@backup-server "cat - > /data/tmp/xtrabackup.tar.gz"
..
real    13m2.701s
user    15m19.741s
sys     0m28.345s

$ ls -lh xtrabackup*
-rw-rw-r-- 1 mysql mysql 4.8G May 19 16:58 xtrabackup.tar.gz

$ mkdir xtrabackup

$ time tar ixf xtrabackup.tar.gz -C xtrabackup

real    1m37.648s
user    1m31.823s
sys     0m21.962s

$ time innobackupex --apply-log xtrabackup
..
real    0m44.944s
user    0m0.277s
sys     0m6.055s


tarボールpbzip2圧縮(8並列)

$ time innobackupex /data/mysql --stream=tar | pbzip2 -p8 -c | ssh backup-server "cat - > /data/tmp/xtrabackup.tar.bz2"
..
real    3m11.137s
user    27m21.804s
sys     0m30.629s

$ ls -lh xtrabackup*
-rw-rw-r-- 1 mysql mysql 4.3G May 19 17:09 xtrabackup.tar.bz2

$ mkdir xtrabackup

$ time pbzip2 -p8 -dc xtrabackup.tar.bz2 | tar ix -C xtrabackup
tar: Read 2560 bytes from -

real    1m24.567s
user    11m18.711s
sys     0m30.188s

$ time innobackupex --apply-log xtrabackup
..
real    0m43.918s
user    0m0.291s
sys     0m6.073s


xbstream圧縮なし(1並列)

$ time innobackupex /data/mysql --stream=xbstream | ssh backup-server "cat - > /data/tmp/xtrabackup.xb"
..
real    5m17.412s
user    4m36.084s
sys     0m38.236s

$ ll -h xtrabackup.*
-rw-rw-r-- 1 mysql mysql 8.5G May 19 17:54 xtrabackup.xb

$ mkdir xtrabackup

$ time xbstream -x -C xtrabackup < xtrabackup.xb

real    1m32.016s
user    0m18.126s
sys     0m27.725s

$ time innobackupex --apply-log xtrabackup
..
real    0m47.103s
user    0m0.297s
sys     0m6.376s
xbstream圧縮あり(1並列)
$ time innobackupex /data/mysql --stream=xbstream --compress | ssh backup-server "cat - > /data/tmp/xtrabackup.xb"
..
real    5m44.481s
user    4m59.169s
sys     0m29.153s

$ ll -h xtrabackup.*
-rw-rw-r-- 1 mysql mysql 6.7G May 19 18:13 xtrabackup.xb

$ mkdir xtrabackup

$ time xbstream -x -C xtrabackup < xtrabackup.xb

real    1m11.434s
user    0m14.041s
sys     0m21.624s

$ time innobackupex --decompress xtrabackup/
..
real    1m54.178s
user    1m31.540s
sys     0m24.585s

$ time innobackupex --apply-log xtrabackup
..
real    0m45.782s
user    0m0.263s
sys     0m5.995s
xbstream圧縮あり(8並列)
$ time innobackupex /data/mysql --stream=xbstream --compress --compress-thread=8 --parallel=8 | ssh backup-server "cat - > /data/tmp/xtrabackup.xb"
..
real    3m40.315s
user    5m0.383s
sys     0m26.421s

$ ll -h xtrabackup.*

$ time xbstream -x -C xtrabackup < xtrabackup.xb
real    1m12.859s
user    0m13.734s
sys     0m20.157s

$ time innobackupex --decompress --parallel=8 xtrabackup/
..
real    2m16.178s
user    1m30.866s
sys     0m24.585s

$ time innobackupex --apply-log xtrabackup
..
real    0m45.722s
user    0m0.289s
sys     0m5.997s
decompress、多重化したらむしろ遅くなっててしょぼん。 tarボール無圧縮、--compact
$ time innobackupex /data/mysql --stream=tar --compact | ssh mysql@backup-server "cat - > /data/tmp/xtrabackup.tar"
..
real    4m50.256s
user    4m5.120s
sys     0m38.300s

$ ll -h xtrabackup.*
-rw-rw-r-- 1 mysql mysql 8.5G May 19 18:53 xtrabackup.tar

$ time tar ixf xtrabackup.tar -C xtrabackup

real    0m14.358s
user    0m0.209s
sys     0m13.879s

$ time innobackupex --apply-log xtrabackup
..
real    3m54.054s
user    0m24.002s
sys     0m41.084s
--stream=tarでは--parallelが効かないので、ごりごりやって良いなら--stream=xbstreamでいきたいところ。 容量面でcompactが全然効いた気配がないのに、--apply-logではちゃんとExpandingになって時間がかかってなんだかなぁ。 --rebuild-threads=8とかすれば多少速くなるのかも知れないけどそこまで試すアレなし。 ところでこの--compact(セカンダリーインデックスのそぎ落とし)が効かないのって、 tpcc_loadかましたあとにALTER TABLEでインデックスつけてるのがいけないような気がしてきた。
mysql> SHOW CREATE TABLE stock\G
*************************** 1. row ***************************
       Table: stock
Create Table: CREATE TABLE `stock` (
  `s_i_id` int(11) NOT NULL,
  `s_w_id` smallint(6) NOT NULL,
  `s_quantity` smallint(6) DEFAULT NULL,
  `s_dist_01` char(24) DEFAULT NULL,
  `s_dist_02` char(24) DEFAULT NULL,
  `s_dist_03` char(24) DEFAULT NULL,
  `s_dist_04` char(24) DEFAULT NULL,
  `s_dist_05` char(24) DEFAULT NULL,
  `s_dist_06` char(24) DEFAULT NULL,
  `s_dist_07` char(24) DEFAULT NULL,
  `s_dist_08` char(24) DEFAULT NULL,
  `s_dist_09` char(24) DEFAULT NULL,
  `s_dist_10` char(24) DEFAULT NULL,
  `s_ytd` decimal(8,0) DEFAULT NULL,
  `s_order_cnt` smallint(6) DEFAULT NULL,
  `s_remote_cnt` smallint(6) DEFAULT NULL,
  `s_data` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`s_w_id`,`s_i_id`),
  KEY `fkey_stock_2` (`s_i_id`),
  CONSTRAINT `fkey_stock_1` FOREIGN KEY (`s_w_id`) REFERENCES `warehouse` (`w_id`),
  CONSTRAINT `fkey_stock_2` FOREIGN KEY (`s_i_id`) REFERENCES `item` (`i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'stock'\G
*************************** 1. row ***************************
           Name: stock
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9793316
 Avg_row_length: 354
    Data_length: 3469737984
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-05-19 15:51:37
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

$ mysql-5.7.4-m14-linux-glibc2.5-x86_64/bin/innochecksum -S /data/tmp/mysql/tpcc/stock.ibd
File::/data/tmp/mysql/tpcc/stock.ibd
================PAGE TYPE SUMMARY==============
#PAGE_COUNT     PAGE_TYPE
===============================================
  224349        Index page
       0        Undo log page
       1        Inode page
       0        Insert buffer free list page
    1158        Freshly allocated page
      14        Insert buffer bitmap
       0        System page
       0        Transaction system page
       1        File Space Header
      13        Extent descriptor page
       0        BLOB page
       0        Compressed BLOB page
       0        Other type of page
===============================================
Additional information:
Undo page type: 0 insert, 0 update, 0 other
Undo page state: 0 active, 0 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other

なぜかindex_lengthに計上されない謎。このあたりなのかなぁ?
【2014/05/20 15:59】 計上されないのはたぶん関係ない ⇒ 日々の覚書: InnoDBオンラインALTER TABLEではIndex_lengthが更新されない

5.7.4のinnochecksumでも、セカンダリーインデックスなのかクラスターインデックス(=データページ)なのかは分けられないのかー。

取り敢えずマシンパワーがあるのあらxbstream+ pbzip2, ほそぼそやるならxbstream+ compressでいいかな。

MariaDB 10.0.5で実装されたROLEを試す

MariaDBで実装されるという噂だったROLE、まだだと思っていたらもうあったんですね。ということでさっくり試してみる。10.0.5から実装されたらしいけど、試したバージョンは10.0.11。

オリジナルのドキュメントはこちら。 https://mariadb.com/kb/en/roles-overview/

まずはROLEを作成してみる。mysqlスキーマに対してSELECTのみの権限を持つsys_selectロールを作成して、yoku0825ユーザーに割り当てる。

MariaDB [mysql]> CREATE ROLE sys_select;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> GRANT SELECT ON mysql.* TO sys_select;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> GRANT sys_select TO yoku0825;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> GRANT USAGE ON *.* TO yoku0825;
Query OK, 0 rows affected (0.00 sec)

…あれ、GRANT .. ON .. TO ..って、これ、sys_select@%ユーザーが出来ちゃうんじゃね?;


MariaDB [mysql]> SELECT user, host, password FROM user ORDER BY 1, 2;
+------------+-----------------+----------+
| user       | host            | password |
+------------+-----------------+----------+
| root       | 127.0.0.1       |          |
| root       | ::1             |          |
| root       | ip-172-31-0-135 |          |
| root       | localhost       |          |
| sys_select |                 |          |
| yoku0825   | %               |          |
+------------+-----------------+----------+
6 rows in set (0.00 sec)

出来てるっぽいけど、hostが空欄だ。


# bin/mysql -usys_select
ERROR 1045 (28000): Access denied for user 'sys_select'@'localhost' (using password: NO)

ログインはできない。


MariaDB [mysql]> SELECT user, host, password, is_role FROM user ORDER BY 1, 2;
+------------+-----------------+----------+---------+
| user       | host            | password | is_role |
+------------+-----------------+----------+---------+
| root       | 127.0.0.1       |          | N       |
| root       | ::1             |          | N       |
| root       | ip-172-31-0-135 |          | N       |
| root       | localhost       |          | N       |
| sys_select |                 |          | Y       |
| yoku0825   | %               |          | N       |
+------------+-----------------+----------+---------+
6 rows in set (0.01 sec)

よく見てみると、mysql.userテーブルにis_roleというカラムが追加されてて、これで制御されてるっぽい。


MariaDB [mysql]> SELECT * FROM roles_mapping;
+-----------+----------+------------+--------------+
| Host      | User     | Role       | Admin_option |
+-----------+----------+------------+--------------+
| %         | yoku0825 | sys_select | N            |
| localhost | root     | sys_select | Y            |
+-----------+----------+------------+--------------+
2 rows in set (0.00 sec)

割り当てたロールはmysql.roles_mappingテーブルに格納されている。
じゃあ早速yoku0825ユーザーでログインしなおして、mysqlスキーマにアクセスを試す。


MariaDB [(none)]> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT user, host FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'yoku0825'@'localhost' for table 'user'

Σ(゚д゚lll) ダメじゃん。
と思ったら、ROLEはログインした後明示的に変更しないといけないぽい。


MariaDB [(none)]> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NULL           |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET ROLE sys_select;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| sys_select     |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT user, host FROM mysql.user;
+------------+-----------------+
| user       | host            |
+------------+-----------------+
| sys_select |                 |
| yoku0825   | %               |
| root       | 127.0.0.1       |
| root       | ::1             |
| root       | ip-172-31-0-135 |
| root       | localhost       |
+------------+-----------------+
6 rows in set (0.00 sec)

sudoっぽい感じ。でもどうやって戻るんだかよくわからない。


MariaDB [(none)]> CREATE ROLE sys_insert;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT INSERT ON mysql.* TO sys_insert;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT sys_insert TO yoku0825;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NULL           |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET ROLE sys_select;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| sys_select     |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET ROLE sys_insert;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| sys_insert     |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT user, host FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'yoku0825'@'localhost' for table 'user'

SET ROLEで上書きすると、それまでのROLEの権限は使えなくなる。


MariaDB [(none)]> GRANT ALL ON d1.* TO yoku0825;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> DROP DATABASE d1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SET ROLE sys_select;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> DROP DATABASE d1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS;
+--------------------------------------------------+
| Grants for yoku0825@%                            |
+--------------------------------------------------+
| GRANT sys_select TO 'yoku0825'@'%'               |
| GRANT sys_insert TO 'yoku0825'@'%'               |
| GRANT USAGE ON *.* TO 'yoku0825'@'%'             |
| GRANT ALL PRIVILEGES ON `d1`.* TO 'yoku0825'@'%' |
| GRANT USAGE ON *.* TO 'sys_select'               |
| GRANT SELECT ON `mysql`.* TO 'sys_select'        |
+--------------------------------------------------+
6 rows in set (0.00 sec)

SET ROLESしても、予め与えられていた権限が上乗せされるわけではなくて、和になる。当然か。
SHOW GRANTSで見るとわかりやすげ。

もっとグループパーミッション的なものを想像していたけど、"sudoっぽい"ということで、取り敢えずそんなかんじ。


【2014/05/19 13:55】
DEFAULT ROLEが実装されればもう少し変わるんだろうけど、これは10.1での実装予定となっております。
https://mariadb.atlassian.net/browse/MDEV-5210

2014年5月15日木曜日

pt-table-checksumでレプリケーション不整合を確認する

わたしはレプリケーション(と、バイナリーログ)フィルターが嫌いです。

フィルターが評価されるルール が理解されないまま運用されてカレントデータベースがNULLのままUPDATEを実行したりする人がいたりするので嫌なんですが、ストレージとかスレーブの性能とかネットワークの帯域とかで使わなければならないことも多々あります。

そんな時によく使う、 pt-table-checksum のメモ。

$ pt-table-checksum --socket /usr/mysql/5.6.17/data/mysql.sock --user root --password xxxx --tables d1.t1,d1.t2,d2.t1 --replicate test.pt-tcs --create-replicate-table
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
05-15T17:36:00      0      0    39028       4       0   0.289 d1.t1
05-15T17:36:01      0      0   173469       4       0   0.239 d1.t2
05-15T17:36:14      0      0  5105937      15       0   7.264 d2.t1

こんなふうにマスターで流すと、


# at 108042433
#140515 17:36:00 server id 33597  end_log_pos 108043217 CRC32 0x0ca8897e        Query   thread_id=420836        exec_time=0
     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1400142960/*!*/;
CREATE TABLE IF NOT EXISTS `test`.`pt-tcs` (
     db             char(64)     NOT NULL,
     tbl            char(64)     NOT NULL,
     chunk          int          NOT NULL,
     chunk_time     float            NULL,
     chunk_index    varchar(200)     NULL,
     lower_boundary text             NULL,
     upper_boundary text             NULL,
     this_crc       char(40)     NOT NULL,
     this_cnt       int          NOT NULL,
     master_crc     char(40)         NULL,
     master_cnt     int              NULL,
     ts             timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (db, tbl, chunk),
     INDEX ts_db_tbl (ts, db, tbl)
  ) ENGINE=InnoDB
/*!*/;
..
# at 108043627
#140515 17:36:00 server id 33597  end_log_pos 108044746 CRC32 0x0a0d40d3        Query   thread_id=420836        exec_time=0
     error_code=0
SET TIMESTAMP=1400142960/*!*/;
REPLACE INTO `test`.`pt-tcs` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'd1', 't1', '1', 'PRIMARY', '1', '1000', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `column1`, `column2`, ..,)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `d1`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`column1` >= '1')) AND ((`column1` <= '1000')) /*checksum chunk*/
/*!*/;
..
# at 108044864
#140515 17:36:00 server id 33597  end_log_pos 108045097 CRC32 0x4799fdd5        Query   thread_id=420836        exec_time=0
     error_code=0
SET TIMESTAMP=1400142960/*!*/;
UPDATE `test`.`pt-tcs` SET chunk_time = '0.011250', master_crc = '4173135a', master_cnt = '1000' WHERE db = 'd1' AND tbl = 't1' AND chunk = '1'
/*!*/;
..

とまあこんな感じで、チェック対象のテーブルに入っている値をハッシュ計算してテーブルに入れ込んでくれます。

このREPLACE INTOやUPDATEはスレーブにも伝播され、スレーブ側で再実行(スレーブのテーブルに本当に入っている値をハッシュ計算してテーブルに入れ込む)されるので、スクリプトが流れ終わったあとにマスターとスレーブのこのテーブル(test.pt-tcs)の中身を比較してやれば、同じデータが入っているであろうことが判断できます。

pt-tcsを実行するサーバーからログインできるユーザーがいれば、pt-tcsの中でも直接比較してexit codeとかに反映してくれるようなことも書いてありますし、PMPを入れ込めば、この値に差分がないかどうかを定期的にNagiosから確認できたりもするようです。

http://www.percona.com/doc/percona-monitoring-plugins/1.1/nagios/pmp-check-pt-table-checksum.html


弱点は、RBRの環境下では使えないこと。マスターで計算されたハッシュ値がそのままスレーブに渡されてしまうので、意味を為さなくなってしまう。

が、PXC(Galera Cluster)でも使えるようなことが書いてあって、どうやって計算しているのだろう。SET SESSION binlog_format= STATEMENTを押し込むのかな?