というわけで、今日はSPIDERストレージエンジンの基本的な使い方について紹介する。少し長いエントリであるが、最後までお付き合い頂ければ幸いである。
完成形
今回の解説では、次のように4つのSPIDERノードと4つのデータノードを構築する。
何はともあれまずはインストール
まずはSPIDERをダウンロードしよう。SPIDERはBazaarでlp:spiderformysqlからゲットすることも出来るが、launchpad上のSPIDERのページで配布されているのでこちらをダウンロードするといいだろう。今回は、バージョン2.17（直リンク）を利用して解説する。なお、バージョン2.17はMySQL 5.1.44に対応したバージョンであるので、MySQL 5.1.44をダウンロード（直リンク）しよう。また、SPIDER利用時などにCondition Pushdownを有効化するパッチ（直リンク）もあるので、こちらもダウンロードすること。launchpad上のSPIDERのページでは、ドキュメント類一式も配布されている。詳細については、こちらのドキュメントを参照して頂きたい。ただし、一部わかり辛い部分もあるのでその点をカバーしようというのが、本エントリの主旨である。
さて、以下はドキュメントに含まれる「03_install_spider_storage_engine.txt」からの抜粋+αである。
・MySQL全コンパイルでのインストール MySQLのソースの展開。 # tar zvfx ./mysql-5.1.44.tar.gz spiderストレージエンジンのソースの展開と移動。 # tar zvfx ./spider-2.17-for-5.1.44.tar.gz # tar zvfx ./partition_cond_push-0.1-for-5.1.44.tgz (http://launchpad.net/partitionconditionpushdownformysql からダウンロード) # mv ./spider ./mysql-5.1.44/storage パッチの適用、コンパイル準備、コンパイル、インストール。 # cd ./mysql-5.1.44 # patch -p2 < ../mysql-5.1.44.partition_cond_push.diff # patch -p2 < ../mysql-5.1.44.spider.diff # autoconf # automake # ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --libexecdir=/usr/local/mysql/bin --enable-thread-safe-client --enable-local-infile --with-pic --with-fast-mutexes --with-client-ldflags=-static --with-mysqld-ldflags=-static --with-zlib-dir=bundled --with-big-tables --with-ssl --with-readline --with-embedded-server --with-partition --without-innodb --without-ndbcluster --without-archive-storage-engine --without-blackhole-storage-engine --with-csv-storage-engine --without-example-storage-engine --without-federated-storage-engine --with-extra-charsets=complex (オプションは適当に変更すること) # make # make install
この手順では、MySQLごとSPIDERをビルドしている。libmysqld（組み込み型のサーバーライブラリ）を利用する人はきっと少ないので、コンパイルオプションの--with-embedded-serverは--without-embedded-serverに変更しておくと良いだろう。なお、SPIDERのソースコードを展開すると、カレントディレクトリにspiderというディレクトリが作成されるので注意しよう。ビルドは作業用のディレクトリを作成して、その中でファイルを展開したほうがいいだろう。
partitionconditionpushdownformysqlについてはドキュメント中で触れられていないが、SPIDERに対してCondition Pushdown最適化を有効にするためのパッチである。劇的に性能が変化するので必ず適用しよう。
今回はソースコードをコンパイルすることを前提に解説しているが、Linux向けバイナリ（ha_spider.so）も配布されているので、そちらを利用する場合にはSHOW VARIABLESで表示される「plugin_dir」にha_spider.soをコピーすれば良い。（ただしパッチを適用するとSPIDERの性能と使い勝手が向上するため、ソースコードをコンパイルするのがお勧めである。）
お次はインスタンスの設定であるが、SPIDERは多数のノードを連携して利用することを想定したストレージエンジンである。今回はMySQL Sandboxを利用して8つのインスタンスを構築する。MySQL SandboxはMySQLのテスト環境を素早くセットアップするためのツールだ。
shell> ln -s /path/to/mysql $HOME/opt/mysql/5.1.44 shell> make_multiple_sandbox --how_many_nodes=8 5.1.44インスタンスのインストールが完了したら、MySQL Sandboxに含まれるuse_allコマンドを使ってテスト用のデータベースを作成しておく。use_allコマンドは、一括で全てのインスタンスに対してSQL文を実行するためのコマンドだ。
shell> cd $SANDBOX_HOME/multi_msb_5_1_44 shell> ./use_all 'create database spider_test character set utf8'インストールが完了したらSPIDERの設定を行う必要がある。「03_install_spider_storage_engine.txt」を読むと、テーブルを作成してプラグインとUDFを認識させるコマンドが書かれている。それを全て実行する必要があるのだが、この部分の手順がわかり辛い＆面倒だと斯波氏に相談したところ、SPIDERをインストールするSQLスクリプトを作成してくれたので今回はそれを利用する。SQLスクリプトは未公開なので、ここからダウンロード（直リンク）して頂きたい。 スクリプトは次のように実行しよう。
shell> ./use_all 'source install_spider.sql'
仕上げとして、n1〜n4の設定ファイルを記述しよう。設定ファイルはnode[1-4]/my.sandbox.cnfにある。SPIDERのパラメータは数が多くてわかり辛いと思うので、とりあえずは試験用にサンプル（直リンク）を作成したのでこれを利用して頂きたい。現時点のバージョンでは、spider_support_xaを有効にすると上手く動作しないようなので、これをOFFにしている。また、その他のパラメータについては、筆者の経験と勘から独断と偏見で値を設定しているのであしからず。
また、それぞれのインスタンスで、文字コードと最大パケットサイズの設定をし、n1〜n4ではInnoDBの無効化を、n5〜n8ではInnoDBのバッファプールとログファイルサイズの設定をしておこう。
これらの設定をコマンドでまとめて行うと次の通り。shell> mv my_spider.cnf $SANDBOX_HOME/multi_msb_5_1_44
shell> cd $SANDBOX_HOME/multi_msb_5_1_44
shell> n=1
shell> while [ $n -le 4 ]
do
echo '[mysqld]
character-set-server=utf8
max-allowed-packet=16M
skip-innodb' >> node${n}/my.sandbox.cnf
cat my_spider.cnf >> node${n}/my.sandbox.cnf
n=$(($n + 1))
done
shell> while [ $n -le 8 ]
do
echo '[mysqld]
character-set-server=utf8
max-allowed-packet=16M
innodb-buffer-pool-size=128M
innodb-log-file-size=32M' >> node${n}/my.sandbox.cnf
n=$(($n + 1))
done
Sandboxの再起動が出来れば、インストールと設定は完了である。
shell> ./stop_all shell> rm node?/data/ib* # InnoDBのデータファイルはいったん削除しよう。 shell> ./start_all
リモートホストの登録
SPIDERでは、FEDERATEDストレージエンジンで利用可能なSERVERをサポートしている。CREATE SERVERを実行すれば、予めリモートホストへの接続パラメータを設定しておくことが出来るので非常に便利である。今回は次のようにCREATE SERVERを実行する。MySQL Sandboxでは、ユーザー名とパスワードが初期状態でmsandboxとなっているので、皆さんが設定される場合には実際の環境に合わせて頂きたい。ポート番号については、「./use_all 'select @@port'」などで事前に調べておこう。./use_all "CREATE SERVER n1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 16545); CREATE SERVER n2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 16546); CREATE SERVER n3 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 16547); CREATE SERVER n4 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 16548); CREATE SERVER n5 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 16549); CREATE SERVER n6 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 16550); CREATE SERVER n7 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 16551); CREATE SERVER n8 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 16552)"登録は一度実行しておけば問題ない。n1〜n4は登録する必要はなく冗長である。
テーブルの作成その1 - HASHパーティショニング
今回のエントリでは、MySQL公式のサンプルデータベースであるemployeeデータベースを利用する。employeesデータベースは、MySQLのドキュメントサイトからダウンロードして頂きたい。 まず、HASHパーティショニングを利用してsalariesテーブルを分散させてみよう。オリジナルのsalariesテーブルの定義は次の通りである。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`), CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8※筆者の手元の環境では、ローカルのMySQL Server（127.0.0.1:3306）に別途employeesデータベースをロードしてある。皆さんもSPIDERで遊ぶときには、どこかのサーバーに元になるデータベースがあると便利なのでロードしておこう。 SPIDERストレージエンジンは、リモートホストへのリンクをはるだけであり、先にリモートホストにはテーブルが存在している必要がある。なので、次のような手順で、n5〜n8にテーブルを作成しておこう。（外部キー制約は使えないので省いてある。）
./n5 spider_test -e "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" ^5^6 ^6^7 ^7^8それから、次のようにSPIDERストレージエンジンを使ってテーブルを作成する。
shell> ./n1 spider_test mysql> 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 'server "n5", table "salaries"', PARTITION p2 COMMENT 'server "n6", table "salaries"', PARTITION p3 COMMENT 'server "n7", table "salaries"', PARTITION p4 COMMENT 'server "n8", table "salaries"' );このように、SPIDERではパーティションごとにCOMMENT句を指定して接続パラメータを指定する。パラメータはカンマ区切りの文字列であり、「param_name "value"」という書式をである。各パラメータの詳細については「06_table_parameters.txt」を参照して頂きたい。 n2〜n4についても同様にCREATE TABLE文を実行しよう。以上でテーブルの作成は完了である。
データのロード
ローカルのMySQL Server（127.0.0.1:3306）にemployeesデータベースが格納されているので、それをSPIDERテーブルにロードしてみよう。なお、そのテーブルへ接続するためにもSPIDERストレージエンジンを利用すると便利である。以下は、SERVERを使わずにテーブルを定義する例である。host、port、user、passwordを指定する必要があるので、少々面倒かも知れない。mysql> CREATE TABLE salaries_datasource ( 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 CONNECTION 'host "127.0.0.1", port "3306", user "user", password "password", database "employees", table "salaries"'; mysql> insert into salaries select * from salaries_datasource; Query OK, 2844047 rows affected (2 min 4.38 sec) Records: 2844047 Duplicates: 0 Warnings: 0 mysql> drop table salaries_datasource;SPIDERはリモートのMySQL Server（データノード）上にあるテーブルに対してリンクをはるストレージエンジンである。従って、次のように異なるインスタンスから同じデータを参照することが出来るのである。（同時に複数のSPIDERストレージエンジンから参照／更新されることになるが、排他処理はリモートのMySQL Server上で行われる。）
node1 [localhost] {msandbox} (spider_test) > select count(1) from salaries;
+----------+
| count(1) |
+----------+
| 2844047 |
+----------+
1 row in set (1.78 sec)
node2 [localhost] {msandbox} (spider_test) > select count(1) from salaries;
+----------+
| count(1) |
+----------+
| 2844047 |
+----------+
1 row in set (2.49 sec)
salariesテーブルはemp_noカラムでパーティショニングをしたため、emp_noを検索条件として指定することによりパーティションの刈り込みが行われる。その様子は、次のようにEXPLAIN PARTITIONSコマンドで確認することが出来る。
node1 [localhost] {msandbox} (spider_test) > explain partitions select * from salaries where emp_no = 1001;
+----+-------------+----------+------------+------+----------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+------+----------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | salaries | p2 | ref | PRIMARY,emp_no | PRIMARY | 4 | const | 4 | |
+----+-------------+----------+------------+------+----------------+---------+---------+-------+------+-------+
1 row in set (0.02 sec)
テーブルの作成その2 - RANGEパーティショニング
HASHパーティショニングの場合は、「パーティション数＝データノード数」とすれば良いのでテーブルの作成は単純であったが、RANGEパーティショニングの場合にはパーティションの範囲が「月ごと」「一週間ごと」などのように日付の範囲で決まることが多いため、うまくパーティション数とデータノード数が一致しないことが多い。ここでは、RANGEパーティショニングの例として、employeesテーブルを次の図のように17個に分割する場合について紹介しよう。
./n5 spider_test -e "CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender enum('M','F') NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no,gender)
) ENGINE=InnoDB DEFAULT CHARSET=utf8"
^5^6
^6^7
^7^8
しかし、このままでは各パーティションごとにデータを格納する先のテーブル名が被ってしまう。パーティショニングを利用する利点は、パーティションごとにデータを削除出来る点にあるのだが、データを格納するテーブルが被っていたらそのような操作はできない。そこで、次のようにパーティションごとに異なる名前のテーブルを作成するのである。
./n5 spider_test -e "CREATE TABLE employees_p1 LIKE employees; CREATE TABLE employees_p5 LIKE employees; CREATE TABLE employees_p9 LIKE employees; CREATE TABLE employees_p13 LIKE employees; RENAME TABLE employees TO employees_p17" ./n6 spider_test -e "CREATE TABLE employees_p2 LIKE employees; CREATE TABLE employees_p6 LIKE employees; CREATE TABLE employees_p10 LIKE employees; RENAME TABLE employees TO employees_p14" ./n7 spider_test -e "CREATE TABLE employees_p3 LIKE employees; CREATE TABLE employees_p7 LIKE employees; CREATE TABLE employees_p11 LIKE employees; RENAME TABLE employees TO employees_p15" ./n8 spider_test -e "CREATE TABLE employees_p4 LIKE employees; CREATE TABLE employees_p8 LIKE employees; CREATE TABLE employees_p12 LIKE employees; RENAME TABLE employees TO employees_p16"データノード側に受け皿となるテーブルの作成が完了したので、お次はn1〜n4でSPIDERテーブルを定義しよう。
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender enum('M','F') NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no,gender,hire_date)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(hire_date)) (
PARTITION p1 VALUES LESS THAN (1986) COMMENT 'server "n5", table "employees_p1"',
PARTITION p2 VALUES LESS THAN (1987) COMMENT 'server "n6", table "employees_p2"',
PARTITION p3 VALUES LESS THAN (1988) COMMENT 'server "n7", table "employees_p3"',
PARTITION p4 VALUES LESS THAN (1989) COMMENT 'server "n8", table "employees_p4"',
PARTITION p5 VALUES LESS THAN (1990) COMMENT 'server "n5", table "employees_p5"',
PARTITION p6 VALUES LESS THAN (1991) COMMENT 'server "n6", table "employees_p6"',
PARTITION p7 VALUES LESS THAN (1992) COMMENT 'server "n7", table "employees_p7"',
PARTITION p8 VALUES LESS THAN (1993) COMMENT 'server "n8", table "employees_p8"',
PARTITION p9 VALUES LESS THAN (1994) COMMENT 'server "n5", table "employees_p9"',
PARTITION p10 VALUES LESS THAN (1995) COMMENT 'server "n6", table "employees_p10"',
PARTITION p11 VALUES LESS THAN (1996) COMMENT 'server "n7", table "employees_p11"',
PARTITION p12 VALUES LESS THAN (1997) COMMENT 'server "n8", table "employees_p12"',
PARTITION p13 VALUES LESS THAN (1998) COMMENT 'server "n5", table "employees_p13"',
PARTITION p14 VALUES LESS THAN (1999) COMMENT 'server "n6", table "employees_p14"',
PARTITION p15 VALUES LESS THAN (2000) COMMENT 'server "n7", table "employees_p15"',
PARTITION p16 VALUES LESS THAN (2001) COMMENT 'server "n8", table "employees_p16"'
);
こうしておくことで、必要に応じてパーティションを追加することも思いのままである。ちなみに、パーティションの追加は次のように実行する。
mysql> ALTER TABLE employees ADD PARTITION (PARTITION p17 VALUES LESS THAN (2002) COMMENT 'server "n5", table "employees_p17");では、先ほどと同様にローカルのMySQL Server（127.0.0.1:3306）からデータをロードしよう。
mysql> CREATE TABLE employees_datasource (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender enum('M','F') NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no,gender)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION 'host "127.0.0.1", port "3306", user "user", password "password", database "employees", table "employees"';
mysql> insert into employees select * from employees_datasource;
Query OK, 300024 rows affected (6.41 sec)
Records: 300024 Duplicates: 0 Warnings: 0
これでテーブルの作成およびデータのロードは完了である。パーティショニングに用いたカラム（hire_date）を検索条件にすることで、パーティションの刈り込みが効いていることが分かる。
node1 [localhost] {msandbox} (spider_test) > explain partitions select * from employees where hire_date = '1999-03-03'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
partitions: p15
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 604241
Extra: Using where with pushed condition
1 row in set (0.02 sec)
HASHパーティショニングで複数のデータノードに分散されたsalariesテーブルと、RANGEパーティショニングで分散されたemployeesをJOINしてみよう。
node1 [localhost] {msandbox} (spider_test) > select emp_no, first_name, last_name, sum(salary) total_s from employees join salaries using(emp_no) group by emp_no order by total_s limit 100;
+--------+-------------+----------------+---------+
| emp_no | first_name | last_name | total_s |
+--------+-------------+----------------+---------+
| 10015 | Guoxiang | Nooteboom | 40000 |
| 10186 | Shigehito | Kropatsch | 40000 |
| 10204 | Nevio | Ritcey | 40000 |
（中略）
| 21178 | Ymte | Skrikant | 40000 |
| 21449 | Masanao | Zweizig | 40000 |
| 21915 | Kitty | Erie | 40000 |
+--------+-------------+----------------+---------+
100 rows in set (1 min 30.36 sec)
このように、複数のノードにShardingされていてもバッチリJOINすることが出来るのである。
まとめとSPIDER利用時の注意点
今回のエントリでは、SPIDERストレージエンジンのインストールとテーブルの作成方法について紹介した。インストールに関しては、MySQL Server本体にパッチをあててソースコードからビルドするのがお勧めである。テーブル作成については、データを格納するテーブルを、事前にデータノード上に作成しておく必要があるため少々手間であるが、SELECT/INSERT/UPDATE/DELETEなどのDML系のSQLは他のストレージエンジンと同じように利用出来る（JOINもサブクエリもOK）ので、DDL系のSQLだけ少し注意を払えば良い。 DDL系のSQL文について特筆したいのは、SPIDERテーブルをDROPしてもデータノード上のデータは消えないということである。例えば次のようにテーブルを再作成すれば、再びデータを参照出来るようになる。mysql> drop table employees;
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
:
（中 略）
:
PARTITION p16 VALUES LESS THAN (2001) COMMENT 'server "n8", table "employees_p16"',
PARTITION p17 VALUES LESS THAN (2002) COMMENT 'server "n5", table "employees_p17"'
);
mysql> select count(1) from employees;
+----------+
| count(1) |
+----------+
| 300024 |
+----------+
1 row in set (1.04 sec)
SPIDERはFEDERATEDストレージエンジンと同じようにリモートのMySQL Server（データノード）上にあるテーブルへのリンクを作成するストレージエンジンであり、テーブルのDROP時にはリンクを削除するだけだからこのような動作になるのである。ちなみに、TRUNCATE文はリモートのMySQL Server上のデータを全てクリアしてしまうので注意しよう。
性能特性について注意したい点としては、SPIDERはパーティショニングを前提としたストレージエンジンであり、パーティションの刈り込みを上手に使いこなす必要があるということが挙げられる。この点については、MySQL Clusterと同様の最適化が求められるので、過去に書いたMySQL ClusterにおけるDistribution Awareness（ユーザーレベルパーティショニング）に関するエントリなどを参照して頂きたい。ただし、SPIDERの場合はMySQL Clusterと同様Engine Condition Pushdownが有効であり、そのうえバックグラウンドで行のフェッチを行うため、パーティションの刈り込みが効いていなくてもSELECTは高速化されるだろう。性能特性については、よくベンチマークしてアプリケーションとの相性を確かめて欲しい。
SPIDERストレージエンジンは斯波氏個人による作品であるため、ノウハウの共有やマニュアルの整備などに荒が目立つ感は否めないが、筆者としては手作りな感じがして好感を抱いてしまう。手作りのソフトウェアを皆で協力して育てていくのがオープンソースの醍醐味である。ぜひSPIDERストレージエンジンを利用された際には、ノウハウの共有や斯波氏へのフィードバックをして、一緒にSPIDERストレージエンジンを育てて頂きたい！！
本エントリを執筆している時点では、斯波氏はシリコンバレーに居る。MySQL Conference & ExpoでSPIDERストレージエンジンについての発表を行うためである。MySQL Conference & Expoでは、斯波氏が手がけるもう一つのストレージエンジンであるVPストレージエンジンや、SPIDERに新たにデータノードを追加したときのReshard（データの再分散）の手順、SPIDERを使ってHAを構成する方法などについて紹介される予定である。プレゼン資料は後日公開されるだろう。
SPIDERストレージエンジンの進化は目覚ましい。しかし、さらにもっと進化するためには、あなたの協力が不可欠なのである。コミュニティひとりひとりのフィードバックがあれば、SPIDERストレージエンジンはまだまだ進化する。今後もSPIDERストレージエンジンに注目して頂きたい！！
0 コメント:
コメントを投稿