ぶろぐ

日記です

チューニンガソン#5に行ってきた


チューニンガソン#5行ってきました

結果

順位は13位ぐらいで9.7秒ぐらいでした。

[ec2-user@ip-10-132-137-95 ~]$ python tgbench_mysql.py 127.0.0.1
0.000227928161621
0.785279035568
1.62677598
2.58574199677
5.90068292618
9.73505592346
9.73513007164

チューニングの内容は

  • innodb_buffer_pool_sizeを増やしてDBの内容をメモリに置く
  • インデックスを頑張って考える！

の２つでした。

やったこと(下準備編)

遅刻しました。チューニンガソン初参加なのに説明聞けなかったのでちょっと焦りましたが、レギュレーションのPDFに色々書かれていたのと、電車で移動中に沖縄勢の友人とLineでやり取りしていてマシンの情報等はもらっていたので何とか立ち上がれました。

バックアップ

そっこーでmysqldumpしときました。

mysqldump -u root -x --all-databases > all_dump.sql
mysqldump -u root wikipedia > wikipedia.sql

念のため用意されているファイルもバックアップしておきました。
結構時間かかっていたので、その間にレギュレーション読んでました。

tmux入れた

初めの頃スマフォのテザリングWi-Fiを使っていて、ちょっと放っておいたらsshのセッションが切れまくりでした。keepaliveの設定入れてみても切られちゃうので、tmux入れてストレス軽減しました。
ふと後ろを振り返るとそこにはWi-Fiのssidとパスワードが書かれたホワイトボードがありました。会場のWi-Fiに切り替えました。ターミナルのレスポンスがマッハに！！
設定はgithubに置いているのを取ってきて、tmux-powerline周りの設定を削除して使いました。

dstat入れた

「dstatを見ながらやるのがいいよ！」というのを見たことがあるので、入れました。この人いかにもチューニングしているっぽい、という雰囲気を出す効果もあります。

xlageインスタンス

メモリ16G、CPU 4コア。ヤバイです。

この時のスコアです。まだ何もやってません。

やったこと(チューニング編)

InnoDB周り

メモリが16Gだということもあり、DB全部メモリに乗るじゃんと思いメモリをガッツリ割り当てました。パラメータの内容はGoogle先生に教えてもらいながら。
ココを参考にしました。

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server=utf8

# slow query log
long_query_time=1
log_slow_queries=/var/lib/mysql/slow_query.log
log-queries-not-using-indexes
log=/var/lib/mysql/query.log

# グローバル設定
innodb_file_per_table
# InnoDBのデータやインデックスをキャッシュするためのメモリ上の領域
innodb_buffer_pool_size=9000M
# InnoDBの更新ログを記録するメモリ上の領域
innodb_log_buffer_size=16M
# InnoDBの更新ログを記録するディスク上のファイル
innodb_log_file_size=128M
innodb_flush_method=O_DIRECT
query_cache_size=64M

# スレッド設定
sort_buffer_size=8M
read_buffer_size=2M
read_rnd_buffer_size=2M
max_allowed_packet=8M

# そのまま
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8

# そのまま
[mysql]
default-character-set=utf8

InnoDBのキャッシュがメモリに乗ってくれて、大分早くなりました。

インデックス周り

計測スクリプトのクエリを見たところ、使用しているテーブルはpageとrevisionだけのようでした。
今はどういうインデックスを張っているんだろう、と覗いてみるとちょーたくさんインデックスが張られていました。クエリ見ながら「使ってないだろ」というカラムに「これ消すか」とか狙いをつけて消すと見せかけ、全部消しました。

# インデックス確認
mysql> show index from page;
mysql> show index from revision;

# 全部全部消して主キーのインデックスだけにする
mysql> ALTER TABLE page DROP INDEX name_title;
mysql> ALTER TABLE page DROP INDEX page_random;
mysql> ALTER TABLE page DROP INDEX page_len;
mysql> ALTER TABLE page DROP INDEX page_redirect_namespace_len;
mysql> ALTER TABLE revision DROP INDEX rev_page_id;
mysql> ALTER TABLE revision DROP INDEX rev_timestamp;
mysql> ALTER TABLE revision DROP INDEX page_timestamp; 
mysql> ALTER TABLE revision DROP INDEX user_timestamp; 
mysql> ALTER TABLE revision DROP INDEX usertext_timestamp; 
mysql> ALTER TABLE revision DROP INDEX page_user_timestamp;

一気に10秒〜11秒台ぐらいまで行きました。

pageテーブルのインデックス

where句で使っているカラムにはインデックスを張ろう、と思い色々試してみましたがインデックスを増やすだけでは意味がなかった！
どうやら1クエリで1インデックスしか使われないようなので、たくさん作ってもオプティマイザ？がどのインデックスを使うかを判断するっぽいのであまり効果がない。

  • WHERE page_is_redirect = 0 AND page_namespace = 0

という条件があったので、page_is_redirecとpage_namespaceはどうせ0だし複合インデックスでまとめてしまってもいいんじゃね、と思ってまとめました。

CREATE INDEX page_redirect_namespace_id ON page (page_namespace, page_is_redirect);

でも、そのカラムの中身は全て0のようでした。(0以外で検索すればよかった)

select page_namespace from page where page_namespace = 1
select page_is_redirect from page where page_is_redirect = 1

の結果が0件だったので、このカラムにインデックスを貼る意味はあったのか…？とか思っています。
あとから気づいたのですが、page_touchedも2番目のクエリで条件に使っていたので、それも入れてあげたほうが良かったかもしれません。

create index page_redirect_namespace_id on page(page_is_redirect,page_namespace,page_touched);
revisionテーブルのインデックス
  • rev_page
  • rev_user

が検索条件として使われていました。そのためこのカラムを複合インデックスにしました。が、結果は良くなりませんでした。

  • page_id = rev_page

が条件としてあるため、rev_pageにインデックスを張ったほうがいいんじゃね、思い貼ってみますが効果なし。revisionテーブルにはどう手を加えていいのか分からなかったです＞＜
測定時にはなんとなくrev_userにだけインデックスを張っていました。

反省

revisionテーブルにインデックスを張るポイント

友人のid:ma2k8にどんなインデックスを張ったのか教えてもらいました。張ったのは以下の２つのようです。
参考

create index red_name_touched on page(page_is_redirect,page_namespace,page_touched);
create index user_page_time on revision(rev_user,rev_page,rev_timestamp(6));

なるほど、rev_timestampにインデックスを張っている上に、計測のクエリがSUBSTRING(rev_timestamp,1,6)で頭の6つの値しか見ていないのでインデックスもそこまでの値で作成している。
ホントにrevisionテーブルには何していいか分からなかったので、終わった後に隣の人に質問してみたのですが、その人も複合インデックスでrev_timestampにインデックスを張っていました。複合インデックスの順序は、rev_timestampを1番はじめにしていました。
「GROUP BYは重たい処理なので、ココで使っているカラムにインデックスを張ると効果がある」と教えてくれました。
しかしよくよく考えると正直どういう意味なのかわかりませんでした（小さな声で）
自分の理解としては

  • インデックスは検索条件のカラムに張ることで効果を発揮する
  • GROUP BYは検索が終わり、データをかき集めた後に処理が行われるのでインデックスによる効果は望めない

と思っていました。
しかーしそれはちょっと違った！
問題のクエリの内容を貼ります(計測スクリプトの5番目のクエリ)

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

rev_timestamp別に集計結果を求めるクエリだと思います。
集計結果を求めるのに、レコードの内容は必要ないため、集計対象のカラムにインデックスを張っているとインデックスの内容だけで集計処理を行うことが出来るようです。
参考にしたサイトはここ

そういう事か。

1位の人のコメントを聞いて

rev_idではなく、rev_pageが一意の値だったらしいです。
そのためrev_pageを主キーにしたところ効果があったそうです。
rev_pageを主キーにした時の、5番目のクエリのEXPLAIN結果は id:ma2k8 のはてブロに書いてあったりします。

準備不足
  • InnoDBのパラメータは会場で調べていた
  • インデックスの張替えの方法も会場で調べた
    • インデックスの再構築は本当にCREATE INDEXで行われるのか？とかも不安で調べてた
  • long_query_time=1 にしていたので、ほぼ大体のクエリが出力されていた。意味ない。そもそもどんなクエリが発行されて、何秒かかっているのかは、計測スクリプトで知ることが出来たのでslow query logとか見る必要がなかった。
  • nice値上げるの忘れてた
  • DBをrestartするとInnoDBのキャッシュが消えるので、キャッシュを貯める用の計測、本チャンの計測、と2度実行しなければいけなかったので少しめんどくさかった。今思えば以下でいけた。
  • sudo service mysqld restart && python ~/tgbench_mysql.py 127.0.0.1 && sleep 3 && python ~/tgbench_mysql.py 127.0.0.1

かなりいい勉強になりました。5時間あっという間で、集中して出来たので楽しかったです。ありがとうございました。
チューニングで使っていたインスタンスのAMIが公開されるらしいので、贅沢にもxlargeのインスタンスをたちあげて反省点をフィードバックすると面白そうです。AMIよろしくお願いします！