MariaDB NoSQL with 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
tcp
tcp6
(jlive)[isfdb]>SHOW PROCESSLIST;
+----+-------------+---------------------+---------------+---------+------+-------------------------------------------+------------------+----------+
| Id |
User
+----+-------------+---------------------+---------------+---------+------+-------------------------------------------+------------------+----------+
|
|
|
|
|
|
|
|
|
| 10 |
system user | connecting host
| 11 |
system user | connecting host
| 12 |
system user | connecting host
| 13 |
system user | connecting host
| 14 |
system user | connecting host
| 15 |
system user | connecting host
| 16 |
system user | connecting host
| 17 |
system user | connecting host
| 21 |
zabbix
| 22 |
jlive
| 23 |
zabbix
| 24 |
zabbix
| 25 |
zabbix
+----+-------------+---------------------+---------------+---------+------+-------------------------------------------+------------------+----------+
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 (
);
INSERT INTO hs_test VALUES
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');
my $pk = 1;
$res
=
$hs->execute_single(0,
'=',
[ "$pk"
],
10,
0);
shift(@$res);
while
(
$res->[0]
)
{
}
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
#!/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 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
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行记录