First, what is Handlersocket?
Handlersocket is a MySQL plugin written by Akira Higuchi. In the form of MySQL Daemon plugin to provide similar NoSQL network services, through this plug-in, you can directly with the MySQL back-end storage engine to do key-value interaction, eliminating the MySQL upper layer of SQL interpretation, open the closed table, Create a CPU overhead, such as a query plan.
Most of the Web sites currently using MySQL use Memcache as the key-value cache. Although such a structure is extremely popular, there are many successful cases, but too dependent on memcache, virtually let memcache become the root cause of failure:
Memcache Data Consistency problem: When the MySQL data changes, if not timely and effectively clean out the outdated data, will cause data inconsistency. This is not desirable in the Web2.0 era, which emphasizes immediacy.
The avalanche effect after the collapse of Memcache: As cached memcache crashes, MySQL is likely to withstand high load and downtime in a short time.
Note: On the issue of cleaning up expired data, you can find a way to do it in the program architecture, if the data operation has unified DAO package, you can use observer mode to clean up outdated data, not subject content, data self-examination.
Faced with the above problems, Handlersocket project is a good solution, it through plug-ins to give MySQL complete NoSQL function, from the principle, it skips the most time-consuming in MySQL syntax parsing, query plan, and other steps, directly read the data, if the memory is large enough to be able to load the index, MySQL query efficiency can be increased several times!
Handlersocket:https://github.com/ahiguti/handlersocket-plugin-for-mysql
php-handlersocket:http://code.google.com/p/php-handlersocket/
System Information Conventions:
System version: CentOS 6.3 X86
PHP installation directory:/usr/local/webserver/php5318
MySQL installation directory:/usr/local/webserver/mysql5520
Handlersocket Installation path:/usr/local/webserver/handlersocket
Second, install the configuration Handlersocket
Prior to installation, it is recommended that you install the relevant support and component packages as needed:
Copy Code code as follows:
Yum install gcc gcc-c++ libtool make openssl-devel perl-dbi perl-dbd-mysql
Yum install rpm-build gperf readline-devel ncurses-devel time Perl-time-hires
1. Installation
If you use Percona server version of MySQL is simple, because it has built-in handlersocket support, but in view of its built-in version is not new, there are some already fixed bugs, so it is best to use source code compilation. Handlersocket is based on the MySQL database, so before installing handersocket, you need to deploy the MySQL service in the usual way, and you need to pay attention to the source of MySQL when Handlersocket. therefore also need MySQL source code compilation method installs.
Copy Code code as follows:
[Root@iredmail opt]# git clone https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL.git
[Root@iredmail opt]# CD Handlersocket-plugin-for-mysql
[Root@iredmail handlersocket-plugin-for-mysql]#./autogen.sh
[Root@iredmail handlersocket-plugin-for-mysql]#./configure--prefix=/usr/local/webserver/handlersocket-- with-mysql-source=/opt/mysql-5.5.20--with-mysql-bindir=/usr/local/webserver/mysql5520/bin-- With-mysql-plugindir=/usr/local/webserver/mysql5520/lib/mysql/plugin
Tips:
--with-mysql-source Specify MySQL source directory
--with-mysql-bindir represents the MySQL binary executable directory
--with-mysql-plugindir Specify the storage path for the MySQL plug-in, if you do not know where the directory is, you can query the following methods:
Copy Code code as follows:
Mysql> Show variables like ' plugin% ';
+---------------+-------------------------------------------+
| variable_name | Value |
+---------------+-------------------------------------------+
| Plugin_dir | /usr/local/webserver/mysql5520/lib/plugin |
+---------------+-------------------------------------------+
1 row in Set (0.00 sec)
[Root@iredmail handlersocket-plugin-for-mysql]# make
Common errors:
Copy Code code as follows:
Libtool:link:only Absolute run-paths are allowed
MAKE[2]: * * * [handlersocket.la] Error 1
MAKE[2]: Leaving directory '/opt/handlersocket-plugin-for-mysql/handlersocket '
MAKE[1]: * * * [all-recursive] Error 1
MAKE[1]: Leaving directory '/opt/handlersocket-plugin-for-mysql '
Make: * * * [ALL] Error 2
Workaround:
[Root@iredmail handlersocket-plugin-for-mysql]# VI handlersocket/makefile
Line 301:
$ (Handlersocket_la_link)-rpath $ (pkgplugindir) $ (handlersocket_la_objects) $ (handlersocket_la_libadd) $ (LIBS)
-->
$ (Handlersocket_la_link)-rpath/opt/handlersocket-plugin-for-mysql/handlersocket $ (Handlersocket_la_OBJECTS) $ ( Handlersocket_la_libadd) $ (LIBS)
[Root@iredmail Handlersocket-plugin-for-mysql] #make Install
After completion, the Mysql-plugindir directory should have handlersocket related documents
2. Configure MySQL:
To modify the MY.CNF configuration file:
Copy Code code as follows:
[Root@iredmail handlersocket-plugin-for-mysql]# vi/etc/my.cnf
[Mysqld]
Plugin-load=handlersocket.so (Plugin-load may be slightly unworthy)
Loose_handlersocket_port = 9998 # Specifies the read request port number
# The port number to bind to (for read requests)
LOOSE_HANDLERSOCKET_PORT_WR = 9999 # Specifies the write request port number
# The port number to bind to (for write requests)
Loose_handlersocket_threads = 16 # Specifies the number of read threads
# The number of worker threads (for read requests)
LOOSE_HANDLERSOCKET_THREADS_WR = 1 # Specifies the number of write threads
# The number of worker threads (for write requests)
Open_files_limit = 65535
# to allow Handlersocket accept many concurren connections, make open_files_limit as large as possible.
Tips: InnoDB's innodb_buffer_pool_size, or MyISAM's key_buffy_size, and so on, are related to caching index options as large as possible, so that the potential of the handlersocket can be played.
Login to MySQL and activate the Handlersocket plugin:
Copy Code code as follows:
[Root@iredmail handlersocket-plugin-for-mysql]# Mysql-uroot-p
mysql> Install plugin handlersocket soname ' handlersocket.so ';
ERROR 1126 (HY000): Can ' t open shared library '/usr/local/webserver/mysql5520/lib/plugin/handlersocket.so ' (errno:2 Cannot open shared object File:no such file or directory)
Note: This tip does not find the handlersocket.so extension file, please see if the extension file exists.
mysql> Install plugin handlersocket soname ' handlersocket.so ';
Query OK, 0 rows Affected (0.00 sec)
Mysql> quit;
At this point, the Handlersocket plug-in installation is complete.
Restart the MySQL service:
Copy Code code as follows:
[Root@iredmail handlersocket-plugin-for-mysql]# Service mysqld Restart
3, Handlersocket State test:
You can also confirm that the installation was successful by querying whether the port that you just configured is already occupied by MySQL:
Copy Code code as follows:
[Root@iredmail handlersocket-plugin-for-mysql]# lsof-i-P | grep mysqld
Mysqld 26871 MySQL 11u IPv4 72467 0t0 TCP *:9998 (LISTEN)
Mysqld 26871 mysql 29u IPv4 72469 0t0 TCP *:9999 (LISTEN)
Mysqld 26871 mysql 31u IPv4 72474 0t0 TCP *:3306 (LISTEN)
Tips:if ports 9998 and 9999 don ' t show up. Make sure SELinux are not running.
Third, install the Configuration Php-handlersocket expansion module:
1, Installation Php-handlersocket expansion
Copy Code code as follows:
[Root@iredmail opt]# wget http://php-handlersocket.googlecode.com/files/php-handlersocket-0.3.1.tar.gz
[Root@iredmail opt]# TAR-ZXVF php-handlersocket-0.3.1.tar.gz
[Root@iredmail opt]# CD handlersocket/
[Root@iredmail handlersocket]#/usr/local/webserver/php5318/bin/phpize
[Root@iredmail handlersocket]#./configure--with-php-config=/usr/local/webserver/php5318/bin/php-config
./configure can be added parameters:
Tips:if You get error:
Configure:error:Can ' t find hsclient headers,please install libhsclient first,or./ Configure--disable-handlersocket-hsclient--with-php-config=/usr/local/webserver/php5318/bin/php-config Use Native type.
Copy Code code as follows:
[Root@iredmail Handlersocket] #make && make install
A successful install would have created handlersocket.so and put it into the PHP extensions directory. You'll need to and adjust php.ini and add a extension=handlersocket.so line before your can use the extension.
Copy Code code as follows:
[Root@iredmail handlersocket]# Vi/usr/local/webserver/php5318/etc/php.ini
Extension=handlersocket.so
Now that the PHP extension installation is complete, let's ask the Php.info page and we can see that the handlersocket extension has been successfully loaded
2, Php-handlersocket use examples:
Copy Code code as follows:
/*
* String $host: MySQL IP;
* String $port: The Handlersocket plug-in's listening port, which has two ports to choose from: one for reading, one for writing
*/
$HS = new Handlersocket ($host, $port);
Open a data table:
/*
* Int $index: This number corresponds to the handle in the file operation, and all other methods of Handlersocket are based on this number to manipulate the table opened by this openindex,
* String $dbname: library name
* String $table: Table name
* String $key: A table's primary key (Handlersocket::P rimary) or "index name" as the Search key field, which means that the table must have a primary key or index
* Personal Understanding: The key field to be used as the where condition, so you can think that Handlersocket has only one where condition
* String $column: The fields of the table opened by ' Column1,column2 ' (separated by commas), which means that the other fields of the $table table are not manipulated
*/
$hs->openindex ($index, $dbname, $table, $key, $column);
Inquire:
/*
* Int $index: $index for Openindex ()
* String $operation: the operator used in the $key field specified in the Openindex method, currently supports ' = ', ' >= ', ' < = ', ' > ', and ' < '; can be understood as where condition
* Array $value
* Int $number (default is 1): Gets the maximum number of bars for the result; the second parameter of limit in SQL
* Int $skip (default is 0): Skip past several; equivalent to the first parameter of limit in SQL
*/
$retval = $hs->executesingle ($index, $operation, $value, $number, $skip);
Insert (note: The openindex here is to use $PORT_WR, read-write port):
/*
* Int $index: $index for Openindex ()
* Array $arr: The number of number elements is the same as Openindex $column
*/
$retval = $hs->executeinsert ($index, $arr);
Delete (Note: The openindex here is to use $PORT_WR, read-write port):
/*
* Int $index: $index for Openindex ()
* String $operation: the operator used in the $key field specified in the Openindex method, currently supports ' = ', ' >= ', ' < = ', ' > ', and ' < '; can be understood as where condition
* Array $value
* Int $number (default is 1): Gets the maximum number of bars for the result; the second parameter of limit in SQL
* Int $skip (default is 0): Skip past several; equivalent to the first parameter of limit in SQL
*/
$retval = $hs->executedelete ($index, $operation, $value, $number, $skip);
Update (note: The openindex here is to use $PORT_WR, read-write port):
/*
* Int $index: $index for Openindex ()
* String $operation: the operator used in the $key field specified in the Openindex method, currently supports ' = ', ' >= ', ' < = ', ' > ', and ' < '; can be understood as where condition
* Array $value
* Int $number (default is 1): Gets the maximum number of bars for the result; the second parameter of limit in SQL
* Int $skip (default is 0): Skip past several; equivalent to the first parameter of limit in SQL
*/
$retval = $hs->executeupdate ($index, $operation, $value, $number, $skip);
Example:
Test library hstestdb, test table hstesttbl:
Copy Code code as follows:
CREATE TABLE ' Hstesttbl ' (
' id ' int (one) not NULL auto_increment,
' K ' char (6) DEFAULT NULL,
' V ' char (6) DEFAULT NULL,
PRIMARY KEY (' id '),
KEY ' Idx_hstesttbl_k ' (' K ')
) Engine=innodb auto_increment=7 DEFAULT Charset=utf8;
PHP Test Code:
Copy Code code as follows:
$host = ' localhost ';
$port = 9998;
$port _WR = 9999;
$dbname = ' Hstestdb ';
$table = ' hstesttbl ';
Get
$HS = new Handlersocket ($host, $port);
if (!) ( $hs->openindex (1, $dbname, $table, Handlersocket::P rimary, ' k,v ')) {
echo $hs->geterror (), Php_eol;
Die ();
}
$retval = $hs->executesingle (1, ' = ', Array (' K1 '), 1, 0);
Var_dump ($retval);
$retval = $hs->executemulti (
Array
Array (1, ' = ', Array (' K1 '), 1, 0),
Array (1, ' = ', Array (' K2 '), 1, 0)
)
);
Var_dump ($retval);
Unset ($HS);
UPDATE
$HS = new Handlersocket ($host, $port _WR);
if (!) ( $hs->openindex (2, $dbname, $table, ', ' V ')) {
echo $hs->geterror (), Php_eol;
Die ();
}
if ($hs->executeupdate (2, ' = ', Array (' K1 '), Array (' V1 '), 1, 0) = = False) {
echo $hs->geterror (), Php_eol;
Die ();
}
Unset ($HS);
INSERT
$HS = new Handlersocket ($host, $port _WR);
if (!) ( $hs->openindex (3, $dbname, $table, ', ' k,v ')) {
echo $hs->geterror (), Php_eol;
Die ();
}
if ($hs->executeinsert (3, Array (' K2 ', ' v2 ')) = = False) {
echo $hs->geterror (), Php_eol;
}
if ($hs->executeinsert (3, Array (' K3 ', ' v3 ')) = = False) {
Echo ' A ', $hs->geterror (), Php_eol;
}
if ($hs->executeinsert (3, Array (' K4 ', ' v4 ')) = = False) {
Echo ' B ', $hs->geterror (), Php_eol;
}
Unset ($HS);
DELETE
$HS = new Handlersocket ($host, $port _WR);
if (!) ( $hs->openindex (4, $dbname, $table, ', ')) {
echo $hs->geterror (), Php_eol;
Die ();
}
if ($hs->executedelete (4, ' = ', Array (' K2 ')) = = False) {
echo $hs->geterror (), Php_eol;
Die ();
}
?>
Tips: theoretically handlersocket supports various engines such as MYISAM,INNODB, but it is recommended to use InnoDB.
Tips: To avoid the Insert Error,please remember set storage Engine:innodb.
Tips: A common misconception about handlersocket is that only the primary type of KV query can be implemented, in fact, as long as the support index, the general simple query it can be competent, here is not much to say, the official document has introduced.
Handlersocket's flaws:
(1) Write operation does not eliminate the query cache-if the write operation through Handlersocket, because there is no invalid query cache, then you may read the old data from MySQL;
(2) Automatic increment is not supported-the increment value cannot be automatically obtained from the self-added column when inserting.
In view of the above problems, to avoid weaknesses, using its merged query operation, play its nosql performance to obtain the MySQL InnoDB type table data, the specific operations are as follows:
Copy Code code as follows:
<?php
Get data through Handlersocket
$HS = new Handlersocket (Hs_host, Hs_port);
if (!) ( $hs->openindex (1, ' dbname ', ' table ', Handlersocket::P rimary, ' id,content,create_uid,create_user,created,state ') )){
echo $hs->geterror (), Php_eol;
Die ();
}
$dataList = Array ();
foreach ($ids as $id) {
$dataList [] = Array (1, "=", Array ($id));
}
$data = $hs->executemulti ($dataList);
Written at the end of:
MySQL5.6 provides native memcached API, the actual is KV type NoSQL, but Handlersocket is not limited to KV form, so still have living space.
