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>
テーブルを作成し終わったのでデータを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>
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入門