railsで利用しているmysqlテーブルでパーティショニングを試してみた

あるrailsアプリで利用しているmysqlテーブルでパーティショニングを試してみました。

パーティショニングとは、テーブルのデータ格納領域を分割することで大量データを保持するテーブルのパフォーマンスを改善する手段の一つです。

以下が参考になります。
http://www.s-quad.com/wordpress/?p=62

パーティショニングをするには、分割に利用するカラムを決定する必要があります。
利用状況に応じた適切なカラムを選択しないと帰ってパフォーマンスが落ちることがあります。

今回、試したmysqlのバージョンは5.1で
以下のような毎月一括でデータを登録するようなテーブルです。

テーブル名：hoges
カラム：
id：int(PK)AUTO INCREMENT
nengetu：varchar
その他カラム多数

このテーブルには、毎月一度一括で大量のデータを登録し、検索する際にもnengetuが多くのケースで利用されています。
なのでnengetuで分割することを目指します。

調べてみたところ以下のことがわかりました。
・分割に利用するカラムは、プライマリキーの一部である必要がある
今回のhogesはrailsで普通に利用しているテーブルなのでプライマリーキーはidだけで構成されています。
なのでnengetuもプライマリーキーに含めて、idとnengetuの二つのカラムで構成されるプライマリキーにします。
ユニークインデックスの一部になっていれば、よいかとも思ったのですが、プライマリーキーの一部でないとダメでした。

・文字列カラムで利用できる分割はKEYパーティショニングのみ
パーティショニングの方法にはRANGE,LIST,HASH,KEYとあるのですが、文字列カラムで分割できるのはKEYパーティショニングだけのようです。
mysql 5.5だとRANGEでも文字列カラムを利用できるっぽいです。
なので今回はKEYパーティショニングを利用します。
分割数は、毎月分割して10年分も格納できれば文句もなかろうという安易な考えから128とします。


以上のことから以下のようなmigrationを用意しました。

class AddPartitionToHoge < ActiveRecord::Migration
  def self.up
    execute("ALTER TABLE hoges DROP PRIMARY KEY, ADD PRIMARY KEY (id, nengetu);")
    execute("alter table hoges PARTITION BY LINEAR KEY (nengetu) PARTITIONS 128;")
  end

  def self.down
    execute("ALTER TABLE hoges REMOVE PARTITIONING;")
    execute("ALTER TABLE hoges DROP PRIMARY KEY, ADD PRIMARY KEY (id);")
  end
end

以下を参考にさせていただきました。
http://d.hatena.ne.jp/d2mr/20080522/1211447409

今回利用しているLINEAR KEYに関しては、以下が参考になります。
http://nippondanji.blogspot.jp/2009/05/linear-hash.html


これでアプリには変更なしで動きました。

ただテストの方に問題が出てしまいました。
test用dbのhogesの主キーがうまく設定されずにidがAUTO INCREMENTされなくなり
このため多くのテストが失敗するようになってしまいました。

確かに
db/schema.rb
を見るとidの設定がおかしな感じになっていました。
どうも複合プライマリキーが設定されているテーブルだとこんなことになるようです。

これを改善するには、

config/environment.rb
に
config.active_record.schema_format = :sql
と記載してテスト用db作成に
db/schema.rb
を利用しないようにすればOKな感じです。

参考までに35万行程度のテーブルでのパフォーマンスの変化です。
時間はrailsのログから判別したのと、それぞれ一回しか試していないので本当に参考までです。

nengetu単月での検索(7万件ぐらい)
　前：Completed in 277404ms (View: 34, DB: 142744)
　後：Completed in 154913ms (View: 29, DB: 27970)
nengetu複数月での検索（20万件ぐらい）
　前：Completed in 353752ms (View: 67, DB: 21555)
　後：Completed in 411954ms (View: 139, DB: 66896)
単月のデータ一括登録（3万件ぐらい）
　前：Completed in 2016712ms (DB: 205944)
　後：Completed in 2131911ms (DB: 161670)

単月の検索はかなり早くなった感じです。
他は、それほどでもないのですがもっとデータがあれば違う結果になりそうな気がしています。