ある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)
単月の検索はかなり早くなった感じです。
他は、それほどでもないのですがもっとデータがあれば違う結果になりそうな気がしています。
0 件のコメント:
コメントを投稿