Tuningathon - まっちゅーのチラ裏

1/19(土)のTuningathonに友人の@murahumと参加しました。うろ覚えですが詳細をまとめておきます。

お題

単一スレッドで5つのクエリを走らせ、どれだけ早く処理できるかを競いました。

状況確認

CPU、メモリともかなり潤沢。。。
CPU4コアのメモリ16GBでした（1.6GBと見間違えました
AWSの【M1 Extra Large】インスタンスが配布されていたようです。

最初にベンチを取ると140秒近くかかってました。

基本戦略の決定

・インデックスの最適化
　⇛テーブル数の割に計測スクリプトで参照しているテーブルが少ないので「計測スクリプト対策INDEX」を揃えれば効果あるかも

・できるだけDBをメモリに載せる
　⇛メモリがかなり余っていたのでBuffer poolにDBを全て載せれば早くなるだろうと推測し採用
innodbは業務でも使用したことがあるので、ベンチ取りながら知っているパラメータを試してみようと考えてました。

序盤

とりあえず思いつくinnoDBのパラメータを設定するとスコアが大幅に改善
140秒⇛18秒

次にINDEXを確認
参照しているテーブルはpage,revisionのみだったので確認すると色々INDEXが貼られていました。
相棒と話し合った結果、取り敢えず全部消して、1から貼り直そうという話になりました。

一旦SQLdumpを取得後、page,revisionテーブルのINDEXを主キー以外全て削除したところ、またまたスコアが改善
18秒⇛10秒
※このスコアで3回目の計測1位に！！！！（二人ともテンション上がりまくりでしたｗｗｗ

中盤

・ここから実機のMySQL5.5を5.6にバージョンアップしようと試みるがソースコンパイル久方ぶり過ぎて不安になり断念。

・page,revisionに通常のINDEXを貼っていく。
　が、貼れば貼るほど遅くなる。。。なにこれ・・・
　
　MySQLは一つのクエリにつき、一つのINDEXしか使用できないので複数ある場合はあまり効率のよい動きはしないみたい。

　そこで複合INDEXを採用してみることにして適当にぺたぺた

　10秒⇛17秒に！！！
　なぜにｗｗｗｗ

　泣く泣くrevisionのINDEXのみ削除。pageのINDEXは上手く効くいていたようで

   create index red_name_touched on page(page_is_redirect,page_namespace,page_touched);

　17秒⇛9秒に
　

終盤

ここからは試行錯誤で色々やりましたが中々効果がでず。。。
運営からは6秒台とのアナウンスもあって焦る焦る。。。

効果が出たのは@murahumのINDEX
　●revisionに以下のクエリで複合INDEXを追加

   create index user_page_time on revision(rev_user,rev_page,rev_timestamp(6));

9秒⇛7秒に

※【SUBSTRING(rev_timestamp,1,6)】の部分で6バイト目までしか参照しないのでINDEXもそこで止めてます。これによりCardinality値を下げる効果ああります。
groupbyにINDEXが効くのは知識として知っていましたが、INDEXを6バイト目で止める部分は自分も考えつかなかった。。。さすがの@murahumクォリティ
@murahumの方で検証したところ、
create index user_page_time on revision(rev_user,rev_page,rev_timestamp);
でも測定値はあまり変わらなかったそうです。

以下によると一意なら参照も早くなったかもですが、timestampは一意ではなかったのであまり変化はなかった模様。勉強になりました！
http://dev.mysql.com/doc/refman/5.1/ja/create-index.html
＞もしカラム内の名前の最初の10文字が違っていれば、このインデックスは name カラム全体から作成されたインデックスよりも遅くは無いはずです。また、部分的なカラムをインデックスに利用する事でインデックス　ファイルを小さくする事ができるので、ディスクのスペースを節約し、 INSERT 操作を早くする事ができます。

この後は試行錯誤。。。。

MyISAMのパラメータを入れてストレージエンジンをMyISAMに変更
7秒⇛45秒（即切り戻し)

インデックスの探索タイプをBTREE⇛HASHへ変更
⇛なぜかUSINGパラメータが効かず断念

最後にmysqldにniceかけてログの出力を全て止めてタイムアップ

結果

なんと「7.32306790352」で総合三位でした！！！！
イケメン相棒のINDEXが素敵だったのが勝因でしたｗｗｗあざすｗｗｗ

そして、沖縄勢が10位以内を4つの枠を占める大健闘！

考察＆反省点

1位との差は5つ目のクエリの部分だったと思います。
5つめのクエリをEXPLAINしたらtypeがALLになっていたのは気づいていたのですが全然手が思い浮かびませんでした。

インタビューでは主キーを変更したといっていたのでインスタンスが生きているうちにちょろっと調べてみたところ、rev_pageを主キーに指定することができました。
でした。まさかrev_pageが一意な値だったとは。。。。
そしてうっかりして主キーに変更後のベンチをとれなかった。。。

create index id on revision(rev_id);
create index user_time on revision(rev_user,rev_timestamp);
ALTER TABLE revision drop PRIMARY KEY;
ALTER TABLE revision ADD PRIMARY KEY(rev_page);

・クエリ5

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	revision	ALL	NULL	NULL	NULL	NULL	1620793	Using temporary; Using filesort
1	SIMPLE	page	eq_ref	PRIMARY,red_name_touched	PRIMARY	4	wikipedia.revision.rev_page	1	Using where

・クエリ5(主キー変更後)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	page	ref	PRIMARY,red_name_touched	red_name_touched	5	const,const	822882	Using index; Using temporary; Using filesort
1	SIMPLE	revision	eq_ref	PRIMARY	PRIMARY	4	wikipedia.page.page_id	1

my.cnfは以下のとおり
DB自体は800MBほどしか容量がありませんでしたが、最終的にbuffer poolは5GB割り当て
その他のbuffer_sizeたちはあまり多く割り当てすぎると若干遅くなった気がしたので
以下のようにしてます。一応、単一スレッドの処理速度を計る今回の競技には全く関係ありませんがdefaultのmax_connections150きても耐えうる感じにはしてみました。
正直buffer pool以外は効果薄だったかも。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Global Area
innodb_file_per_table
innodb_buffer_pool_size=5120M
innodb_log_buffer_size=8M
innodb_log_file_size=128M
innodb_flush_method=O_DIRECT
query_cache_size=100M
table_open_cache=1024

# Thread Area
sort_buffer_size=16M
read_rnd_buffer_size=16M
max_allowed_packet=16M
join_buffer_size=8M
key_buffer_size=8M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

ということで大分ふわふわした道筋でしたが、3位だったので大満足です！

運営の方お疲れ様でした。次回も参加したいと思いますのでよろしくお願いいたします。

追記：
運営よりAMIが公開されたので主キーを張り替えて試してみました。

結果「6.32319498062」で優勝には届かず。。。

他にオーバーヘッドがないか確認し、pageのINDEXを以下のように張り替えたら「5.8722589016」がでました。満足。

Table

Non_unique

Key_name

Seq_in_index

Column_name

Collation

Cardinality

Sub_part

Packed

Null

Index_type

Comment

Index_comment

page

PRIMARY

page_id

1714494

NULL

BTREE

page

name_red

page_namespace

NULL

BTREE

page

name_red

page_is_redirect

NULL

BTREE

page

touched

page_touched

206

NULL

BTREE