isuconのために
TTPしたいこと
Gotanda.pm #2
who am i
- @kfly8
- Application engineer
- like Perl, JavaScript
- develop games
今日の話は 自称isuconファンの話です
まだの方 5秒待ちます
唐突ですが
Question
「isuconで100万円をとる銀の弾丸はある？」
Answer
「@kazeburoさん、@fujiwaraさんと同じチームになる？！」
...
!!
まねしよう!!
TTP
徹底的にパkる
先輩の知恵を拝借させてもらう
5位以内でも賞金が出るらしい
制限時間内に基本的なことが出来たら、
もしかしたら、、、
もしかしたら!!
まず初めに感謝したいこと
感謝(2)
- ウェブアプリケーションのプロファイリングの仕方
- Explain
- SQLアンチパターン
isuconによって多くの素敵な知見が
得られています
ありがとうございます!!
アジェンダ
- 事前準備
- 当日準備
- 今日やらないこと
- TTPしたい知見
事前準備
- [x] 参加申し込み
- [ ] レギュレーション良く読む
- 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が使われているか、都度確認
わすれがち、やりがちなこと
必要だったインデックスが
コードの変化でゴミになることもある
関連
SQLアンチパターン インデックスショットガン
- 雑に言うと「インデックスガンガン貼ってこうぜ！」っていうアンチパターン
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 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)
- 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 |
'----+-------------+-------+------+---------------+------+---------+------+------+-------------'`counter table
Cache
- Query Cache
- Memcached
- Redis
- etc..
生存時間に注意しつつ、
効率的なキャッシュを作る...
cacheの知見じゃなくて、
伝えたいこと
ファンとして伝えたい
isuconの名シーン...
2012
isucon2
fujiwara組
- template のcache
- busy loopを用意して、そこでfurl
- furl先でredisにcache
- 調整の仕方もすごい
- 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アプリとかありえますよね？！
ご清聴ありがとうございました。
株式会社モバイルファクトリー は、
開発好きなエンジニアを求めています。