Edited at
RubyDay 13

Rails3からMySQLのパーティショニングを扱う時の注意点

More than 5 years have passed since last update.

10月に札幌でMySQL勉強会というのがありまして、その時はもくもく作業して成果発表という形だったのですが、そのときにRailsとMySQLを組み合わせて色々試したのでそれを記事にしたいと思います。

今回はパーティショニング用のgemを使っていない状態です(後で見たら存在していることに気づきました...

パーティショニングについてですが、DB内部で物理的にファイルを分けるようなものです。

例えば、MySQLのDELETE文はとても遅いですが、パーティション自体を消す場合はTRUNCATE並に早いです(1ヶ月ごとにパーティションを切って、月頭に3ヶ月より前のログを削除する時とかに便利

他にも、MySQLのオプティマイザが必要なパーティションを探しだして、そのパーティションに絞ってから検索してくれるメリットもあります。

https://github.com/miio/mysql-partition-test

基本形はこちらです


20121013061506_create_no_part_items.rb

class CreateNoPartItems < ActiveRecord::Migration

def change
create_table :no_part_items do |t|
t.integer :user_id, null: false
t.integer :item_id, null: false
t.datetime :deleted_at
t.timestamps
end

add_index :no_part_items, [:user_id]
add_index :no_part_items, [:deleted_at]

end
end


これにレンジパーティションとリストパーティションを張ってみます

レンジパーティションを貼ったのがこちら


20121013061526_create_part_range_items.rb

class CreatePartRangeItems < ActiveRecord::Migration

def change
create_table :part_range_items do |t|
t.integer :user_id, null: false
t.integer :item_id, null: false
t.datetime :deleted_at
t.timestamps
end

add_index :part_range_items, [:id]
add_index :part_range_items, [:user_id]
add_index :part_range_items, [:deleted_at]

# ADD PRIMARY
execute "ALTER TABLE part_range_items DROP PRIMARY KEY"
execute "ALTER TABLE part_range_items add primary key(id, deleted_at)"

remove_index :part_range_items, [:id]

# ADD PARTITION
execute "
ALTER TABLE part_range_items
PARTITION BY RANGE (TO_DAYS(deleted_at))
(
PARTITION p20120101 VALUES LESS THAN (TO_DAYS('2012-01-01')) ENGINE = InnoDB,
PARTITION p20120201 VALUES LESS THAN (TO_DAYS('2012-02-01')) ENGINE = InnoDB,
PARTITION p20120301 VALUES LESS THAN (TO_DAYS('2012-03-01')) ENGINE = InnoDB,
PARTITION p20120401 VALUES LESS THAN (TO_DAYS('2012-04-01')) ENGINE = InnoDB,
PARTITION p20120501 VALUES LESS THAN (TO_DAYS('2012-05-01')) ENGINE = InnoDB,
PARTITION p20120601 VALUES LESS THAN (TO_DAYS('2012-06-01')) ENGINE = InnoDB,
PARTITION p20120701 VALUES LESS THAN (TO_DAYS('2012-07-01')) ENGINE = InnoDB,
PARTITION p20120801 VALUES LESS THAN (TO_DAYS('2012-08-01')) ENGINE = InnoDB,
PARTITION p20120901 VALUES LESS THAN (TO_DAYS('2012-09-01')) ENGINE = InnoDB,
PARTITION p20121001 VALUES LESS THAN (TO_DAYS('2012-10-01')) ENGINE = InnoDB,
PARTITION p20121101 VALUES LESS THAN (TO_DAYS('2012-11-01')) ENGINE = InnoDB,
PARTITION p20121201 VALUES LESS THAN (TO_DAYS('2012-12-01')) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);
"

end
end


少々奇妙なことに、わざわざidカラムにインデックスを張ってから消してます

これは、レンジパーティションを貼る際に、idとレンジの対象となるカラム(今回だとdeleted_atですね)で複合プライマリを作る必要があります。

しかしながら、AUTO_INCREMENTを持ったプライマリキーをそのままドロップしちゃうと、MySQLに怒られてしまいます

そこで、同時にインデックスも張っておいてからidカラムをprimaryから外すということをやっています

また、複合プライマリになりますが、実運用上はidカラムを見ればいいのでModel側ではこういう設定を行います


part_range_item.rb

class PartRangeItem < ActiveRecord::Base

attr_accessible :user_id, :item_id, :deleted_at
self.primary_key = :id
# self.primary_keys = :id, :deleted_at
end

self.primary_key = :id とすることで、いつも通りに使えます

ちなみに、リストパーティションもレンジパーティションと同じ要領です


20121013061604_create_part_two_items.rb

class CreatePartTwoItems < ActiveRecord::Migration

def change
create_table :part_two_items do |t|
t.integer :user_id, null: false
t.integer :item_id, null: false
t.boolean :is_deleted
t.timestamps
end

add_index :part_two_items, [:id]
add_index :part_two_items, [:user_id]
add_index :part_two_items, [:is_deleted]

# ADD PRIMARY
execute "ALTER TABLE part_two_items DROP PRIMARY KEY"
execute "ALTER TABLE part_two_items add primary key(id, is_deleted)"

remove_index :part_two_items, [:id]

# ADD PARTITION
execute "
ALTER TABLE part_two_items
PARTITION BY LIST(is_deleted) (
PARTITION pTrue VALUES IN (1),
PARTITION pFalse VALUES IN (0)
);
"

end
end


最後に、この記事とは別ですが宣伝

https://github.com/miio/activerecord-locking-symbolic

Example.find(1, lock: "LOCK IN SHARE MODE")

みたいな書き方が個人的にどうよ?って思ったので

 Example.find(1, lock: :read)

みたいにできるgem作りました。もしよければどうぞ〜