Spiderってなんじゃ？（SUBPARTITIONで負荷分散しながらストレージ容量を無限追加）

Spiderシリーズです。Spiderではパーティションを使ってシャーディングしますが、パーティションの定義によって分散の特性が変わります。

1. サロゲートキーなどのHASHパーティション

• データは均等に分散
• 更新負荷は均等に分散
• 増設はデータ全体の再ハッシュが必要

2. 日付の年月などのHASHパーティション

• データは順次格納
• 更新負荷は常に1データノードにのみかかる
• 増設は新しい年月を追加

3. サロゲートキーなどのRANGEパーティション

• データは順次格納
• 更新負荷は常に1データノードにのみかかる
• 増設は新しいキー範囲を追加

理想は均等に負荷分散しながら、データ容量を後から増やせるのがベストですが、１，２，３とも負荷分散と容量追加のどちらかにしか親和性がありません。
そこで、作者の斯波さん(@kentokushiba)に伺ったところ、　SpiderでもSUBPARTITIONが使えるとのことでRANGEとHASHを組み合わせることで実現できると教えて頂きました。

早速やってみます。

CREATE TABLE `spdoc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `content` text,
  `docname` varchar(255),
  INDEX(created_at) ,
  PRIMARY KEY (`id`)
) ENGINE=SPIDER DEFAULT CHARSET=utf8 CONNECTION=' table "spdoc", user "xxxxxxxx", password "yyyyyyyyy" auto_increment_mode "1" '
PARTITION BY RANGE (id)
SUBPARTITION BY HASH(id) (
  PARTITION p1 VALUES LESS THAN (1000000) (
    SUBPARTITION db1 COMMENT = 'host "10.0.1.141", port "3306"' ENGINE = SPIDER,
    SUBPARTITION db2 COMMENT = 'host "10.0.1.142", port "3306"' ENGINE = SPIDER
  ),
  PARTITION p2 VALUES LESS THAN (2000000) (
    SUBPARTITION db3 COMMENT = 'host "10.0.1.143", port "3306"' ENGINE = SPIDER,
    SUBPARTITION db4 COMMENT = 'host "10.0.1.144", port "3306"' ENGINE = SPIDER
  )
);

まずRANGEパーティションをidの値の範囲で定義します。試しに1000000,2000000に設定します。
次に、SUBPARTITIONでidでHASHパーティションを定義し、ここにデーターノードを指定します。

すると、idが1000000以下のデータについてdb1, db2のデータノードに均等に分散され、
1000000〜2000000の範囲では、db3, db4に均等に分散されます。

idが2000000を超えそうになった場合は、後から３つめのパーティションを追加することができます。

ALTER TABLE spdoc ADD PARTITION
(
  PARTITION p3 VALUES LESS THAN (3000000) (
    SUBPARTITION db5 COMMENT = 'host "10.0.1.145", port "3306"' ENGINE = SPIDER,
    SUBPARTITION db6 COMMENT = 'host "10.0.1.146", port "3306"' ENGINE = SPIDER
  )
);

これでデータ容量はほぼ無限に増やすことができます。

パーティションごとにSUBPARTITIONの数を変えることはできないので、負荷の分散数は同じデータノード数で賄わなければいけませんが、その場合はパーティションごとのデータノードのインスタンスタイプやPIOPSを増やすことで処理能力をアップさせることで対応できるかと思います。

斯波さんありがとうございました！

以上です。

spiderってなんじゃ？（spider自身の冗長化）

いままではSpiderがひとつに対して、データノードが複数ある構成をとってきましたが、実戦ではSpiderが単一障害点になるため好ましくありません。ここではSpider自身の冗長化を行なってみます。

いままでのSpider x 1 + mroonga x 2 の構成に対してデータを入れ込むためのappノードを用意します。

SpoderのSPOF

mroonga

 CREATE TABLE doc (
  id INT PRIMARY KEY AUTO_INCREMENT,
  created_at datetime,
  content text,
  FULLTEXT INDEX (content)
 ) ENGINE = mroonga; 

spider

 CREATE TABLE doc (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  created_at datetime,
  content text,
  FULLTEXT INDEX (content)
 ) ENGINE = spider CHARSET utf8
 CONNECTION ' table "doc", user "memorycraft_user", password "memorycraft_pass" '
 PARTITION BY KEY() (
  PARTITION db1 comment 'host "10.0.1.136", port "3306"',
  PARTITION db2 comment 'host "10.0.1.137", port "3306"'
 ); 

app

# yum install -y php php-mbstring php-pdo php-mysql
$ vim links.php

<?php

//ランダム文字列
function getRandomString($size = 8){
    $chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
    mt_srand();
    $char = "";
    for($i = 0; $i < $size; $i++) {
        $char .= $chars{mt_rand(0, strlen($chars) - 1)};
    }
    return $char;
}

//XMLテンプレート
$tmpl =<<< END
<?xml version="1.0" encoding="UTF-8" ?>
<data>
  <info>
    <datetime>%s</datetime>
    <address>%s</address>
    <content>%s</content>
  </info>
</data>
END;

//XMLテンプレートに流しこむcontent要素の内容を読み込む
$contents = array();
for($i=0;$i<10;$i++){
  $contents[] = file_get_contents(dirname(__FILE__)."/dummy".$i.".txt");
}

//spiderホストは引数から取得する
$host = $argv[1];
//タイムゾーン
date_default_timezone_set('Asia/Tokyo');

//無限ループ
while(true){
  //XMLテンプレートに各要素を割り当てる
  $xml = sprintf($tmpl, date('Y/m/d H:i:s'), getRandomString(), $contents[rand(0,9)]);
  echo ".";
  //DB接続
  $pdo = new PDO(
    "mysql:dbname=memorycraft;host=$host;",
    "memorycraft_user",
    "memorycraft_pass",
    array(
     PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET `utf8`"
   ));
  $pdo->query("SET NAMES utf8;");
  //投入
  $st = $pdo->prepare("INSERT INTO doc VALUES(NULL,NOW(),?);");
  $rslt = $st->execute(array($xml));
}
?>

実行します

$ php links.php 10.0.1.20
.......................................

Spiderノードで投入されていることを確認します。

mysql> select id, created_at from doc limit 10;
+------+---------------------+
| id   | created_at          |
+------+---------------------+

|    1 | 2013-05-25 11:27:47 |
|    2 | 2013-05-25 11:27:47 |
|    3 | 2013-05-25 11:27:47 |
|    4 | 2013-05-25 11:27:47 |
|    5 | 2013-05-25 11:27:47 |
|    6 | 2013-05-25 11:27:48 |
|    7 | 2013-05-25 11:27:48 |
|    8 | 2013-05-25 11:27:48 |
|    9 | 2013-05-25 11:27:48 |
|   10 | 2013-05-25 11:27:48 |
+------+---------------------+
10 rows in set (0.06 sec)

これで、spider x 1 + mroonga x 2 + app x 1になりました。

Spiderの冗長化

次は、Spider x 2 + mroonga x 2 + internal ELB + app x 4 にしてみます。

Spiderノードをもう１台(spider2)追加します。

テーブルはspider1と同じ内容にします。
/etc/my.cnfでauto_increment_increment = 100でoffsetを１，２でずらします。
これについては、別の記事で触れています。

mysqlってなんじゃ？（マルチマスタでauto_increment）
http://memocra.blogspot.jp/2013/05/mysqlautoincrement.html

また、spiderを複数にした場合、appからの接続は１つにまとめます。
Proxy系のソフトウェアを入れるのも１つの方法ですが、ここでは内部ELBを利用してみます。

ヘルスチェックは3306ではなく別のポート経由でMySQL監視プロセスに接続スべきですが、ここではとりいそぎhttpdを立ちあげ80番に対してヘルスチェックします。


それではappで投入プログラムを実行します。
対象のホストは内部ELBのDNSNameになります。

$ php links.php internal-spider-2040122974.ap-northeast-1.elb.amazonaws.com
........................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................

spider1でデータを見てみます。

mysql> select id, created_at from doc limit 20;
+------+---------------------+
| id   | created_at          |
+------+---------------------+
|    1 | 2013-05-25 11:27:47 |
|  101 | 2013-05-25 11:27:47 |
|  201 | 2013-05-25 11:27:47 |
|  202 | 2013-05-25 11:27:47 |
|  601 | 2013-05-25 11:27:47 |
|  701 | 2013-05-25 11:27:48 |
| 1101 | 2013-05-25 11:27:48 |
| 1201 | 2013-05-25 11:27:48 |
| 1601 | 2013-05-25 11:27:48 |
| 1701 | 2013-05-25 11:27:48 |
| 2101 | 2013-05-25 11:27:49 |
| 2201 | 2013-05-25 11:27:49 |
| 2301 | 2013-05-25 11:27:49 |
| 2601 | 2013-05-25 11:27:49 |
| 2701 | 2013-05-25 11:27:49 |
| 2802 | 2013-05-25 11:27:49 |
| 3101 | 2013-05-25 11:27:49 |
| 3201 | 2013-05-25 11:27:49 |
| 3301 | 2013-05-25 11:27:49 |
| 3601 | 2013-05-25 11:27:50 |
+------+---------------------+

mysql> select count(*) from doc;
+----------+
| count(*) |
+----------+
|   111433 |
+----------+

順調に入っていってるみたいです。
あとはSpiderノードを必要に応じて追加していき、my.cnfでoffsetをずらして起動するだけです。
offsetずらしの処理はcloud-initなどで行うと、AutoScalingすることもできそうです。

今回は以上です。

mysqlってなんじゃ？（マルチマスタでauto_increment）

MySQLでマルチマスタやシャーディングなどを行った場合、auto_incrementを使いたい場合があります。

たとえばspiderだとすると、複数台のSpiderノードを経由してデータが投入されると、データノードでauto_increment値が競合してしまうことがあるため、auto_incrementのオフセットをspiderノードごとにずらします。

spider1

/etc/my.cnf
------
[mysqld]
auto_increment_increment = 100
auto_increment_offset = 1

spider2

/etc/my.cnf
------
[mysqld]
auto_increment_increment = 100
auto_increment_offset = 2

auto_increment_incrementはカウントアップされる単位です。上の例では100ずつ増えていきます。
auto_increment_offsetはauto_incrementのスタート時点の値です。

これによって、各spiderノードのauto_increment値は以下のように増えていきます。

spider1

1
101
201
301
401
.....
123401

spider2

2
102
202
302
402
....
123402

つまり３桁目以上は全体で同じ値をカウントアップしていきますが、下２桁は重複しなくなります。

これを元に投入されたデータはspider上で抽出すると以下のようになります。

1
2
101
102
201
202
301
302
401
402
.....
123401
123402

随分値を飛ばしているように感じますが、ビットずらしのような感覚でとらえれば違和感はなくなると思います。そして、この場合は、Spiderノードを99台まで増やしても値が重複しません。
将来的に何台まで増えるかわからない場合はこのようにするとよさそうです。

以上です。

mroongaってなんじゃ？（Spiderで分散全文検索）

前回の記事でmroongaを使用しましたが、全文検索のデータは大きくなりがちなので、Spiderを利用して書き込み負荷やストレージ容量を分散してみます。

設定

mroongaデータノード

前回と同じようにｍroongaテーブルを作りますが、Spiderノードからアクセスするためにユーザー権限を登録します。今回はホストは特に絞りません。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'memorycraft_user'@localhost IDENTIFIED BY 'memorycraft_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'memorycraft_user'@'%' IDENTIFIED BY 'memorycraft_pass';

新しくデータベースをつくり、blogテーブルを作ります。

mysql> CREATE DATABASE memorycraft;
mysql> use memorycraft;
mysql> 
mysql> CREATE TABLE blog (
mysql>  id INT PRIMARY KEY AUTO_INCREMENT,
mysql>  content text,
mysql>  FULLTEXT INDEX (content)
mysql> ) ENGINE = mroonga DEFAULT CHARSET utf8;

Spiderノード

Spiderのインストールは以前の記事のとおりです。
同じように権限を登録し、データベースを作成します。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'memorycraft_user'@localhost IDENTIFIED BY 'memorycraft_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'memorycraft_user'@'%' IDENTIFIED BY 'memorycraft_pass';
mysql> 
mysql> CREATE DATABASE memorycraft;
mysql> use memorycraft;

Spiderテーブルを作成します。
ここでは、２つのノードにKEYパーティションで分散してみます。

mysql> CREATE TABLE blog (
mysql>  id INT PRIMARY KEY AUTO_INCREMENT,
mysql>  content text,
mysql>  FULLTEXT INDEX (content)
mysql> ) ENGINE = Spider DEFAULT CHARSET utf8
mysql> CONNECTION ' table "blog", user "memorycraft_user", password "memorycraft_pass" '
mysql> PARTITION BY KEY() (
mysql>  PARTITION db1 comment 'host "10.0.1.169", port "3306"',
mysql>  PARTITION db2 comment 'host "10.0.1.170", port "3306"'
mysql> );

これで設定は完了です。

確認

次にデータを登録してみます。
登録の仕方は前回と同様です。


Spiderノード

mysql> INSERT INTO blog (content) VALUES ("前回はpsqlでRedshiftを利用してみましたが、通常データウェアハウス（DWH）というのはBI（Buisiness Intelligence）ツールを利用することが多いようです。

エンジニアの観点からすると複雑なSQLを書くだけでいいかもしれませんが、経営者などの立場からするとBIツールなどを使って画面上でポチポチやって分析できることが重要なようです。
今回はそのBIツールの中で、Redshiftにいち早く対応しているJaspersoftという製品を使ってRedshiftに接続してみたいと思います。");

mysql> INSERT INTO blog (content) VALUES ("久しぶりにnagiosの話題です。
アプリログ内容の監視の仕方には様々な要件がありますが、特定の間隔でログを監視し「error」などの文言があったらアラートする。などがよくあるケースで、以前の記事にも書きました。
その逆に、例えば、多量のアクセスがあるにも関わらず頻繁に出力されるはずの重要なキーワードがでていない場合は、不測の事態がおこっているかも知れません。
今回は特定の間隔でログを監視し、その中にキーワードが含まれていなかったらアラートする
というものです。
ではやってみます。");

mysql> INSERT INTO blog (content) VALUES ("S3のwebホスティングで、ログ出力の設定をしていた場合、ログファイルが大量に出力されます。
ログの記録時間は標準時で出力されていてわかりづらいです。
今回はEMRのHiveを利用して、日本時間の0時〜翌日の0時までのログを1ファイルにまとめてみたいと思います。");

mysql > INSERT INTO blog (content) VALUES ("久しぶりのSpiderの話題です。
今回はtpcc-mysqlというベンチマークツールを使ってSpiderのベンチマークをとってみました。
mysqlにかぎらずDBのベンチマークツールの多くは、TPCという団体の定めたベンチマーク仕様に基いて実装されていて、トランザクションやアクセスなどのDB用途によっていくつかのベンチマークタイプに分かれていて、OLTP向けのTPC-Eや意思決定システム向けのTPC-HやTPC-DSなど色々あるようです。");


mysql> select id,MATCH(content) AGAINST("ログ アクセス" IN BOOLEAN MODE) as score from blog WHERE MATCH(content) AGAINST("ログ アクセス" IN BOOLEAN MODE)
    -> ;
+----+-------+
| id | score |
+----+-------+
|  3 |     4 |
|  2 |     4 |
|  4 |     1 |
+----+-------+
3 rows in set (0.01 sec)

mysql> select id, MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) from blog WHERE MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) ORDER BY  MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) DESC;
+----+--------------------------------------------------+
| id | MATCH(content) AGAINST("ログ" IN BOOLEAN MODE)   |
+----+--------------------------------------------------+
|  3 |                                                4 |
|  2 |                                                3 |
+----+--------------------------------------------------+
2 rows in set (0.01 sec)

データノード

mysql> select id from blog;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)


mysql> select id from blog;
+----+
| id |
+----+
|  2 |
|  4 |
+----+
2 rows in set (0.00 sec)

うまく分散されているようです。
これで沢山データがあっても分散されます。

以上です。

Spiderってなんじゃ？（tpcc-mysqlでベンチマークをとってみた）

久しぶりのSpiderの話題です。
今回はtpcc-mysqlというベンチマークツールを使ってSpiderのベンチマークをとってみました。

mysqlにかぎらずDBのベンチマークツールの多くは、TPCという団体の定めたベンチマーク仕様に基いて実装されていて、トランザクションやアクセスなどのDB用途によっていくつかのベンチマークタイプに分かれていて、OLTP向けのTPC-Eや意思決定システム向けのTPC-HやTPC-DSなど色々あるようです。
http://www.tpc.org/information/benchmarks.asp

その中で、TPC-Cというのが複数ユーザーのトランザクションが発生するベンチマーク仕様で、顧客が商品を注文し在庫チェックと発送などをシュミレートするもののようです。
そして、このTPC-C仕様をmysqlのベンチマークとして実装したものが、tpcc-mysqlです。

このあたりのことは、以下のサイトに詳しく書かれていて非常に勉強になりました。
データベース負荷テストツールまとめ(2)
tpcc-mysqlによるMySQLのベンチマーク

今回はtpcc-mysqlを使ってSpider+RDS*4とRDS単体をテストしてみます。
構成は以下の通りです。

tpcc-mysqlのインストール

まず、ベンチ用のインスタンス（10.0.1.99）にtpcc-mysqlをインストールします。

# yum install bzr mysql-devel -y
$ mkdir ~/test
$ cd ~/test/
$ bzr init
$ bzr branch lp:~percona-dev/perconatools/tpcc-mysql
$ cd tpcc-mysql/
$ cd src
$ make all

これでインストールできました。
~/test/tpcc-mysql配下にtpcc_loadとtpcc_startという実行ファイルができていれば成功です。

$ ls -l ~/test/tpcc-mysql/
合計 248
-rw-rw-r-- 1 appadmin appadmin    851  4月  1 20:03 2013 README
-rw-rw-r-- 1 appadmin appadmin   1621  4月  1 20:03 2013 add_fkey_idx.sql
-rw-rw-r-- 1 appadmin appadmin    317  4月  1 20:03 2013 count.sql
-rw-rw-r-- 1 appadmin appadmin   3105  4月  1 20:03 2013 create_table.sql
-rw-rw-r-- 1 appadmin appadmin    763  4月  1 20:03 2013 drop_cons.sql
-rw-rw-r-- 1 appadmin appadmin    477  4月  1 20:03 2013 load.sh
drwxrwxr-x 2 appadmin appadmin   4096  4月  1 20:03 2013 schema2
drwxrwxr-x 5 appadmin appadmin   4096  4月  1 20:03 2013 scripts
drwxrwxr-x 2 appadmin appadmin   4096  4月  1 20:33 2013 src
-rwxrwxr-x 1 appadmin appadmin  60751  4月  1 20:33 2013 tpcc_load
-rwxrwxr-x 1 appadmin appadmin 154558  4月  1 20:33 2013 tpcc_start

テーブルの準備

予めstressという名前のデータベースを作成しておきます。
（名前はなんでも構いません）

次に、対象のテーブルとインデックスを作成します。
tpcc-mysql配下には、テーブルとインデックスの作成用DDL（create_table.sql、add_fkey_idx.sql）が付属しています。

インデックス用のSQLファイルには外部キーの作成も含まれていますが、
Spiderでは外部キーが使用できないため、外部キーからインデックス部分を除いたものを作ります。


また、Spiderノード用のテーブル作成DDLも作成します。

またSpiderノードからRDSに接続するときのために、Spiderノードのmy.cnfに以下の設定をしておきます。

spider_remote_sql_log_off = 1

次に作成したDDLを各インスタンスに流し込みます。

$ mysql -h 10.0.1.172 -u memorycraft stress -p < ~/test/tpcc-mysql/create_table.sql
$ mysql -h 10.0.1.20 -u memorycraft stress -p < ~/test/tpcc-mysql/create_table-spider.sql
$ mysql -h data1.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/create_table.sql
$ mysql -h data2.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/create_table.sql
$ mysql -h data3.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/create_table.sql
$ mysql -h data4.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/create_table.sql

$ mysql -h 10.0.1.172 -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql
$ mysql -h 10.0.1.20 -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql
$ mysql -h data1.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql
$ mysql -h data2.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql
$ mysql -h data3.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql
$ mysql -h data4.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql

ここまででDDLは出来上がりました。

テストデータの投入

次にデータの投入です。データの投入には先ほどのビルドで出来たtpcc_loadコマンドを使用します。
第１〜第４引数まではそれぞれホスト、DB名、DBユーザー、DBパスワードを指定し、第５引数には倉庫(warehouse)の数を指定します。 warehouseは１つにつき一番レコードの多いorder_lineテーブルで30万件程度に増えます。ここではwarehouseを100（order_lineテーブルで3000万件程度） に設定します。
このデータ投入処理はとても時間がかかります。

./tpcc_load 10.0.1.172 stress memorycraft xxxxxxxxx 100
./tpcc_load 10.0.1.20 stress memorycraft xxxxxxxxx 100

テストの実行

データがロードできたらtpcc_startでテストを実行します。
-w以降のオプションは以下の通りです。

• -w：warehouseの数、基本的にはloadで設定したのと同じ値
• -c：接続数 
• -r：待機時間 
• -l：実行時間（秒） 

基本的にはwarehouse数と、接続数を調整していろいろなパターンでテストしていきます。

./tpcc_start -h 10.0.1.172 -P 3306 -d stress -u memorycraft -p xxxxxxxxx -w 100 -c 10 -r 300 -l 3600
./tpcc_start -h 10.0.1.20 -P 3306 -d stress -u memorycraft -p xxxxxxxxx -w 100 -c 10 -r 300 -l 3600

結果は以下のようになり、一番最後のTpmC（1分間に処理できたトランザクション数）が指標になります。

***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '10.0.1.20'
option P with value '3306'
option d with value 'stress'
option u with value 'memorycraft'
option p with value 'satoru00'
option w with value '100'
option c with value '10'
option r with value '300'
option l with value '3600'

     [server]: 10.0.1.20
     [port]: 3306
     [DBname]: stress
       [user]: memorycraft
       [pass]: satoru00
  [warehouse]: 100
 [connection]: 10
     [rampup]: 300 (sec.)
    [measure]: 3600 (sec.)

RAMP-UP TIME.(300 sec.)

MEASURING START.

  10, 257(0):3.061|3.773, 258(0):0.580|0.773, 25(0):0.297|0.415, 26(0):3.753|4.259, 26(0):9.967|11.042
  20, 265(0):3.003|3.222, 264(0):0.570|0.617, 27(0):0.252|0.329, 26(0):3.448|3.478, 26(0):8.935|9.011
  30, 258(0):3.185|3.452, 256(0):0.626|0.684, 26(0):0.374|0.414, 25(0):3.700|4.063, 26(0):9.790|10.474
  40, 247(0):3.191|3.331, 249(0):0.605|0.630, 24(0):0.279|0.298, 26(0):3.702|4.258, 24(0):10.012|10.016
  50, 251(0):2.930|2.980, 248(0):0.526|0.548, 25(0):0.258|0.275, 24(0):3.111|3.113, 25(0):8.949|8.967
  60, 252(0):2.864|3.176, 254(0):0.544|0.585, 25(0):0.248|0.253, 26(0):3.249|3.315, 26(0):9.449|9.567
  70, 249(0):2.850|2.878, 247(0):0.527|0.540, 25(0):0.247|0.276, 25(0):3.126|3.156, 24(0):9.074|9.135
  80, 246(0):3.337|3.388, 249(0):0.651|0.663, 25(0):0.310|0.338, 24(0):3.897|3.967, 25(0):11.210|11.281
  90, 261(0):2.923|3.133, 262(0):0.548|0.577, 26(0):0.284|0.297, 27(0):3.218|3.283, 27(0):9.433|9.563
 100, 262(0):2.910|3.139, 258(0):0.528|0.561, 27(0):0.254|0.282, 26(0):3.170|3.193, 26(0):8.908|9.046
 110, 254(0):3.013|3.200, 253(0):0.570|0.586, 25(0):0.279|0.289, 25(0):3.328|3.511, 25(0):9.278|9.294
 120, 269(0):2.912|2.975, 272(0):0.546|0.553, 26(0):0.250|0.259, 27(0):3.180|3.212, 26(0):8.939|8.954
 130, 266(0):2.936|2.960, 265(0):0.533|0.567, 27(0):0.257|0.264, 26(0):3.190|3.192, 27(0):9.350|9.428
 140, 263(0):2.936|2.963, 263(0):0.578|0.632, 26(0):0.256|0.259, 27(0):3.348|3.456, 27(0):9.167|9.708
 150, 248(0):2.888|2.948, 247(0):0.545|0.603, 25(0):0.252|0.258, 24(0):3.144|3.165, 24(0):9.086|9.450
 160, 231(0):2.936|3.002, 232(0):0.526|0.539, 23(0):0.245|0.262, 24(0):3.199|3.237, 24(0):8.894|10.282
~(略)~
3480, 265(0):2.957|3.211, 265(0):0.534|0.603, 26(0):0.264|0.267, 26(0):3.267|3.330, 26(0):9.178|9.661
3490, 262(0):2.919|2.949, 266(0):0.527|0.538, 26(0):0.249|0.251, 27(0):3.178|3.183, 27(0):8.766|9.398
3500, 259(0):2.952|3.189, 257(0):0.541|0.652, 27(0):0.263|0.282, 26(0):3.266|3.279, 25(0):9.387|9.501
3510, 259(0):2.901|3.069, 262(0):0.532|0.612, 25(0):0.246|0.252, 25(0):3.205|3.226, 27(0):8.785|9.184
3520, 260(0):2.880|2.899, 259(0):0.519|0.586, 26(0):0.253|0.254, 27(0):3.168|3.189, 25(0):8.908|9.342
3530, 259(0):2.914|3.136, 250(0):0.534|0.604, 25(0):0.269|0.278, 25(0):3.296|3.330, 26(0):8.985|9.735
3540, 253(0):3.034|3.061, 261(0):0.572|0.632, 27(0):0.258|0.262, 26(0):3.234|3.319, 26(0):9.063|9.236
3550, 270(0):2.959|3.097, 269(0):0.522|0.547, 26(0):0.254|0.273, 27(0):3.242|3.244, 27(0):8.790|9.263
3560, 251(0):3.057|3.258, 250(0):0.609|0.653, 26(0):0.276|0.360, 25(0):3.360|3.444, 25(0):9.423|9.474
3570, 244(0):3.031|3.133, 248(0):0.546|0.618, 24(0):0.251|0.259, 24(0):3.286|3.299, 25(0):8.948|9.190
3580, 260(0):2.933|3.057, 260(0):0.527|0.542, 26(0):0.243|0.249, 27(0):3.175|3.190, 26(0):8.928|9.061
3590, 260(0):3.000|3.370, 255(0):0.560|0.667, 26(0):0.257|0.310, 25(0):3.327|3.329, 25(0):9.263|9.397
3600, 262(0):2.933|3.016, 263(0):0.523|0.540, 27(0):0.256|0.257, 27(0):3.180|3.230, 26(0):9.017|9.607

STOPPING THREADS..........


  [0] sc:91448  lt:0  rt:0  fl:0 
  [1] sc:91446  lt:0  rt:0  fl:0 
  [2] sc:9145  lt:0  rt:0  fl:0 
  [3] sc:9145  lt:0  rt:0  fl:0 
  [4] sc:9144  lt:0  rt:0  fl:0 
 in 3600 sec.


  [0] sc:91448  lt:0  rt:0  fl:0 
  [1] sc:91446  lt:0  rt:0  fl:0 
  [2] sc:9145  lt:0  rt:0  fl:0 
  [3] sc:9145  lt:0  rt:0  fl:0 
  [4] sc:9144  lt:0  rt:0  fl:0 

 (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 100.00%  [OK]
        Payment: 100.00%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]


                 1524.133 TpmC

何通りかテストをした結果、RDS単体とSpider+RDS*4で結果が入れ替わる条件がありました。

タイプ	データ数	接続数	RDS*1	Spider+RDS*4
medium	warehouse=100	10	2287.250 tpm	1524.133 tpm
medium	warehouse=100	20	2368.133 tpm	1564.283 tpm
medium	warehouse=150	20	1418.667 tpm	1540.333 tpm

作者の斯波さんにも伺ったのですが、Spiderの特性としてデータノードへのオーバーヘッドがある分、通常の単体DBに比べると最初はパフォーマンスが落ちますが、並列アクセス（同時アクセス数）が多くなり、データ数も多くなってくるとSpiderの優位性が出てくるとのことで、それが上の結果と一致しました。

細かなチューニングなどで結果も変わってくるかと思いますが、最初からSpider構成にするというよりも、負荷やデータ量の拡大に応じて移行していくのがよいのかなと感じました。
前述のように外部キーが使用できないなど、Spiderはいくつかの特殊な制限があるため、運用後のSpiderによるスケールアウトを視野に入れているのであれば、Spiderの制限などを見越したテーブル、データ設計なども考慮したほうが良いのかもしれません。

以上です。

EC2でMySQL(世界編2 Spiderとレプリケーションで高速負荷分散)

前回のつづきです。

前回はSpiderとレプリケーションで、リージョン間での高速なデータ分散を紹介しました。前回で少し触れましたが、この構成ではたとえばEU側のDBにJPのデータの書き込みには対応できません。

それを解決し、どちらのリージョンからもすべての商品を投入できるようにしてみたいと思います。
構成は以下のとおりです。

SpiderノードのDBで、書き込みと読み込みでそれぞれ別々のテーブルを用意し、別々のデータノードにそれぞれシャーディングを行います。

データノードの作成とレプリケーションの設定は前回と同じなので割愛します。

Spiderノード

spider_ja

CREATE TABLE item_r (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION ja_ja VALUES IN (1) COMMENT = 'host "111.111.0.1", port "3306"',
  PARTITION ja_eu VALUES IN (2) COMMENT = 'host "111.111.0.2", port "3306"'
)
;

CREATE TABLE item_w (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION ja_ja VALUES IN (1) COMMENT = 'host "111.111.0.1", port "3306"',
  PARTITION eu_eu VALUES IN (2) COMMENT = 'host "222.222.0.2", port "3306"'
)
;

spider_eu

CREATE TABLE item_r (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION eu_ja VALUES IN (1) COMMENT = 'host "222.222.0.1", port "3306"',
  PARTITION eu_eu VALUES IN (2) COMMENT = 'host "222.222.0.2", port "3306"'
)
;

CREATE TABLE item_w (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION ja_ja VALUES IN (1) COMMENT = 'host "111.111.0.1", port "3306"',
  PARTITION eu_eu VALUES IN (2) COMMENT = 'host "222.222.0.2", port "3306"'
)
;

item_wに対して書き込んでみます。

mysql> INSERT INTO item_w (name, shop_id, region_id) VALUES('おいしい水', 1, 1), ('おしゃれなバッグ', 2, 1), ('Cool Watch', 3, 2),('Cute Ring', 4, 2);
Query OK, 4 rows affected (1.95 sec)
Records: 4  Duplicates: 0  Warnings: 0

書き込みが成功しました。
item_wで参照すると、

mysql> select * from item_w;
+----+--------------------------+---------+-----------+
| id | name                     | shop_id | region_id |
+----+--------------------------+---------+-----------+
|  1 | おいしい水          |       1 |         1 |
|  2 | おしゃれなバッグ |       2 |         1 |
|  3 | Cool Watch               |       3 |         2 |
|  4 | Cute Ring                |       4 |         2 |
+----+--------------------------+---------+-----------+
4 rows in set (1.96 sec)

正しく投入されていることがわかりますが、レスポンスはやはり悪いです。

そこでitem_rで参照してみます。

mysql> select * from item_r;
+----+--------------------------+---------+-----------+
| id | name                     | shop_id | region_id |
+----+--------------------------+---------+-----------+
|  1 | おいしい水          |       1 |         1 |
|  2 | おしゃれなバッグ |       2 |         1 |
|  3 | Cool Watch               |       3 |         2 |
|  4 | Cute Ring                |       4 |         2 |
+----+--------------------------+---------+-----------+
4 rows in set (0.01 sec)

高速で読み込みできています。

これで、どちらのリージョンからも全リージョン用のデータの書き込みができ、なおかつ読み込みスピードを維持することが出来ました。

書き込み時は他方のリージョンにSpiderでアクセスするので、少し時間がかかります。
前回のように基本はJPのデータはJPリージョンから投入するようにルール化すれば書き込みスピードが損なわれることはありません。
すべてmicroインスタンスでの確認ということもあり、もしかしたらSpiderのサーバーやテーブルパラメータをいじることで他リージョンへのSpiderアクセスも速くなるかもしれません。それは今後の課題にしたいと思います。

以上です。

EC2でMySQL(世界編1 Spiderとレプリケーションで世界進出)

ふたたびSpiderの話題です。

WEBアプリを世界展開する場合、要件の１つとして、世界各国のユーザーは全てのデータを参照する必要があるが、データの登録は各国、地域の担当者が各自のデータを登録したい、というケースが多いと思います。

たとえば、商品のサイトで、EUと日本にそれぞれ管理者がいるとします。
その場合のユースケースとしては、

• EUの管理者はEUの商品を登録する
• 日本の管理者は日本の商品を登録する
• ユーザーは全ての商品を閲覧できる

となります。

このとき、DBがどのリージョンにあるかによって、ユーザーや管理者の間で、レスポンススポードに差がでます。
DBを日本に置くとEUのユーザーや管理者には遅いシステムとなってしまい、EUに置くとその逆になります。
そのため、各リージョンに全てのリージョンで登録した商品データを用意し、ユーザーのロケーションに関わらず、DBアクセスをユーザーと同じリージョン内で済ませる必要があります。管理者がアクセスするDBも管理者のいるリージョン内で済ませる必要があります。

これをSpiderとレプリケーションで実現してみます。
上図をみると想像できるかも知れませんが、方法としてはEUとJPそれぞれのリージョンに属する商品マスタに対して書き込み、EUではJPの、JPはEUのスレーブをもち、それぞれをSpiderでシャーディングします。
DBの図で表すと、下記のようになります。

それでは実際に試してみます。

データノード

CREATE TABLE item (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
;

Spiderノード

spider_ja

CREATE TABLE item (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION ja_ja VALUES IN (1) COMMENT = 'host "111.111.0.1", port "3306"',
  PARTITION ja_eu VALUES IN (2) COMMENT = 'host "111.111.0.2", port "3306"'
)
;

spider_eu

CREATE TABLE item (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION eu_ja VALUES IN (1) COMMENT = 'host "222.222.0.1", port "3306"',
  PARTITION eu_eu VALUES IN (2) COMMENT = 'host "222.222.0.2", port "3306"'
)
;

レプリケーション

ja-eu

$ mysql -u root
mysql> CHANGE MASTER TO
  MASTER_HOST='222.222.0.2',
  MASTER_USER='remote_user',
  MASTER_PASSWORD='remote_user',
  MASTER_PORT=3306;

eu-ja

$ mysql -u root
mysql> CHANGE MASTER TO
  MASTER_HOST='111.111.0.1',
  MASTER_USER='remote_user',
  MASTER_PASSWORD='remote_user',
  MASTER_PORT=3306;

※各ノード間のアクセス権限やサーバーIDの登録、セキュリティグループの設定などは以前の記事に書いたので割愛します

ポイントはregion_id(1:ja, 2:eu)のLISTパーティションにするため、プライマリキーをidとregion_idの複合キーにするところです。INSERT時には、登録したリージョンの region_idをセットします。
※登録したリージョンではないregion_idをセットするとパーティションに含まれず、シャーディング対象にならないので、注意が必要です。

spider-ja

mysql> INSERT INTO item (name, shop_id, region_id) VALUES('おいしい水', 1, 1), ('おしゃれなバッグ', 2, 1);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

spider-eu

mysql> insert into item(name,shop_id,region_id) values('Cool Watch', 3, 2),('Cute Ring', 4, 2);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

すると、
それぞれのspiderノードですべてのitemがselectできます。

mysql> select * from item;
+----+--------------------------+---------+-----------+
| id | name                     | shop_id | region_id |
+----+--------------------------+---------+-----------+
|  1 | おいしい水          |       1 |         1 |
|  2 | おしゃれなバッグ |       2 |         1 |
|  1 | Cool Watch               |       3 |         2 |
|  2 | Cute Ring                |       4 |         2 |
+----+--------------------------+---------+-----------+
4 rows in set (0.01 sec)

このように、リージョンが分かれていても、アクセス速度を気にすること無くシャーディングができました。
以上です。

EC2でMySQL(運用編 VP＋Spiderで無停止負荷分散)

前回はVPでテーブルのALTERを行いましたが、ほとんど同様に通常のInnoDBテーブルをSpiderシャーディングに無停止で移行することも可能です。 さっそくやってみます。 初期テーブルは以下のとおりです。

mysql> create table gift(
  id int auto_increment,
  name varchar(255),
  description text,
  created_at datetime not null,
  primary key(id)
)engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

これをSpider化していきますが、今回は定期的にデータを投入しながらSpider化を行ってみます。 まず、以下のようなシェルを実行し、常にデータの投入がされている状態をつくります。

$ vi insert2gift.sh

$ sh insert2gift.sh
................................

それではVPをつかって無停止でSpiderに移行を行なってみたいと思います。 基本的には、前回と同様です。 切り替え用のダミーと２つのデータノードをもつ新規Spiderテーブル、それらを束ねるVPテーブルを用意します。


データノードの設定

mysql> GRANT ALL PRIVILEGES ON *.* TO 'xxxxxxxxxxxxxx'@localhost IDENTIFIED BY 'xxxxxxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'xxxxxxxxxxxxxx'@'%' IDENTIFIED BY 'xxxxxxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'xxxxxxxxxxxxxx'@'123.123.123.123' IDENTIFIED BY 'xxxxxxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)

mysql> use cloudpack;
Database changed

mysql> create table gift(
  id int auto_increment,
  name varchar(255),
  description text,
  created_at datetime not null,
  primary key(id)
)engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

SpiderノードのSpider、ダミー、VPテーブルの設定

mysql> create table gift_new(
  id int auto_increment,
  name varchar(255),
  description text,
  created_at datetime not null,
  primary key(id)
) engine = Spider DEFAULT CHARSET=utf8
CONNECTION ' table "gift", user "xxxxxxxxxxxxx", password "xxxxxxxxxxxxx" '
PARTITION BY LIST(MOD(id, 2)) (
    PARTITION hostb VALUES IN (0) comment 'host "111.111.111.111", port "3306"',
    PARTITION hostc VALUES IN (1) comment 'host "222.222.222.222", port "3306"'
);
Query OK, 0 rows affected (0.02 sec)

mysql> create table gift_dummy like gift;
Query OK, 0 rows affected (0.01 sec)

mysql> create table gift_vp(
   id int auto_increment,
   name varchar(255),
   description text,
   created_at datetime not null,
   primary key(id)
 )engine=vp
 comment 'table_name_list "gift_dummy gift_new", cit "2", cil "2", ctm "1", ist "1", zru "1"';
Query OK, 0 rows affected (0.03 sec)

この時点では、以下のイメージのような構成になっています。

テーブルのリネーム

ここも前回同様テーブルのリネームを行い、接続先をVPテーブルに向けます。

mysql> rename table 
  gift_dummy to gift_delete,  
  gift to gift_dummy,  
  gift_vp to gift;
Query OK, 0 rows affected (0.03 sec)

データのコピー

ここも前回同様です。

mysql> select vp_copy_tables('gift', 'gift_dummy', 'gift_new');
Query OK, 0 rows affected (0.01 sec)

テーブルの再リネーム

コピーが完了したら、テーブルのリネームを再度行い、移行先のSpiderテーブルをgiftにします。

mysql> rename table 
  gift to gift_vp,  
  gift_new to gift;
Query OK, 0 rows affected (0.01 sec) 

不要テーブルの削除

最後に、必要のなくなったテーブルを削除します。

mysql> drop table 
  gift_dummy,
  gift_vp, 
  gift_delete;
Query OK, 0 rows affected (0.00 sec)

これで、移行が完了しました。 それでは、実際にテーブルの内容を見てみましょう。

host A

mysql> select * from gift order by id;
+------+----------------------------------+----------------------------------+---------------------+
| id   | name                             | description                      | created_at          |
+------+----------------------------------+----------------------------------+---------------------+
|   1  | d84c7d13d56e48999f6e42396bb0d6b8 | 57b6f87681df4141953b63cd6ee74... | 2011-09-21 22:35:23 |
|   2  | fc52fbdee1904e40a92711bc3ff5b53b | 4fe1116428e142369ab419d603a8b... | 2011-09-21 22:35:23 |
|   3  | 936917c0e7b246b380573654c80863e1 | 08cf01342f1149b5a7fa15f2df5b6... | 2011-09-21 22:35:24 |
|   4  | 5d630544409e4ef98ece5b489cd5aff5 | c3c1848832d04b69bc9f9c1bd7249... | 2011-09-21 22:35:23 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 5429 | 0462eaf7646b4855a993a49128ba3f4d | 0d07cdbac3c14723b25bc1a1bc74a... | 2011-09-22 01:07:42 |
| 5430 | b67fdb0b633c40829dd812c0358f5ccb | 85a310e34668427ab392b07fe36f8... | 2011-09-22 01:07:49 |
| 5431 | 431a0be6d0d94223bad8405d0011560e | f78b63b0ed5c42d0a947dc3db6bcb... | 2011-09-22 01:07:56 |
| 5432 | 4a0f9c4dcbaa42a99aeb014232091551 | 9ceb2dce039e49268280560631578... | 2011-09-22 01:08:02 |
+------+----------------------------------+----------------------------------+---------------------+
5007 rows in set (0.06 sec)

host B

mysql> select * from gift order by id;
+------+----------------------------------+----------------------------------+---------------------+
| id   | name                             | description                      | created_at          |
+------+----------------------------------+----------------------------------+---------------------+
|   1  | d84c7d13d56e48999f6e42396bb0d6b8 | 57b6f87681df4141953b63cd6ee74... | 2011-09-21 22:35:23 |
|   3  | 936917c0e7b246b380573654c80863e1 | 08cf01342f1149b5a7fa15f2df5b6... | 2011-09-21 22:35:24 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 5429 | 0462eaf7646b4855a993a49128ba3f4d | 0d07cdbac3c14723b25bc1a1bc74a... | 2011-09-22 01:07:42 |
| 5431 | 431a0be6d0d94223bad8405d0011560e | f78b63b0ed5c42d0a947dc3db6bcb... | 2011-09-22 01:07:56 |
+------+----------------------------------+----------------------------------+---------------------+
2503 rows in set (0.01 sec)

host C

mysql> select * from gift order by id;
+------+----------------------------------+----------------------------------+---------------------+
| id   | name                             | description                      | created_at          |
+------+----------------------------------+----------------------------------+---------------------+
|   2  | fc52fbdee1904e40a92711bc3ff5b53b | 4fe1116428e142369ab419d603a8b... | 2011-09-21 22:35:23 |
|   4  | 5d630544409e4ef98ece5b489cd5aff5 | c3c1848832d04b69bc9f9c1bd7249... | 2011-09-21 22:35:23 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 5430 | b67fdb0b633c40829dd812c0358f5ccb | 85a310e34668427ab392b07fe36f8... | 2011-09-22 01:07:49 |
| 5432 | 4a0f9c4dcbaa42a99aeb014232091551 | 9ceb2dce039e49268280560631578... | 2011-09-22 01:08:02 |
+------+----------------------------------+----------------------------------+---------------------+
2504 rows in set (0.00 sec)

このように、元々入っていたデータと、移行中に投入されたデータがきれいにシャーディングされていることがわかります。
本日はここまで。

EC2でMySQL(Spider編3 Spiderの先にSpider)

EC2はあまり関係なくなってきていますが、Spiderの話題はまだ続きます。
Spiderの特性や構成を考えていくと、Spiderで分割するためのパーティショニングにより、さまざまな分割方法を考えることができます。
また、データが増加していくと、分散した先でもさらに負荷軽減が必要になる可能性も考えられます。

Spider編1で少し触れましたが、Spiderはストレージエンジンであり、リンク先のテーブルのストレージエンジンに制限はありません。
ここで、Spiderのリンク先にSpiderテーブルを指定したらどうなるか実験してみました。

構成は以下のとおりです。
ここでは全てTokyoリージョン内とします。
またIPは仮のものです。

• SpiderノードA(111.111.0.1)
• データノードA-1(111.111.0.2)
• SpiderノードB(111.111.0.3)
• データノードB-1(111.111.0.4)
• データノードB-2(111.111.0.5)

SpiderノードBはSpiderノードAのデータノードとしての振る舞い、また一方でデータノードB-1,B-2のSpiderノードとしても振る舞います。
インストールやデータベースの作成、セキュリティグループの設定など基本的な部分は、前回、前々回と同じため省きます。


データノードの設定

SpiderノードA、SpiderノードB以外の全てのデータノードでmemberテーブルを作成します。

# mysql -u root
mysql> use cloudpack
Database changed
mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

データノードA-1、SpiderノードBではSpiderノードAを許可します。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"spider-a" IDENTIFIED BY 'remote_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"111.111.0.1" IDENTIFIED BY 'remote_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"ec2-111-111-0-1.ap-northeast-1.compute.amazonaws.com" IDENTIFIED BY 'remote_pass'; 

データノードB-1、B-2ではSpiderノードBを許可します。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"spider-b" IDENTIFIED BY 'remote_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"111.111.0.3" IDENTIFIED BY 'remote_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"ec2-111-111-0-3.ap-northeast-1.compute.amazonaws.com" IDENTIFIED BY 'remote_pass';

SpiderノードAの設定

まず最終的に全てを束ねるSpiderノードAを設定します。
データノードA-1とSpiderノードBにリンクを張りますが、
ここでのパーティショニングはRANGEパーティションを採用します。
idの1～5がデータノードA-1、6～10がSpiderノードBに流れるように設定しました。

mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
) engine = Spider DEFAULT CHARSET=utf8
CONNECTION ' table "member", user "remote_user", password "remote_pass" '
PARTITION BY RANGE (id) (
    PARTITION data-a1 VALUES LESS THAN (6) comment 'host "111.111.0.2", port "3306"',
    PARTITION spider-b VALUES LESS THAN (11) comment 'host "111.111.0.3", port "3306"'
);

SpiderノードBの設定

次にSpiderノードBを設定します。
データノードB-1,B-2を束ねますが、
ここではKEYパーティションで分散させます。

mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
) engine = Spider DEFAULT CHARSET=utf8
CONNECTION ' table "member", user "remote_user", password "remote_pass" '
PARTITION BY KEY() (
    PARTITION data-b1 comment 'host "111.111.0.4", port "3306"',
    PARTITION data-b2 comment 'host "111.111.0.5", port "3306"'
);

これで設定は終了です。


確認

それではSpiderノードAで以下のように、10行追加してみます。

# mysql -u root
mysql> use cloudpack
Database changed
mysql> INSERT INTO member (name) VALUES ('ichiro'),('jiro'),('sub-Low'),('shiro'),('goro'),('rokuro'),('shichro'),('hachiro'),('kuro'),('juro');
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

SELECTすると正常に10件投入されていることが確認できます。

mysql> select * from member order by id;
+----+---------+
| id | name    |
+----+---------+
|  1 | ichiro  |
|  2 | jiro    |
|  3 | sub-Low |
|  4 | shiro   |
|  5 | goro    |
|  6 | rokuro  |
|  7 | shichro |
|  8 | hachiro |
|  9 | kuro    |
| 10 | juro    |
+----+---------+
10 rows in set (0.00 sec)

それでは、各ノードにどのように分配されているか見てみましょう。

●データノードA-1
ここにはRANGEパーティションのルールどおり、idの1～5が投入されています。

mysql> select * from member order by id;
+----+---------+
| id | name    |
+----+---------+
|  1 | ichiro  |
|  2 | jiro    |
|  3 | sub-Low |
|  4 | shiro   |
|  5 | goro    |
+----+---------+
5 rows in set (0.00 sec)

●SpiderノードB
こちらもRANGEパーティションのルールどおり、idの6～10が投入されています。

mysql> select * from member order by id;
+----+---------+
| id | name    |
+----+---------+
|  6 | rokuro  |
|  7 | shichro |
|  8 | hachiro |
|  9 | kuro    |
| 10 | juro    |
+----+---------+
5 rows in set (0.01 sec)

しかし、これは別のSpiderノードでもあり、データノードB-1,B-2にさらに分散されています。
さらに、その分散先を見てみます。

●データノードB-1

mysql> select * from member order by id;
+----+---------+
| id | name    |
+----+---------+
|  7 | shichro |
|  9 | kuro    |
+----+---------+
2 rows in set (0.00 sec)

●データノードB-2

mysql> select * from member order by id;
+----+---------+
| id | name    |
+----+---------+
|  6 | rokuro  |
|  8 | hachiro |
| 10 | juro    |
+----+---------+
3 rows in set (0.00 sec)

SpiderノードBの先で、さらにKEYパーティショニングによって、idのハッシュ値で均等に分散されていることがわかります。

このように、途中で負荷が増えても、さらに分散させて1ノードあたりの負荷を減らすことも可能です。

以上です。

EC2でMySQL(Spider編2 リージョン間SpiderでSSL接続)

少し間があいてしまいましたが、Spiderの続きです。
前回はSpiderを使用して、書き込みの分散をおこないました。

今回は、Spiderによる書き込み分散をリージョン間で試してみます。
構成は以下のとおりです。(IPは仮のものです)

• Spiderノード(Tokyoリージョン、123.123.123.123)
• データノード1(Tokyoリージョン、111.111.111.111)
• データノード2(EUリージョン、222.222.222.222)

また、リージョン間の接続はインターネット越しになるため、SSLで接続するのが安全です。
インストールやデータベースの作成、セキュリティグループの設定など基本的な部分は前回と同じなので割愛します。ちなみにこのサンプルで使用している証明書類は自前の証明書です。

データノードの設定

２つのデータノードで以下の設定をします。
前回と違うのは、データノードはSpiderのSSL接続先になるためのサーバー証明書の配置です。

# mkdir -p /tmp/ssl
# cd /tmp/ssl
# openssl genrsa -out ca-key.pem 2048
# openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem
# openssl req -newkey rsa:2048 -nodes -keyout server-key.pem -out server-req.pem -days 3650
# openssl rsa -in server-key.pem -out server-key.pem
# openssl x509 -req -in server-req.pem -CA ca-cert.pem -CAkey ca-key.pem -out server-cert.pem -set_serial 2 -days 3650
# chown mysql:mysql *

my.cnfで証明書の場所を追記します。

# vi /etc/my.cnf
ssl-ca=/tmp/ssl/ca-cert.pem
ssl-cert=/tmp/ssl/server-cert.pem
ssl-key=/tmp/ssl/server-key.pem

SSL接続が有効になっていることを確認します。

# mysql -u root
mysql> show variables like '%ssl%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| have_openssl  | YES                      |
| have_ssl      | YES                      |
| ssl_ca        | /tmp/ssl/ca-cert.pem     |
| ssl_capath    |                          |
| ssl_cert      | /tmp/ssl/server-cert.pem |
| ssl_cipher    |                          |
| ssl_key       | /tmp/ssl/server-key.pem  |
+---------------+--------------------------+

spiderノードのアクセスを許可します。
もしSSL以外受け付けない場合はGRANT文の最後に REQUIRE SSLをつけます。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"ja-spider" IDENTIFIED BY 'remote_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"123.123.123.123" IDENTIFIED BY 'remote_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"ec2-123-123-123-123.ap-northeast-1.compute.amazonaws.com" IDENTIFIED BY 'remote_pass';

memberテーブルを作成します。

mysql> use cloudpack
Database changed
mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

Spiderノードの設定

続いてSpiderノードでの設定を行います。
前回との違いは、SpiderテーブルのSSLオプションと、クライアント証明書の配置です。
証明書を作成します。

# mkdir -p /tmp/ssl
# cd /tmp/ssl
# openssl genrsa -out ca-key.pem 2048
# openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem
# openssl req -newkey rsa:2048 -nodes -keyout client-key.pem -out client-req.pem -days 3650
# openssl rsa -in client-key.pem -out client-key.pem
# openssl x509 -req -in client-req.pem -CA ca-cert.pem -CAkey ca-key.pem -out client-cert.pem -set_serial 2 -days 3650
# chown mysql:mysql *

次にSpiderテーブルを作成します。
まず最初に、前回と同じSSLオプションなしで作成してみます。

# mysql -u root
mysql> use cloudpack
Database changed
mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
) engine = Spider DEFAULT CHARSET=utf8
CONNECTION ' table "member", user "remote_user", password "remote_pass" '
PARTITION BY KEY() (
    PARTITION ap_northeast_1 comment 'host "111.111.111.111", port "3306"',
    PARTITION eu_west_1 comment 'host "222.222.222.222", port "3306"'
);
Query OK, 0 rows affected (0.04 sec)

ここで、別コンソールでSpiderノードにtcpflowをインストールし、
3306ポートの通信を見てみましょう。

コンソール2

# yum install tcpflow -y
# tcpflow -c port 3306

コンソール1でこのようにINSERTして見ます。

mysql> INSERT INTO member (name) VALUES('memorycraft'),('ichiro'),('jiro');

するとコンソール2では、以下のように平文で通信されていることがわかります。

046.137.176.190.03306-010.146.027.110.49671: ...........
010.146.027.110.49671-046.137.176.190.03306: .....SET NAMES utf8
046.137.176.190.03306-010.146.027.110.49669: ...........
010.146.027.110.49669-046.137.176.190.03306: 1....show table status from `cloudpack` like 'member'
046.137.176.190.03306-010.146.027.110.49671: ...........
010.146.027.110.49671-046.137.176.190.03306: @....insert into `cloudpack`.`member`(`id`,`name`)values(2,'ichiro')
046.137.176.190.03306-010.146.027.110.49669: .....*....def..TABLES..Name
TABLE_NAME.!...........(....def..TABLES..Engine.ENGINE.!...........*....def..TABLES..Version.VERSION.?...... ....0....def..TABLES.
Row_format
ROW_FORMAT.!...........*....def..TABLES..Rows
TABLE_ROWS.?...... ....8....def..TABLES..Avg_row_length.AVG_ROW_LENGTH.?...... ....2....def..TABLES..Data_length.DATA_LENGTH.?...... ....:....def..TABLES..Max_data_length.MAX_DATA_LENGTH.?...... ....4..
.def..TABLES..Index_length.INDEX_LENGTH.?...... .........def..TABLES..Data_free.DATA_FREE.?...... ....8....def..TABLES..def..TABLES..Create_time.CREATE_TIME.?...........2....def..TABLES..Update_time.UPDATE_TIME.?...........0....def..TABLES.
Check_time
CHECK_TIME.?...........4....def..TABLES..Collation.TABLE_COLLATION.!.`.........,....def..TABLES..Checksum.CHECKSUM.?...TABLE_COMMENT.!..................".Z....member.InnoDB.10.Compact.0.0.16384.0.0.4194304.1.2011-08-30 22:59:16...utf8_general_ci..........".
046.137.176.190.03306-010.146.027.110.49671: ...........
010.146.027.110.54750-046.051.243.054.03306: .....commit
046.051.243.054.03306-010.146.027.110.54750: ...........
010.146.027.110.49671-046.137.176.190.03306: .....commit
046.137.176.190.03306-010.146.027.110.49671: ...........
010.146.027.110.49671-046.137.176.190.03306: .....

ここで、コンソール１にて、SpiderノードのmemberテーブルをSSL仕様に作り直してみます。
SpiderテーブルにはMySQLのSSLオプションと同じ項目があるので、それを利用します。
以下のようにConnectionの部分に、ssl_ca,ssl_cert,ssl_keyの項目を追加し、それぞれに先ほど作った証明書類のパスを記載します。

mysql> DROP TABLE member;
Query OK, 0 rows affected (0.00 sec)

mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
) engine = Spider DEFAULT CHARSET=utf8
Connection ' table "member", user "remote_user", password "remote_pass", ssl_ca "/tmp/ssl/ca-cert.pem", ssl_cert "/tmp/ssl/client-cert.pem", ssl_key "/tmp/ssl/client-key.pem" '
PARTITION BY KEY() (
    PARTITION ap_northeast_1 comment 'host "111.111.111.111", port "3306"',
    PARTITION eu_west_1 comment 'host "222.222.222.222", port "3306"'
);
Query OK, 0 rows affected (0.04 sec)

mysql> select * from member;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | memorycraft |
|  3 | jiro        |
|  2 | ichiro      |
+----+-------------+
3 rows in set (3.26 sec)

ここで、コンソールを見ると、今度は暗号化されているのがわかります。

046.137.176.190.03306-010.146.027.110.51913: .... .{..5.n.?.....~...q....K..!..h18.... %C..m...3./;.K...|g..... C<XxM..
046.137.176.190.03306-010.146.027.110.51913: .... Z.?.a...qU.mex ...\...K$...
........ #5.c.AAC.l.
'.....Q3..W+.DA[Z.6.
046.137.176.190.03306-010.146.027.110.51913: .... .
......>...XQ.e;./x..!...l7........ :/........9.=........H..vAi....F
046.137.176.190.03306-010.146.027.110.51913: .... .....j..!\qr.,B-Y.e....p...]t=...... ....O....Y........Y_.+.n.9.L.TvB
046.137.176.190.03306-010.146.027.110.51913: .... ..3..'.sw..9..H....bYs.F.A....(..... /.. .Y....Yi...A..h.C....,k.w|..
010.146.027.110.51913-046.137.176.190.03306: ....p.
.)...}E8...p......o-.I.J..@%>........EW.E<.n.S..e3G.T.b....7........a......^.
046.137.176.190.03306-010.146.027.110.51913: .... -.,.eh.\g....Z0.7..PW..a..k)..........j@........._...o.^v.Qn./.G..".vf..\d.\..A..0,.0../.F..b..........+.J+l=.<....U'.3$U..RHi[V...y....N5.G.n..".?.q1.i}..A...1=~O..............vN'.>Xgj-....&W_...Y]..oL....J
010.146.027.110.49139-046.051.243.054.03306: ....0.....FOa^..K3!`.......y....9..k+.Y.e..lk.nV.g...
.t3...X....&054.03306-010.146.027.110.49139: .... .M..{.X.$Yb.\.q...^....PWy..M.S..... j..zGu..<>h.V}.#...
010.146.027.110.51913-046.137.176.190.03306: ....0$}..q.....E.......w.t..5.KW...r. eo...Ak...W..2.
046.137.176.190.03306-010.146.027.110.51913: .... .o..z...fn\S..

これで、リージョン間でも安心してSpiderが利用できます。
以上です。

EC2でMySQL(Spider編1 Spiderってなんじゃ？)

今回はEC2上での、MySQLとSpiderの話になります。
MySQLでの負荷分散というとレプリケーションがメインでしたが、参照系の負荷は分散できても更新処理は分散することが難しく、それがボトルネックになっていました。
このSpiderを利用すると、更新も参照も負荷分散をすることができます。

Spiderは斯波健徳さんが開発したMySQLのストレージエンジンで、MySQLでのシャーディング(データを分散して保存することで負荷を分散すること)がすることができます。
Spiderには以下の機能と特徴があります。

• 異なるMySQLインスタンスのテーブルを同一のインスタンスのテーブルのように扱うことを可能にします。
• xaトランザクションを含むトランザクションをサポートしているため、更新系DBのクラスタリングに利用することが可能です。
• テーブルパーティショニングをサポートしているため、パーティショニングのルールを利用して、同一テーブルのデータを複数サーバに分散配置することが可能です。
• spiderストレージエンジンのテーブルを作成すると、MySQL内部ではファイルへのシンボリックリンクのように、リモートサーバのテーブルへのテーブルリンクを生成します。
• テーブルリンクは、具体的にはローカルMySQLサーバからリモートMySQLサーバへのコネクションを確立することで実現されます。
• リンク先のテーブルのストレージエンジンに制限はありません。

Spiderの構成

Spiderはストレージエンジンなのでテーブル単位で分散ができます。Spiderテーブルはデータそのものは保持しておらず、データ自体は接続先の分散用テーブルに保持され、Spider自体はデータノードへの分散、集約のためのゲートウェイとして機能します。

分散と集約には、パーティションの機能を利用しています。
本来パーティションは、そのテーブル内のデータ領域を内部で分けておくことによって、検索などの効率をあげるためのシステムですが、Spiderはこの設定を擬似的に利用することで、その領域を他のDBインスタンスにまで拡大して分散、集約するように作られています。
言い換えれば他のDBを全て１つのDBの1パーティションとしてあつかえるストレージエンジンです。

今回はサンプルとしてmemberテーブルに対する書き込みを分散するという目的で、以下のようなEC2インスタンスの構成で試してみます。
[]内は仮のIPです。
ここでは、123.123.123.123をSpiderノード、残りのDB1,DB2をデータノードと呼ぶことにします。
Spiderは更新/参照するべきデータノードをテーブルパーティション設定によって判断します。

│[123.123.123.123]
            ┌──┴──┐
            │  Spider  │
            └──┬──┘
                  │
                  │
    ┌──────┴───────┐
    │[111.111.111.111]           │[222.222.222.222]
┌─┴─┐                    ┌─┴─┐
│ DB1  │                    │  DB2 │
└───┘                    └───┘

Spider、DB1、DB2の各データベースは共通して、以下のデータベースを持つことにします。
また、３つのノードで別々のDBやユーザー、パスワードのものを接続することも可能です。

• データベース名：cloudpack
• DBのユーザー名：cloudpack_user
• DBのパスワード：cloudpack_pass

---

データノードの設定

データノードは普段使用している通常のMySQLでかまいません。特別なインストールも必要なしです。
前々回と同様、Linuxバイナリを使用してインストールします。


MySQLのインストールと起動
 mysqlのダウンロードページから適切なバイナリを選んでダウンロードします。

su -
cd /usr/local/src

wget http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.14-linux2.6-i686.tar.gz

tar xzvf mysql-5.5.14-linux2.6-i686.tar.gz
mv mysql-5.5.14-linux2.6-i686 /usr/local/mysql-5.5.14
ln -s /usr/local/mysql-5.5.14 /usr/local/mysql

groupadd mysql
useradd -r -g mysql mysql

cd /usr/local/mysql
chown -R mysql:mysql .
yum list installed | grep libaio
./scripts/mysql_install_db --user=mysql
chown -R root .
chown -R mysql data
cp support-files/my-medium.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld
mkdir -p /var/log/mysql
chown -R mysql:mysql /var/log/mysql

/etc/init.d/mysqld start
chkconfig mysqld on

ユーザーの作成

mysql -u root

mysql> GRANT ALL PRIVILEGES ON *.* TO 'cloudpack_user'@localhost IDENTIFIED BY 'cloudpack_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'cloudpack_user'@'%' IDENTIFIED BY 'cloudpack_pass';
mysql> flush privileges;

データベースの作成

mysql> create database cloudpack;

テーブルの作成

mysql> use cloudpack;
mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

接続の許可
 データノードのセキュリティグループに3306を追加、許可IPに接続先サーバーのIPを指定します。

---

Spiderノードの設定

前述のとおり、Spiderは更新/参照するべきデータノードをテーブルパーティション設定によって判断します。
今回はKEYパーティションを利用した分散をしてみます。
Spiderを導入するには、素のMySQLのパッチ適用やコンパイルなどが必要ですが、Spiderやパッチ込みのLinuxバイナリが提供されているので、今回はこれを使用します。

Spiderビルド済みMySQLのインストール
 Spiderのダウンロードページからビルド済みバイナリをダウンロードして展開します。

su -
cd /usr/local/src

wget http://spiderformysql.com/downloads/spider-2.26/mysql-5.5.14-spider-2.26-vp-0.15-hs-1.0-linux-i686-glibc23.tgz
tar xzvf mysql-5.5.14-spider-2.26-vp-0.15-hs-1.0-linux-i686-glibc23.tgz
mv mysql-5.5.14-spider-2.26-vp-0.15-hs-1.0-linux-i686-glibc23 /usr/local/
ln -s /usr/local/mysql-5.5.14-spider-2.26-vp-0.15-hs-1.0-linux-i686-glibc23 /usr/local/mysql

groupadd mysql
useradd -r -g mysql mysql
cd /usr/local/mysql
chown -R mysql:mysql .
scripts/mysql_install_db --user=mysql
chown -R root .
chown -R mysql data
cp support-files/my-medium.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld

mkdir -p /var/log/mysql
chown -R mysql:mysql /var/log/mysql

/etc/init.d/mysqld start
chkconfig mysqld on

初期化スクリプトの実行
 mysqlデータベースにSpiderがバックエンドで使用するのに必要なテーブルを作成するためのSQLファイルを同じページからダウンロードして実行します。

cd /usr/local/src
wget http://spiderformysql.com/downloads/spider-2.26/spider-init-2.26-for-5.5.14.tgz

tar xzvf spider-init-2.26-for-5.5.14.tgz 
mysql -u root < install_spider.sql

ユーザーの作成

mysql -u root

mysql> GRANT ALL PRIVILEGES ON *.* TO 'cloudpack_user'@localhost IDENTIFIED BY 'cloudpack_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'cloudpack_user'@'%' IDENTIFIED BY 'cloudpack_pass';
mysql> flush privileges;

データベースの作成

mysql> create database cloudpack;
mysql> use cloudpack;

テーブルの作成
 いよいよSpiderストレージエンジンの作成を行います。

mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
) engine = Spider DEFAULT CHARSET=utf8
CONNECTION ' table "member", user "cloudpack_user", password "cloudpack_pass" '
PARTITION BY KEY() (
    PARTITION db1 comment 'host "111.111.111.111", port "3306"',
    PARTITION db2 comment 'host "222.222.222.222", port "3306"'
);

SpiderはMyISAMやInnoDBと同じくストレージエンジンなので、engine=Spiderと記載します。
そして、このCREATE TABLE文でのPARTITION節とCONNECTIONがSpiderの分散設定の要です。
ここでは、KEYパーティションによりパーティションをデータノードの数だけ、つまり２つに分けてあります。

KEYパーティションは簡単に言うとPRIMARY KEYのHash値を元にデータを格納すべきパーティションを決定する方式です。
もちろんそれ以外のパーティションタイプを使用することも可能です。
Spiderはここで定義したPARTITION分割ルールにしたがって、更新・集約するデータノードを決定します。

そしてそれぞれのデータノードの接続先情報を定義するのが、PARTITION節のCOMMENT文字列と、ストレージエンジンの後のCONNECTION文字列です。
これらは通常は別の目的で使用されるものですが、Spiderエンジンはこれらをデータノードの接続情報の設定として解釈するように動作します。
どちらもデータノードへの接続情報などの情報を記載することができますが、主な利用の仕方としては、

• CONNECTION文字列：テーブル全体としての共通の接続設定 
• COMMENT文字列：各データノード用の独自の接続設定

というように分けて設定することが多いようです。
これらの設定文字列には多数の細やかな設定ができるので、詳しくはプロダクト同包のマニュアルを参照してください。

ここでは、CONNECTION文字列に、DB名、DBユーザー名、DBパスワードを、各PARTITIONのCOMMENT文字列には、各データノードのホスト名とポート番号を記載しました。
もしデータノードが３つだった場合はPARTITION句を３つ設定しますし、それぞれDB名やテーブル名が異なっている場合には、databaseやtableなどの情報もPARTITION節ののCOMMENTのほうにそれぞれ記載します。

---

動作の確認

それでは実際にどのようにSpiderが動作するのか、確認してみます。
まず、Spiderノードで何件かINSERTしてみます。

mysql> INSERT INTO member (name) VALUES('memorycraft'),('ichiro'),('jiro'),('sub-LOW'),('shiro');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from member;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | memorycraft |
|  3 | jiro        |
|  5 | shiro       |
|  2 | ichiro      |
|  4 | sub-LOW     |
+----+-------------+
5 rows in set (0.00 sec)

一見、普通の１つのテーブルに見えます。idの順がばらばらですが、通常のテーブルではauto incrementなカラムがあれば、その順にSELECTされることが多いです。
しかし、基本的にORDER BY句がないと順序保証はされないので、特別変わった動作ではなく通常のMySQLの仕様の範囲です。

SpiderテーブルはDROP TABLEしてもデータノードのテーブルは削除されません。これはSpiderテーブルが接続や分散/集約のハブとして機能しているだけで、データの保持、管理を行っていないことをあらわします。DROP TABLEしたあとに再度CREATE TABLEをするだけで、SELECT結果は元通りのデータが返ってきます。

mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
) engine = Spider DEFAULT CHARSET=utf8
CONNECTION ' table "member", user "cloudpack_user", password "cloudpack_pass" '
PARTITION BY KEY() (
    PARTITION db1 comment 'host "111.111.111.111", port "3306"',
    PARTITION db2 comment 'host "222.222.222.222", port "3306"'
 );
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> select * from member;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | memorycraft |
|  3 | jiro        |
|  5 | shiro       |
|  2 | ichiro      |
|  4 | sub-LOW     |
+----+-------------+
5 rows in set (0.00 sec)

一方、TRUNCATE TABLEはデータの除去クエリなので、データノードのデータは削除されます。

mysql> truncate table member;Query OK, 0 rows affected (0.01 sec)

mysql> select * from member;
Empty set (0.00 sec)

再度INSERTをしなおして、各データノードを見てみます。

SpiderテーブルでINSERT

mysql> INSERT INTO member (name) VALUES('memorycraft'),('ichiro'),('jiro'),('sub-LOW'),('shiro');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

db1でSELECT

mysql> select * from member;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | memorycraft |
|  3 | jiro        |
|  5 | shiro       |
+----+-------------+
3 rows in set (0.00 sec)

db2でSELECT

mysql> select * from member;
+----+---------+
| id | name    |
+----+---------+
|  2 | ichiro  |
|  4 | sub-LOW |
+----+---------+
2 rows in set (0.00 sec)

このようにきれいに分散されて保存されていることがわかります。

ここで、データノードのidカラムにそれぞれauto_incrementが設定されているにもかかわらずidが重複しないのは、
Spiderのテーブル設定のauto_increment_modeパラメータ（CONNECTION文字列で設定できるパラメータ）の動作に基づきます。
auto_increment_modeの動作として、

• 0：通常モード。(リモートサーバにロック付き問い合わせで取得した最新付番を利用して、付番を行う。) 遅い。テーブルパーティショニングを利用しており、auto incrementカラムが indexの第一カラムである場合は、簡易モードで動作する。
• 1：簡易モード。(Spiderテーブル内のカウントで付番を行う。) 速いが、更新は１テーブルからのみに限定しないと値の重複が発生する。
• 2:割愛
• 3:割愛

デフォルトは0

となっており、今回の場合1の簡易モードが有効になり、Spider側で自動採番しているためです。
この様に、複数の分散されたDBをまったく１つのDBとほぼ同じように扱えるため、読込みだけでなく書込みにも負荷分散でき、非常に有用なプロダクトだといえます。

疲れた、、、、今回はここまで。