MySQLのSQLでNagiosに値を戻すスクリプト書いた

ぱっと見で見当たらなかったので書いた。

SQLで取得した値を評価して、Nagiosさんに戻り値を返すスクリプト。

https://github.com/yoku0825/my_script/blob/master/nagios_for_mysql.pl

こんな風に使う。

$ ./nagios_for_mysql.pl --user root --password xxxx --host 127.0.0.1 --port 64056 --sql "SELECT COUNT(*) FROM information_schema.processlist WHERE state IN ('update', 'updating') AND time > 10" --warning 10 --critical 20
$ echo $?
3

走行中(というかgrn_io_lock待ちだ)のスレッドの数を数えてみたり。

$ perl ./nagios_for_mysql.pl --user root --host 127.0.0.1 --port 64056 --sql "SELECT table_rows FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'user'"
$ echo $?
0

テーブルに格納されている件数を確認してみたり(i_sだから正確じゃないのはいいとして)、各パーティションに入ってる件数もi_sから引けるので色々どうとでもなるし、
(このへんのクエリーと組み合わせる感じ http://yoku0825.blogspot.jp/2014/03/informationschematips.html ）

$ perl ./nagios_for_mysql.pl --user root --host 127.0.0.1 --port 64056 --sql "SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'sort_merge_passes'" --warning 2 --critical 10
$ echo $?
0

累計だけど、ステータス変数の監視もできるっちゃできる。

絶対どこかにあるんだけど、探すより書いちゃう方が早かった。。


【2014/07/30 17:01】
早速教えていただいた :)

@yoku0825 機能見てないけど、クエリ書けるpluginありますね https://t.co/Mb14xJqqyf
— masahiro nagano (@kazeburo) 2014, 7月 30

ありがとうございます(*-人-)

YAPC::Asia Tokyo 2014でMySQLのWHERE狙いのキーとORDER BY狙いのキーの話をします

YAPC::Asia Tokyo 2014に応募していたトークを採択していただきました :)
WHERE狙いのキー、ORDER BY狙いのキー - YAPC::Asia Tokyo 2014

たくさんの人に応援していただいていて、本当に感謝しております :)
Talks Social Ranking - YAPC::Asia Tokyo 2014


WHERE狙いって何よとかORDER BY狙いってしゃらっと言ってますが、このへんはフィーリングで呼んでいるだけの造語です。MySQLに詳しい方にはなんとなーく伝わるんじゃないかなと期待していますが、どちらかというと「なんだよそれ造語かよ道理で聞いたこともない」って方に聞いていただきたいなぁと思っていたりします。

たとえば、EXPLAIN(目XPLAINでも可)でtype: ALLになるような(=テーブルスキャンの)クエリーってヤバそうじゃないですか。なんでヤバいかって、テーブルに格納されるレコードの件数に比例して(本当は線形じゃなくもっとヤバい）処理量が増えていきそうなのがある程度みんな「あ、やべっ」って感じになるじゃないですか。

mysql56> show create table Country\G
*************************** 1. row ***************************
       Table: Country
Create Table: CREATE TABLE `Country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql56> EXPLAIN SELECT Code, Name, Population FROM Country WHERE Continent = 'Asia';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Country | ALL  | NULL          | NULL | NULL    | NULL |  239 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MySQLのworldデータベースからインデックスを全て取っ払ったテーブルを用意します。EXPLAINを取るとtype: ALLでExtra: Using whereです。
これを「Perlのコードっぽく書くと」こんな感じですね。

my @country_table= ({Code => "ABW", Name => "Aruba",       Continent => "North America", .., Population => 103000},
                    {Code => "AFG", Name => "Afghanistan", Continent => "Asia",          .., Population => 22720000},
                    {Code => "AGO", Name => "Angola",      Continent => "Africa",        .., Population => 12878000},
                    ..);

foreach my $row (@country_table)
{
  if ($row->{Continent} eq "Asia")
  {
    printf("Code:%s, Name:%s, Population:%d\n", $row->{Code}, $row->{Name}, $row->{Population});
  }
}

ヤバそうですよね。
これにKEY(Continent) を足すとこんな感じになります。

mysql56> EXPLAIN SELECT Code, Name, Population FROM Country WHERE Continent = 'Asia';
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key       | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | Country | ref  | Continent     | Continent | 1       | const |   51 | Using index condition |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

Perlで表現すると、

my @country_table= ({Code => "ABW", Name => "Aruba",       Continent => "North America", .., Population => 103000},
                    {Code => "AFG", Name => "Afghanistan", Continent => "Asia",          .., Population => 22720000},
                    {Code => "AGO", Name => "Angola",      Continent => "Africa",        .., Population => 12878000});

my %continent_index= (Asia   => [1, 9, 19, ..],
                      Europe => [4, 5, 15, ..],
                      ..);

foreach my $row_num (@{$continent_index{Asia}})
{
    printf("Code:%s, Name:%s, Population:%d\n",
           $country_table[$row_num]->{Code},
           $country_table[$row_num]->{Name},
           $country_table[$row_num]->{Population});
}

MySQLの人からもPerlの人からも怒られそうな感じがしてきましたが、こんな感じの話になる予定です。興味を持っていただけたら幸いです :)

TokuDBで変にパーティションが遅い件の比較用XtraDB

これもメモ調で。
昨日の TokuDBでパーティションが変に遅い件 との比較。

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.08 sec)

mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `num` int(10) unsigned NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (num)
PARTITIONS 10 */
1 row in set (0.05 sec)

mysql> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `num` int(10) unsigned NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (num)
(PARTITION p0 VALUES LESS THAN (100000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (500000) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (600000) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (700000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (800000) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (900000) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (1000000) ENGINE = InnoDB,
 PARTITION px VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

昨日のテーブルをデータ入ったままALTER TABLE .. Engine= InnoDBしただけ。

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.46 sec)

mysql> SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.49 sec)

mysql> SELECT COUNT(*) FROM t3;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.49 sec)

同じクエリーを4回投げて一番遅いのを捨てて中央値を取るのはいっしょ。
プルーニングが効かない全スキャンなのでt1が一番速いとはいえ、やっぱりこんなもんだよね。

mysql> SELECT COUNT(*) FROM t1 WHERE num > 900000;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.06 sec)

mysql> SELECT COUNT(*) FROM t2 WHERE num > 900000;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.05 sec)

mysql> SELECT COUNT(*) FROM t3 WHERE num > 900000;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.05 sec)

プルーニング効かないはずのt2のが速い。。

mysql> explain partitions SELECT COUNT(*) FROM t1 WHERE num > 900000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 214438 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.03 sec)

mysql>
mysql> explain partitions SELECT COUNT(*) FROM t2 WHERE num > 900000;
+----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | partitions                    | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t2    | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | range | PRIMARY       | PRIMARY | 4       | NULL | 201950 | Using where; Using index |
+----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> explain partitions SELECT COUNT(*) FROM t3 WHERE num > 900000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | t3    | p9,px      | range | PRIMARY       | PRIMARY | 4       | NULL | 49983 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

XtraDBはちゃんとプルーニング効きつつtype: rangeになった。

TokuDBでパーティションが変に遅い件

とりあえずメモ。

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `num` int(10) unsigned NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (num)
PARTITIONS 10 */
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `num` int(10) unsigned NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (num)
(PARTITION p0 VALUES LESS THAN (100000) ENGINE = TokuDB,
 PARTITION p1 VALUES LESS THAN (200000) ENGINE = TokuDB,
 PARTITION p2 VALUES LESS THAN (300000) ENGINE = TokuDB,
 PARTITION p3 VALUES LESS THAN (400000) ENGINE = TokuDB,
 PARTITION p4 VALUES LESS THAN (500000) ENGINE = TokuDB,
 PARTITION p5 VALUES LESS THAN (600000) ENGINE = TokuDB,
 PARTITION p6 VALUES LESS THAN (700000) ENGINE = TokuDB,
 PARTITION p7 VALUES LESS THAN (800000) ENGINE = TokuDB,
 PARTITION p8 VALUES LESS THAN (900000) ENGINE = TokuDB,
 PARTITION p9 VALUES LESS THAN (1000000) ENGINE = TokuDB,
 PARTITION px VALUES LESS THAN MAXVALUE ENGINE = TokuDB) */
1 row in set (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/md5' INTO TABLE t1;
Query OK, 1000000 rows affected (12.70 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA INFILE '/tmp/md5' INTO TABLE t2;
Query OK, 1000000 rows affected (13.12 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA INFILE '/tmp/md5' INTO TABLE t3;
Query OK, 1000000 rows affected (13.00 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

パーティショニングなし, HASHパーティショニング, RANGEパーティショニングの3つに同じデータを詰める。

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.54 sec)

mysql> SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (3.39 sec)

mysql> SELECT COUNT(*) FROM t3;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (3.51 sec)

同じクエリーを4回投げて、一番遅いものを捨てて(キャッシュがあったまってないやつ)、3回の中央値で比較。
WHERE句なし。パーティション2つが異様に重い。

mysql> SELECT COUNT(*) FROM t1 WHERE num > 900000;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.06 sec)

mysql> SELECT COUNT(*) FROM t2 WHERE num > 900000;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.05 sec)

mysql> SELECT COUNT(*) FROM t3 WHERE num > 900000;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.36 sec)

…あれ、パーティションの刈り込みが効くはずのt3がものすごく遅い。。

mysql> explain partitions SELECT COUNT(*) FROM t1 WHERE num > 900000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 114200 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.02 sec)

mysql> explain partitions SELECT COUNT(*) FROM t2 WHERE num > 900000;
+----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | partitions                    | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t2    | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | range | PRIMARY       | PRIMARY | 4       | NULL | 103200 | Using where; Using index |
+----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> explain partitions SELECT COUNT(*) FROM t3 WHERE num > 900000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t3    | p9,px      | index | PRIMARY       | PRIMARY | 4       | NULL | 100001 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

ちゃんと刈り込めてはいるんだけどなーてか、t3のときだけインデックススキャンになってる。なんでだ。


【2014/07/15 11:55】
XtraDBでも調べた => TokuDBで変にパーティションが遅い件の比較用XtraDB


【2014/07/16 18:09】
バグらしい。中の人がMLにリプライくれた。=> https://groups.google.com/d/msg/tokudb-user/EjKSr_QuLYI/DSBH7PZ3v7AJ

MySQL Casual Talks vol.6でTokuDBについて話してきた

去る 7/11(金) ("セブンイレブンの日とおぼえてください" (c) RKajiyama) にMySQL Casual Talks vol.6がありました。 http://www.zusaar.com/event/11507003
とぅぎゃったまとめ http://togetter.com/li/691589


↓わたしの発表スライドです。

TokuDB試してみる from yoku0825

( ´-｀).oO（これ、レイテンシーのグラフとかスループットのグラフとかかなり読み飛ばしながらだったので、発表の前にslideshare公開しておくべきだったな。。

今回のネタはTokuDBという、最近(?) PerconaやMariaDBにも組み込まれているストレージエンジンのおはなし。2ヶ月くらい調べてみた感想だけ言うと、「圧縮ウマー」「一応MVCC対応してるしレイテンシーもInnoDBに比べて(ケースにはよれど) *そこまで悪くない*」「たまに静寂に陥るのがマジ怖い」「ロック粒度本当に謎」という感じで、「アプリケーションが叩いてくる本番にはまだ入れたくないけどバッチ用サーバーとかに入れて経験値をためたい」が一番素直な感想でしょうか。

公式MLが過疎ってる(中の人が返信してくれたらいいのに。。)のはちょっと問題な気がするけれど、公式Twitterはちょくちょく話しかけてくれるので、なんかこうその辺りは探りつつなんとかなりそうな予感。

.@yoku0825 Your presentation has generated a lot of interest in #TokuDB for #MySQL *and* incidentally in #Tokumx for #MongoDB. ありがとう
— Tokutek (@tokutek) 2014, 7月 11

いずれにせよ、誰か(いや俺じゃなくてもいいんだ)が日本語で積極的に良いところ悪いところ発信していって、
　み　ん　な　で　地　雷　除　去
していけばいいんじゃないですかね。

ところでいまさら気付いたんですが、○racleのオフィスでPerconaとMariaDBにだけ搭載されてるストレージエンジンの話とかしてしまったので、俺が連絡を絶ったら赤い会社に消されあれ誰か来た、ちょっと待ってｔ

Percona XtraDB Clusterでいつの間にかPITRできるようになってた

以前の覚書 日々の覚書: Percona XtraDB Cluster設計の迷いどころ（現在進行形） で迷ってたんですが、気付いたらPITRフツーにできたのでメモ。

あの記事を書いた時点で検証していたのはwsrep 25.2.xだったんですが、wsrepも気付けば25.5.x (MySQLと同じで、25. x . y のうち25はAPIバージョンなので、wsrepのバージョンは2.x系から5.x系なのでメジャー2世代くらい？)、25.2.xと25.3.xの間で結構インプリメントされたような話は聞いてたんですが、そのせいなのかどうかはよく判らず。

( ´-｀).oO（なんか25.2.xの時のcodershipのMLで、25.3.x(の初期)ではマルチマスターでPITRまだできないねー、みたいなのを読んだ気がしたんですがソース見つからず。

このディスカッション見ると、25.3.xと5.6の組み合わせで実装される予定(当時からすれば未来)だったのかなと思いますが。
http://www.linkedin.com/groups/How-do-pointintime-recovery-in-3973740.S.5802907499266850819


で、まあ、できました。
必要なのはmy.cnfにlog-slave-updates、これだけ。
これで、全てのインスタンスのバイナリーログに(ROWモードでだけど)記録されるようになる。
(前はこれができなかった。log-slave-updatesしても、実際にステートメントを実行したインスタンスのバイナリーログのみ)

mysql> use d1
Database changed

mysql> INSERT INTO t1 VALUES (1, 'one'), (2, 'two');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DELETE FROM t1 WHERE num= 1;
Query OK, 1 row affected (0.00 sec)

$ ll pxc*/bin*
-rw-rw---- 1 mysql mysql 501 Jul  9 08:28 pxc1/bin.000001
-rw-rw---- 1 mysql mysql  13 Jul  9 08:28 pxc1/bin.index
-rw-rw---- 1 mysql mysql 487 Jul  9 08:28 pxc2/bin.000001
-rw-rw---- 1 mysql mysql  13 Jul  9 08:28 pxc2/bin.index
-rw-rw---- 1 mysql mysql 487 Jul  9 08:28 pxc3/bin.000001
-rw-rw---- 1 mysql mysql  13 Jul  9 08:28 pxc3/bin.index

$ mysqlbinlog -vv pxc*/bin.000001 | grep "^#"
Warning: mysqlbinlog: unknown variable 'loose-default-character-set=utf8'
# at 4
#140709  8:28:26 server id 1  end_log_pos 120   Start: binlog v 4, server v 5.6.15-56-log created 140709  8:28:26 at startup
# Warning: this binlog is either in use or was not closed properly.
# at 120
#140709  8:28:42 server id 1  end_log_pos 191   Query   thread_id=4     exec_time=0     error_code=0
# at 191
#140709  8:28:42 server id 1  end_log_pos 233   Table_map: `d1`.`t1` mapped to number 70
# at 233
#140709  8:28:42 server id 1  end_log_pos 290   Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `d1`.`t1`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='one' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */
### INSERT INTO `d1`.`t1`
### SET
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='two' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */
# at 290
#140709  8:28:42 server id 1  end_log_pos 317   Xid = 12
# at 317
#140709  8:28:48 server id 1  end_log_pos 388   Query   thread_id=4     exec_time=0     error_code=0
# at 388
#140709  8:28:48 server id 1  end_log_pos 430   Table_map: `d1`.`t1` mapped to number 70
# at 430
#140709  8:28:48 server id 1  end_log_pos 474   Delete_rows: table id 70 flags: STMT_END_F
### DELETE FROM `d1`.`t1`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='one' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */
# at 474
#140709  8:28:48 server id 1  end_log_pos 501   Xid = 13
# at 4
#140709  8:28:19 server id 1  end_log_pos 120   Start: binlog v 4, server v 5.6.15-56-log created 140709  8:28:19 at startup
# Warning: this binlog is either in use or was not closed properly.
# at 120
#140709  8:28:42 server id 1  end_log_pos 184   Query   thread_id=4     exec_time=0     error_code=0
# at 184
#140709  8:28:42 server id 1  end_log_pos 226   Table_map: `d1`.`t1` mapped to number 70
# at 226
#140709  8:28:42 server id 1  end_log_pos 283   Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `d1`.`t1`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='one' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */
### INSERT INTO `d1`.`t1`
### SET
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='two' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */
# at 283
#140709  8:28:42 server id 1  end_log_pos 310   Xid = 12
# at 310
#140709  8:28:48 server id 1  end_log_pos 374   Query   thread_id=4     exec_time=0     error_code=0
# at 374
#140709  8:28:48 server id 1  end_log_pos 416   Table_map: `d1`.`t1` mapped to number 70
# at 416
#140709  8:28:48 server id 1  end_log_pos 460   Delete_rows: table id 70 flags: STMT_END_F
### DELETE FROM `d1`.`t1`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='one' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */
# at 460
#140709  8:28:48 server id 1  end_log_pos 487   Xid = 13
# at 4
#140709  8:28:22 server id 1  end_log_pos 120   Start: binlog v 4, server v 5.6.15-56-log created 140709  8:28:22 at startup
# Warning: this binlog is either in use or was not closed properly.
# at 120
#140709  8:28:42 server id 1  end_log_pos 184   Query   thread_id=4     exec_time=0     error_code=0
# at 184
#140709  8:28:42 server id 1  end_log_pos 226   Table_map: `d1`.`t1` mapped to number 70
# at 226
#140709  8:28:42 server id 1  end_log_pos 283   Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `d1`.`t1`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='one' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */
### INSERT INTO `d1`.`t1`
### SET
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='two' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */
# at 283
#140709  8:28:42 server id 1  end_log_pos 310   Xid = 12
# at 310
#140709  8:28:48 server id 1  end_log_pos 374   Query   thread_id=4     exec_time=0     error_code=0
# at 374
#140709  8:28:48 server id 1  end_log_pos 416   Table_map: `d1`.`t1` mapped to number 70
# at 416
#140709  8:28:48 server id 1  end_log_pos 460   Delete_rows: table id 70 flags: STMT_END_F
### DELETE FROM `d1`.`t1`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='one' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */
# at 460
#140709  8:28:48 server id 1  end_log_pos 487   Xid = 13
# End of log file

というわけで、どれか1台以上のmysqldにmysqlbinlog -R --raw --stop-neverでつないでおけば、フツーのMySQLと同じようにPITRできそう。ビバ。

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

つらつらと覚書。

サンプルテーブルは以下。

mysql> CREATE TABLE `t1` (
    ->   `id` int(10) unsigned NOT NULL,
    ->   `c1` varchar(32) NOT NULL,
    ->   `c2` varchar(32) NOT NULL,
    ->   PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.32 sec)

mysql> INSERT INTO t1 VALUES (1, 'one', 'eins'), (2, 'two', 'zwei'), (3, 'three', 'drei');
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

まずは innodb-sort-buffer-size= 1M で単一カラムに。performance-schemaはOFFにしておかないとメモリー割り当ての上位がほとんどp_s関連で占められて表示が追い出されてしまう。

$ valgrind --tool=massif bin/mysqld --no-defaults --datadir=./data --performance-schema=0 --innodb-sort-buffer-size=1M

mysql> ALTER TABLE t1 ADD KEY (c1);
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ms_print massif.out.30840 | grep alter

ALTER TABLEに関するメモリ割り当ては下位にあるっぽいので出てこない。


innodb-sort-buffer-size= 8M で単一カラム。

$ valgrind --tool=massif bin/mysqld --no-defaults --datadir=./data --performance-schema=0 --innodb-sort-buffer-size=8M

mysql> ALTER TABLE t1 ADD KEY (c1);
Query OK, 0 rows affected (0.72 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ms_print massif.out.30908 | grep alter
| |     ->49.78% (33,554,432B) 0x99EFC0: ha_innobase::inplace_alter_table(TABLE*, Alter_inplace_info*) (handler0alter.cc:3930)
| |       ->49.78% (33,554,432B) 0x7280AB: mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (handler.h:2868)
| |         ->49.78% (33,554,432B) 0x72B70B: mysql_alter_table(THD*, char*, char*, st_ha_create_information*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool) (sql_table.cc:8319)
| |           ->49.78% (33,554,432B) 0x81EE4A: Sql_cmd_alter_table::execute(THD*) (sql_alter.cc:313)

出てきた。32MB。


innodb-sort-buffer-size= 64M

$ valgrind --tool=massif bin/mysqld --no-defaults --datadir=./data --performance-schema=0 --innodb-sort-buffer-size=64M

mysql> ALTER TABLE t1 ADD KEY (c1);
Query OK, 0 rows affected (1.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ms_print massif.out.30969 | grep alter
| |     ->88.81% (268,435,456B) 0x99EFC0: ha_innobase::inplace_alter_table(TABLE*, Alter_inplace_info*) (handler0alter.cc:3930)
| |       ->88.81% (268,435,456B) 0x7280AB: mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (handler.h:2868)
| |         ->88.81% (268,435,456B) 0x72B70B: mysql_alter_table(THD*, char*, char*, st_ha_create_information*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool) (sql_table.cc:8319)
| |           ->88.81% (268,435,456B) 0x81EE4A: Sql_cmd_alter_table::execute(THD*) (sql_alter.cc:313)

256MB。innodb-sort-buffer-size= 8Mのときの8倍になっているのは良いとして、innodb-sort-buffer-sizeそのものの4倍確保してる？


innodb-sort-buffer-size= 8Mにして、2カラムの複合インデックス。

$ valgrind --tool=massif bin/mysqld --no-defaults --datadir=./data --performance-schema=0 --innodb-sort-buffer-size=8M

mysql> ALTER TABLE t1 ADD KEY (c1, c2);
Query OK, 0 rows affected (0.71 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ms_print massif.out.31031 | grep alter
| |     ->49.78% (33,554,432B) 0x99EFC0: ha_innobase::inplace_alter_table(TABLE*, Alter_inplace_info*) (handler0alter.cc:3930)
| |       ->49.78% (33,554,432B) 0x7280AB: mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (handler.h:2868)
| |         ->49.78% (33,554,432B) 0x72B70B: mysql_alter_table(THD*, char*, char*, st_ha_create_information*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool) (sql_table.cc:8319)
| |           ->49.78% (33,554,432B) 0x81EE4A: Sql_cmd_alter_table::execute(THD*) (sql_alter.cc:313)

複合インデックスにしても変わらないぽい。


同じく、単一カラムのインデックスを一気に2つ作成。

$ valgrind --tool=massif bin/mysqld --no-defaults --datadir=./data --performance-schema=0 --innodb-sort-buffer-size=8M

mysql> ALTER TABLE t1 ADD KEY (c1), ADD KEY (c2);
Query OK, 0 rows affected (0.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ms_print massif.out.31095 | grep alter
| |     ->66.45% (67,108,864B) 0x99EFC0: ha_innobase::inplace_alter_table(TABLE*, Alter_inplace_info*) (handler0alter.cc:3930)
| |       ->66.45% (67,108,864B) 0x7280AB: mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (handler.h:2868)
| |         ->66.45% (67,108,864B) 0x72B70B: mysql_alter_table(THD*, char*, char*, st_ha_create_information*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool) (sql_table.cc:8319)
| |           ->66.45% (67,108,864B) 0x81EE4A: Sql_cmd_alter_table::execute(THD*) (sql_alter.cc:313)

これはシンプルに倍になった。

マニュアルをよく読んでみたら、

(6 /*FTS_NUM_AUX_INDEX*/ *
(3*@@global.innodb_sort_buffer_size) + 2 * (
@@global.innodb_sort_buffer_size/dict_index_get_min_size(index)*/)
* 8 /*64-bit sizeof *buf->tuples*/")

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

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

まあなんとなく4倍くらいでおかしくなさそうなアレだった。納得。

MariaDB 10.0のSEQUENCEストレージエンジンを試してみる

というか、MariaDB 10.1.0がAlphaリリースされたのでそれを試したついでなので、実際にはMariaDB 10.1のSEQUENCEストレージエンジンを使ってみました。

MariaDB [d1]> SELECT @@version;
+--------------------+
| @@version          |
+--------------------+
| 10.1.0-MariaDB-log |
+--------------------+
1 row in set (0.00 sec)

MariaDB [d1]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| TEST_SQL_DISCOVERY | YES     | Minimal engine to test table discovery via sql statements                  | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| CONNECT            | YES     | Management of External Data (SQL/MED), including many file formats         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| SPHINX             | YES     | Sphinx storage engine 2.1.5-release                                        | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                             | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
14 rows in set (0.00 sec)

うぇーい(＾ω＾)

MariaDB [d1]> CREATE TABLE t1 (num int) Engine= SEQUENCE;
ERROR 1005 (HY000): Can't create table `d1`.`t1` (errno: 131 "Command not supported by database")

あれ、なんか違うらしい。
確かに前(10.0に入るって時に)なんかちょっと見た気がするけど、Oracleのシーケンステーブル(?)とかとは根本的に違う何からしいんだよね確か。

おとなしくマニュアル読む。 https://mariadb.com/kb/en/mariadb/mariadb-documentation/mariadb-storage-engines/sequence/


使い方はこうらしい。

MariaDB [d1]> SELECT * FROM seq_1_to_12;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  12 |
+-----+
12 rows in set (0.00 sec)

MariaDB [d1]> SELECT seq FROM seq_10_to_20_step_2;
+-----+
| seq |
+-----+
|  10 |
|  12 |
|  14 |
|  16 |
|  18 |
|  20 |
+-----+
6 rows in set (0.00 sec)

お、おう。連番が欲しい時に便利そうだねってか、偶数だけ欲しい時にWHERE MOD(c1, 2) = 0とかしなくてもJOINすれば良いように…とかそんなかんじ？（思い付かない

MariaDB [d1]> SELECT seq FROM seq_1_to_99999999999999999999999999999999 ORDER BY seq DESC LIMIT 3;
+----------------------+
| seq                  |
+----------------------+
| 18446744073709551615 |
| 18446744073709551614 |
| 18446744073709551613 |
+----------------------+
3 rows in set (0.00 sec)

2 ^ 64 - 1までいけるっぽい。負値はダメ。

MariaDB [d1]> SELECT * FROM seq_10_to_1;
+-----+
| seq |
+-----+
|  10 |
|   9 |
|   8 |
|   7 |
|   6 |
|   5 |
|   4 |
|   3 |
|   2 |
|   1 |
+-----+
10 rows in set (0.00 sec)

MariaDB [d1]> SELECT * FROM seq_10_to_1_step_2;
+-----+
| seq |
+-----+
|   9 |
|   7 |
|   5 |
|   3 |
|   1 |
+-----+
5 rows in set (0.00 sec)

頭の方が数字が大きければ降順っぽく。
ただし、seq_1_to_10_step_2 は 「先の値」の1から2ずつインクリメントするのに対して、seq_10_to_1_step_2は「先の値」の10からデクリメントしてるわけではないので、たぶん単にseq_1_to_10_step_2をひっくり返してるだけ。

MariaDB [d1]> explain SELECT * FROM seq_1_to_10_step_2;
+------+-------------+--------------------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table              | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+--------------------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | seq_1_to_10_step_2 | index | NULL          | PRIMARY | 8       | NULL |    5 | Using index |
+------+-------------+--------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [d1]> explain SELECT * FROM seq_10_to_1_step_2;
+------+-------------+--------------------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table              | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+--------------------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | seq_10_to_1_step_2 | ALL  | NULL          | NULL | NULL    | NULL |    5 |       |
+------+-------------+--------------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

ちゃんとEXPLAIN取れるところは芸が細かいというかなんと言うか。

MariaDB [d1]> SELECT seq, CONCAT(IF(fizz.seq IS NULL, '', 'fizz'), IF(buzz.seq IS NULL, '', 'buzz')) AS str FROM seq_1_to_100 AS main LEFT JOIN seq_0_to_100_step_3 AS fizz USING(seq) LEFT JOIN seq_0_to_100_step_5 AS buzz USING(seq);
+-----+----------+
| seq | str      |
+-----+----------+
|   1 |          |
|   2 |          |
|   3 | fizz     |
|   4 |          |
|   5 | buzz     |
|   6 | fizz     |
|   7 |          |
|   8 |          |
|   9 | fizz     |
|  10 | buzz     |
|  11 |          |
|  12 | fizz     |
|  13 |          |
|  14 |          |
|  15 | fizzbuzz |
..
| 100 | buzz     |
+-----+----------+
100 rows in set (0.00 sec)

FIZZBUZZしてみたけど、IF演算子使うならなんでもいいやね。。