MYSQLHandlerSocket插件（四）安裝與使用

MYSQLHandlerSocket插件（四）安裝與使用

一、安裝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/plugin

vi /etc/my.cnf
[mysqld]
plugin-load=handlersocket.so（plugin-load可略過不配）
loose_handlersocket_port = 9998 # 指定讀請求端口號
# the port number to bind to (for 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 many concurren connections, make open_files_limit as large as possible.

handlersocket_timeout = 3600

# 保持連接空間時間，默認一個連接空閒5分鐘就會被釋放


#mysql.server restart
#mysql
mysql>show plugins;
| handlersocket | ACTIVE | DAEMON | handlersocket.so | BSD |

# netstat -an | grep 9998
tcp 0 0 0.0.0.0:9998 0.0.0.0:* LISTEN

二、安裝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.ini
extension=handlersocket.so

#php -m
handlersocket

三、測試

-- 用戶表
drop table IF EXISTS user;
create table user (
userid char(8) binary PRIMARY key,
username varchar(32) comment '用戶昵稱',
feature char(16) comment '功能',
siminfo char(15) comment 'SIM信息IMSI',
msisdn char(13) comment '用戶手機號碼',
countyrcode char(3) comment '國家碼',
zgtflag char(1) comment '中港通標志YN',
usertype char(16) comment '用戶類型',
operatorid char(32) comment '操作工號',
createtime TIMESTAMP comment '創建時間',
PRIMARY KEY (msisdn),
unique index useridx(userid)
);

-- 用戶帳戶表
drop table IF EXISTS useracnt;
create table useracnt (
userid char(8) binary PRIMARY key ,
activetime TIMESTAMP comment '激活時間',
amount INT UNSIGNED comment '帳戶金額(分)',
validdate date comment '有效期',
status char(1) comment '帳戶狀態',
nextkfdate date comment '下次扣費時間'
);

<?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核CPU，Percona MySQL 5.5.18，InnoDB_buffer_size=16G，read/wrige thread各16

經測試，使用handlersocket比直接用sql大概只能提高0.5倍左右，測試數據均在innodb buffer中

以前MySQL5.1時，HandlerSocket性能會有很大優勢，MySQL5.5性能有提高，優勢不明顯