Install and use nosql for MySQL [handlersocket]

I like MySQL not only because of its simple use, but also because of its open source, plug-in engine, and better plug-in! From queue-type storage engine q4m (http://q4m.github.com/) to memcache UDF (http://hi.baidu.com/ytjwt/blog/item/5fc8303f226c542f71cf6c3c.html)
Nosql
MySQL, one day last year, a friend told me how good nosql performance is. I said that if I extract data like k/V-shaped data, suppose that I use a primary key to query a data, what do you think is the performance?
What about it? In fact, I also knew that it was definitely better than memcache, because MySQL has a series of certifications, a series of syntax and lexical analysis!

Some time ago, when percona-server was downloaded, he suddenly found a device named handlersocket. He was curious and Google, I found this is not what I always wanted to implement nosql in MySQL? I am so excited that I have installed and used it. Next I will list my steps!

Main reference: http://whitesock.javaeye.com/blog/811339

1: Installation

Handlersocket is easy to use. Only Source and binary Versions later than 5.1 are required. Because 5.5ga is used, I chose 5.5.8 for installation.

Install handlersocket

$./Autogen. Sh
$./Configure
-- With-mysql-source = ../mysql-5.5.8/
-- With-mysql-bindir =/usr/local/mysql55/bin/
-- With-mysql-plugindir =/usr/local/mysql55/lib/plugin/

$ Make
$ Make install

2: Configuration

Before official use, we must add the following configuration in the MySQL configuration file:

[Mysqld]
Loose_handlersocket_port = 9998
# The port number to bind to (Read requests)
Loose_handlersocket_port_wr = 9999
# The port number to bind to (for write requests)
Loose_handlersocket_threads = 16
# The number of worker threads (for read requests)
Loose_handlersocket_threads_wr = 1
# The number of worker threads (for write requests)
Open_files_limit = 65535
# To allow handlersocket accept implements concurrent connections, make open_files_limit as large as possible.

After restarting MySQL, log on to MySQL and run

Install plugin handlersocket soname 'handlersocket. so ';

Now we can see two new ports.

Netstat-LNP | grep 999
TCP 0 0 0.0.0.0: 9998 0.0.0.0: * Listen 32010/mysqld
TCP 0 0 0.0.0.0: 9999 0.0.0.0: * Listen 32010/mysqld

Mysql> show plugins;

| Handlersocket | active | daemon | handlersocket. So | BSD |
+ ----------------------- + ---------- + ---------------------- + ---------------- + --------- +
21 rows in SET (0.00 Sec)

Mysql> show processlist;
+ ---- + ------------- + ----------------- + --------------- + --------- + ------ + ----------------------------------------- + ------------------ +
|
Id | user | host | dB | command | time |
State | info |
+ ---- + ------------- + ----------------- + --------------- + --------- + ------ + ----------------------------------------- + ------------------ +
|
2 | system user | connecting host | null | connect | null |
Handlersocket: mode = RD, 0 Conns, 0 active | null |
| 3 | system user | connecting host | null | connect | null | handlersocket: mode = RD, 0 Conns, 0 active | null

In this way, it indicates that it is in normal use!

3: Use

I use Perl to test handlersocket and use online tables (^-^ convenient)

Create Table 'user '(
'User _ id' int (10) unsigned not null,
'User _ name' varchar (50) default null,
'User _ email 'varchar (255) default null,
'Created 'datetime default null,
Primary Key ('user _ id '),
Key 'index _ 01' ('user _ name ')
) Engine = InnoDB;

Manually insert several statements insert into user values (4, "yangting", "D@test.com", current_timestamp );

1) Select

I use handlersocket to query the table as follows:

#! /Usr/bin/perl
Use strict;
Use warnings;
Use Net: handlersocket;

#1. Establishing a connection
My $ ARGs = {Host => 'localhost', Port => 9998 };
My $ HS = new net: handlersocket ($ ARGs );

#2. Initializing an index so that we can use in main logics.
# MySQL tables will be opened here (if not opened)
My $ res = $ HS-> open_index (0, 'test', 'user', 'Primary ', 'user _ name, user_email, created ');

# This is the number of columns to query. Three columns are listed here: user_name, user_email, and created.
Die $ HS-> get_error () If $ res! = 0;
#3. Main Logic
# Fetching rows by ID
# Execute_single (index ID, Cond, Cond value, Max rows, offset)

# The following statement indicates that the primary key is greater than or equal to 1 and the total number of rows is two.
$ Res = $ HS-> execute_single (0, '> =', [1], 2, 0 );

# Note: The returned value type of the execute_single method is arrayref, and the first element of the method is error code. If it is 0, it is normal. Otherwise, it is abnormal, the array is the returned value starting from the second element. The storage format is that the next row follows the previous row!

Die $ HS-> get_error () If $ res-> [0]! = 0;
Shift (@ $ res );

# Print all values of this array for the Branch

For (my $ ROW = 0; $ row <8; ++ $ row ){
My $ user_name = $ res-> [$ row * 3 + 0];
My $ user_email = $ res-> [$ row * 3 + 1];
My $ created = $ res-> [$ row * 3 + 2];
If ($ user_name | $ user_email | $ created)
{
Print "$ user_name \ t $ user_email \ t $ created \ n ";
}
Else
{Last;
}

}
#4. Closing the connection
$ HS-> close ();

2) Insert

My $ ARGs = {Host => 'localhost', Port => 9999 };
My $ HS = new net: handlersocket ($ ARGs );

My $ res = $ HS-> open_index (3, 'test', 'user', 'Primary ', 'user _ id, user_name, user_email, created ');
Die $ HS-> get_error () If $ res! = 0;
# Insert
$ Res = $ HS-> execute_single (3, '+', [5, 'zhongguo', 'zhogonguo @ email.com ', '2017-01-08 13:51:33'], 1, 0 );
Die $ HS-> get_error () If $ res-> [0]! = 0;
$ HS-> close ();

3) Update

My $ ARGs = {Host => 'localhost', Port => 9999 };
My $ HS = new net: handlersocket ($ ARGs );

My $ res = $ HS-> open_index (3, 'test', 'user', 'Primary ', 'user _ name ');
Die $ HS-> get_error () If $ res! = 0;

# When user_id = 5, update 'user _ name' to woaini

$ Res = $ HS-> execute_single (3, '=', [5], 1, 0, 'U', ['woaini ']);

Die $ HS-> get_error () If $ res-> [0]! = 0;

$ HS-> close ();

4) Delete

My $ ARGs = {Host => 'localhost', Port => 9999 };
My $ HS = new net: handlersocket ($ ARGs );
My $ res = $ HS-> open_index (3, 'test', 'user', 'Primary ', 'user _ name ');
Die $ HS-> get_error () If $ res! = 0;

# Delete user_id = 4 Data

$ Res = $ HS-> execute_single (3, '=', [4], 1, 0, 'D ');
Print $ res;
Die $ HS-> get_error () If $ res! = 0;
$ HS-> close ();

Here I only pay attention to the usage. For performance and time, I still need to test it. However, the author of handlersocket will test it himself.

Reference: http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html