MYSQL RANGE COLUMNS PARTITIONING

by

MYSQL RANGE COLUMNS partitioning

データ量の多いテーブルでWHERE句で効率的にインデックスを利用出来ないテーブルや、
テーブルを分ける事が出来ないログなどを蓄積しておくテーブルにはパーティショニングが向いている可能性あります。
特に大きなテーブルでTruncateが出来ないテーブルでのデータ削除は非常に時間がかかるので、
Partitionを組んでPartition単位でデータ削除が出来るのは非常に助かります。
但し常にパフォーマンスが上がるわけでは無いので、どのような用途にテーブルが利用されているかまずは確認して下さい。

今回の検証では、既存テーブルを変更するので先ずはDBダンプしました。

aws$ mysqldump --all-databases --single-transaction --flush-logs > /home/ec2-user/mysql_dump20120929.sql  -u root -p
Enter password:
aws$ ls -l /home/ec2-user/mysql_dump20120929.sql
-rw-rw-r-- 1 ec2-user ec2-user 1119781 Sep 29 00:59 /home/ec2-user/mysql_dump20120929.sql
aws$

テーブルのデータサイズを予め確認しておいても良いです。

mysql> select
    -> table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
    -> floor((data_length+index_length)/1024/1024) as allMB,
    -> floor((data_length)/1024/1024) as dMB,
    -> floor((index_length)/1024/1024) as iMB
    -> from information_schema.tables
    -> where table_schema=database()
    -> order by (data_length+index_length) desc;

テーブルを変更するので先ずは事前にデータ数の確認。
現在はあまりデータが無いですが、将来的に増える可能性があるテーブルを選択。

mysql> select count(*) from wp01_posts;
+----------+
| count(*) |
+----------+
|       77 |
+----------+
1 row in set (0.00 sec)

失敗した時の為にデータを簡単にLOADする為にテーブルのデータだけバックアップしておく。
ファイルに落とすと同時に、ディスクに余裕があるので既存テーブルをRENAMEしていた。

mysql> SELECT * INTO OUTFILE '/tmp/wp01_posts.txt'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> FROM WP01.wp01_posts;
Query OK, 77 rows affected (0.03 sec)

mysql>

mysql> ALTER TABLE wp01_posts RENAME TO wp01_posts_0929;
Query OK, 0 rows affected (0.05 sec)

事前確認とバックアップ

パーティションを適用したテーブルを作成。

mysql> CREATE TABLE `wp01_posts` (
    ->   `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
    ->   `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `post_content` longtext NOT NULL,
    ->   `post_title` text NOT NULL,
    ->   `post_excerpt` text NOT NULL,
    ->   `post_status` varchar(20) NOT NULL DEFAULT 'publish',
    ->   `comment_status` varchar(20) NOT NULL DEFAULT 'open',
    ->   `ping_status` varchar(20) NOT NULL DEFAULT 'open',
    ->   `post_password` varchar(20) NOT NULL DEFAULT '',
    ->   `post_name` varchar(200) NOT NULL DEFAULT '',
    ->   `to_ping` text NOT NULL,
    ->   `pinged` text NOT NULL,
    ->   `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `post_content_filtered` longtext NOT NULL,
    ->   `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
    ->   `guid` varchar(255) NOT NULL DEFAULT '',
    ->   `menu_order` int(11) NOT NULL DEFAULT '0',
    ->   `post_type` varchar(20) NOT NULL DEFAULT 'post',
    ->   `post_mime_type` varchar(100) NOT NULL DEFAULT '',
    ->   `comment_count` bigint(20) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`ID`,`post_date`),
    ->   KEY `post_name` (`post_name`),
    ->   KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
    ->   KEY `post_parent` (`post_parent`),
    ->   KEY `post_author` (`post_author`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> /*!50500 PARTITION BY RANGE COLUMNS(`post_date`)
    -> (
    ->  PARTITION  p001  VALUES LESS THAN ('2012-12-31') ENGINE = InnoDB,
    ->  PARTITION  p002  VALUES LESS THAN ('2013-12-31') ENGINE = InnoDB,
    ->  PARTITION  p003  VALUES LESS THAN ('2014-12-31') ENGINE = InnoDB,
    ->  PARTITION  p004  VALUES LESS THAN ('2015-12-31') ENGINE = InnoDB,
    ->  PARTITION  p005  VALUES LESS THAN ('2016-12-31') ENGINE = InnoDB,
    ->  PARTITION  pmax   VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
    -> ) */
    -> ;
Query OK, 0 rows affected (0.16 sec)

mysql>

create and insert

テーブルを作成し終わったのでデータをINSERTしてデータ量を確認。
サイトの表示も合わせて確認しておく。

mysql> insert into wp01_posts select * from wp01_posts_0929;
Query OK, 77 rows affected (0.04 sec)
Records: 77  Duplicates: 0  Warnings: 0

mysql>


mysql> select count(*) from WP01.wp01_posts;
+----------+
| count(*) |
+----------+
|       77 |
+----------+
1 row in set (0.00 sec)

mysql>

create table

RENAMEしたﾃｰﾌﾞﾙはｻｲﾄの動作確認とﾊﾟﾌｫｰﾏﾝｽ等に問題が無い事が確認出来るまで残しておく。

参考サイト：
18.2.3.1. RANGE COLUMNS partitioning
15.2.1. RANGE パーティショニング
15.3.1. RANGE と LIST パーティションの管理
パーティショニングの使用例 – カーディナリティが低いカラムを使って検索する場合
パーティショニングの使用例 – http session情報
MYSQLにてTABLE名の変更
MySQL 5.5.4が与えるインパクト。
ソーシャルゲームのためのMySQL入門