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)

感謝(2)

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

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

アジェンダ

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

事前準備

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

当日準備

  • [ ] リポジトリ管理
    • appicationと設定ファイルをgit管理にする
  • [ ] 公開鍵登録
  • [ ] hosts登録
  • [ ] ベンチマークツール
    • 仕様、アクセスの傾向
  • [ ] プロファイリング

今日やらないこと

  • 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が使われているか、都度確認

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

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

関連

SQLアンチパターン インデックスショットガン

  • 雑に言うと「インデックスガンガン貼ってこうぜ！」っていうアンチパターン

covering index

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

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 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');

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;

余談(2)

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)

SELECT MAX(id) FROM public_memos;

counter table

Cache

  • Query Cache
  • Memcached
  • Redis
  • etc..

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

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

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

2012

isucon2

fujiwara組

ほんとすごい

まとめ(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アプリとかありえますよね？！

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

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