MariaDB 10.0.10 と Spider ストレージエンジンを使ってみる
MySQL の Sharding 用ストレージエンジンである Spider を試してみました。
公式のバイナリに同梱されている MariaDB 10.0.10 を使っています。
MariaDB のインストール
まずは MariaDB をインストールするために、yum リポジトリ定義ファイルを作成します。
# vi /etc/yum.repos.d/mariadb.repo
内容は以下。
# MariaDB 10.0 CentOS repository list - created 2014-04-02 07:21 UTC # http://mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.0/centos6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 enable=0
保存したら以下のように yum コマンドでインストールします。
# yum install -y --enablerepo=mariadb MariaDB-server MariaDB-client
これで MariaDB 本体のインストールは完了。
mysqld_multi の設定
Sharding を試すのに複数台仮想機を用意してもいいんですが、今回は標準で付属している mysqld_multi コマンドを使用して複数インスタンスを用意しました。
まずは /etc/my.cnf.d/server.cnf を編集します。
# vi /etc/my.cnf.d/server.cnf
mysqld セクションに文字コードの設定を追記しておきます。
[mysqld] character-set-server = utf8
次は /etc/my.cnf を編集。
# vi /etc/my.cnf
以下を追記します。
[mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin log = /var/lib/mysql/multi.log [mysqld1] port = 3306 datadir = /var/lib/mysql1 pid-file = /var/lib/mysql1/mysql.pid socket = /var/lib/mysql1/mysql.sock [mysqld2] port = 3307 datadir = /var/lib/mysql2 pid-file = /var/lib/mysql2/mysql.pid socket = /var/lib/mysql2/mysql.sock [mysqld3] port = 3308 datadir = /var/lib/mysql3 pid-file = /var/lib/mysql3/mysql.pid socket = /var/lib/mysql3/mysql.sock
最初は server.cnf と同じ様に別ファイルを my.cnf.d 以下に作成し、そこに mysqld_multi 用の設定を書いてみたのですが、どうも何か間違っているのか mysqld_multi が設定を上手く認識してくれなかったので、しかたなく /etc/my.cnf 内に直接記述しました。
設定ファイルを保存したら各インスタンス用の datadir を作成します。
# mysql_install_db --datadir=/var/lib/mysql1 --user=mysql # mysql_install_db --datadir=/var/lib/mysql2 --user=mysql # mysql_install_db --datadir=/var/lib/mysql3 --user=mysql # chown -R mysql:mysql /var/lib/mysql1 /var/lib/mysql2 /var/lib/mysql3
これで準備はできたのでインスタンスを起動します。
# mysqld_multi start
コンソールには何も出ませんが、プロセス等を確認するとちゃんと三つのインスタンスが起動している事が分かります。
# ps -ef | grep [m]ysqld root 5963 1 0 21:41 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --port=3306 --datadir=/var/lib/mysql1 --pid-file=/var/lib/mysql1/mysql.pid --socket=/var/lib/mysql1/mysql.sock root 5970 1 0 21:41 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --port=3307 --datadir=/var/lib/mysql2 --pid-file=/var/lib/mysql2/mysql.pid --socket=/var/lib/mysql2/mysql.sock root 5983 1 0 21:41 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --port=3308 --datadir=/var/lib/mysql3 --pid-file=/var/lib/mysql3/mysql.pid --socket=/var/lib/mysql3/mysql.sock mysql 6364 5963 0 21:41 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql1 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql1/mariadb.err --pid-file=/var/lib/mysql1/mysql.pid --socket=/var/lib/mysql1/mysql.sock --port=3306 mysql 6366 5970 0 21:41 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql2 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql2/mariadb.err --pid-file=/var/lib/mysql2/mysql.pid --socket=/var/lib/mysql2/mysql.sock --port=3307 mysql 6372 5983 0 21:41 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql3 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql3/mariadb.err --pid-file=/var/lib/mysql3/mysql.pid --socket=/var/lib/mysql3/mysql.sock --port=3308 # netstat -tln | grep 330* tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
これで MariaDB の準備が出来ました。
Spider をインストール
ここでは一つ目のインスタンス(mysqld1) を Spider ノードとして動かしたいと思います。
mysql コマンドで mysqld1 の sock ファイルを指定して接続。
# mysql -uroot --socket=/var/lib/mysql1/mysql.sock
install_spider.sql を実行して Spider ストレージエンジンをインストールします。
install_spider.sql は /usr/share/mysql/ の下にあります。
MariaDB [(none)]> source /usr/share/mysql/install_spider.sql
SHOW ENGINES で追加されたことを確認します。
MariaDB [(none)]> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | SPIDER | YES | Spider storage engine | YES | YES | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
データノード(mysqld2, mysqld3) を SERVER として登録します。
MariaDB [(none)]> CREATE SERVER mysqld2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', HOST '127.0.0.1', PORT 3307); MariaDB [(none)]> CREATE SERVER mysqld3 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', HOST '127.0.0.1', PORT 3308);
正常に作成された事を確認。
MariaDB [(none)]> SELECT * FROM mysql.servers; +-------------+-----------+----+----------+----------+------+--------+---------+-------+ | Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner | +-------------+-----------+----+----------+----------+------+--------+---------+-------+ | mysqld2 | 127.0.0.1 | | spider | spider | 3307 | | mysql | | | mysqld3 | 127.0.0.1 | | spider | spider | 3308 | | mysql | | +-------------+-----------+----+----------+----------+------+--------+---------+-------+
一旦 mysqld1 を抜けて、Spider 用の DB とユーザを各インスタンスに作成します。
# mysql -uroot --socket=/var/lib/mysql1/mysql.sock -e "CREATE DATABASE example_db; GRANT ALL PRIVILEGES ON *.* TO 'spider'@'localhost' IDENTIFIED BY 'spider'; FLUSH PRIVILEGES;" # mysql -uroot --socket=/var/lib/mysql2/mysql.sock -e "CREATE DATABASE example_db; GRANT ALL PRIVILEGES ON *.* TO 'spider'@'localhost' IDENTIFIED BY 'spider'; FLUSH PRIVILEGES;" # mysql -uroot --socket=/var/lib/mysql3/mysql.sock -e "CREATE DATABASE example_db; GRANT ALL PRIVILEGES ON *.* TO 'spider'@'localhost' IDENTIFIED BY 'spider'; FLUSH PRIVILEGES;"
作成した example_db を指定して再度 mysqld1 に接続。
# mysql -uroot --socket=/var/lib/mysql1/mysql.sock example_db
example_db 上に SPIDER エンジンを指定した以下のようなテスト用のテーブルを作成します。
CREATE TABLE books ( id int AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, price int(11) NOT NULL default 0, created_at DATETIME NOT NULL, updated_at DATETIME, lock_version int(11) NOT NULL default 0, PRIMARY KEY (id) ) ENGINE = SPIDER DEFAULT CHARSET=utf8 PARTITION BY HASH(id) ( PARTITION p1 comment 'server "mysqld2", table "books"', PARTITION p2 comment 'server "mysqld3", table "books"' );
mysqld2, mysqld3 には同じテーブルを InnoDB で作成します。
- mysqld2
# mysql -uroot --socket=/var/lib/mysql2/mysql.sock example_db
CREATE TABLE books ( id int AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, price int(11) NOT NULL default 0, created_at DATETIME NOT NULL, updated_at DATETIME, lock_version int(11) NOT NULL default 0, PRIMARY KEY (id) ) ENGINE = InnoDB DEFAULT CHARSET=utf8;
- mysqld3
# mysql -uroot --socket=/var/lib/mysql3/mysql.sock example_db
CREATE TABLE books ( id int AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, price int(11) NOT NULL default 0, created_at DATETIME NOT NULL, updated_at DATETIME, lock_version int(11) NOT NULL default 0, PRIMARY KEY (id) ) ENGINE = InnoDB DEFAULT CHARSET=utf8;
mysqld1 に接続し、テストデータを投入してみます。
# mysql -uroot --socket=/var/lib/mysql1/mysql.sock example_db
INSERT INTO books(name, price, created_at) VALUES ('3日で分かるJava', 2500, NOW()); INSERT INTO books(name, price, created_at) VALUES ('3日で分かるRuby', 2300, NOW()); INSERT INTO books(name, price, created_at) VALUES ('独習仮想化', 5000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Java入門', 2000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('入門Ruby', 2800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Effective Ruby', 4200, NOW()); INSERT INTO books(name, price, created_at) VALUES ('すごいRuby', 5800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Ruby徹底入門', 3000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('RubyからJavaへ', 1800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('クラウド大全', 6000, NOW());
それぞれのインスタンスでで SELECT してみると以下のような感じでした。
- mysqld1(SPIDER)
MariaDB [example_db]> select * from books order by id; +----+----------------------+-------+---------------------+------------+--------------+ | id | name | price | created_at | updated_at | lock_version | +----+----------------------+-------+---------------------+------------+--------------+ | 1 | 3日で分かるJava | 2500 | 2014-04-18 22:09:30 | NULL | 0 | | 2 | 3日で分かるRuby | 2300 | 2014-04-18 22:09:30 | NULL | 0 | | 3 | 独習仮想化 | 5000 | 2014-04-18 22:09:30 | NULL | 0 | | 4 | Java入門 | 2000 | 2014-04-18 22:09:30 | NULL | 0 | | 5 | 入門Ruby | 2800 | 2014-04-18 22:09:30 | NULL | 0 | | 6 | Effective Ruby | 4200 | 2014-04-18 22:09:30 | NULL | 0 | | 7 | すごいRuby | 5800 | 2014-04-18 22:09:30 | NULL | 0 | | 8 | Ruby徹底入門 | 3000 | 2014-04-18 22:09:30 | NULL | 0 | | 9 | RubyからJavaへ | 1800 | 2014-04-18 22:09:30 | NULL | 0 | | 10 | クラウド大全 | 6000 | 2014-04-18 22:09:31 | NULL | 0 | +----+----------------------+-------+---------------------+------------+--------------+
- mysqld2
MariaDB [example_db]> select * from books order by id; +----+----------------------+-------+---------------------+------------+--------------+ | id | name | price | created_at | updated_at | lock_version | +----+----------------------+-------+---------------------+------------+--------------+ | 2 | 3日で分かるRuby | 2300 | 2014-04-18 22:09:30 | NULL | 0 | | 4 | Java入門 | 2000 | 2014-04-18 22:09:30 | NULL | 0 | | 6 | Effective Ruby | 4200 | 2014-04-18 22:09:30 | NULL | 0 | | 8 | Ruby徹底入門 | 3000 | 2014-04-18 22:09:30 | NULL | 0 | | 10 | クラウド大全 | 6000 | 2014-04-18 22:09:31 | NULL | 0 | +----+----------------------+-------+---------------------+------------+--------------+
- mysqld3
MariaDB [example_db]> select * from books order by id; +----+----------------------+-------+---------------------+------------+--------------+ | id | name | price | created_at | updated_at | lock_version | +----+----------------------+-------+---------------------+------------+--------------+ | 1 | 3日で分かるJava | 2500 | 2014-04-18 22:09:30 | NULL | 0 | | 3 | 独習仮想化 | 5000 | 2014-04-18 22:09:30 | NULL | 0 | | 5 | 入門Ruby | 2800 | 2014-04-18 22:09:30 | NULL | 0 | | 7 | すごいRuby | 5800 | 2014-04-18 22:09:30 | NULL | 0 | | 9 | RubyからJavaへ | 1800 | 2014-04-18 22:09:30 | NULL | 0 | +----+----------------------+-------+---------------------+------------+--------------+
ちゃんとレコードが分散されてますね。
こんな感じで簡単に Sharding する事が出来ました。
ハマったところとか
実は途中、テストデータを INSERT する所で結構ハマりました。
公式ドキュメントの「08_sharding.txt」や、いくつかの参考にさせて頂いたサイトでは、以下の設定を入れていました。
[mysqld1] ・・・ # spider spider_internal_xa=1 spider_semi_trx_isolation=2 spider_support_xa=0
これらの設定は以下のような意味とのこと。
- 08_sharding.txt
spider_internal_xa=1の設定を行うとクライアント側での通常のトランザクションの操作が、内部的にxaトランザクションの操作に置き換えられ、spiderテーブルで複数のDBを利用しても同期更新が実現されます。
spider_semi_trx_isolation=2は、1SQLの間のトランザクション隔離レベルの変更を行うことで、sql実行中に他のクライアントによる更新が行われても一貫性のある結果が得られるようにします。状況に応じてspider_semi_trx_isolation=3の利用を選択しても良いでしょう。なお、spider_semi_trx_isolationはトランザクションをサポートしたストレージエンジンにのみ有効で、トランザクションをサポートしないMyISAMのようなストレージエンジンではspider_semi_table_lockを利用します。
・MySQLは現在2つ以上のxaトランザクションをサポートするストレージエンジンを利用できません。
SpiderとInnoDBを併用する場合は、コンフィグファイルに「spider_support_xa=0」を記載し、Spiderのxaサポートを無効にしてください。
Spiderのxaサポートが無効になっても、「internal_xa」は利用可能です。
http://d.hatena.ne.jp/tetsuyai/20110903/1317288617
説明を読んだ感じ、いかにも入れておいた方が良さそうな設定です。
というわけで、最初は mysqld1 に Spider エンジンをインストール後、これらの設定を追加し、mysqld1 を再起動していました。
# mysqld_multi stop 1 # mysqld_multi start 1
この状態でテストデータを INSERT したところ、以下のようなエラーが発生。
MariaDB [example_db]> INSERT INTO books(name, price, created_at) VALUES ('3日で分かるJava', 2500, NOW()); ERROR 12606 (HY000): This xid member is already exist
インスタンスを再起動したり、最初から作成し直したりしても全く解消しないので、もしやと思い SELECT をしてみると同じようなエラーが発生。
MariaDB [example_db]> SELECT * FROM books; ERROR 12605 (HY000): This xid is already exist
うーむ・・・一体何故だろう?
もしかして mysqld_multi で試しているのがいけなかったりするんだろうか。
いろいろと検索してみたんですが、結局回避策が見つからなかったため、最終的には上でまとめた手順の通り、これらの設定を入れないようにしたところ、上手く動作するようになりました。
しかし、説明によればこれらは XA トランザクション周りの設定との事なので、もしかしてこれらを設定しないとトランザクションが使えなくなってしまったりするのでしょうか。
気になったので確認してみます
mysqld1 に接続し、通常のトランザクションを開始します。
MariaDB [example_db]> begin;
再度同じテストデータを投入。
INSERT INTO books(name, price, created_at) VALUES ('3日で分かるJava', 2500, NOW()); INSERT INTO books(name, price, created_at) VALUES ('3日で分かるRuby', 2300, NOW()); INSERT INTO books(name, price, created_at) VALUES ('独習仮想化', 5000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Java入門', 2000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('入門Ruby', 2800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Effective Ruby', 4200, NOW()); INSERT INTO books(name, price, created_at) VALUES ('すごいRuby', 5800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Ruby徹底入門', 3000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('RubyからJavaへ', 1800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('クラウド大全', 6000, NOW());
SELECT すると当然ですがデータは増えています。
MariaDB [example_db]> select * from books order by id; +----+----------------------+-------+---------------------+------------+--------------+ | id | name | price | created_at | updated_at | lock_version | +----+----------------------+-------+---------------------+------------+--------------+ | 1 | 3日で分かるJava | 2500 | 2014-04-18 22:09:30 | NULL | 0 | | 2 | 3日で分かるRuby | 2300 | 2014-04-18 22:09:30 | NULL | 0 | | 3 | 独習仮想化 | 5000 | 2014-04-18 22:09:30 | NULL | 0 | | 4 | Java入門 | 2000 | 2014-04-18 22:09:30 | NULL | 0 | | 5 | 入門Ruby | 2800 | 2014-04-18 22:09:30 | NULL | 0 | | 6 | Effective Ruby | 4200 | 2014-04-18 22:09:30 | NULL | 0 | | 7 | すごいRuby | 5800 | 2014-04-18 22:09:30 | NULL | 0 | | 8 | Ruby徹底入門 | 3000 | 2014-04-18 22:09:30 | NULL | 0 | | 9 | RubyからJavaへ | 1800 | 2014-04-18 22:09:30 | NULL | 0 | | 10 | クラウド大全 | 6000 | 2014-04-18 22:09:31 | NULL | 0 | | 11 | 3日で分かるJava | 2500 | 2014-04-18 22:36:03 | NULL | 0 | | 12 | 3日で分かるRuby | 2300 | 2014-04-18 22:36:03 | NULL | 0 | | 13 | 独習仮想化 | 5000 | 2014-04-18 22:36:03 | NULL | 0 | | 14 | Java入門 | 2000 | 2014-04-18 22:36:03 | NULL | 0 | | 15 | 入門Ruby | 2800 | 2014-04-18 22:36:03 | NULL | 0 | | 16 | Effective Ruby | 4200 | 2014-04-18 22:36:03 | NULL | 0 | | 17 | すごいRuby | 5800 | 2014-04-18 22:36:03 | NULL | 0 | | 18 | Ruby徹底入門 | 3000 | 2014-04-18 22:36:03 | NULL | 0 | | 19 | RubyからJavaへ | 1800 | 2014-04-18 22:36:03 | NULL | 0 | | 20 | クラウド大全 | 6000 | 2014-04-18 22:36:03 | NULL | 0 | +----+----------------------+-------+---------------------+------------+--------------+
ロールバックを実行。
MariaDB [example_db]> rollback;
SELECT してみます。
MariaDB [example_db]> select * from books order by id; +----+----------------------+-------+---------------------+------------+--------------+ | id | name | price | created_at | updated_at | lock_version | +----+----------------------+-------+---------------------+------------+--------------+ | 1 | 3日で分かるJava | 2500 | 2014-04-18 22:09:30 | NULL | 0 | | 2 | 3日で分かるRuby | 2300 | 2014-04-18 22:09:30 | NULL | 0 | | 3 | 独習仮想化 | 5000 | 2014-04-18 22:09:30 | NULL | 0 | | 4 | Java入門 | 2000 | 2014-04-18 22:09:30 | NULL | 0 | | 5 | 入門Ruby | 2800 | 2014-04-18 22:09:30 | NULL | 0 | | 6 | Effective Ruby | 4200 | 2014-04-18 22:09:30 | NULL | 0 | | 7 | すごいRuby | 5800 | 2014-04-18 22:09:30 | NULL | 0 | | 8 | Ruby徹底入門 | 3000 | 2014-04-18 22:09:30 | NULL | 0 | | 9 | RubyからJavaへ | 1800 | 2014-04-18 22:09:30 | NULL | 0 | | 10 | クラウド大全 | 6000 | 2014-04-18 22:09:31 | NULL | 0 | +----+----------------------+-------+---------------------+------------+--------------+
ちゃんとデータは巻き戻っているようです。
どうやら spider_internal_xa = 1 などの設定を入れなくても、ちゃんと複数DB間でのトランザクションは有効になっているようです。
ということは、ドキュメントには書いてあるけど別にこの辺の設定は不要、って事でいいのかなぁ?
この辺りいまいちまだ良く分かっていないです。
ただ、通常の commit, rollback が使えるのであれば Rails 等のフレームワークからも何も意識せずにトランザクションを使う事は出来そうですね。
まとめ
この他、試した範囲では mysqldump も普通に使えるし、ダンプからのインポートも全く問題ありませんでした。
後はデータノード追加時の Re-sharding をどうやるのかは時間のある時にでも検証しておきたいところ。
Spider は以前から試してみたかったんですが、こうやって公式バイナリに同梱されると導入の敷居が下がって嬉しいです。
個人的に今まで DB は MySQL を使う事が多かったんですが、次に新規に DB サーバ立てる機会があったら MariaDB を使いたいですね。
そのうち VP ストレージエンジンも同梱されるといいなー。
参考
Spider Storage Engine Overview - MariaDB Knowledgebase
https://mariadb.com/kb/en/spider-storage-engine-overview/
漢(オトコ)のコンピュータ道: 快適スケールアウト生活への第一歩。SPIDERストレージエンジンを使ってみよう!
http://nippondanji.blogspot.jp/2010/04/spider.html
MySQL Spiderエンジンを使ってみた。〜データベースシャーディング(sharding)とは〜 - 気ままに書いてみました日記
http://d.hatena.ne.jp/abcb2/20111010/1318224266