パーティショニングが非常にフィットする（たぶん昨日の例よりも）もう一つのケースは、数日間だけ必要なデータを蓄えておくような場合だ。例えば、HTTPセッションやログ情報などが良い例ではないだろうか。そういう場合には、日付を使ってRANGEパーティショニングをするのである。RANGEパーティショニングでももちろんPruningによって性能の向上は出来るのだが、それよりも何よりも高速に不要なパーティションを破棄できるというのが大きい。パーティションの破棄は、内部的にはテーブルのDROPとほぼ同じ扱いなのである。DROPのスピードはストレージエンジンによるが、InnoDBやMyISAM、NDB（MySQL Cluster）ならばいくらデータを含んでいても関係なくDROPは一瞬である。テーブルから大量の行を削除すると、フラグメンテーションが発生したり、インデックスの統計情報が乱れてしまったりするのだが、パーティションごと削除してしまえばそのような心配は一切ないのでお勧めである。
RANGEパーティショニングは次のようにテーブルを定義して使う。
mysql> CREATE TABLE http_session (
-> session_id VARCHAR(32) NOT NULL,
-> last_access TIMESTAMP NOT NULL,
-> created TIMESTAMP NOT NULL,
-> t_session_data VARCHAR(1024)
-> ...(中略)...
-> PRIMARY KEY (session_id, last_access))
-> ENGINE InnoDB
-> PARTITION BY RANGE (TO_DAYS(last_access)) (
-> PARTITION p000001 VALUES LESS THAN (TO_DAYS('2009-04-01')),
-> PARTITION p000002 VALUES LESS THAN (TO_DAYS('2009-04-02')),
-> ...(中略)...
-> PARTITION p000020 VALUES LESS THAN (TO_DAYS('2009-04-20')),
-> PARTITION pmax VALUES LESS THAN MAXVALUE
-> );
パーティショニングでは、日付データなどを直接利用することは出来ない。従って、TO_DAYS()関数などを使って整数値に直してやる必要がある。上記の場合、どのパーティションにアクセスするかということを計算するには、毎回TO_DAYS()関数を呼び出す必要がある。TO_DAYS()関数がオーバーヘッドになるんじゃないか？と思うかも知れないが、その通りである。従って、RANGEパーティショニングをする場合には、整数を弾き出すための関数がどの程度のオーバーヘッドになってしまうのかを計算しておくといい。どうやって？それにはBENCHMARK()関数を使う。BENCHMARK()関数は、ある関数を何度も連続して実行し、掛かった時間を計測するためのものである。例えば、TO_DAYS()を一億回実行した場合には次のような結果になる。
mysql> SELECT BENCHMARK(100000000,TO_DAYS('2009-04-20'));
+--------------------------------------------+
| BENCHMARK(100000000,TO_DAYS('2009-04-20')) |
+--------------------------------------------+
| 0 |
+--------------------------------------------+
1 row in set (21.16 sec)
TO_DAYS()ならば一億回実行しても20秒ちょいなので、あまりオーバーヘッドを気にしなくてもいいだろう。ただし、連続して実行するとCPUのキャッシュがヒットしまくりなので、その分はを少しさっ引いて考える必要があるのだが、この程度の実行時間であればそこまで厳密に考えて心配する必要はないだろう。
あとはこのテーブルに対して普通にアクセス（INSERT/SELECT/UPDATE）すれば良い。しばらく経過して、古いデータが不要になってきたなと思ったら、パーティションごとDROPする。
mysql> ALTER TABLE http_session DROP PARTITION p000001; Query OK, 0 rows affected (1.69 sec) Records: 0 Duplicates: 0 Warnings: 0
時間が経過して、新しいパーティションを作成しないといけなくなった場合には、次のようにしてパーティションを追加する。
mysql> ALTER TABLE http_session REORGANIZE PARTITION pmax INTO (
-> PARTITION p000021 VALUES LESS THAN (TO_DAYS('2009-04-21')),
-> PARTITION pmax VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (1.97 sec)
Records: 0 Duplicates: 0 Warnings: 0
時間の経過と共に、古いパーティションの追加と新しいパーティションの削除を繰り返していけば良いわけである。cronやEvent Schedularを使って自動的に実行すると良いだろう。
パーティショニングはMySQL 5.1で追加された比較的新しい機能であり、MySQLユーザーの間ではまだあまり使い方がよく理解されていないのではないだろうか。しかし、パーティショニングを上手に使えば運用が楽になったり性能が向上したりと美味しいところだらけなので、ぜひうまく活用して快適なDBライフを満喫して頂きたい。
accesslog などどうでしょう？
返信削除create table accesslog(
id int unsigned auto_increment,
type tinyint unsigned not null,
tstmp datetime,
primary key(id, type)
) ...
partition by range(type) (
partition p0 less than(7),
partition p1 less than(15)
:
partition pmax less than mavalue
);
type などはいわゆる種別(resource type:robot,ua)で、どれも同じくらいinsertされる場合に使う。とか
あと、このlogのarchiveとして
create table accesslog_archive(
id int unsigned,
type tinyint unsigned,
tstmp date,
primary key(id, type, tstmp)
)...
partition by hash(extract(YEAR_MONTH from tstmp)) partitions 12;
これだと、where tstmp で期間が指定されたときに、年月レベルでは同じselect性能出せそうじゃないですかねぇ
> nさん
返信削除個人的には前者のような場合はHASHパーティショニングでいいのではないかと思います。
PARTITION BY HASH(type) PARTITIONS 10
などとしておけば、typeの値によってPruningが有効になりますし、typeのとりうる値が変化しても（新しいtypeなどが追加された場合でも）特にメンテナンスの必要がありません。
後者のような場合には、逆にRANGEパーティショニングが良いと思います。
PARTITION BY RANGE (TO_DAYS(tstmp) (PARTITION p1 VALUES LESS THAN (TO_DAYS('2009-04-01')), PARTITION p2 VALUES LESS THAN (TO_DAYS('2009-05-01'))...)
こうしておくと、後で古いログが要らなくなったときに、パーティションごと削除することが可能だからです。
ALTER TABLE accesslog_archive DROP PARTITION p1;
パーティションのドロップはテーブルをドロップするときのように非常に高速に行う事ができます。もちろんフラグメンテーションの心配なども不要ですから、管理面ではメリットがありますね。HASHパーティショニングだと、パーティションごとドロップするという技が使えませんので。
あと、合わせ技として、RANGEとHASHを同時に使うというのが考えられます。
create table accesslog(
id int unsigned auto_increment,
type tinyint unsigned not null,
tstmp datetime,
primary key(id, type, tstmp)
) partition by range(to_days(tstmp))
subpartition by hash(type)
subpartitions 10
(partition p0 values less than (to_days('2009-04-01')),
partition p1 values less than (to_days('2009-05-01')),
...
partition pmax values less than maxvalue
);
このパターンで注意しなければいけないのはパーティションの数です。サブパーティションも含めて1024個までしか定義することが出来ません。
非常にためになるアドバイスありがとうございます!
返信削除確かに、前者は hash がとても有効に使えますね...
後者に関しては、確かに partition のドロップについて考慮していなかったです。
だから、sessionでもto_daysで日付を入れていたのですね(日付を管理するのが面倒だろう。と勝手に思ってました)
subpartitionも検討していたのですが、なんか面倒っぽいなぁ。と
というか partition の drop を考えていなかったです。
ずっと hash で考えていたので subpartition のメリットが考えつかなかったです^^;
ものすごく有用なコメントでしたありがとうございます!
> nさん
返信削除どういたしまして :)
いつでも気軽に質問してください。