MySQL Spiderエンジンを使ってみた。～データベースシャーディング(sharding)とは～

2011-10-10

■[MySQL]MySQL Spiderエンジンを使ってみた。～データベースシャーディング(sharding)とは～

もしかしたら使うかもしれないので調べてみた。

shard

日本語に訳すと(ガラスや貝殻の) 「破片」といったような意味
データベースをshardに分解して複数のサーバに分散して運用するのがDB sharding
データベースパーティショニングとも言えるのかしら？
- 単一のサーバのDBテーブルを複数のファイルに分割するのをパーティショニングとも呼ぶが、「パーティショニング」という言葉を使ってDB shardingのことを言っているブログ等もちらほら見かけます。
より突っ込んでみたい人は「shared nothing」でぐぐってネ。

なぜデータベースを分散処理するのか

横軸にはサービス運用開始からの時間経過を設定し、縦軸には「DBの応答時間」、「DBへの問い合わせ数」、「DBサイズ」の各数量をとります。サービスが順調に利用されていっているものとし、「DBサイズ」や「DBへの問い合わせ数」は時間経過とともに線形に増えていきますが、「DBの応答時間」はある時を境に指数的に増えていくことが想定できます。要するにDBのレスポンスは急激に悪化することがあります。原因としては以下が考えられます。

メモリに乗り切らないデータはディスクを読み込んで探さなければならない。
ディスクからデータを探すのとメモリからデータを探すのでは速度差に10の５乗～６乗近くの差が出る(10万倍～１００万倍)

スケールアップ・スケールアウトという枠組みで解決法が考えられますが、今回の主題から言うと次のようになります。

スケールアウト戦略

DB shardingは単一のサーバでの処理能力が著しく落ちない程度にデータの大きさを調節する(データベースサイズをスケールする)ことでDBの性能を最大限に引き出せるようにする手法です。MySQLのSpiderエンジンは手軽にDBを分散処理できるようにしてくれています。

これに対してレプリケーションはDBへの参照問い合わせをスケールしてくれていると言えるでしょう。

Spiderを使った時のイメージ

上記の図はサーバ4台の構成で、黄色のサーバはアプリケーションサーバでDB サーバも兼ねています。が、DBのデータの実体は各オレンジ色のDB サーバに分散されて黄色のサーバはデータは保持していません。

実際に使ってみる前に

テーブルパーティショニングについて
MySQL Sandboxについて

テーブルパーティショニング

5.1からサポートされた(水平パーティショニングのみ)。
水平パーティショニングと垂直パーティショニング
- 水平･･･テーブル内の異なる行(row)が異なるパーティションに割り当てられる
- 垂直･･･テーブル内の異なる列(column)が異なるパーティションに割り当てられる(SPIDERエンジンの開発者がVPエンジンなるものも開発している。)
分割方法
- RANGE分割(パーティション毎にカラムの値の取る範囲を指定する。)
- LIST分割(パーティション毎にカラムの値を直接指定する。)
- HASH分割、KEY分割 (要するにHASH値でもってパーティションの割り当てをします。)

RANGE分割

下記のようなCREATE文を発行することで通常は一つのMYDファイルに保存されるところをp0.MYD, p1.MYD, p2.MYD, p3.MYDのように４つのファイルに分割してデータを保存することができるようになります。ちなみにこのSQL文だけだと単に同一のサーバにファイル分割しただけとなります。同一のサーバでも異なるHDに分割できるようにすればデータの読み込み速度も上がるのかなと思ったりしますが、単純にファイル分割しただけで何がどう変わるとかまでは調べておりません。。

CREATE TABLE t_range (
  name text,
  hight int,
  birthday date
) ENGINE=MYISAM
PARTITION BY RANGE(hight) (
  PARTITION p0 VALUES LESS THAN (150),
  PARTITION p1 VALUES LESS THAN (170),
  PARTITION p2 VALUES LESS THAN (180),
  PARTITION p3 VALUES LESS THAN MAXVALUE
)

LIST分割

CREATE TABLE t_list (
  name text,
  hight int,
  birthday date
) ENGINE=MYISAM
PARTITION BY LIST(MONTH(birthday)) (
  PARTITION p0 VALUES IN (1, 2, 3),
  PARTITION p1 VALUES IN (4, 5, 6),
  PARTITION p2 VALUES IN (7, 8, 9),
  PARTITION p3 VALUES IN (10, 11, 12)
);

HASH分割

CREATE TABLE t_hash (
  name text,
  hight int,
  birthday date
) ENGINE=MYISAM
PARTITION BY HASH(MONTH(birthday)) PARTITIONS 4;

KEY分割

CREATE TABLE t_key (
  id int,  
  name text,  
  hight int,
  birthday date
) ENGINE=MYISAM 
PARTITION BY KEY(id) PARTITIONS 4;

MySQL Sandboxとは

簡単に複数の検証用環境を立ち上げることができます。
CPANからインストールできる。(http://search.cpan.org/~gmax/MySQL-Sandbox-3.0.17/lib/MySQL/Sandbox.pm)
Perlerでない場合は
- wget http://search.cpan.org/CPAN/authors/id/G/GM/GMAX/MySQL-Sandbox-3.0.19.tar.gz
- tar zxvf MySQL-Sandbox-3.0.19.tar.gz
- cd MySQL-Sandbox-3.0.19
- perl Makefile.PL
- make
- make test
- sudo make install

Spiderを実際に使ってみる。

Spiderエンジンのインストール
MySQL::Sandboxでの環境構築
Spiderエンジンのインストール
Spiderテーブルとリンク先のテーブルを作成
データ操作してみる

Spiderエンジンのインストール

https://launchpad.net/spiderformysqlからダウンロード。5.5系はSpiderのソースも組み込まれたMySQLのソースを全コンパイルしてのインストールのみとなる。SpiderのみをPluginで追加できるのは現在は5.1系のみ

5.5系は./configureの代わりにcmakeを使うようになった。PREFIXを指定して自分のホームディレクトリにインストール
- tar zxvf mysql-5.5.14-spider-2.26-vp-0.15-hs-1.0.tgz
- cd mysql-5.5.14-spider-2.26-vp-0.15-hs-1.0
- cmake . -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DCMAKE_INSTALL_PREFIX=/home/you/mysql_spider_5.5.14
- make
- make install

MySQL::Sandboxでの環境構築

/home/opt/mysql以下にインストールした各バージョンのエイリアスを設置
make_multiple_sandbox --how_many_nodes=4 5.5.14-spider

Spiderエンジンのインストール

spider同梱のMySQLのソースにscripts/install_spider.sqlがあるのでそれをspiderエンジンを使いたいノードにインストールします。
- cd ~/sandbox/multi_msb_5_5_14
- ./n1 < install_spider.sql
上記のスクリプトを実行するとmysql データベース以下にspiderの管理テーブルが生成され、spiderエンジンが使用可能となる。
./use_all -e 'create database spider_test'などとしてテスト用のDBを作っておくといいかも。

Spiderテーブルとリンク先のテーブルを作成

mysqlのドキュメントページからダウンロード(http://dev.mysql.com/doc/index-other.html)できるemployeeデータベースを使ってサンプルを作成する。

spiderノードにはspiderテーブルを作成する
- ./n1 spider_test < table_for_spider_node.sql

CREATE TABLE salaries (
  emp_no int(11) NOT NULL,
  salary int(11) NOT NULL,
  from_date date NOT NULL,
  to_date date NOT NULL,
  PRIMARY KEY (emp_no,from_date),
  KEY emp_no (emp_no)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
PARTITION BY HASH(emp_no) (
  PARTITION p1 COMMENT 'user "msandbox", password "msandbox",  host "127.0.0.1", port "13916", table "salaries",',
  PARTITION p2 COMMENT 'user "msandbox", password "msandbox",  host "127.0.0.1", port "13917", table "salaries",',
  PARTITION p3 COMMENT 'user "msandbox", password "msandbox",  host "127.0.0.1", port "13918", table "salaries",'
);

node2～node4までは通常のテーブルをインストールする
- ./n2 spider_test < table_for_normal_node.sql

CREATE TABLE salaries (
    emp_no int(11) NOT NULL,
    salary int(11) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no,from_date),
    KEY emp_no (emp_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

先ほどのサンプルのemployeesデータベースを解凍し、salariesテーブルをspiderノードにロードしてきちんと分散処理されているかを確認します。

データ操作してみる

./n1 spider_test < load_salaries.dump (topで様子を見ると並列処理している様子がみれて面白いかなと)

$ ./n1 spider_test -e 'select count(*) from salaries';
+----------+
| count(*) |
+----------+
|  2844047 |
+----------+

$ ./use_all -e 'use spider_test; select count(*) from salaries';
# server: 1: 
count(*)
2844047
# server: 2: 
count(*)
947747
# server: 3: 
count(*)
947505
# server: 4: 
count(*)
948795

既存のテーブルをshardingしたいという場合もspiderを使うと簡単に処理できます。
- 先ほどは直接spiderノードにデータをロードしましたが、既存のテーブルにspiderのリンクを貼ってそこからデータを直接引っ張ってくることもできます。下記のようなSQLで一発です。

insert into salaries select * from salaries_datasource

集約関数は機能する？
- 単一のサーバにロードされたsalariesテーブルからデータを間引いてサラリーの平均を確認し、spiderで運用しているデータにも同様の操作をしてきちんと数値が一致するかを確認します。

まずは単一のサーバにロードされたsalariesテーブル
mysql > desc salaries;
 +-----------+---------+------+-----+---------+-------+
 | Field     | Type    | Null | Key | Default | Extra |
 +-----------+---------+------+-----+---------+-------+
 | emp_no    | int(11) | NO   | PRI | NULL    |       |
 | salary    | int(11) | NO   |     | NULL    |       |
 | from_date | date    | NO   | PRI | NULL    |       |
 | to_date   | date    | NO   |     | NULL    |       |
 +-----------+---------+------+-----+---------+-------+
 4 rows in set (0.00 sec)

mysql > select AVG(salary) from salaries;
+-------------+
| AVG(salary) |
+-------------+
|  63810.7448 |
+-------------+
1 row in set (1.17 sec)

mysql > select count(*) from salaries;
+----------+
| count(*) |
+----------+
|  2844047 |
+----------+
1 row in set (0.74 sec)

mysql > select count(*) from salaries where emp_no in (10011, 10012, 10013);
+----------+
| count(*) |
+----------+
|       34 |
+----------+

mysql > delete from salaries where emp_no in (10011, 10012, 10013);
Query OK, 34 rows affected (0.01 sec)

mysql > select AVG(salary) from salaries;
+-------------+
| AVG(salary) |
+-------------+
|  63810.9068 |
+-------------+

mysql > select count(*) from salaries;
+----------+
| count(*) |
+----------+
|  2844013 |
+----------+
1 row in set (0.87 sec)

次いでspiderテーブル
node1 > select AVG(salary) from salaries;
+-------------+
| AVG(salary) |
+-------------+
|  63810.7448 |
+-------------+
1 row in set (3.33 sec)

node1 > select count(*) from salaries where emp_no in (10011, 10012, 10013);
+----------+
| count(*) |
+----------+
|       34 |
+----------+

node1 > delete from salaries where emp_no in (10011, 10012, 10013);
Query OK, 34 rows affected (0.02 sec)

node1 > select AVG(salary) from salaries;
+-------------+
| AVG(salary) |
+-------------+
|  63810.9068 |
+-------------+
1 row in set (3.31 sec)