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位に!!!!(二人ともテンション上がりまくりでしたwww


中盤

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

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

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

 10秒⇛17秒に!!!
 なぜにwwww

 泣く泣く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が素敵だったのが勝因でしたwwwあざすwww

そして、沖縄勢が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 0 PRIMARY 1 page_id A 1714494 NULL NULL BTREE
page 1 name_red 1 page_namespace A 18 NULL NULL BTREE
page 1 name_red 2 page_is_redirect A 18 NULL NULL BTREE
page 1 touched 1 page_touched A 206 NULL NULL BTREE