サブパーティショニングを試してみた

http://dev.mysql.com/doc/refman/5.1/ja/partitioning-subpartitions.html

mysqlでは、パーティショニングにより分割したテーブルを更に分割できるサブパーティショニングという機能があるそうですので、その手順を書き留めておきます。

※mysqlのドキュメントには次のように記載されており、親パーティションと子パーティションの組み合わせには制限があるように思えますが、その点については時間がある際に調べます。

MySQL 5.1 では、RANGE や LIST によってパーティショニングされたテーブルを
さらにサブパーティショニングすることが可能です。
サブパーティショニングは HASH または KEY  パーティショニングを
用いることがあります。これは、複合パーティショニング  とも呼ばれます。 

あるテーブルに対してパーティショニング=RANGE,サブをパーティショニング=HASHを行うには、次のようなsqlを使用します。

CREATE TABLE sokutei_data (
  user_sid int,
  ch_no	   int,
  date	   date,
  time	   time,
  val	   decimal(10,2),
  primary  key(user_sid,ch_no,date,time)
);

ALTER TABLE sokutei_data
PARTITION BY RANGE( YEAR(date) )
SUBPARTITION BY HASH( user_sid )
SUBPARTITIONS 3 (
  PARTITION p_2010 VALUES LESS THAN (2010),
  PARTITION p_2011 VALUES LESS THAN (2011),
  PARTITION p_max  VALUES LESS THAN MAXVALUE
);

実際のパーティショニング内容の確認やするには、パーティショニングの解除は次のsql。

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS 
         WHERE TABLE_NAME='sokutei_data'\G
*************************** 1. row ***************************
                TABLE_CATALOG: NULL
                 TABLE_SCHEMA: test
                   TABLE_NAME: sokutei_data
               PARTITION_NAME: p_2010
            SUBPARTITION_NAME: p_2010sp0
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: 1
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: HASH
         PARTITION_EXPRESSION:  YEAR(date)
      SUBPARTITION_EXPRESSION:  user_sid
        PARTITION_DESCRIPTION: 2010
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 1710227456
                  CREATE_TIME: NULL
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
＜略＞

ALTER TABLE sokutei_user REMOVE PARTITIONING;

1テーブルのパーティション数は最大1024(サブパーティション含む)

この辺りも参考に
http://dev.mysql.com/doc/refman/5.1/ja/partitioning-limitations.html
http://dev.mysql.com/doc/refman/5.1/ja/creating-many-tables.html

最後に

サブパーティショニングの作成手順は難しくありませんでしたが、パフォーマンスに対する影響までは調べていません。
rsyncによるバックアップでは、バックアップ対象が限定されて負荷軽減になるとおもいますが...