ひげろぐ

最近管理しているDBサーバで継続的にスロークエリが出るようになったので、チューニングしてみたら気持ちの良い結果が出た。
結論から言うとカラム数が多いテーブルに対しては:selectで取得するカラムを絞るのがかなり有効かと思う。

現状把握

今回スロークエリの発生していたテーブルの状況を整理したのが以下。

• レコード件数は110万件くらい
• カラム数は30程度
• インデックスは効いている（explainで確認済み）
• 処理の性質的にキャッシュは使えない

スロークエリになっているのはもっぱら以下のクエリ。

select * from pages order by updated_at limit 100;

Railsのコードで見るとこんなかんじ。

Page.all(:order => 'updated_at', :limit => 100)

こんな単純なクエリが実行に2秒から10秒程度もかかってスロークエリとして記録されているのは切ない。
インデックスは効いているので問題解決には他のアプローチが必要になる。

考えるに対象は30以上カラムがあってレコードサイズもそこそこ大きいテーブル。
そこで取得するカラムを絞って余計なカラムを取得しないようにしてみたらどうかと思った。

というかクエリが単純すぎてまずはそれくらいしか浮かばなかったわけだけど。

ベンチマークとチューニング

計測なくしてチューニングなしということでベンチマークで使ったのはmybench。

• mybench – a simple benchmarking tool for MySQL

ベンチマークとチューニングは手元の開発環境で実行した。
こちらレコード件数は3万件程度。本番環境より大幅に少ないが十分だろう。たぶん。

全カラム取得とカラムを絞った結果の比較が以下。
10クライアントから100回ずつ、計1000回のリクエストを送るというのを試行回数3回ずつ行った結果。
serialは経過時間（秒）です。

まずは全部まるごと取得している現状のクエリ。

select * from pages order by updated_at limit 100;
# Page.all(:order => 'updated_at', :limit => 100)
  serial  : 29.173278
  serial  : 29.433684
  serial  : 30.258237

これを取得カラムを絞ったものにしてみると。

select id, updated_at from pages order by updated_at limit 100;
# Page.all(:select => 'id, updated_at', :order => 'updated_at', :limit => 100)

  serial  : 16.422306
  serial  : 17.562543
  serial  : 16.070013

倍近く速くなった。
うつくしい。

いやぁ、チューニングって本当に気持ちがいいものですね。
これを本番環境にアップしたらスロークエリもパッタリなくなり幸せになれました。

以下詳しく見たい人向け

select * from pages order by updated_at limit 100;
# Page.all(:order => 'updated_at', :limit => 100)

test: 1000 0.001631 0.060372 0.029173278 29.173278 342.779443571614
  clients : 10
  queries : 1000
  fastest : 0.001631
  slowest : 0.060372
  average : 0.029173278
  serial  : 29.173278
  q/sec   : 342.779443571614

test: 1000 0.001535 0.06981 0.029433684 29.433684 339.746801657584
  clients : 10
  queries : 1000
  fastest : 0.001535
  slowest : 0.06981
  average : 0.029433684
  serial  : 29.433684
  q/sec   : 339.746801657584

test: 1000 0.00298 0.065291 0.030258237 30.258237 330.488521191767
  clients : 10
  queries : 1000
  fastest : 0.00298
  slowest : 0.065291
  average : 0.030258237
  serial  : 30.258237
  q/sec   : 330.488521191767

select id, updated_at from pages order by updated_at limit 100;
# Page.all(:select => 'id, updated_at', :order => 'updated_at', :limit => 100)

test: 1000 0.000327 0.037233 0.016422306 16.422306 608.927881382797
  clients : 10
  queries : 1000
  fastest : 0.000327
  slowest : 0.037233
  average : 0.016422306
  serial  : 16.422306
  q/sec   : 608.927881382797

test: 1000 0.001182 0.050836 0.017562543 17.562543 569.393623691057
  clients : 10
  queries : 1000
  fastest : 0.001182
  slowest : 0.050836
  average : 0.017562543
  serial  : 17.562543
  q/sec   : 569.393623691057

test: 1000 0.000301 0.04706 0.016070013 16.070013 622.277032383234
  clients : 10
  queries : 1000
  fastest : 0.000301
  slowest : 0.04706
  average : 0.016070013
  serial  : 16.070013
  q/sec   : 622.277032383234

RailsのconsoleでActiveRecordを使って大量のレコードを処理しようとする場合、とりあえずコントローラ内に書くのと同じように以下のように打ってみると思う。

Item.all.each {|item| item.update_price }

ところが経験ある人も多いと思うけれども、これをやるとレコード数が1000程度でも大量のメモリを食う。
自分の経験を言えば、開発マシンのMacbook Proがスワップを大量発生させてコマ送りのようなレスポンスになった。

それぞれのレコードに対応するオブジェクトがループ終了までガベージコレクションによって破棄されないのでこんな風になる模様。
処理が進むにつれだんだんメモリ消費量が増えていく。
メモリリークのようだけどメモリリークではないんですと。

で、この敵に立ち向かうためにしばらくの間は勤勉さを発揮してある方法を採った。
一度に取得するレコード数にlimitを指定して↑キーとエンターを連打するという頭が悪いけど力技で確実な方法。

Item.all(:limit => 100, :order => 'updated_at').each {|item| item.update_price }

しかしながらさすがにこれを数十回繰り返さないとダメとなった段に勤勉さが底をついた。
というわけでいろいろ試して、これを以下のように書いてみたらメモリ消費は少ないということに気がついた。

(Item.count).times { Item.first(:order => 'updated_at').update_price }

これだとループごとにガベージコレクションがちゃんと働いてくれるみたいだ。

各レコードのidの決まっているマスタを作るときなどに知っていると便利な小技。
普通ActiveRecordのidは自動で採番され、レコード作成時に指定することも後で変更することもできない。

例えば最後のレコードのidが100の時に

maker = Maker.create(:id => 1000)

などとしても

maker.id
#=> 101

といった結果になる。

しかしfind_or_create_by_idを使うとidを指定してレコードを作成できる。

maker = Maker.find_or_create_by_id(1000)

とすると

maker.id
#=> 1000

イレギュラーな挙動かもしれないけれど、今のところはこのように動く。

ちなみに更新はやっぱりできないので後からidを変えたくなった場合はexecute_sqlなどで強引に変えるしかないっぽいです。

前回RailsでAmazonのWebサービスを使ってみたけれども、何かあるたびに毎回リクエストを送るのはいろいろ無駄があって忍びないのでレスポンスをキャッシュしなければならない。

ということで今回はActiveRecordを使ってざっくりキャッシュしてみる。
キャッシュテーブルにたくさんカラムを定義するのは面倒なので検索キーにしたいものだけカラムとして定義して、あとは結果をシリアライズして突っ込む方針で。

キャッシュ用のテーブルとモデルを作る

まずはキャッシュするためのテーブルとモデルを準備。

テーブル

とりあえずマイグレーションでテーブルを作る。

class CreateAmazonCaches < ActiveRecord::Migration
  def self.up
    create_table :amazon_caches do |t|
      t.string :asin
      t.string :title
      t.text :response_dump

      t.timestamps
    end
  end

  def self.down
    drop_table :amazon_caches
  end
end

モデル

キャッシュを参照するためのモデル。
データを参照するときにレスポンスの内容をデシリアライズする。

class AmazonCache < ActiveRecord::Base
  @reponse_hash = nil
  @search_limit = 10

  def get(name)
    @response_hash = Marshal.restore(response_dump) if @response_hash.nil?
    @response_hash[name.to_sym]
  end

  def self.search(title, order = 'id', limit = nil)
    @search_limit = limit if limit
    self.find(:all, :conditions => ["title like ?", "%#{title}%"], :order => order, :limit => @search_limit)
  end
end

ハッシュのデシリアライズのタイミングはafter_findでもいいかもしれないなぁ。

キャッシュの保存と参照

テーブルとモデルができたので次はそれを使って実際にキャッシュする。

レスポンスをキャッシュする

以下がレスポンスをキャッシュするためのコード。

get_hashで取得したデータのハッシュだけシリアライズして突っ込む。
（itemのままだとなにやらうまいことMarshalでシリアライズできなかった）

res = Amazon::Ecs.item_search("Rails", :response_group => 'Large')
res.items.each do |item|
  AmazonCache.create(:asin => item.get('asin'), :title => item.get('title'), :response_dump => Marshal.dump(item.get_hash))
end

キャッシュを参照する

参照は以下のように。

items = AmazonCache.search("Rails")
items.first.get(:title) # => Railsの何かの本とかそんなタイトル

smallimageのように子要素を持つ要素はXMLの断片が保存されている。

items.first.get(:smallimage) # => "<url>http://ecx.images-amazon.com/images/I/hogehogehoge-.jpg</url><height units=\"pixels\">75</height><width units=\"pixels\">58</width>"

これは正規表現とかで値を取り出すのが楽かね。
モデルにそれ用のメソッドを追加してしまってもいいかもしれない。

まあひとまず今回はこんなところで。

Railsのコントローラのテストにおいて、フィクスチャのリロードをしないでテストを進めると期待通りの結果にならず首をかしげることになる。
初心者のハマリがちなところ。

例えば次のようなフィクスチャがあり

first_feed:
  id: 1
  title: hoge

次のような更新のテストを書くと

post :update, :id => @first_feed.id, :title => 'hogehoge'

@first_feed.title.should == 'hogehoge'

更新処理（updateアクション）が成功してもこのテストは失敗する。
@first_feed.titleは”hoge”のままだからだ。

期待通りの結果にするには次のようにしなくてはならない。

post :update, :id => @first_feed.id, :title => 'hogehoge'

@first_feed.reload
@first_feed.title.should == 'hogehoge'

これはなぜかというとデータベースの更新が@first_feedとは別のActiveRecordインスタンスで行われるため。たぶん。

更新処理内で同じレコードを参照する別のActiveRecordのインスタンスが作られてデータベースの更新はそちらで行われる。
他のインスタンスによる変更を既存のインスタンスであるところの@first_feedが知るすべはないので、リロードしてやらなければならないというわけだ。

というわけでフィクスチャの、と言っているが実はフィクスチャ以外でもこの現象は起こる。
でもテスト時は特にフィクスチャでよくやる。

で、初心者のハマリがちなところとか偉そうに行ってる割に自分自身が昨日これで30分くらいはまってたわけだが。

@first_feed.itemsにアクションで変更が発生したときにも@first_feedをリロードしないといけないんだぜ。
あと長時間の連続コーディングもNGなんだぜ。

両方とも id を指定してレコードを探すことに違いはないが、レコードが見つからなかった場合の挙動に差がある。

find(id) に find_by_id(id) の挙動を期待していたので以下のようなコードを書いてしまっていた。

@item = Item.find(params[:id]) if params[:id]
render(:nothing => true, :status => '404 Not Found') unless @item

これだとレコードが見つからなかったときにサーバーエラー（ステータスコード 500）になってしまう。
そうならないためには以下のコードが正しい。

@item = Item.find_by_id(params[:id]) if params[:id]
render(:nothing => true, :status => '404 Not Found') unless @item

奥が深い。

参考

ActiveRecord のお勉強 – Rails で行こう！ – Ruby on Rails を学ぶ

Railsおさらいシリーズ。
忘れてること多々なので書いて思い出そうという趣旨です。

ActiveRecordの前提

テーブルとモデルクラスが1対1で対応するO/Rマッパー。
テーブルの構造に関しては以下の規約が基本。

• 主キーがオートナンバー型が必須
• 主キーのカラム名はidがデフォルト
• テーブル名は複数形がデフォルト
• テーブル名はモデルクラス名を小文字アンダースコア区切りにしたものがデフォルト

主キーがオートナンバー型必須なのはマスタデータのIDを任意に設定したいときにちょっとやりにくかったりする。
主キーのカラム名とテーブル名に関しては変更可能だが、必要がなければ規約に従ったほうが絶対幸せになれる。

主キーとテーブル名を変更する

どうしても変更したい場合はモデルクラスの中で以下のメソッドを使って変更できる。

• set_primary_key(column_name)
• set_table_name(table_name)

class HogeModel < ActiveRecord::Base
  set_primary_key :code
  set_table_name  :hoges
end

その昔は私もcode派でした。

テーブル名の複数型をやめたい

全体的にテーブル名の複数形を止めたい場合は、pluralize_table_names属性の値をfalseにするとできる。
個々のモデルでset_table_nameしても実現できるが、テーブルが多すぎてめんどくさい場合になど使うと便利っぽい。

参考

Ruby on Rails入門―優しいRailsの育て方

posted with amazlet on 07.10.06

西 和則
秀和システム (2006/08)
売り上げランキング: 45695

おすすめ度の平均:

これは入門書ではありません。
入門用としては残念。
Railsスタートのベストチョイス

Amazon.co.jp で詳細を見る

素敵な本だけど、入門用としては濃すぎる気がする。

近況

その日布団にはいるまで一日は終わらないんだ。
日付をこの日にするためにタイムスタンプちょっと戻したけどセーフってことで。ひとつ。