森の夢

mysqlパーティショニングのまとめ① - 設定・再コンパイル
mysqlパーティショニングのまとめ② - パーティショニングのタイプ
mysqlパーティショニングのまとめ③ - パフォーマンス

さて、前回まではパーティショニングの設定やパーティショニングのタイプについて述べてきたが、
ここからはパフォーマンスの話をしていく。

サンプルテーブル

まず、以下のサンプルテーブルを用意した。
それぞれのテーブルに2倍にあたる245746件の郵政省のデータをいれてある。

・postal_normal

CREATE TABLE postal_normal (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(7) NOT NULL,
  ken_kana varchar(255) DEFAULT NULL,
  ctiy_kana varchar(255) DEFAULT NULL,
  town_kana varchar(255) DEFAULT NULL,
  ken varchar(255) DEFAULT NULL,
  citiy varchar(255) DEFAULT NULL,
  town varchar(255) DEFAULT NULL,
  flg1 tinyint(1) NOT NULL,
  flg2 tinyint(1) NOT NULL,
  flg3 tinyint(1) NOT NULL,
  flg4 tinyint(1) NOT NULL,
  flg5 tinyint(1) NOT NULL,
  flg6 tinyint(1) NOT NULL,
  entry timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY town (town),
  KEY flg1 (flg1),
  KEY entry (entry),
  KEY `code` (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

・postal_hash16

CREATE TABLE postal_hash16 (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(7) NOT NULL,
  ken_kana varchar(255) DEFAULT NULL,
  ctiy_kana varchar(255) DEFAULT NULL,
  town_kana varchar(255) DEFAULT NULL,
  ken varchar(255) DEFAULT NULL,
  citiy varchar(255) DEFAULT NULL,
  town varchar(255) DEFAULT NULL,
  flg1 tinyint(1) NOT NULL,
  flg2 tinyint(1) NOT NULL,
  flg3 tinyint(1) NOT NULL,
  flg4 tinyint(1) NOT NULL,
  flg5 tinyint(1) NOT NULL,
  flg6 tinyint(1) NOT NULL,
  entry timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY town (town),
  KEY flg1 (flg1),
  KEY entry (entry),
  KEY `code` (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8
PARTITION BY LINEAR HASH (id) PARTITIONS 16;

テストケース

上記のデータに対して、以下のテストを行う。

• id=240000で検索
• id IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007)で検索
• code=1000000で検索
• ken LIKE '%北海道%'
• １レコード追加

id=240000で検索

・パーティショニング

select * from postal_hash16 where id = 240000;
1 row in set (0.02 sec)

・ノーマル

select * from postal_normal where id = 240000;
1 row in set (0.01 sec)

さすがに、パーティションしていないとはいえインデックスの効いたたかが24万件なので差はあまり無いと思われる。
ただ、パーティションしてあるほうはたかだか1万ちょっとからの検索になるはずなので早いはずだが・・・

id IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007)で検索

・パーティショニング

select * from postal_hash16 where id IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007);
8 rows in set (0.00 sec);

・ノーマル

select * from postal_normal where id  IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007);
8 rows in set (0.00 sec)

うーん・・・検索件数を増やしてもスピードは変わらず・・・
やはり24万件程度では差がでないということか。

code=1000000で検索

・パーティショニング

 select * from postal_hash16 where code = 1000000;
2 rows in set (0.26 sec)

・ノーマル

 select * from postal_normal where code = 1000000;
2 rows in set (0.03 sec)

これは違いがはっきり出た。
やはりパーティションしているほうが遅いということである。この場合インデックスは効いていないのかも・・・。

ken LIKE '%北海道%'で検索

・パーティショニング

 select * from postal_hash16 where ken LIKE '%北海道%'
16454 rows in set (2.34 sec)

・ノーマル

 select * from postal_normal where ken LIKE '%北海道%'
16454 rows in set (1.74 sec)

なるほど、インデックスの効いていないカラムに対しては、
ノーマルもやはり遅いが、やはりパーティショニングしている方が遅い結果になった。

１レコード追加

・パーティショニング

INSERT INTO `test2`.`postal_hash16` (`code`, `ken_kana`, `ctiy_kana`, `town_kana`, `ken`, `citiy`, `town`, `flg1`, `flg2`, `flg3`, `flg4`, `flg5`, `flg6`, `entry`) VALUES ( "9071801","ｵｷﾅﾜｹﾝ","ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁﾖｳ","ﾖﾅｸﾞﾆ","沖縄県","八重山郡与那国町","与那国",0,0,0,0,0,0,NOW() );
Query OK, 1 row affected (0.04 sec)

・ノーマル

INSERT INTO `test2`.`postal_normal` (`code`, `ken_kana`, `ctiy_kana`, `town_kana`, `ken`, `citiy`, `town`, `flg1`, `flg2`, `flg3`, `flg4`, `flg5`, `flg6`, `entry`) VALUES ( "9071801","ｵｷﾅﾜｹﾝ","ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁﾖｳ","ﾖﾅｸﾞﾆ","沖縄県","八重山郡与那国町","与那国",0,0,0,0,0,0,NOW() );
Query OK, 1 row affected (0.02 sec)

パーティショニングを計算してインサートする分どうしても遅くなってしまう。

まとめ

今回の実験結果では、パーティショニングのキーとなったプライマリキーの検索ではパフォーマンスは同じであったが、
それ以外のカラムの検索ではパフォーマンスが大きく低下してしまう結果になった。
ただし、24万件ではなく、100万件、1000万件レベルのテストになるとパーティショニングした場合のプライマリキーでの検索に効果が見られる可能性はある。

現時点では他のカラムの検索や、インサートなどのパフォーマンスが劇的に落ちてしまうため、24万レコードレベルのテーブルではパーティショニングは導入すべきでないと考える。

もし、使用するとなれば、アクセスログやクエリーログをテーブル化し、データ量がそれこそ何千万件となったときに効果を発揮するのかもしれない。
是非、大容量のデータでテストをしてみたいが、なんせインサートが遅いのとマシンのスペックが追いつかないため今回は見送らせていただく。

実は仕事でパーティショニングを使ってみたかったが、今回のパフォーマンスではなかなか難しそうだなぁ～

written by YSU \\ tags: mysql, まとめ

mysqlパーティショニングのまとめ① - 設定・再コンパイル
mysqlパーティショニングのまとめ② - パーティショニングのタイプ
mysqlパーティショニングのまとめ③ - パフォーマンス
前回の記事では、mysqlのパーティショニングを使うための、mysql本体のセッティングについて述べたが、
今回はパーティショニングの種類について記述していく。

サンプルテーブル

まず、説明を始める前に今回はサンプルとして、郵政省の郵便番号データを扱う以下のテーブルを用意した。
いろいろカラムが分かれているが、code(郵便番号)にたいして、フラグやら県名を保持しているテーブルだ。

CREATE TABLE　`postal` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(7) NOT NULL,
  `ken_kana` varchar(255) DEFAULT NULL,
  `ctiy_kana` varchar(255) DEFAULT NULL,
  `town_kana` varchar(255) DEFAULT NULL,
  `ken` varchar(255) DEFAULT NULL,
  `citiy` varchar(255) DEFAULT NULL,
  `town` varchar(255) DEFAULT NULL,
  `flg1` tinyint(1) NOT NULL,
  `flg2` tinyint(1) NOT NULL,
  `flg3` tinyint(1) NOT NULL,
  `flg4` tinyint(1) NOT NULL,
  `flg5` tinyint(1) NOT NULL,
  `flg6` tinyint(1) NOT NULL,
  `entry` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `town` (`town`),
  KEY `flg1` (`flg1`),
  KEY `entry` (`entry`)
)
DEFAULT CHARSET=utf8
ENGINE=InnoDB;

パーティショニングの種類

さて、本題に入る。
mysqlのパーティショニングには以下の４つのタイプが存在する。

• RANGEパーティショニング
• LISTパーティショニング
• [LINEAR] HASHパーティショニング
• [LINEAR] KEYパーティショニング

RANGEパーティショニング

LANGEパーティショニングとは、値の範囲を絞って、パーティショニングする方法である。
あくまでも、パーティショニングする値がある程度決まっている場合などに使用できる。
今回は郵便番号は12万レコード前後で推移すると考え、ＩＤを30000毎に区切ってみる。
（サンプルでは、日付で絞る例がおおい）
「ENGINE=InnoDB」の後ろに以下のように書き加える。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY RANGE (id) (
PARTITION p1 VALUES LESS THAN (30000),
PARTITION p2 VALUES LESS THAN (60000),
PARTITION p3 VALUES LESS THAN (90000),
PARTITION p4 VALUES LESS THAN MAXVALUE
);

LISTパーティショニング

LISTパーティショニングは、あるカラムの値の候補が決まっている場合に使われる。
例えば、性別、誕生月、などであろうか・・・。
今回はいい例ではないかもしれないが、flg1を1,0に分けてみる。
「ENGINE=InnoDB」の後ろに以下のように書き加える。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LIST (flg1) (
PARTITION p1 VALUES IN (0),
PARTITION p2 VALUES IN (1)
);

しかし、これはエラーになる。

 A PRIMARY KEY must include all columns in the table's partitioning function

プライマリーはパーティショニングの定義に入れなければならない・・・とのこと。なんやねん。
といわけで、MySQLのパーティショニングで必要そうな工夫を元に以下みたいな書き方をしてみる。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LIST (( id * 0 ) + flg1) (
PARTITION p1 VALUES IN (0),
PARTITION p2 VALUES IN (1)
);

うーん・・・いけない・・・。
ちなみに、15.5. パーティショニングの制約と制限によれば、

とのこと。是非、取り除いてほしいですね。
どうやらlistを使用するにはprimaryを外さないと使えなさそうだが、いけてなさすぎるので今回はつかわない。

[LINEAR] HASHパーティショニング

HASHパーティショニングとは、行が格納されるパーティションを算出するのにMOD（剰余）を利用するものである。
RANGEやLISTと違い、あらかじめ値の範囲や候補をしらなくても動的にパーティショニングできる。
また、各パーティショニングのかたよりも無いと思われる。

また、LINER HASHというものがあるが、これは
15.2.3.1. LINEAR HASH パーティショニングによれば

とのことらしい、だが計算が難しくてよくわからないが漢(オトコ)のコンピュータ道によれば、

ということらしい。
なので、HASHを使う場合は「ENGINE=InnoDB」の後ろに以下のように書き加えるとよいだろう。
下記の例だと、idをキーにして1024パーティショニングすることになる。
１パーティショニングあたり、120くらい、めっちゃはやそう。
＊1024がパーティショニングの限界値

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LINEAR HASH (id) PARTITIONS 1024;

[LINEAR] KEYパーティショニング

KEYパーティショニングはPASSWORD()関数を使ってハッシュ値を算出する。PASSWORD関数を利用するので、文字列に対しても利用することが出来る。が、ここでもPrimaryの制約があり使用できそうにない（ためしていない）。
もし、教科書的に書くなら「ENGINE=InnoDB」の後ろに以下のように書き加える感じだと思う。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LINEAR KEY(ken) PARTITIONS 1024
);

パーティショニング確認

以上がパーティショニングの説明となる。
これらのテーブルのパーティショニングの状態をチェックするには、

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=postal

というＳＱＬを投げれば確認できる。
では、次回はパーティショニングのパフォーマンスについて述べていこうと思う。

written by YSU \\ tags: mysql, まとめ

mysqlパーティショニングのまとめ① - 設定・再コンパイル
mysqlパーティショニングのまとめ② - パーティショニングのタイプ
mysqlパーティショニングのまとめ③ - パフォーマンス

mysqlのパーティショニングを試してみたかったが、
私のmysqlではパーティショニングが使えなかったようなので
今日はmysqlパーティショニングの再コンパイルについて

まずは、mysqlのパーティショニングが使えるかどうか調べる。

mysql> SHOW VARIABLES LIKE '%PARTITION%' ;
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.01 sec)

上記のようにhave_partitioningがYESとなっていたら、パーティショニングが使用可能である。
私は使用不可であったので、再コンパイルしなければならない。

まずは念のためmysqlをストップ

/usr/local/mysql/share/mysql/mysql.server stop

*パスは各自のパスをご確認ください。

以前のコンパイルオプションに

--with-ndbcluster--with-partition

をつけて

make
make install

で再コンパイル完了。
ちなみに、以前のコンパイルオプションは
mysqlのソースがはいっているディレクトリに移動して

grep "$ ./configure" config.log

とでも打てばいいだろう。

あとはmysqlを起動して、パーティショニングが使用できることを確認できればＯＫだ。

/usr/local/mysql/share/mysql/mysql.server start

[おまけ]現在の私のオプション

--with-charset=utf8
--with-extra-charsets=all
--with-mysqld-user=mysql
--with-innodb
--with-falcon
--with-maria
--with-heap
--with-myisam
--enable-local-infile
--prefix=/usr/local/mysql
--with-unix-socket-path=/tmp/mysql.sock
--with-ndbcluster
--with-partition
--with-blackhole-storage-engine

いろんなストレージを試してみたい。

written by YSU \\ tags: mysql, まとめ, インフラ, 自宅サーバー

ひさびさのwordpress関連の投稿。
最近、twitterにはまっているので、ブログの更新をtwitterでつぶやきたいなーとかサイドバーに自分のつぶやき表示したいなーとか思っていたところ、やはりtwitterがらみのプラグンがあった。

twitter tools

これをいれると、自分のつぶやきがサイドバーに表示されたり、ブログの更新をtwitterに投稿したり、はたまた自分のつぶやきをblogに投稿できる優れものだ！

導入は以下のとおり
まず、ダウンロード・インストール

cd wp-content/plugins/
wget http://downloads.wordpress.org/plugin/twitter-tools.2.0.zip
unzip twitter-tools.2.0.zip
rm twitter-tools.2.0.zip

これプラグインの導入は完了。
つづいて、管理画面->プラグイン　でtwitter toolsを有効にする。
その後、管理画面->設定->twitter tools　で色々設定できる。

サイドバーに表示する場合は、
管理画面->概観->ウィジット　にtwitter toolsというプラグインがあるので、それをドラッグ＆ドロップすれば完了だ！

というわけで、これを投稿したらtwitterにも反映されるはずだが・・・。

written by YSU \\ tags: wordpress, プラグイン, ワードプレス