かみぽわーる

kamipo's blog

ISUCON6予選にチーム「それぞれの椅子」で参加した

ISUCON6予選1日目にチーム「それぞれの椅子(kamipo, Yappo, kan)」で参加した(kanさんは予定があってリモートからの友情出演)。

結果からいうとスコア15万ぐらいで安定したとこでもう時間ないから触るのやめて再起動チェックだけやって終わろうって再起動したら3万ぐらいまでスコア下がって原因特定するには時間なさすぎて死んだ(最後6万ぐらいまでは回復したっぽい)。俺の屍を越えてゆく者へ言えることは、不測の事態にそなえて再起動チェックは時間に余裕をもって何度かやるべきということです。

結果は残念だったけど今回はとても楽しめた。これまでのISUCONではせっかく声をかけて集まってもらったのだからみんなのパフォーマンスを引き出さなければというプレッシャーがハンパなかったけど、みんな大人なんだから自分のパフォーマンスぐらい自分で発揮するやろって気持ちでやれたのがよかった。やっぽさん当日の朝6時ぐらいまで飲んでたけどみそ汁飲んだら復活してたし。

今回のお題ははてなダイアリーを模したキーワードリンクするWebアプリ(+はてなスターとスパム判定が別サービスで立ってるマイクロサービス構成)。キーワードリンクしたHTMLを生成する処理がボトルネックのほぼすべてといっていいお題で、この処理をいかに高速化するか(もしくはオフロードするか)という問題だと理解しました。キーワードリンクは登録されているエントリ名(keyword)の最長マッチが期待されていて、チェッカーは既存のエントリ名をプレフィックスにもつkeywordをPOSTしてきて最長マッチが正しく考慮されているかをチェックしているようでした。

まず我がチームのとった戦略はキーワードマッチのためのregexp生成をRegexp::Assembleを使う&キャッシュすること。これで3万ぐらいまではいったけど、ここからどう速くするかは結構悩んだ。他に多少無駄なところ(高速化の余地)があってもキーワードリンク生成を速くできる目処が立たない限り効果が誤差レベルなので。

このregexp生成のキャッシュ時に確認できたことは、キーワードの最長マッチは常に正確じゃなくてもチェッカーがPOSTしてくる既存のkeywordより長いものが考慮されていれば減点はないということ。そこで最終的には、初期状態からある既存のkeywordに対して置換の前処理(html_escapeの前)をキャッシュすることですべてのページのキーワードリンク処理を高速化し、チェッカーに怒られるkeywordは後で個別に対応するという戦略でキャッシュいれてみたら予想通りスコア上がって15万ぐらいで安定するようになった。残り時間がもう30分を切ってたので個別対応を入れるのは諦めて最後に再起動チェックだけしようって再起動したらスコア3万。原因特定をするにも残り10分切ってたのでenqueueガチャで最後少し回復させるのが限界でフィニッシュ。

再起動チェックする時間が足りなかったのは今後の教訓として、最後まで楽しく取り組めたのは自分の心構え以上に、ボトルネックはわかってるのに高速化する方法が簡単じゃない良い問題だったからだと思う。また、ISUCON5予選と違ってデータベースにボトルネックを寄せていないことは今回学生枠が多いISUCON6にとっても取り組みやすい問題だったのではないかと思います。

とりあえずいつでも飲みにいけます🍺🍶

f:id:kamipo:20160918181234p:plain

入れる時間がなかった人の手による温かみのある個別対応

Comparing master...exclude_keyword · kamipo/isucon6q · GitHub

MySQL 5.7のONLY_FULL_GROUP_BYはちょっと進化してた

このエントリはMySQL Casual Advent Calendar 2015の14日目です。

TL;DR

MySQL 5.7ではデフォルトONLY_FULL_GROUP_BYが有効である。MySQL 5.7.5からONLY_FULL_GROUP_BYが有効のとき

  • GROUP BY句のカラムと関数従属性のあるカラムはSELECT句に書けるようになった😤
  • ORDER BY句のカラムはDISTINCTのカラムリストに含めなければいけなくなった😣
  • ONLY_FULL_GROUP_BYを無効にしなくてもHAVING句のalias拡張が使えるようになった😆

GROUP BY句のカラムと関数従属性のあるカラムはSELECT句に書けるようになった

[mysqlcasual] > CREATE TABLE users (id int unsigned auto_increment primary key, name varchar(255));
Query OK, 0 rows affected (0.03 sec)

[mysqlcasual] > CREATE TABLE friends (user_id int unsigned, friend_id int unsigned);
Query OK, 0 rows affected (0.04 sec)

以下のクエリはu.nameがGROUP BY句に含まれてなくて集合関数にもかけられてないけどf.user_idに関数従属しているので5.7では実行できます!

[mysqlcasual] > SELECT f.user_id, u.name, COUNT(*) FROM friends f JOIN users u ON f.user_id = u.id GROUP BY f.user_id;
Empty set (0.00 sec)

残念ながら5.6ではONLY_FULL_GROUP_BYが有効だとエラーです。

[mysqlcasual] > SELECT f.user_id, u.name, COUNT(*) FROM friends f JOIN users u ON f.user_id = u.id GROUP BY f.user_id;
ERROR 1055 (42000): 'mysqlcasual.u.name' isn't in GROUP BY

ORDER BY句のカラムはDISTINCTのカラムリストに含めなければいけなくなった

とにかくそういうことらしいです。他のが便利になる系なのに対してこれは既存のクエリに影響が出る系なので5.7にアップグレードして既存のクエリに影響があるか確認しておく必要があります。ちなみにActiveRecordというORマッパーは余裕でDISTINCTついてるクエリにORDER BYつくので滅茶苦茶影響がある。とにかくこれを一日も早くマージしてほしい。

github.com

ONLY_FULL_GROUP_BYを無効にしなくてもHAVING句のalias拡張が使えるようになった

これもとにかくそういうことです!

[mysqlcasual] > SELECT user_id, COUNT(*) c FROM friends GROUP BY user_id HAVING c = 1;
Empty set (0.00 sec)

以上、MySQL 5.7のONLY_FULL_GROUP_BYはちょっと進化してました!

MySQL 5.7のoptimizer_switch、derived_mergeとは何ぞや

このエントリはMySQL Casual Advent Calendar 2015の8日目です。

MySQL 5.7.6からoptimizer_switchderived_mergeが追加されデフォルトで有効になっている。基本的にこれはほっといたらだいたいサブクエリが速くなるやつなので気にしなくてもいいんですが、ちょっと非互換があるのでさくっと説明します。

root@localhost [mysqlcasual] > CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.03 sec)

root@localhost [mysqlcasual] > CREATE TABLE t2 (b int);
Query OK, 0 rows affected (0.03 sec)

root@localhost [mysqlcasual] > INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

root@localhost [mysqlcasual] > INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

これまでFROM句のサブクエリは一旦マテリアライズされてから外側のクエリとかWHERE句とかと結合されていた。

root@localhost [mysqlcasual] > SET optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysqlcasual] > EXPLAIN SELECT * FROM t1 JOIN (SELECT * FROM t2) dt;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | PRIMARY     | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                  |
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
3 rows in set, 1 warning (0.00 sec)

これがderived_merge=onだとマテリアライズせずに外側の条件とマージできそうなときはマージされるようになるのだ!

root@localhost [mysqlcasual] > SET optimizer_switch = 'derived_merge=on';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysqlcasual] > EXPLAIN SELECT * FROM t1 JOIN (SELECT * FROM t2) dt;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                  |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

これでサブクエリが多い日も安心!!

しかしその代償に以下の更新クエリが通らなくなった。

root@localhost [mysqlcasual] > UPDATE t2 SET b=1 WHERE b IN (SELECT b FROM (SELECT * FROM t2) dt WHERE b=1);
ERROR 1093 (HY000): You can't specify target table 't2' for update in FROM clause

どうもMySQLにはひとつの更新系クエリ(UPDATE, DELETE)で更新するテーブルと同じテーブルをFROM句の中で両方同時に参照できないという制約があるらしく、いままでマテリアライズされて別テーブルになってたから通ってたクエリが最適化によってマージされてそのまま参照されることでこの制約に引っかかるようになるみたいです。

回避策としては、derived_merge=offにするか、サブクエリをマージできない(マテリアライズされる)クエリに書き換えるとよいです(DISTINCTLIMITをつけるとマージできなくなる)。

root@localhost [mysqlcasual] > UPDATE t2 SET b=1 WHERE b IN (SELECT b FROM (SELECT DISTINCT * FROM t2) dt WHERE b=1);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

root@localhost [mysqlcasual] > SET optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysqlcasual] > UPDATE t2 SET b=1 WHERE b IN (SELECT b FROM (SELECT * FROM t2) dt WHERE b=1);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

じつはVIEWもderived tableの仲間なので、ALGORITHM=MERGEなVIEWもこの制約に引っかかるので注意です。

HomebrewのMySQL 5.7.9でSSL接続するとコケるので気をつけよう

このエントリはMySQL Casual Advent Calendar 2015の3日目です。

で、これです。

github.com

ざっと調べた感じだと、openssl 1.0.1eあたりからDiffie-Hellman (DH) key length 1024bit以上を要求するようになったかなんかで、MySQLが512bitとかいうかなり短いkey lengthを使ってるせいでopensslに怒られる感じらしい。

MySQL 5.7からデフォルトsslオプションが有効なのでRDSとかみたいなSSL接続受け付けてるホストに5.7のクライアントでそらで接続しようとするとコケるっぽい。

$ mysql --help
...
  --ssl               If set to ON, this option enforces that SSL is
                      established before client attempts to authenticate to the
                      server. To disable client SSL capabilities use --ssl=OFF.
                      (Defaults to on; use --skip-ssl to disable.)
...

https://bugs.mysql.com/bug.php?id=77275 を見ると mysql/mysql-server/commit/866b988 で直した的なことが書いてあるけど、Homebrewで入るバイナリだとダメっぽいんで、とりあえずDHを使わないcipher suite(DEFAULTでも大丈夫でした)を指定して回避できるようです。

$ mysql --ssl-cipher='DEFUALT' -u kamipo -h mysql-casual.rds.amazonaws.com

以上、入社エントリ以来ひさしぶりにブログ書いた😊

Treasure Dataに入社しました

近況などをブログに書いたことはなかったんですが、4月からTreasure Dataで働くことになりました。

3月に新しい仕事を探してたタイミングでちょうど声をかけてもらって、他に誘ってくれてるところもあっていろいろ考えたんですけど、今まで自分がやってたWeb屋さんとは結構ちがう専門的なプロダクトが面白そうだったこと、話してみてエンジニアリング上の解決したい課題についてすごく具体的にいろいろ話してくれたので、畑違いな気もするけどやれることは結構ありそうだなとイメージできたので入社することにしました。

あとは声をかけてくれるのが2週間遅かったら他のところに決めちゃってたので、お互いのタイミングが合ってたことで自分が想像していなかった選択肢が生まれたことにも面白さを感じて、まあこれも自分の中のひとつのチャレンジだと思って返事をしたという感じです。

HadoopもFluentdもよく分からんしSlackも全部英語で、これはやばいところに来てしまったなという気持ちでいっぱいではあるけど、自分がこれまでやってたインフラだったり運用的な方面ではやれることあるっていうか無限にやることあってやばいみたいな感じなので、とりあえずめっちゃがんばれそうな気がしています。

とりいそぎ今後ともよろしくお願いします!

SEE ALSO

MySQL と寿司ビール問題

MySQL と Unicode Collation Algorithm (UCA) - かみぽわーる に関連するトピックで、 MySQL には寿司ビール問題というのがある。

これはどういう問題かというと、 MySQLUnicode では binary collation にしてコードポイントで比較しないと🍣と🍺に限らず絵文字が同値判定されるという問題です。

なぜこんな挙動にしたのか、どういうケースのときにこの挙動だとうれしいのか全く理解が及ばないが、残念ながらこの挙動はドキュメントに明記されており、仕様である。

以下に引用しますが、ざっくりいうと

  • collating weight がある文字はそれを使う。ない場合は以下に従う。
  • BMP文字で general collation (xxx_general_ci) の場合、コードポイントを使う。
  • BMP文字で unicode collation (xxx_unicode_ci) の場合、なんかいい感じの計算式で導出する。
  • SMP文字(絵文字とか)の場合、0xfffd REPLACEMENT CHARACTER と同じ weight になる。

とのこと😨。

For all Unicode collations except the “binary” (xxx_bin) collations, MySQL performs a table lookup to find a character's collating weight. This weight can be displayed using the WEIGHT_STRING() function. (See Section 12.5, “String Functions”.) If a character is not in the table (for example, because it is a “new” character), collating weight determination becomes more complex:

  • For BMP characters in general collations (xxx_general_ci), weight = code point.
  • For BMP characters in UCA collations (for example, xxx_unicode_ci and language-specific collations), the following algorithm applies:
    (snip)
  • For supplementary characters in general collations, the weight is the weight for 0xfffd REPLACEMENT CHARACTER. For supplementary characters in UCA 4.0.0 collations, their collating weight is 0xfffd. That is, to MySQL, all supplementary characters are equal to each other, and greater than almost all BMP characters.

http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-sets.html

なので、絵文字を検索で区別する必要がある場合は utf8mb4_bin にするしかないんですが、'ハハ' = 'パパ' 問題を気にしなければもうひとつ方法があることをドキュメント読んでて知りました。

xxx_unicode_ci というのは UCA 4.0.0 というかなり古いと思われる仕様に対する実装で、より新しい UCA 5.2.0 を実装した xxx_unicode_520_ci だとSMP文字にも weight を持っており、 weight がなくてもいい感じの計算式で導出するので 0xfffd REPLACEMENT CHARACTER と同じ weight にはならないと書いてある。

  • For supplementary characters based on UCA versions later than 4.0.0 (for example, xxx_unicode_520_ci), supplementary characters do not necessarily all have the same collation weight. Some have explicit weights from the UCA allkeys.txt file. Others have weights calculated from this algorithm:
    (snip)

http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-sets.html

実際試してみるとこんな感じ。まず区別できない例。

SET NAMES utf8mb4;

DROP TABLE IF EXISTS wishlist_general;
CREATE TABLE wishlist_general (
  item varchar(1)
) COLLATE utf8mb4_general_ci;

DROP TABLE IF EXISTS wishlist_unicode;
CREATE TABLE wishlist_unicode (
  item varchar(1)
) COLLATE utf8mb4_unicode_ci;

INSERT INTO wishlist_general VALUES ('🍣'), ('🍺');
INSERT INTO wishlist_unicode VALUES ('🍣'), ('🍺');

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_general WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FFFD                     |
| 🍺     | FFFD                     |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FFFD                     |
| 🍺     | FFFD                     |
+------+--------------------------+

区別できる例。

SET NAMES utf8mb4;

DROP TABLE IF EXISTS wishlist_unicode_520;
CREATE TABLE wishlist_unicode_520 (
  item varchar(1)
) COLLATE utf8mb4_unicode_520_ci;

DROP TABLE IF EXISTS wishlist_bin;
CREATE TABLE wishlist_bin (
  item varchar(1)
) COLLATE utf8mb4_bin;

INSERT INTO wishlist_unicode_520 VALUES ('🍣'), ('🍺');
INSERT INTO wishlist_bin VALUES ('🍣'), ('🍺');

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode_520 WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FBC3F363                 |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode_520;
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FBC3F363                 |
| 🍺     | FBC3F37A                 |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_bin WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | 01F363                   |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_bin;
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | 01F363                   |
| 🍺     | 01F37A                   |
+------+--------------------------+

とりあえず現状これが仕様だというのは分かったけど、どう考えてもSMP文字を 0xfffd REPLACEMENT CHARACTER と同じ weight にするの現世においてデメリットしかないと思うんで、これがちゃんと区別されるようになるのを願ってやまないです。

MySQL と Unicode Collation Algorithm (UCA)

utf8_unicode_ci に対する日本の開発者の見解 - かみぽわーる で、日本語が分かる人には utf8_unicode_ci のヤバさを感じてもらえたと思うんですけど、この挙動はドキュメントによると UCA というアルゴリズムによるものらしい。

MySQL implements the xxx_unicode_ci collations according to the Unicode Collation Algorithm (UCA) described at http://www.unicode.org/reports/tr10/. The collation uses the version-4.0.0 UCA weight keys: http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt. Currently, the xxx_unicode_ci collations have only partial support for the Unicode Collation Algorithm. Some characters are not supported yet. Also, combining marks are not fully supported. This affects primarily Vietnamese, Yoruba, and some smaller languages such as Navajo. http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-sets.html

これもしかして、 Unicode って日本語のこと分かってない人たちによって作られてて日本語の検索とかなんも考慮されてないんとちゃうんかと思って調べてみたら、 そんなことはまったくなくて MySQL が UCA を部分的にしか実装していないということだった。

UTS #10: Unicode Collation Algorithm によると、 UCA では Multi-Level Comparison といって複数レベルに分けて文字列の比較を行い、各レベルの比較で文字列が一致した場合(tie-breaking level)、次のレベルで比較を行うを繰り返して順序を決定する。L1は大文字小文字アクセント無視(Base Charaters)、L2はアクセントを無視しない(Accents)、L3は大文字小文字を無視しない(Case/Variants)、のような感じである。

MySQL が UCA を部分的にしか実装していないとはどういうことかというと、 allkeys.txt でいうところの Primary weight range (L1比較のためのweight) しか実装していないので、アクセントを区別しないと常用に適さない言語(日本語とか)殺しな挙動になっているということだった。

mysql-5.7.6/strings/ctype-uca.c#L17-L31 からコメントを引用する。

/* 
   UCA (Unicode Collation Algorithm) support. 
   Written by Alexander Barkov <bar@mysql.com>
   
   Currently supports only subset of the full UCA:
   - Only Primary level key comparison
   - Basic Latin letters contraction is implemented
   - Variable weighting is done for Non-ignorable option
   
   Features that are not implemented yet:
   - No Normalization From D is done
     + No decomposition is done
     + No Thai/Lao orderding is done
   - No combining marks processing is done
*/

アクセントを区別しないと常用に適さない言語が日本語以外にどれぐらいあるのか言語に詳しくないので分からないけど、これを理由に以下のコミットをrevertできたりしないですかね。

参考