■モチベーション
spiderとhandlersocketでスケーラブルかつキャッシュいらずなmysql作れないかな。
# spider2.24からhandlersocketと一緒に使えるそうだ。
# 入れてみよう。
http://wild-growth-ja.blogspot.jp/2011/02/mysqlspidervpotherspider-224-vp-013.html
■結果概要
・インストールと、spider, handlersocketの単体の動作は問題なくできた。
・spider経由でMySQL::sandbox上のテーブルにhandlersocketで接続することができなかった。（調査中）
※ 本来はhandlersocketを使ってspiderのテーブルにアクセスできるはずだが。
■備考
mysqlはhomebrewで入れたかったけど、spiderを5.5系に入れる場合はソースから入れる方法しかなく、
なので、仕方なくmysqlとspiderを両方ソースから入れました。
mysqlとspiderとhandlersocketとq4mなども一緒にしたソースを下記から取得できますが、Macでのインストールではいろいろ苦戦。
https://launchpad.net/spiderformysql/spider-2.x/2.28-for-5.5.14
■前準備
# brewが入っていなければ
## /usr/bin/ruby -e "$(/usr/bin/curl -fksSL https://raw.github.com/mxcl/homebrew/master/Library/Contributions/install_homebrew.rb)"
# cmakeやwgetが入っていない人は
## brew install cmake
## brew install wget
■まずはmysqlとspiderをインストール
# ダウンロード
https://launchpad.net/spiderformysql/spider-2.x/2.28-for-5.5.14
mysql-5.5.14-spider-2.28-vp-0.17-hs-1.1-q4m-0.95.tgz
# macで綺麗に動いてくれないので、少し修正する。
http://bugs.mysql.com/bug.php?id=65050
http://lists.mysql.com/commits/140413
# q4mとhandlersocketもmacで綺麗にmakeしてくれないので、削除。(handlersocketは後で別で入れる)
mv storage/q4m ~/bk
mv plugin/handlersocket ~/bk
# configure
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.14-spider \
-DMYSQL_DATADIR=/var/lib/mysql/data \
-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DWITH_EXTRA_CHARSETS=complex \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_ARCHIVE_STORAGE_ENGINE=1 \
-DWITHOUT_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_PIC=1 .
# makeするも失敗。
sudo make
[ 93%] Building C object unittest/mysys/CMakeFiles/my_atomic-t.dir/my_atomic-t.c.o
/var/tmp//cco9mhO6.s:596:suffix or operands invalid for `add'
make[2]: *** [unittest/mysys/CMakeFiles/my_atomic-t.dir/my_atomic-t.c.o] Error 1
make[1]: *** [unittest/mysys/CMakeFiles/my_atomic-t.dir/all] Error 2
make: *** [all] Error 2
# これは解決策が見つからず、-iで無理やりインストール。。。
sudo make -i
sudo make install -i
# 起動スクリプトをコピー
cd /usr/local/mysql-5.5.14-spider
sudo cp support-files/mysql.server bin/
$ spider インストールスクリプトを取得
sudo wget http://launchpad.net/spiderformysql/spider-2.x/2.26-for-5.5.14/+download/spider-init-2.26-for-5.5.14.tgz
tar zxvf spider-init-2.26-for-5.5.14.tgz
# spiderをインストール
mysql -u root < install_spider.sql
ERROR 1049 (42000) at line 20: Unknown database 'mysql'
# mysqlがない。
#rootでログインしてshow databasesしても、mysqlデータベースがなかった。
sudo ./scripts/mysql_install_db --datadir=/var/lib/mysql/data --user=mysql
# databaseを初期化
# 再度実行
mysql -u root < install_spider.sql
ERROR 1436 (HY000) at line 211: Thread stack overrun: 13056 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.
# 次はスタック数のエラー
# 設定ファイル変更
sudo cp support-files/my-small.cnf /etc/my.cnf
vi /etc/my.cnf
thread_stack = 256K
# 再度実行
mysql -u root < install_spider.sql
# 通った!
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| SPIDER | YES | Spider storage engine | YES | YES | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MyISAM | YES | MyISAM storage engine | NO | NO | 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 |
| CSV | YES | CSV storage engine | NO | NO | NO |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)
■続いてhandlersocket
https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL
git clone git://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL.git
cd HandlerSocket-Plugin-for-MySQL/
./autogen.sh
./configure --with-mysql-source=/usr/src/mysql-5.5.14-spider-2.28-vp-0.17-hs-1.1-q4m-0.95 --with-mysql-bindir=/usr/local/mysql-5.5.14-spider/bin/ --with-mysql-plugindir=/usr/local/mysql-5.5.14-spider/lib/plugin
make
make install
# 別コンソールでmysqlを起動し、
mysql> install plugin handlersocket soname 'handlersocket.so';
# プラグインが入ったかな。
ls /usr/local/Cellar/mysql/5.5.20/lib/plugin/
# handlersocketの設定を入れる。
sudo vi /etc/my.cnf
[mysqld]セクションに追記
handlersocket_port = 9998
handlersocket_port_wr = 9999
handlersocket_address =
handlersocket_verbose = 0
handlersocket_timeout = 300
handlersocket_threads = 16
thread_concurrency = 128
open_files_limit = 65535
mysql> show plugins;
+-----------------------+--------+--------------------+------------------+---------+
| Name | Status | Type | Library | License |
+-----------------------+--------+--------------------+------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SPIDER | ACTIVE | STORAGE ENGINE | ha_spider.so | GPL |
| handlersocket | ACTIVE | DAEMON | handlersocket.so | BSD |
+-----------------------+--------+--------------------+------------------+---------+
19 rows in set (0.01 sec)
# よし、両方入った
■spiderを使ってみる
# ここを参考に、MySQL::sandboxを立ち上げる
http://nippondanji.blogspot.jp/2010/04/spider.html
http://odokuchang.blogspot.jp/2012/04/mysqlspidersandboxsandbox.html
export SANDBOX_BINARY=$HOME/opt/mysql
export SANDBOX_HOME=$HOME/opt/sandboxes
mkdir ~/opt/mysql
cd ~/opt/mysql
ln -s /usr/local/mysql-5.5.14-spider 5.5.14
make_multiple_sandbox --how_many_nodes=4 5.5.14
cd ~/opt/sandboxes/multi_msb_5_5_14/
./use_all 'create database spider_test character set utf8'
cp /usr/local/mysql-5.5.14-spider/install_spider.sql .
./use_all 'source install_spider.sql'
# 再起動
./stop_all
./start_all
# 各nodeのportを調査
./use_all 'select @@port'
# server: 1:
@@port
13915
# server: 2:
@@port
13916
# server: 3:
@@port
13917
# server: 4:
@@port
13918
# 各nodeの接続先を登録
# msandboxはmysql::sandboxのデフォルトユーザー名,パスワード
./use_all "CREATE SERVER n1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'root', PASSWORD '', HOST '127.0.0.1', PORT 3306);
CREATE SERVER n2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 13915);
CREATE SERVER n3 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 13916);
CREATE SERVER n4 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 13917);
CREATE SERVER n5 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 13918);"
# 消すときは
# ./use_all "drop server n1;drop server n2;drop server n3;drop server n4;drop server n5;"
# ※通常のmysqlクライアントで各sandboxに繋ぐのも、いつもと同じ要領
# mysql -umsandbox -pmsandbox -h 127.0.0.1 -P 13916
# 3306のmysqlにテーブルを作り、同時にspiderのマスターにする。
mysql -uroot -h localhost -P 3306
use spider_test;
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)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
PARTITION BY HASH(emp_no) (
PARTITION p1 COMMENT 'user "msandbox", password "msandbox", host "127.0.0.1", port "13915", table "salaries",',
PARTITION p2 COMMENT 'user "msandbox", password "msandbox", host "127.0.0.1", port "13916", table "salaries",',
PARTITION p3 COMMENT 'user "msandbox", password "msandbox", host "127.0.0.1", port "13917", table "salaries",',
PARTITION p4 COMMENT 'user "msandbox", password "msandbox", host "127.0.0.1", port "13918", table "salaries",'
);
# node1からnode4までにテーブルを作る
./use_all "
use spider_test;
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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
# 消す場合は
# ./use_all 'use spider_test; drop table salaries;'
# 3306にデータをinsert
mysql -uroot -h localhost -P 3306
use spider_test;
insert into salaries(emp_no, salary) values(1, 1000);
insert into salaries(emp_no, salary) values(2, 2000);
insert into salaries(emp_no, salary) values(3, 3000);
./use_all 'use spider_test;
select * from salaries;'
# server: 1:
# server: 2:
emp_no salary from_date to_date
1 1000 0000-00-00 0000-00-00
# server: 3:
emp_no salary from_date to_date
2 2000 0000-00-00 0000-00-00
# server: 4:
emp_no salary from_date to_date
3 3000 0000-00-00 0000-00-00
# node1から4に分散されてます。
# sandbox便利だのう。
■handlersocket経由でspiderを使ってみる
# ruby用のhandlersocketクライアントを入れる
cd /usr/src/HandlerSocket-Plugin-for-MySQL/
sudo wget https://bitbucket.org/winebarrel/ruby-handlersocket/get/tip.tar.gz
sudo tar zxvf tip.tar.gz
sudo mv winebarrel-ruby-handlersocket-c19841e47ea2/* .
sudo rm -r winebarrel-ruby-handlersocket-c19841e47ea2/
sudo ruby extconf.rb
sudo make
sudo make install
# spiderとhandlersocketを同時に利用できるように、設定を追加
# ここを参照
http://wild-growth-ja.blogspot.jp/2011/02/mysqlspidervpotherspider-224-vp-013.html
vi /etc/my.cnf
spider_use_hs_read=1
spider_use_hs_write=1
# 全部再起動
mysql.server restart
cd ~/opt/sandboxes
./start_all
■ruby -> spider(9999) -> sandbox spider-node へ接続
vi ahndler_sample.rb
require 'rubygems'
require 'handlersocket'
hs = HandlerSocket.new('127.0.0.1', 9999)
# Args (default value)
# host(localhost), port(9998), timeout(600), listen_backlog(256)
# 3は識別子
hs.open_index(3, 'spider_test', 'salaries', 'PRIMARY', 'emp_no,salary');
# select 接続、条件、条件値、count、offset
p res = hs.execute_single(3, '>=', [2], 100, 0);
hs.close
## エラーでとれない
ruby handler_sample.rb
[-1, "read: eof"]
# しかも、mysqlがクラッシュ
tail /var/log/system.log
May 3 18:40:19 xxx-no-MacBook-Air librariand[356]: no ubiquity account configured, not creating collection
May 3 18:40:19 xxx-no-MacBook-Air librariand[356]: error in handle_client_request: LibrarianErrorDomain/10/Unable to configure the collection.
# 起動しようとするとエラー。
mysqld
120503 18:43:11 [Warning] Setting lower_case_table_names=2 because file system for /var/lib/mysql/data/ is case insensitive
120503 18:43:11 InnoDB: The InnoDB memory heap is disabled
120503 18:43:11 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120503 18:43:11 InnoDB: Compressed tables use zlib 1.2.5
120503 18:43:11 InnoDB: Initializing buffer pool, size = 128.0M
120503 18:43:11 InnoDB: Completed initialization of buffer pool
120503 18:43:11 InnoDB: highest supported file format is Barracuda.
120503 18:43:11 InnoDB: Waiting for the background threads to start
120503 18:43:12 InnoDB: 1.1.8 started; log sequence number 1604997
[Warning] handlersocket: open_files_limit is too small.
[Warning] handlersocket: open_files_limit is too small.
120503 18:43:12 [Note] Recovering after a crash using tc.log
120503 18:43:12 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=16384
read_buffer_size=262144
max_used_connections=0
max_threads=151
thread_count=17
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 50051 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x0 thread_stack 0x40000
0 mysqld 0x00000001073c00fe my_print_stacktrace + 46
1 mysqld 0x000000010720d13b handle_segfault + 523
2 libsystem_c.dylib 0x00007fff87054cfa _sigtramp + 26
3 ??? 0x00007fff66d47ae0 0x0 + 140734918589152
4 mysqld 0x00000001071e43e5 _ZN11TC_LOG_MMAP4openEPKc + 1301
5 mysqld 0x00000001072134a5 _Z11mysqld_mainiPPc + 10965
6 mysqld 0x000000010714a7f4 start + 52
7 ??? 0x0000000000000001 0x0 + 1
# あれ、そういえばhandlersocketのportは[9998-9999] なんだけど、
# sandbox使ってる場合、sandboxのmysqlのhandlersocketはどのportになるんだろう。
# しかも、handlersocket プラグインを各sandboxにinstallせねばならないのではなかろうか。
# spider-nodeがhandlersocket非対応ならsqlでデータ取得するというspiderの仕様だったと思うが、その動きは見られず。
■sandboxを1台に変更する
ruby -> spider(9997) -> sandbox spider-node(9999) へ接続
# spiderからsandboxのspider-nodeにhandlersocketでアクセスする際の設定を変更したいけど、
# spider -> sandboxへのアクセスでのport指定をどこでやればよいのか見つからないため、sandboxを1台に変更する。(デフォルトの9999で繋いでくれるかな。)
# 1台のsandboxを再定義
cd ~/opt/mysql
make_multiple_sandbox --how_many_nodes=1 5.5.14
cd ~/opt/sandboxes/multi_msb_5_5_14/
./use_all 'create database spider_test character set utf8'
cp /usr/local/mysql-5.5.14-spider/install_spider.sql .
./use_all 'source install_spider.sql'
# 再起動
./stop_all
./start_all
# handlersocket
./n1
install plugin handlersocket soname 'handlersocket.so';
vi node1/my.sandbox.cnf
[mysqld]
handlersocket_port = 9998
handlersocket_port_wr = 9999
handlersocket_address =
handlersocket_verbose = 0
handlersocket_timeout = 300
handlersocket_threads = 16
thread_concurrency = 128
open_files_limit = 65535
spider_internal_xa=1
spider_semi_trx_isolation=2
spider_use_handler=1
spider_use_hs_read=1
spider_use_hs_write=1
# 各nodeのportを調査
./use_all 'select @@port'
# server: 1:
@@port
13915
# 各nodeの接続先を登録
# msandboxはmysql::sandboxのデフォルトユーザー名,パスワード
./use_all "CREATE SERVER n1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'root', PASSWORD '', HOST '127.0.0.1', PORT 3306);
CREATE SERVER n2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'msandbox', PASSWORD 'msandbox', HOST '127.0.0.1', PORT 13915);"
# ※通常のmysqlクライアントで各sandboxに繋ぐのも、いつもと同じ要領
# mysql -umsandbox -pmsandbox -h 127.0.0.1 -P 13916
# 3306のmysqlにテーブルを作り、同時にspiderのマスターにする。
mysql -uroot -h localhost -P 3306
use spider_test;
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)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
PARTITION BY HASH(emp_no) (
PARTITION p1 COMMENT 'user "msandbox", password "msandbox", host "127.0.0.1", port "13915", table "salaries",'
);
# node1にテーブルを作る
./use_all "
use spider_test;
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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
# spiderのhandlersocketのportを変更する。
vi /etc/my.cnf
[mysqld]
handlersocket_port = 9996
handlersocket_port_wr = 9997
mysql.server restart
cd ~/opt/sandboxes/
./restart_all
■実際にhandlersocketで接続
handler_sample1.rb
require 'rubygems'
require 'handlersocket'
# spider経由
hs = HandlerSocket.new('127.0.0.1', 9997)
# 直接sandbox
#hs = HandlerSocket.new('127.0.0.1', 9999)
# Args (default value)
# host(localhost), port(9998), timeout(600), listen_backlog(256)
# 3は識別子
hs.open_index(3, 'spider_test', 'salaries', 'PRIMARY', 'emp_no,salary');
# select 接続、条件、条件値、count、offset
p res = hs.execute_single(3, '>=', [2], 100, 0);
# insert 接続、行
hs.execute_insert(3, [1,"10000"]);
hs.execute_insert(3, [2,"20000"]);
hs.execute_insert(3, [3,"30000"]);
# delete 接続、条件、条件値、count、offset ← selectして消すため
# 詳細はここ
# https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-ja/perl-client.ja.txt
hs.execute_delete(3, '=', [2], 1, 0);
# 高速なexecute_multi
p hs.execute_multi(
[
[3, '=', [3], 1, 0],
[3, '=', [1], 1, 0]
]
)
hs.close
■実行結果
# port 9997(spider)だとエラー
# ruby -> spider -> sandbox
ruby handler_sample1.rb
[-1, "read: eof"]
[[-1, "read: eof"]]
# port 9999(spider-node)だととれる
# ruby -> sandbox
ruby handler_sample1.rb
[0, "3", "3000"]
[[0, "3", "3000"], [0, "1", "1000"]]
なんでやねん!!
documentのspider_use_hs_writeオプションの項に下のように書いてあるけど、handlersocketを別で入れたから駄目なのかな。
「このオプションは、handlersocket用パッチ適用済みバイナリでのみ利用可能。」
今日はここまで。。
