MYSQLHandlerSocket plug-in (4) Installation and Use

MYSQLHandlerSocket plug-in (4) Installation and Use

1. Install HandlerSocket-Plugin-for-MySQL

https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL#cd HandlerSocket-Plugin-for-MySQL # sh. /autogen. sh #. /configure -- with-mysql-source =/home/jfy/soft/Percona-Server-5.5.18-rel23.0 -- with-mysql-bindir =/usr/local/mysql/bin -- with-mysql-plugindir =/ usr/local/mysql/lib/pluginvi/etc/my. cnf [mysqld] plugin-load = handlersocket. so (plugin-load can be skipped) loose_handlersocket_port = 9998 # specify the read Request port number # the port number to bind to (for read requests) loose_handlersocket_port_wr = 9999 # specify write Request port # the port number to bind to (for write requests) loose_handlersocket_threads = 16 # specify the number of read threads # the number of worker threads (for read requests) threads = 1 # specify the number of write threads # the number of worker threads (for write requests) open_files_limit = 65535 # to allow handlersocket accept handle concurren connections, make open_files_limit as large as possible. handlersocket_timeout = 3600 # Keep the connection space. By default, a connection will be released after being idle for 5 minutes # mysql. server restart # mysqlmysql> show plugins; | handlersocket | ACTIVE | DAEMON | handlersocket. so | BSD | # netstat-an | grep 9998tcp 0 0 0.0.0.0: 9998 0.0.0.0: * LISTEN

Ii. install php-ext-handlersocketi

https://github.com/piteer1/php-handlersocket#cd php-handlersocket-master#/usr/local/php/bin/phpize#./configure --with-php-config=/usr/local/php/bin/php-config#make & make install#vi /usr/local/php/lib/php.iniextension=handlersocket.so#php -mhandlersocket

Iii. Test

-- User table drop table if exists user; create table user (userid char (8) binary PRIMARY key, username varchar (32) comment 'user nicknames ', feature char (16) comment 'function', siminfo char (15) comment 'sim info imsi', msisdn char (13) comment 'user mobile phone number', countyrcode char (3) comment 'country Code ', zgtflag char (1) comment 'Hong Kong Connect flag', usertype char (16) comment 'user type', operatorid char (32) comment 'operator number ', createtime TIMESTAMP comment 'creation time', primary key (msisdn), unique index useridx (userid); -- User Account table drop table if exists useracnt; create table useracnt (userid char (8) binary PRIMARY key, activetime TIMESTAMP comment 'activation time', amount int unsigned comment' account amount (points) ', validdate date comment 'expires ', status char (1) comment 'account status', nextkfdate date comment 'Next deduction Time ');

<?php$host = 'localhost';$port = 9998;$port_wr = 9999;$dbname = 'voip';$table1 = 'user';$table2 = 'useracnt';try{    $hs_read = new HandlerSocket($host, $port);    $idx_user_r = $hs_read->createIndex(1, $dbname, $table1, 'PRIMARY', array('userid','zgtflag'));    $hs_write = new HandlerSocket($host, $port_wr);    $idx_useracnt_w = $hs_write->createIndex(2, $dbname, $table2, 'PRIMARY', 'amount');}catch (HandlerSocketException $exception){    var_dump($exception->getMessage());    die();}//GET$retval = $idx_user_r->find('85265101177');list($userid,$zgtflag) = $retval[0];echo $userid . "\n";echo $zgtflag . "\n";//UPDATE$amount=round(0.95*100);$ret = $idx_useracnt_w->update($userid, array('-?' => $amount));var_dump($ret);if ( $ret === false){    echo __LINE__, ':', $index->getError(), ':', PHP_EOL;    die();}if ( !is_array($ret) ) {    echo "update not found!\n";    die();}if ( intval($ret[0][0]) < $amount ) {    echo "balance is not enough!\n";    die();}echo "update ok!\n";unset($index);unset($hs);exit;

12-core CPU, Percona MySQL 5.5.18, InnoDB_buffer_size = 16G, read/wrige thread each 16

According to tests, handlersocket can only be improved by about 0.5 times over SQL. the test data is in innodb buffer.

In MySQL5.1 in the past, HandlerSocket performance had a great advantage, and MySQL5.5 performance had improved, but its advantage was not obvious.