先日「MRG_MyISAM使ってる人なんていねーよPARTITIONつかえ、PARTITION」というお告げをもらったので、ちょっとだけpartition使ってみることにしました。といってもMySQLを5.1にするところから始めないといけないので、いろいろ面倒なこともありつつ。
ただ、根本的に使用目的がちがう部分もあるので、単純にMRG_MyISAMから乗り換えってわけにもいかないでしょう。特に、既存のテーブルを気楽にmergeできるメリットは、あらかじめ分割ストラテジを決める必要があるpartitionでは実現できないものです。
とりあえず今日は使ってみた感触ですが、MyISAMのときにpackがどうなるのかとか、いつか調べたいと思います。
mysql5.1導入
すでにある5.0運用のdatadirにそのままかぶせる形で5.1に上げることにしました。
最初にハマったのは basedir が正しく取れない。というより my_print_defaults が余計な basedir を拾ってきちゃう。使ってない my.cnf が転がってる時は消すとうまくいくかも。
うまいこと起動すると、5.1からinformation schemaに新しいカラムが追加されたようで、そのままでは(起動はするけど)エラーが出てる様子。エラーに書いてあるように mysql_upgrade を実行します。
バイナリなので何してるのかよく分かりませんが、upgradeするようなsqlをなげているみたい。なので、slaveがいたらupgradeもslaveに伝播してきてちょっとハマった。あと、けっこうたくさんのテーブルでrepairが走ることになります。
5.1になって他に気づいたのは、/var/lib/mysql/.hoge みたいな "." から始まるディレクトリが database ディレクトリと認識されてしまうこと。それから、show slave statusがさらに盛りだくさんになったのと、reset masterした直後のposが98から106に変わりました。いや、だからなんだってわけでもないですが…。
partitionつくる
すでにあるテーブルからコピーで分割することにします。もとのテーブルの定義から ENGINE= を以下のように編集。
CREATE TABLE `fuga_parted` ( `fuga_id` int(10) unsigned NOT NULL DEFAULT '0', ... PRIMARY KEY (`fuga_id`), ... ) ENGINE=MyISAM PARTITION BY RANGE (fuga_id) ( PARTITION id0 VALUES LESS THAN (1000000), PARTITION id1 VALUES LESS THAN (2000000), PARTITION id2 VALUES LESS THAN (3000000), PARTITION id3 VALUES LESS THAN (4000000), PARTITION id4 VALUES LESS THAN (5000000), PARTITION id5 VALUES LESS THAN (6000000), PARTITION id6 VALUES LESS THAN (7000000), PARTITION id7 VALUES LESS THAN (8000000), PARTITION id8 VALUES LESS THAN (9000000), PARTITION id9 VALUES LESS THAN MAXVALUE );
そんで移行。
INSERT INTO fuga_parted SELECT * FROM fuga;
うーん、MYDもMYIもいいかんじに分割されてます。
% ls -lh /var/lib/mysql/hoge -rw-r--r-- 1 mysql mysql 110M Nov 18 10:26 fuga_parted#P#id0.MYD -rw-r--r-- 1 mysql mysql 319M Nov 18 10:31 fuga_parted#P#id0.MYI -rw-r--r-- 1 mysql mysql 106M Nov 18 10:31 fuga_parted#P#id1.MYD -rw-r--r-- 1 mysql mysql 137M Nov 18 10:31 fuga_parted#P#id1.MYI ... -rw-r--r-- 1 mysql mysql 13K Nov 18 08:42 fuga_parted.frm -rw-r--r-- 1 mysql mysql 88 Nov 18 08:42 fuga_parted.par
insert ... select from federated は失敗
ちなみに(パーティションとは関係ないですが)、最初は federated でつないだテーブルから insert しようとしてました。ところが Ouf Of Memory と悲しいエラーが発生してうまくいきませんでした。
local からの insert は (当然ながら) できるので、 federated で一旦どこかに保存するときにメモリに乗せているのかな。ためしに
INSERT fuga_parted SELECT SQL_BUFFER_RESULT * FROM fuga_fdr;
とかやってみたけど変わりませんでした。
ha_federated.cc の rnd_init() 部分がクライアントとしていけてないってことなのかな。なんかバグっぽい気もする。えらい人に散々言われたように、ちゃんと報告しなきゃだなー。
alterでもいけるのか?
さっきはinsert ... selectしたけど、直接alterしてみるとどうなるかな、と思ってやってみた。
ALTER TABLE fuga PARTITION BY RANGE (fuga_id) (PARTITION id0 LESS THAN (1000000), ...);
まあふつうにうまくいきました。しかも insert select よりもずいぶん速い。あんま速度とか気にしてなかったのでサーバに他の負荷があったかわかんないんだけど、50分が5分くらいに変わりました。
ついでなので元に戻してみる。これも5分くらい。
ALTER TABLE fuga REMOVE PARTITIONING;
というか "remove partitioning" って書き方が最初わかんなくて困った。どんな使い方が出来るのかは配布物の mysql-test/r/partition*.result を見るのがいちばん網羅的ですね。
性能はどんなもん?
mysql-test眺めてたら、"explain partitions select ..." という書き方ができるらしい。partition使ってないテーブルでもエラーにはならないので、いつもこれでいけそう。
primary key であるところの fuga_id を直接指定してもあんまありがたみがないので、rangeで別のカラムbarをgroup byしてみた。
まずは1つのパーティション内に収まるもの。
元のテーブル
mysql> explain partitions select sql_no_cache bar, count(*) from fuga
-> where fuga_id between 2000000 and 2999999 group by bar\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fuga
partitions: NULL <--- (1)
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 6232356 <--- (2)
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)mysql> select sql_no_cache bar, count(*) from fuga
-> where fuga_id between 2000000 and 2999999 group by bar\G
+-----+----------+
| bar | count(*) |
+-----+----------+
| 0 | 324160 |
| 1 | 335107 |
| 2 | 340733 |
+-----+----------+
2 rows in set (4.33 sec)
分割したテーブル
mysql> explain partitions select sql_no_cache bar, count(*) from fuga_parted
-> where fuga_id between 2000000 and 2999999 group by bar\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fuga
partitions: id2 <--- (1)
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 999840 <--- (4)
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)mysql> select sql_no_cache bar, count(*) from fuga_parted
-> where fuga_id between 2000000 and 2999999 group by bar;
+-----+----------+
| bar | count(*) |
+-----+----------+
| 0 | 324160 |
| 1 | 335107 |
| 2 | 340733 |
+-----+----------+
2 rows in set (1.42 sec)うん、まあ確かに速くなってますね。元のテーブルと比べて分割したやつはpartitionsにid2が選ばれていて(1, 3)、rowsが減ってます(2, 4)。
性能(2)
つづいてパーティションをまたがるもの。
元のテーブル
mysql> select sql_no_cache bar, count(*) from fuga
-> where fuga_id between 2500000 and 3499999 group by bar;
+-----+----------+
| bar | count(*) |
+-----+----------+
| 0 | 340461 |
| 1 | 333140 |
| 2 | 326549 |
+-----+----------+
2 rows in set (4.28 sec)
分割したテーブル
mysql> select sql_no_cache bar, count(*) from fuga_parted
-> where fuga_id between 2500000 and 3499999 group by bar;
+-----+----------+
| bar | count(*) |
+-----+----------+
| 0 | 340461 |
| 1 | 333140 |
| 2 | 326549 |
+-----+----------+
2 rows in set (2.44 sec)おおー、いいじゃないですか!!
性能(3)
最後。全パーティションでgroup by。ただし、実際には途中までのパーティションにしかデータが入っていません。
元のテーブル
mysql> select sql_no_cache bar, count(*) from fuga group by bar; +-----+----------+ | bar | count(*) | +-----+----------+ | 0 | 3363499 | | 1 | 1911692 | | 2 | 2197900 | +-----+----------+ 3 rows in set (5.17 sec)
分割したテーブル
mysql> select sql_no_cache bar, count(*) from fuga_parted group by bar; +-----+----------+ | bar | count(*) | +-----+----------+ | 0 | 3363499 | | 1 | 1911692 | | 2 | 2197900 | +-----+----------+ 3 rows in set (7.83 sec)
うーん、やっぱ余分なパーティションまで調べてる的な遅さがあるのかな。partitionは(repairが必要だけど)あとで追加もできるので、あまり大きく取りすぎると良くないようです。
パーティションの状態を知るには
show index すると、primary key 以外の cardinality は null になってしまいました。show table status相当なのは
mysql> select * from information_schema.partitions where TABLE_NAME = 'fuga_parted';
で得ることができます。うーん、もうちょっと、かな。
そのほか
partitionの分割はけっこういろんな方法が選べて、ドメインをシリアルに分割できるならrange、たんに分散させたいならhashかkey、またrangeに渡す値も(deterministicっぽい制約があるみたいだけど)自由に演算をかますことができます (partitioning expressions ってやつ) 。
まとめ
長くなってきたのでこのへんでまとめ。
将来のことを考えずに肥大化してきたテーブルを分割したいときに、1回のalterで分割できる気持ちよさはあるんですが、結局おなじmysqldのインスタンス配下にあるのは変わりません。インデックスもどうせb-treeなので、なにも考えず適当に分割すると、パーティションを跨いでしまった場合にすごい遅くなっちゃうかもしれません。たとえばusing indexのみで済むときは当然遅くなると思います。
それでもinsertはけっこう楽になりそうだし、もちろんパーティションを跨がなければ上で見たようにだいぶ速くなっているし、物理的にディスク分けることですごい速くなるとか、でかいファイルを扱うのがすごい遅いファイルシステムだとか、そういう場合はかなりの効果なんでしょう。
ということで、どんなインデックス張るかってのと同じように、うまく使わないといけなそうってことでした。おわり。
なーんて思ってちょっと外に出たら、今月のW+Dに特集ありますね。やっぱデータの鮮度で分ける話(i.e., 特定のパーティションにアクセスが集中する場面) が多いのかな。
