気ままに書いてみました日記 RSSフィード

 | 

2011-10-10

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

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

shard

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

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

f:id:abcb2:20111005212113p:image

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

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

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


スケールアウト戦略

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

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


Spiderを使った時のイメージ

f:id:abcb2:20111010121523p:image:w640

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


実際に使ってみる前に

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

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

  • 5.1からサポートされた(水平パーティショニングのみ)。
  • 水平パーティショニングと垂直パーティショニング
    • 水平・・・テーブル内の異なる行(row)が異なるパーティションに割り当てられる
    • 垂直・・・テーブル内の異なる列(column)が異なるパーティションに割り当てられる (SPIDERエンジンの開発者がVPエンジンなるものも開発している。)
  • 分割方法

RANGE分割

下記のようなCREATE文を発行することで通常は一つのMYDファイルに保存されるところをp0.MYD, p1.MYD, p2.MYD, p3.MYDのように4つのファイルに分割してデータを保存することができるようになります。ちなみにこの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とは


Spiderを実際に使ってみる。

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

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

  • 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での環境構築

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を作っておくといいかも。

f:id:abcb2:20111010132324p:image


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

f:id:abcb2:20111010140108p:image:w640

  • 集約関数は機能する?
    • 単一のサーバにロードされた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)

おわりに

とまあ以上のような感じで検証しました。参考にさせてもらったのは主にhttp://nippondanji.blogspot.com/2010/04/spider.htmlでした。

あと、レプリケーションして使いたいとか、そういうケースも調べてみたのでまた別エントリで書きたいと思います。

自宅サーバ派の自分としては結構使い道があるなと思ったりもしています。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

リンク元
 | 
Connection: close