isuconのために

TTPしたいこと

Gotanda.pm #2

who am i

@kfly8
Application engineer
- like Perl, JavaScript
- develop games

俺の彼女が２人とも可愛すぎる！ http://jp.apps.gree.net/ja/60641

ステーションメモリーズ！ http://ekimemo.lap.colopl.jp

今日の話は自称isuconファンの話です

isucon4

申し込みましたか？

http://isucon.net/archives/39780986.html

まだの方 5秒待ちます

http://isucon.net/archives/39780986.html

唐突ですが

Question

「isuconで100万円をとる銀の弾丸はある？」

Answer

「@kazeburoさん、@fujiwaraさんと同じチームになる？！」

...

まねしよう!!

TTP

徹底的にパkる

先輩の知恵を拝借させてもらう

5位以内でも賞金が出るらしい

制限時間内に基本的なことが出来たら、
もしかしたら、、、

もしかしたら!!

まず初めに感謝したいこと

感謝(1)

isucon

感謝(2)

ウェブアプリケーションのプロファイリングの仕方
- https://dl.dropboxusercontent.com/u/224433/YAPC2012/index.html
Explain
- http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
SQLアンチパターン
- http://www.amazon.co.jp/dp/4873115892

isuconによって多くの素敵な知見が
得られています

ありがとうございます!!

アジェンダ

事前準備
当日準備
今日やらないこと
TTPしたい知見

事前準備

[x] 参加申し込み
- http://isucon.net/archives/39780986.html
[ ] レギュレーション良く読む
- http://isucon.net/archives/39979344.html
- 採点方式(減点式？加点式？) (制限 1秒以内に返せばいい?)
- 環境 (AWS, OSなど)

参加申し込みはさっきしたはず...

当日準備

[ ] リポジトリ管理
- appicationと設定ファイルをgit管理にする
[ ] 公開鍵登録
[ ] hosts登録
[ ] ベンチマークツール
- 仕様、アクセスの傾向
[ ] プロファイリング
- fujiwaraさんの発表
- アクセスの傾向、重いページ、クエリ実行計画、etc...

今日やらないこと

middlewareの調整
ネットワークの調整
nginx luaとか
golangとか

TTPしたい知見

[ ] SELECT * やめる
[ ] N+1問題
[ ] add index / drop index
[ ] covering index
[ ] count
[ ] Cache

TTPしたい知見

SELECT * やめる
N+1問題
- for { query } -> join? IN句? Perlで組み立て
add index / drop index
covering index
- IN句, subquery
count
- counter table / MAX(id)
Cache
- memcached, redis / Query cache, Template cache

もうこれで瞬間的にコードが頭に浮かぶなら
TTPできている!!たぶん。

`SELECT *` やめる

BEFORE
```
  SELECT * FROM memos WHERE ...
```

AFTER

  SELECT id, content, created_at FROM memos WHERE ...

不要なトラフィック発生しないようにする

ORM 使ってても、同様

schemaから自動で設定してくれているので、明示的に利用したい時使う

my $memos = $teng->search(memos => { ... }, {
    columns => [qw/id content created_at/],
});

https://github.com/cho45/p5-Teng/blob/master/lib/Teng.pm#L486

パフォーマンスだけじゃなく、運用上もやだ

SQLアンチパターン暗黙の列

運用時にADD COLUMNした時に、* だとアプリケーションの挙動に影響でる

大きなデータカラムは

抜粋できないか考える

SELECT id, SUBSTRING_INDEX(content, "\n", 1) AS title, created_at FROM memos WHERE ...

参照頻度が高いなら事前に必要最低限のカラムを用意
```
ALTER TABLE memos ADD COLUNN title ..;
```

N+1問題

for my $memo (@$memos) {
    $memo->{username} = $self->dbh->select_one(
        'SELECT username FROM users WHERE id=?',
        $memo->{user},
    );
}

実装しやすい（良い意味でも悪い意味でも）
Nの大きさに線形比例して、性能劣化

対策(1) JOIN

    SELECT
        memos.id, memos.content, memos.created_at,
        users.username
    FROM
        memos, users
    WHERE
        memos.user_id = users.id
        ...

対策(2) 非正規化

memosに必要なデータを入れておく非正規化
usernameの更新に注意
こそ泥チューニング

    INSERT memos (content, username) VALUES ($content, $user.username);

    SELECT
        id, content, created_at,
        username
    FROM
        memos
    WHERE
        ...

対策(3) Perlで組み立て

    my $memos = $self->dbh->select_all(
        sprintf("SELECT id, content, username, created_at FROM memos WHERE is_private=0 ORDER BY created_at DESC, id DESC LIMIT 100 OFFSET %d", $page * 100)
    );

    my @user_ids = uniq map { $_->{user} } @$memos;
    my %user_id2username = @{$self->dbh->selectcol_arrayref(
        qq/SELECT id, username FROM users WHERE id IN ( @{[join ',', ('?')x@user_ids]} )/,
        {Columns => [1,2]},
        @user_ids,
    )};
    # -> ( 1 => 'username1', 2 => 'username2' )

組データの、selectcol_arrayref のColumns => [1,2] からの array -> hash は(bk) idiom
documentに載ってる

余談 selectall_hashrefも好きだ！

my $user_id2data = $self->dbh->selectall_hashref(
    qq/SELECT id, username, created_at FROM users WHERE id IN ( @{[join ',', ('?')x@user_ids]} )/,
    'id',
    {},
    @user_ids,
);
# ->
{
    1 => { id => 1, username => 'username1', created_at => '2014-09-17 19:00:00' },
    2 => { id => 2, username => 'username1', created_at => '2014-09-17 19:00:00' },
}

add index

drop index

実行計画をカジュアルに見て、明らかに変なのをあぶり出す

use DBIx::QueryLog
DBIx::QueryLog->explain(1);

# フルテーブルスキャーン！
tail -f access.log | grep -B 4 ALL 

# rowsもあわせてみたい
tail -f access.log | grep -B 4 filesort
tail -f access.log | grep -B 4 temporary

[2014-09-17T01:06:13] [DBIx::Sunny::db] [0.003505] SELECT * FROM memos WHERE is_private=0 ORDER BY created_at DESC, id DESC LIMIT 100 at /Users/kfly8/lab/isucon3/qualifier/webapp/perl/local/lib/perl5/DBIx/Sunny.pm line 142
.--------------------------------------------------------------------------------------------------------------.
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | memos | ALL  | NULL          | NULL | NULL    | NULL |  202 | Using where; Using filesort |

もっとくわしく

アプリケーションでの占有時間を確認
一発のクエリが軽くても、頻度次第でよろしくない
pt-query-digest && tcpdump とかクエリの解析ツールを使う

Explain して想定通りにindexが使われているか、都度確認

わすれがち、やりがちなこと

必要だったインデックスが
コードの変化でゴミになることもある

covering index

データが入っているleaf nodeにアクセスしないように、クエリの実行計画を立てる
InnoDBがクラスタインデックスの構造だから預かれる恩恵

kazeburoさんの資料 49pageから参照
これ以上ないくらい分かりやすい!!!!!!!

isucon3 qualifier での例

よくないクエリ

mysql> EXPLAIN SELECT * FROM memos WHERE is_private = 0 ORDER BY created_at LIMIT 100 OFFSET 10000;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | memos | ALL  | NULL          | NULL | NULL    | NULL |  202 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

元々のスキーマ

CREATE TABLE `memos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `content` text,
  `is_private` tinyint(4) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

ADD INDEX

ALTER TABLE memos ADD INDEX is_private_created_at_idx (is_private, created_at);

filesortしないで済むようになった

mysql> EXPLAIN SELECT * FROM memos WHERE is_private = 0 ORDER BY created_at LIMIT 100 OFFSET 10000;
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys             | key                       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | memos | ref  | is_private_created_at_idx | is_private_created_at_idx | 1       | const |   52 | Using where |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

is_private_created_at_idx INDEX で参照できた is_private,created_at,id 以外のデータにアクセスしないようにする
= Covering Index

mysql> EXPLAIN SELECT id,is_private,created_at FROM memos WHERE is_private = 0 ORDER BY created_at LIMIT 100 OFFSET 10000;
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys             | key                       | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | memos | ref  | is_private_created_at_idx | is_private_created_at_idx | 1       | const |   52 | Using where; Using index |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+--------------------------+

idだけの指定でも、もちろんusing index

mysql> EXPLAIN SELECT id FROM memos WHERE is_private = 0 ORDER BY created_at LIMIT 100 OFFSET 10000;
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys             | key                       | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | memos | ref  | is_private_created_at_idx | is_private_created_at_idx | 1       | const |   52 | Using where; Using index |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+--------------------------+

例えば、userとか使っているINDEX以外のカラム足しちゃうとダメ(Using indexが消えている)

mysql> EXPLAIN SELECT id,user FROM memos WHERE is_private = 0 ORDER BY created_at LIMIT 100 OFFSET 10000;
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys             | key                       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | memos | ref  | is_private_created_at_idx | is_private_created_at_idx | 1       | const |   52 | Using where |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+-------------+

逆に言うと、userデータがどうしても必要なら、INDEXは大きくなるが、わざとINDEXに加えるというのもありえるが、、
けど、今回の例では、他にも必要なデータがある

IN句???

mysql> EXPLAIN SELECT memos.id, memos.content FROM memos WHERE id IN (SELECT id FROM memos WHERE is_private = 0 ORDER BY created_at LIMIT 100 OFFSET 10000);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

ぬーん

PerlでIN句

my @public_memos = $self->dbh->select_all(q/
    SELECT id FROM memos WHERE is_private = 0 ORDER BY created_at, id DESC LIMIT 100
/);
my $memos = $self->dbh->select_all(q/
    SELECT id, title, created_at FROM memos WHERE id IN (?)
/, \@public_memos);

分かりやすい
複数台構成になったときにも都合いい

けどまだTTPできてない

SELF JOIN

mysql> EXPLAIN SELECT memos.id, memos.content FROM memos, (SELECT id FROM memos WHERE is_private = 0 ORDER BY created_at LIMIT 100 OFFSET 10000) AS t WHERE memos.id = t.id;
+----+-------------+------------+--------+---------------------------+---------------------------+---------+-------+------+--------------------------+
| id | select_type | table      | type   | possible_keys             | key                       | key_len | ref   | rows | Extra                    |
+----+-------------+------------+--------+---------------------------+---------------------------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                      | NULL                      | NULL    | NULL  |   52 | NULL                     |
|  1 | PRIMARY     | memos      | eq_ref | PRIMARY                   | PRIMARY                   | 4       | t.id  |    1 | NULL                     |
|  2 | DERIVED     | memos      | ref    | is_private_created_at_idx | is_private_created_at_idx | 1       | const |   52 | Using where; Using index |
+----+-------------+------------+--------+---------------------------+---------------------------+---------+-------+------+--------------------------+

分かりやすい
複数台構成になったときにも都合いい
分かりやすい

余談

SQL::Maker, SQL::Abstract

スカラリファレンスを値とすると、SQLとして展開してくれる

SQL::Maker::Select->new->add_select(\'COUNT(*) AS cnt')

SQL::Maker
- https://github.com/tokuhirom/SQL-Maker/blob/master/lib/SQL/Maker/Select.pm#L138
SQL::Abstract
- http://perl-users.jp/articles/advent-calendar/2009/hacker/06.html

SQL::Maker subquery example(1)

my $builder = SQL::Maker->new(driver => 'mysql');
my $stmt = $builder->new_select
         ->add_select('id')->add_select('title')->add_select('created_at')
         ->add_from('memos')
         ->add_from(\q/(SELECT id FROM memos LIMIT 100) AS t/) # !!
         ->add_where('memos.id' => 't.id');

SQL::Maker subquery example(2)

my $builder  = SQL::Maker->new(driver => 'mysql');
my $sub_stmt = $builder->new_select
            ->add_select('id')
            ->add_from('memos')
            ->limit(100);

my $stmt = $builder->new_select
         ->add_select('id')->add_select('title')->add_select('created_at')
         ->add_from('memos')
         ->add_from($sub_stmt, 't'/) # !!
         ->add_where('memos.id' => 't.id');

https://github.com/tokuhirom/SQL-Maker/blob/master/lib/SQL/Maker/Select.pm#L376

SQL::Maker subquery example(3)

use SQL::Maker;
SQL::Maker->load_plugin('JoinSelect'); # !!!!

my $builder = SQL::Maker->new(driver => 'mysql', new_line => ' ');
my ($sql, @binds) = $builder->join_select(
    user_item => [
        \q/(SELECT user_id FROM user_item) AS t/ => {'user_item.user_id' => 't.user_id' },
    ],
    ['*'],
    {
        'user_item.user_id' => 1,
    },
);

is $sql,
    'SELECT * FROM `user_item` INNER JOIN (SELECT user_id FROM user_item) AS t ON `user_item`.`user_id` = `t`.`user_id` WHERE (`user_item`.`user_id` = ?)';
is $binds[0], 1;

https://github.com/Songmu/p5-SQL-Maker-Plugin-JoinSelect

余談(2)

WHERE句なら、add_where_rawのが良さそう
https://github.com/tokuhirom/SQL-Maker/blob/master/lib/SQL/Maker/Select.pm#L282

count

# フルテーブルスキャーン
[2014-09-17T11:48:43] [DBIx::Sunny::db] [0.000952] SELECT count(*) FROM memos WHERE is_private=0 at /Users/kfly8/lab/isucon3/qualifier/webapp/perl/local/lib/perl5/DBIx/Sunny.pm line 126
.----------------------------------------------------------------------------------------------.
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | memos | ALL  | NULL          | NULL | NULL    | NULL |  202 | Using where |
'----+-------------+-------+------+---------------+------+---------+------+------+-------------'`

MAX(id)

@kazeburoさんの資料参照 page99

SELECT MAX(id) FROM public_memos;

counter table

単純 <-> insert cost
https://github.com/karupanerura/isucon2013-yosen/commit/6d2081d23328830d351daa953ae3d48d3a0dc496

countの細かい話
- http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html

Cache

Query Cache
Memcached
Redis
etc..

生存時間に注意しつつ、
効率的なキャッシュを作る...

cacheの知見じゃなくて、
伝えたいこと

ファンとして伝えたい
isuconの名シーン...

2012

isucon2

fujiwara組

template のcache
busy loopを用意して、そこでfurl
furl先でredisにcache
調整の仕方もすごい
- http://d.hatena.ne.jp/sfujiwara/20121103/1351959629
- https://github.com/kayac/isucon2/commits/redis
isuconの制限時間内におさめてすごい

ほんとすごい

まとめ(1)

SELECT * やめる
N+1問題 -> for { query } -> join? IN句? Perlで組み立て
add index / drop index
covering index -> IN句の実装速いけど、サブクエリだって実装簡単
count -> counter table / Max(id)
Cache -> memcached, redis / Query cache, Template cache

まとめ(2)

isuconたのしい

まとめ(3)

これだけ話してRDB使ってない
NoSQLアプリとかありえますよね？！

ご清聴ありがとうございました。

株式会社モバイルファクトリーは、
開発好きなエンジニアを求めています。

isucon ttp

By kfly8

isucon ttp

talk at #gotandapm. http://gotanda-pm.connpass.com/event/8404/

5,475

kfly8

kfly8

isuconのために

TTPしたいこと

who am i

isucon4

感謝(1)

感謝(2)

アジェンダ

事前準備

当日準備

今日やらないこと

TTPしたい知見

TTPしたい知見

SELECT * やめる

ORM 使ってても、同様

大きなデータカラムは

抜粋できないか考える

N+1問題

対策(1) JOIN

対策(2) 非正規化

対策(3) Perlで組み立て

余談 selectall_hashrefも好きだ！

add index

drop index

実行計画をカジュアルに見て、明らかに変なのをあぶり出す

もっとくわしく

関連

covering index

isucon3 qualifier での例

SQL::Maker, SQL::Abstract

SQL::Maker subquery example(1)

SQL::Maker subquery example(2)

SQL::Maker subquery example(3)

余談(2)

count

MAX(id)

counter table

Cache

まとめ(1)

まとめ(2)

まとめ(3)

ご清聴ありがとうございました。

isucon ttp

More from kfly8

`SELECT *` やめる