初识mysql插件之HandlerSocket

HandlerSocket是日本人写的一个mysql插件，具体是谁上网搜吧
通过这个插件，你可以直接跟MySql后端的存储引擎做key-value式的交互，
省去了MySql上层的SQL解释、打开关闭表、创建查询计划等CPU消耗型的开销，
按照作者给出的数据可以在数据全部在内存的情况下可以达到75W的QPS查询。
具体信息可以看这篇Blog，中文介绍可以看这篇文章《HandlerSocket in action》。
这个东西为什么让我很激动呢？首先性能是程序员的G点，
一听高性能你不由地激动，其次，这也解决了缓存跟数据库的一致性问题，
因为缓存就在数据库里面，第三，这个东西不仅仅是NoSQL，
简单的CRUD你可以通过HandlerSocket，但是复杂的查询你仍然可以走MySql，
完全符合我们应用的场景，
并且从实际测试来看，性能确实非常优秀。但是呢，这个东西的代价也少不了，
例如没有权限检查（未来可能添加）；不能启用MySql的查询缓存，否则会导致数据的不一致；
协议设计也不合理，使用\t做分隔符，使用\n做换行符，
那么你插入或者更新的字段数据就不能含有这些字符，
否则行为将不如预期。

原文：http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
相关的日文PPT：http://www.slideshare.net/akirahiguchi/handlersocket-plugin-for-mysql-4664154

相关新能测试请参考：http://www.cnblogs.com/inrie/archive/2011/02/22/1961415.html

下载地址：https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

安装方式：
下载 ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-73-g0e63366.tar.gz https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/tarball/master
[root@niutian365 tmp]# tar -zxvf ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-73-g0e63366.tar.gz
[root@niutian365 tmp]# cd ahiguti-HandlerSocket-Plugin-for-MySQL-0e63366
[root@niutian365 tmp]# ./autogen.sh
[root@niutian365 tmp]# ./configure --with-mysql-source=/niutian365/softs/mysql-5.1.54 --with-mysql-bindir=/usr/local/mysql/bin  --with-mysql-plugindir=/usr/local/mysql/lib/mysql/plugin
##注意这里我的mysql版本是mysql-5.1.54 已经安装过 不详述mysql安装 源码目录是/niutian365/softs/mysql-5.1.54
[root@niutian365 tmp]# make
[root@niutian365 tmp]# make install

[root@niutian365 tmp]# vi /etc/my.cnf

[mysqld]
......
#下面添加
loose_handlersocket_port = 9998
loose_handlersocket_port_wr = 9999
loose_handlersocket_threads = 16
loose_handlersocket_threads_wr = 1
open_files_limit = 65535
保存退出
重启mysql
[root@niutian365 tmp]# mysql restart
登录mysql
[root@niutian365 tmp]# mysql -uroot -pniutian
mysql> install plugin handlersocket soname 'handlersocket.so';
mysql> show processlist;
     +----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
| Id | User        | Host            | db            | Command | Time | State                                     | Info             |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
|  4 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  5 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  6 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  7 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  8 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  9 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 10 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 11 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 12 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 13 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 14 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 15 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 16 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 17 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 18 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 19 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 20 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL             |
| 21 | root        | localhost       | NULL          | Query   |    0 | NULL                                      | show processlist |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
18 rows in set (0.00 sec)
mysql> show plugins;
       +---------------+--------+----------------+------------------+---------+
| Name          | Status | Type           | Library          | License |
+---------------+--------+----------------+------------------+---------+
| binlog        | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| CSV           | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| FEDERATED     | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| MEMORY        | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| InnoDB        | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| MyISAM        | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| MRG_MYISAM    | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| handlersocket | ACTIVE | DAEMON         | handlersocket.so | BSD     |
+---------------+--------+----------------+------------------+---------+
8 rows in set (0.00 sec)
[root@niutian365 tmp]# mysql restart

评论

2 楼 xiaoyi829 2013-01-07  

应该可以

grzrt 写道

分区表partition，能用handlersocket查询指定的某一个分区吗？

我没试过，理伦上应该可以

1 楼 grzrt 2012-04-25  

分区表partition，能用handlersocket查询指定的某一个分区吗？