日々精進

新しく学んだことを書き留めていきます

MySQL - ストレージエンジン変更

日頃からselect以外のDMLがやたら遅いのを不思議に思っていたんだけど、この記事InnoDBのinsert等はMyISAMよりずっと遅いと書いてあるのを発見。
countもMyISAMの方がかなり速いみたいなんでMyISAMに変更してみた。
以下手順とデータ。


1.まず計測
変更前の計測を行う。
計測方法はproduction.logをrubyで解析してパフォーマンスに関する部分を抜き出し、エクセルで一覧にするというもの。
A.以下のrubyコードをproduction.logと同じフォルダに置いて実行。(なんか必要以上に複雑なコードになった気がする。もっと簡潔に書く方法を勉強せねば。。)

f = open("production.log")
w = open("perf.log", "w")
f.each do |line|
  if /Completed in ([0-9]+\.[0-9]+).*Rendering: ([0-9]+\.[0-9]+).*magicians-portal.com\/([^\/]*)\/([^\/]*)\?.*\]/ =~ line || /Completed in ([0-9]+\.[0-9]+).*Rendering: ([0-9]+\.[0-9]+).*magicians-portal.com\/([^\/]*)\/([^\/]*).*\]/ =~ line
    w.puts "#{$1}\t#{$2}\t#{$3}\t#{$4}"
  elsif /Completed in ([0-9]+\.[0-9]+).*Rendering: ([0-9]+\.[0-9]+).*magicians-portal.com\/([^\/]*)\?.*\]/ =~ line || /Completed in ([0-9]+\.[0-9]+).*Rendering: ([0-9]+\.[0-9]+).*magicians-portal.com\/([^\/]*).*\]/ =~ line
    w.puts "#{$1}\t#{$2}\t#{$3}"
  elsif /Completed in ([0-9]+\.[0-9]+).*Rendering: ([0-9]+\.[0-9]+).*magicians-portal.com.?\]/ =~ line
    w.puts "#{$1}\t#{$2}\tlogin\tlogin"
  elsif /Completed in ([0-9]+\.[0-9]+).*magicians-portal.com\/([^\/]*)\/([^\/]*)\?.*\]/ =~ line || /Completed in ([0-9]+\.[0-9]+).*magicians-portal.com\/([^\/]*)\/([^\/]*).*\]/ =~ line
    w.puts "#{$1}\t\t#{$2}\t#{$3}"
  elsif /Completed in ([0-9]+\.[0-9]+).*magicians-portal.com\/([^\/]*)\?.*\]/ =~ line || /Completed in ([0-9]+\.[0-9]+).*magicians-portal.com\/([^\/]*).*\]/ =~ line
    w.puts "#{$1}\t\t#{$2}"
  elsif /Completed in ([0-9]+\.[0-9]+).*magicians-portal.com.?\]/ =~ line
    w.puts "#{$1}\t#{$2}\tlogin\tlogin"
  end
end
f.close
w.close



B.エクセルに貼り付け



C.ピボットテーブルで平均処理時間で並べ替え

Totalがサーバの処理時間合計でそのうちレンダリングにかかった時間がの割合が% of Rendering。↑のデータから「search, top」の二つのアクションをチューニングすべきということがわかった。


2.チューニングを実施。
本当はアクションのプロファイルを取ったり、遅いSQLを特定したりしないといけないところだけどイケそうな解決策があったのでそちらを実施した。解決策は以下二点。
・エンジンをInnoDBからMyISAMに変更
・インデックスの追加
エンジンの変更は簡単。
インデックスの追加はExplain句で追加したインデックスが使用されているかどうかを確認しつつ追加していった。


3.チューニング後の測定
チューニング後のパフォーマンス測定値は以下。

searchアクションは5倍、topアクションは3倍ほど速くなった。


4.考察
とても速くなって嬉しいが、おかしいところやリスクがいくつか。
レンダリングも速くなってる?
topアクションのチューニング前の% of Renderingの値は75%。ということはDBのチューニングだけでは25%までしか改善できないはずなのに実際には66%ぐらい改善できている??
考えられる原因は
A:Renderingの測定値は実際にはDBアクセスもある程度含んでいる
B:たまたま改善後に重い時間帯がなかった(レンタルサーバなので他のプロセスの影響を受ける)


Aの仮説を検証するにはアクション内に余分なクエリを入れておいて、それがある場合とない場合のパフォーマンスを比較する。
Bの仮説を検証するにはローカルのPCでサーバを動かしてそこでパフォーマンスを計測する。
上記検証はまた後日。。


追記:
ココによると、production環境ではRenderingにDBアクセスの時間も含まれるらしい。これではパフォーマンス問題の切り分けができないので困るなー。


○リスク:並列性・データの信頼性低下
MyISAMInnoDBよりいくつかの面で速い。その理由は
トランザクションを管理していない
・テーブルロックしかサポートしていない
というものだったりするので、短時間に書き込みが多数発生するアプリだと速度の低下やデータの不整合が起こる可能性がある。
何らかの問題が起きるかどうか今後監視していきたい。