2014年8月25日月曜日

LOAD DATA LOCAL INFILE .. REPLACE INTO ..の闇

LOAD DATA *LOCAL* INFILEはIGNOREキーワードを指定しなくてもエラーをWarningにフォールバックしてくれる。と思っていた。

With LOAD DATA LOCAL INFILE, data-interpretation and duplicate-key errors become warnings and the operation continues because the server has no way to stop transmission of the file in the middle of the operation. For duplicate-key errors, this is the same as if IGNORE is specified. IGNORE is explained further later in this section.
Treatment of empty or incorrect field values differs from that just described if the SQL mode is set to a restrictive value. For example, if sql_mode='TRADITIONAL, conversion of an empty value or a value such as 'x' for a numeric column results in an error, not conversion to 0. (With LOCAL, warnings occur rather than errors, even with a restrictive sql_mode value, because the server has no way to stop transmission of the file in the middle of the operation.)
http://dev.mysql.com/doc/refman/5.6/en/load-data.html

( ´-`).oO(ER_DUP_ENTRYだけをハンドルするようにも見えれば、ER_WARN_TOO_MANY_RECORDSやER_WARN_TOO_FEW_RECORDSもハンドルしてくれるようにも見える。。

ハンドルの仕方の説明も、ErrorではなくWarningにフォールバックするようにも見えれば、IGNOREと同じように *握りつぶした後にWarningだけ出す* ようにも見える。




そのへんの挙動で毛玉氏がハマっていたようなので、試してみた。


$ echo -e "1\tone" > /tmp/test

mysql56> CREATE TABLE t1 (num int);
Query OK, 0 rows affected (0.07 sec)

カラムの数が合わないテーブルとtsvファイルを用意。


mysql56> SELECT @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql56> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql56> TRUNCATE t1;
Query OK, 0 rows affected (0.03 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql56> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

STRICTでないsql_modeな場合、両方ともWarningで出力される。


mysql56> SELECT @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql56> show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1;
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns

STRICTなsql_modeでLOAD DATA LOCAL INFILE .. *REPLACE* INTO .. の場合は、エラーになってアボートする。

ざっと読んだ感じ、LOAD DATA LOCAL INFILEの時はエラー(やワーニング)を握りつぶしてから *最後に* その内容をWarningとして出力するのに対し、LOAD DATA LOCAL INFILE .. REPLACE INTO ..のときは最初からWarningとして扱っているので、STRICT_TRANS_TABLESに引っかかってエラーに昇格されている。


318   /* We can't give an error in the middle when using LOCAL files */
 319   if (read_file_from_client && handle_duplicates == DUP_ERROR)
 320     ignore= 1;

REPLACEなしのLOAD DATA LOCAL INFILEの時はここでignore= 1(エラーやワーニングを握りつぶしてから *最後に* その内容をWarningとして出力)をセットする。


Breakpoint 1, mysql_load (thd=0x36c9430, ex=0x7f217c373170, table_list=0x7f217c373200, fields_vars=..., set_fields=...,
    set_values=..., handle_duplicates=DUP_ERROR, ignore=false, read_file_from_client=true)
    at /home/yoku0825/mysql-5.6.20/sql/sql_load.cc:190
190     {
(gdb) p thd->query_string->string->str
$3 = 0x7f217c373080 "LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1"

Breakpoint 1, mysql_load (thd=0x36c9430, ex=0x7f217c373180, table_list=0x7f217c373210, fields_vars=..., set_fields=...,
    set_values=..., handle_duplicates=DUP_REPLACE, ignore=false, read_file_from_client=true)
    at /home/yoku0825/mysql-5.6.20/sql/sql_load.cc:190
190     {
(gdb) p thd->query_string->string->str
$2 = 0x7f217c373080 "LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1"

REPLACEありの場合、handle_duplicates= DUP_REPLACEでこの箇所が呼ばれるため、ignore= 1にならない。

これがドキュメント上のバグ(REPLACEキーワードを指定した場合はエラーハンドルの仕方が更に変わる)なのか、REPLACEキーワードがあった時のエラーハンドルの不備なのか(sql/sql_load.cc:319 の条件文が (read_file_from_client && (handle_duplicates == DUP_ERROR || handle_duplicates == DUP_REPLACE)) でなければならないのか)は知らない。

とりあえずドキュメント上のバグとしてレポートはした。。

http://bugs.mysql.com/bug.php?id=73654

2014年8月7日木曜日

Percona ServerでMroongaの./configureに失敗したら

Percona Server 5.6.19にMroonga(故あってGroonga 3.1.0のnightly + Mroonga 3.10だけど)を載せようとしたら、configureスクリプトが転けた。


$ ./configure CFLAGS=-O3 CXXFLAGS=-O3 PKG_CONFIG_PATH=/usr/groonga/3.1.0.20131209/lib/pkgconfig --with-mysql-source=/usr/local/src/percona-server-5.6.19-67.0 --with-mysql-config=/usr/local/percona5619/bin/mysql_config
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
..
checking mysql source... yes
checking mysql_config... /usr/local/percona5619/bin/mysql_config
checking for libmysqlservices.a directory... configure: error: libmysqlservices.a is not found in </usr/local/percona5619/lib/mysql/> and </usr/local/percona5619/lib/mysql/mysql/>

libmysqlservices.aが見つからないんだって。


$ ll /usr/local/percona5619/lib/libmysqlservices.a
-rw-r--r-- 1 root root 15662  7月  1 18:20 /usr/local/percona5619/lib/libmysqlservices.a

あるんだけどなぁ…ん? 俺なんかこれ知ってる気がしてきた。
ああ、これかな。。

日々の覚書: Percona Serverのmysql_configが変?

Bug #1099681 “mysql_config returns wrong path” : Bugs : Percona Server


$ /usr/local/percona5619/bin/mysql_config --variable=pkglibdir
/usr/local/percona5619/lib/mysql

$ ll /usr/local/percona5619/lib/mysql
合計 1372
lrwxrwxrwx 1 root root      16  8月  7 12:10 libjemalloc.so -> libjemalloc.so.1
-rwxr-xr-x 1 root root 1395722  7月  1 18:26 libjemalloc.so.1
drwxr-xr-x 2 root root    4096  8月  7 16:32 plugin

OK大正解。
Mroongaのconfigureスクリプトはlibmysqlservices.aのありかをmysql_config --variable=pkglibdirから探すから…ってこれ、こんなことをだいぶ前にgroonga-devでも言ったような気がする。

Feature #1730: [groonga-dev,01335] mysql 5.6.11でmroonga 3.03をビルドするとエラー - Mroonga - Groonga issues!


…なるほどなるほど。
というわけでconfigureスクリプトをほげって何とかした。


$ diff -c configure.orig configure
diff -c configure.orig configure
*** configure.orig      Thu Aug  7 17:18:35 2014
--- configure   Thu Aug  7 17:18:45 2014
***************
*** 18363,18369 ****
      *)
          { $as_echo "$as_me:${as_lineno-$LINENO}: checking for libmysqlservices.a directory" >&5
  $as_echo_n "checking for libmysqlservices.a directory... " >&6; }
!         pkglibdir="$($ac_mysql_config --variable=pkglibdir)"
          mysql_build_libservices_dir="${MYSQL_BUILD_DIR}/libservices"
          if test -f "${mysql_build_libservices_dir}/libmysqlservices.a"; then
            mysql_services_lib_dir="${mysql_build_libservices_dir}"
--- 18363,18369 ----
      *)
          { $as_echo "$as_me:${as_lineno-$LINENO}: checking for libmysqlservices.a directory" >&5
  $as_echo_n "checking for libmysqlservices.a directory... " >&6; }
!         pkglibdir="/usr/local/percona5619/lib"
          mysql_build_libservices_dir="${MYSQL_BUILD_DIR}/libservices"
          if test -f "${mysql_build_libservices_dir}/libmysqlservices.a"; then
            mysql_services_lib_dir="${mysql_build_libservices_dir}"

早く直らないかなこれってか忘れられてんじゃないかな。。

2014年7月30日水曜日

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】
早速教えていただいた :)



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

2014年7月17日木曜日

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

2014年7月15日火曜日

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になった。

2014年7月14日月曜日

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


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





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

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

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



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

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