MariaDB NoSQL with HandlerSocket

MariaDB NoSQL with HandlerSocket

环境:

CentOS7.1 x64

mariadb-10.1.13

安装配置HandlerSocket

https://mariadb.com/kb/en/handlersocket/

INSTALL SONAME 'handlersocket'

[mysqld]

handlersocket_address = "127.0.0.1"

handlersocket_port = "9998"

handlersocket_port_wr = "9999"

root@jlive:~#/etc/init.d/mysql restart

Restarting mysql (via systemctl): [ 确定 ]

root@jlive:~#netstat -tunlp|grep mysql

tcp 0 0 127.0.0.1:9998 0.0.0.0:* LISTEN 24591/mysqld

tcp 0 0 127.0.0.1:9999 0.0.0.0:* LISTEN 24591/mysqld

tcp6 0 0 :::3306 :::* LISTEN 24591/mysqld

(jlive)[isfdb]>SHOW PROCESSLIST;

+----+-------------+---------------------+---------------+---------+------+-------------------------------------------+------------------+----------+

+----+-------------+---------------------+---------------+---------+------+-------------------------------------------+------------------+----------+

| 22 | jlive | 192.168.130.1:63960 | isfdb | Query | 0 | init | SHOW PROCESSLIST | 0.000 |

+----+-------------+---------------------+---------------+---------+------+-------------------------------------------+------------------+----------+

22 rows in set (0.00 sec)

[mysqld]

# HandlerSocket Read Threads:

handlersocket_threads = 16

# HandlerSocket Write Threads:

handlersocket_threads_wr = 1

handlersocket_plain_secret = 'readSocketPassword'

handlersocket_plain_secret_wr = 'writeSocketPassword'

提示:

handlersocket默认情况下会启动16个读线程和1个写线程，可以通过上面参数修改线程数，读写的最大线程数最大值为3000，官方推荐写线程数设为1，读线程数为CPU核数的两倍

还可以设置为读写handlersocket设置不同的明文密码

libhsclient

https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL

git clone https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL.git

cd HandlerSocket-Plugin-for-MySQL/libhsclient

make

mkdir -p /usr/local/include/handlersocket

install -m 644 *.hpp /usr/local/include/handlersocket

install -m 644 libhsclient.a /usr/local/lib

ln -s /usr/local/include/handlersocket/* /usr/local/include/

HandlerSocket perl client库

1.安装perl handlersocket库

cd HandlerSocket-Plugin-for-MySQL/perl-Net-HandlerSocket/

perl Makefile.PL

make -j4 && make install

2.创建示例数据

CREATE DATABASE IF NOT EXISTS test;

USE test;

DROP TABLE IF EXISTS hs_test;

CREATE TABLE hs_test (

id SERIAL PRIMARY KEY,

givenname varchar(64),

surname varchar(64)

);

INSERT INTO hs_test VALUES

(1,"William","Hartnell"),

(2,"Patrick","Troughton"),

(3,"John","Pertwee"),

(4,"Tom","Baker"),

(5,"Peter","Davison"),

(6,"Colin","Baker");

3.创建示例perl脚本

#!/usr/bin/perl

use strict;

use warnings;

use Net::HandlerSocket;

my $read_args = { host => 'localhost', port => 9998 };

my $hs = new Net::HandlerSocket($read_args);

my $res = $hs->open_index(0, 'test', 'hs_test', 'PRIMARY', 'id,givenname,surname');

die $hs->get_error() if $res != 0;

my $pk = 1;

$res = $hs->execute_single(0, '=', [ "$pk" ], 10, 0);

die $hs->get_error() if $res->[0] != 0;

shift(@$res);

while ( $res->[0] ) {

printf("%s %s %s ",$res->[0],$res->[1],$res->[2]);

$pk++;

$res = $hs->execute_single(0, '=', [ "$pk" ], 20, 0);

die $hs->get_error() if $res->[0] != 0;

shift(@$res);

}

root@jlive:~#perl hs_read_test.pl

1 William Hartnell

2 Patrick Troughton

3 John Pertwee

4 Tom Baker

5 Peter Davison

6 Colin Baker

HandlerSocket python client库

1.安装python handlersocket库

https://bitbucket.org/excieve/pyhs

yum -y install mercurial

hg clone http://bitbucket.org/excieve/pyhs

cd pyhs

sed -i '4a ext_errors = (CCompilerError, DistutilsExecError,DistutilsPlatformError)' setup.py

python setup.py install

注意:ext_errors至今仍然有个未定义的bug,https://bitbucket.org/excieve/pyhs/ issue/11/setuppy-error-ext_errors-not-defined

2.创建示例python脚本

#!/bin/env python

from pyhs import Manager

hs = Manager()

data = hs.get('test', 'hs_test', ['id', 'givenname', 'surname'], '5')

print dict(data)

from pyhs.sockets import ReadSocket

hsr = ReadSocket([('inet', '127.0.0.1', 9998)])

r_id = hsr.get_index_id('test', 'hs_test', ['id', 'givenname', 'surname'])

print hsr.find(r_id, '=', ['5'])

print hsr.find(r_id, '=', ['6'])

print hsr.find(r_id, '>=', ['1'],20)

root@jlive:~#python py_hs_read.py

{'givenname': 'Peter', 'surname': 'Davison', 'id': '5'}

[('5', 'Peter', 'Davison')]

[('6', 'Colin', 'Baker')]

[('1', 'William', 'Hartnell'), ('2', 'Patrick', 'Troughton'), ('3', 'John', 'Pertwee'), ('4', 'Tom', 'Baker'), ('5', 'Peter', 'Davison'), ('6', 'Colin', 'Baker')]

HandlerSocket ruby client库

1.安装ruby handlersocket库

gem install handlersocket

2.创建示例ruby脚本

#!/bin/env ruby

require 'rubygems'

require 'handlersocket'

hs = HandlerSocket.new(:host => '127.0.0.1',:port => '9998')

hs.open_index(0,'test','hs_test','PRIMARY','id,givenname,surname')

p hs.execute_single(0,'=',[1])

p hs.execute_single(0,'>',[1],2,2)

p hs.execute_single(0,'>=',[1],20)

提示:也可以直接通过交互式的irb来执行上面语句

root@jlive:~#ruby rb_hs_read.rb

[0, [["1", "William", "Hartnell"]]]

[0, [["4", "Tom", "Baker"], ["5", "Peter", "Davison"]]]

[0, [["1", "William", "Hartnell"], ["2", "Patrick", "Troughton"], ["3", "John", "Pertwee"], ["4", "Tom", "Baker"], ["5", "Peter", "Davison"], ["6", "Colin", "Baker"]]]

HandlerSocket Telnet

root@jlive:~#telnet 127.0.0.1 9998

Trying 127.0.0.1...

Connected to 127.0.0.1.

Escape character is '^]'.

P 0 test hs_test PRIMARY id,givenname,surname

0 1

0 = 1 1

0 3 1 William Hartnell

0 > 1 2 2

0 3 3 John Pertwee 4 Tom Baker

0 >= 1 1 20

0 3 1 William Hartnell 2 Patrick Troughton 3 John Pertwee 4 Tom Baker 5 Peter Davison 6 Colin Baker

telnet> Connection closed.

注意:分隔符为Tab

第一行是定义到数据库的信道连接

P 0 test hs_test PRIMARY id,givenname,surname

P关键字，第二列为确认信道，这里设为0，第三列为数据库，第三列为数据库中的表，第五列为search key,第六列为查询字段

0 = 1 1

0是信道，第二列为操作符(>,>=,<,<=,=),第三列是索引，只能<=第一行中定义的search key数量，因为只定义了一个search key所以这里设为1，第四列为查询第几字段，1表示id,2表givenname,3表示surname

0 3 1 William Hartnell

0状态返回码，0为成功，第二列为查询字段数量，定义连接时定义了3个查询字段，这里返回3，之后的几列就是id,givenname,surname的值

0 > 1 2 2

查询第2字段(givenname)比PRIMARY大的记录，第五字段限制返回记录的数量，这里为2最多返回2条记录，没有第五列则只返回1行记录