チューニンガソン 2013 参加記

チューニンガソン

> 第5弾！ いろいろチューニングしてパフォーマンスを競うバトルイベント「チューニンガソン」1月19日（土） 開催！ #tuningathon on Zusaar
> http://www.zusaar.com/event/467012

イベント参加してました．

## 当日の流れ

遅刻！

歩きながら レギュレーション読む．
MySQL ！

ついて早速ネットつないで ssh して環境確認．

Server version: 5.5.29 MySQL Community Server (GPL)

5.5 か．なら別にバージョンあげなくてもいいかな（前回とか自前で入れて失敗したような気がする）


最初のベンチ

$ python tgbench_mysql.py 127.0.0.1
0.000352144241333
30.6134359837
40.169380188
77.9782850742
117.878194094
156.010751963
156.010817051

クエリの実行時間らしく，それぞれの値が小さいほど良い．で，最後の値が実際に評価される値らしい．

まあクエリを見てみないことにはなんにもならないので　スクリプトを見てクエリ見る．

            RESET QUERY CACHE;

クエリキャッシュ削除してるからとりあえずクエリキャッシュがどーのとか言うアプローとはナシにしとくかな．

クエリ１こめ

            SELECT COUNT(*)
              FROM page
              JOIN revision
                ON page_id = rev_page
             WHERE page_is_redirect = 0
               AND page_namespace = 0
               AND rev_user = 0

クエリ２こめ

            SELECT COUNT(*)
              FROM page
              JOIN revision
                ON page_id = rev_page
             WHERE page_is_redirect = 0
               AND page_namespace = 0
               AND rev_user = 0
               AND page_touched > DATE_FORMAT(NOW() - INTERVAL 1 YEAR,'%Y0101000000')

クエリ３こめ

            SELECT page_id
              FROM page
              JOIN revision
                ON page_id = rev_page
             WHERE page_is_redirect = 0
               AND page_namespace = 0
               AND rev_user = 0
          ORDER BY page_touched DESC
             LIMIT 10

クエリ４こめ

            SELECT rev_user,count(*) AS c
              FROM page
              JOIN revision
                ON page_id = rev_page
             WHERE page_is_redirect = 0
               AND page_namespace = 0
          GROUP BY rev_user
          ORDER BY c DESC

クエリ５こめ

            SELECT SUBSTRING(rev_timestamp,1,6),count(*) AS c
              FROM page
              JOIN revision
                ON page_id = rev_page
             WHERE page_is_redirect = 0
               AND page_namespace = 0
               GROUP BY SUBSTRING(rev_timestamp,1,6)
          ORDER BY c DESC


なるほど．
page と revision のテーブルだけなんかな．
とりあえず page と revision の show create table を見ておく．（記録してない）
とりあえず自分が動かしてる DB サーバの設定を見ながら調整してみる．

設定内容

key_buffer = 16M

実行結果

$ python tgbench_mysql.py 127.0.0.1
0.000257015228271
9.65802717209
19.0670251846
48.2355220318
85.3107271194
123.534044027
123.534102201

いろいろ結構早くなった．


ストレージエンジンが innodb だったので．バッファ増やす．

設定内容

innodb_buffer_pool_size=1638M


実行結果

$ python tgbench_mysql.py 127.0.0.1
0.000259876251221
4.83838295937
5.74692988396
10.7054519653
18.0805239677
24.5209450722
24.5210080147


おー結構早くなった．
ここで xlarge のメモリ量 よく見たら 15G あるんじゃね ってことに気づいてガッツリ増やしてみた．（コレあとで気づいたら意味なかったけど）

設定内容

innodb_buffer_pool_size=12000M


実行結果

$ python tgbench_mysql.py 127.0.0.1
0.000226974487305
0.730866193771
1.55102205276
5.34630703926
11.1979050636
17.3137311935
17.3137981892


なるほど? mysql の再起動直後だと 23 になるけど 一回動かした後だと 17.3 になるっぽい．（さっきの innodb_buffer_pool_size 増やした意味ない）
なんで二回目で早くなるかは調査してないけど，今後は二回目の計測を表示する。　


MEMORY ストレージエンジン使うといいんかな．
git いれて $HOME/data 以下 を git 管理に追加して data/tables.sql をいじってみる．
ん？

ERROR 1163 (42000) at line 219: The used table type doesn't support BLOB/TEXT columns

https://dev.mysql.com/doc/refman/5.1/ja/memory-storage-engine.html
> MEMORY テーブルは BLOB や TEXT カラムをサポートしません。

まじかー．諦める．

group by を早くする

sort_buffer_size=4M
$ python tgbench_mysql.py 127.0.0.1
0.000229835510254
0.732585906982
1.54724097252
5.77867388725
11.4459989071
17.6631388664
17.6632049084

かわらんふやす

sort_buffer_size=1024M
$ python tgbench_mysql.py 127.0.0.1
0.000241041183472
0.72815990448
1.53270101547
5.81017899513
11.553994894
17.8257920742
17.8258590698


インデックス削除してみた（ len は使ってない）

mysql> alter table page drop index page_redirect_namespace_len;
mysql> alter table revision drop index page_user_timestamp;
mysql> alter table revision drop index usertext_timestamp;


最後２つのクエリがそれぞれ 6 s ぐらい使ってるんだよなー

            SELECT rev_user,count(*) AS c
              FROM page
              JOIN revision
                ON page_id = rev_page
             WHERE page_is_redirect = 0
               AND page_namespace = 0
          GROUP BY rev_user
          ORDER BY c DESC
            SELECT SUBSTRING(rev_timestamp,1,6),count(*) AS c
              FROM page
              JOIN revision
                ON page_id = rev_page
             WHERE page_is_redirect = 0
               AND page_namespace = 0
               GROUP BY SUBSTRING(rev_timestamp,1,6)
          ORDER BY c DESC

それぞれ分解して実行してみる．

とりあえず全開で．

            SELECT rev_user,count(*) AS c
              FROM page
              JOIN revision
                ON page_id = rev_page
             WHERE page_is_redirect = 0
               AND page_namespace = 0
          GROUP BY rev_user
          ORDER BY c DESC
39434 rows in set (5.55 sec)

order by 削除

            SELECT rev_user,count(*) AS c
              FROM page
              JOIN revision
                ON page_id = rev_page
             WHERE page_is_redirect = 0
               AND page_namespace = 0
          GROUP BY rev_user
39434 rows in set (5.54 sec)

group by 削除

            SELECT rev_user,count(*) AS c
              FROM page
              JOIN revision
                ON page_id = rev_page
             WHERE page_is_redirect = 0
               AND page_namespace = 0
   1 row in set (4.35 sec)

4s かー．これ高速化してみるか． たぶん count(*) で使うインデックスを小さいものにすると早くなりそうな気がする page_is_redirect が一番小さいかな tinyint(4) だからだけどあんまりかわらんかった．

ここでクエリをもう一度よく見ると３こめのクエリが若干遅い．これをみると order by やってる場所あるのでこれのインデックスはる

          ORDER BY page_touched DESC

ここでいろいろインデックス削除したり追加したりしてた（記録してない）．
いろいろやってたら終了．

## 結果

順位のページをこっそり見たら 29 位だった．最初 1 時間ぐらいの内容が知識的に限界だったけどいろいろ検証できたのですごく勉強になった．

29 nise_nabe 13.4976408482

## 感想

前回までは Web アプリっぽかったけど，今回は DB （MySQL）ということで，中途半端な知識でのそこそこガッツリ減った感覚があってとても楽しかったです．

最終的な設定

key_buffer = 16M
key_buffer_size = 1024M
innodb_buffer_pool_size=10000M
max_connection=3
innodb_flush_log_at_trx_commit=0
join_buffer_size=0
sort_buffer=16M
sort_buffer_size=1024M