(originally published 07/08/13)
other handlersocket entries:
part II – handlersocket syntax Perl/PHP
part III – handlersocket examples – Perl
——————————–
There is very little practical documentation on the web regarding all of the functionality behind handlersocket, this series of posts is an attempt to shed a little more light on what you can do with this fantastic protocol, heavy on the syntax of basic and meatier examples.
Why would I want to use HandlerSocket?
HandlerSocket is essentially a protocol to bypass much of the work done by a traditional SQL call, and particular useful for simple queries where the relative percentage of work done is preparing and executing the statement itself is considerable compared to the work and time of retrieving the data. In that case, the work per query for Handlersocket is a fraction of an SQL request. Additionally, the connections are very light in comparison, and so it’s feasible to have many more concurrent connections. Considering these factors, in an extremely high volume environment, largely consisting of simple key lookups, it can help scale load many times.
For a great deal more information on HandlerSocket,
http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
Comparison to prepared statements
Certainly a valid question, prepared statements bypass the most costly part of the process as well, the parsing, which puts the performance in the same ballpark, however there is still open/closing lock/unlock and running the query execution plan to consider, making HandlerSocket somewhat faster. It also consumes quite a bit less memory consumption (particularly if the result set is large), and can handle multiple threads per connection and can serve requests in bulk, resulting in many more simultaneous requests served. Additionally, HandlerSocket give you the flexibility to keep queries dynamically built in your code instead of defining them in a server side prepared statement.
You can find a detailed comparison on performance here,
http://www.mysqlperformanceblog.com/2011/03/16/where-does-handlersocket-really-save-you-time/
Installation
I’m not going to cover installation here, but there are two main ways,
1) download and install separately https://github.com/DeNADev/HandlerSocket-Plugin-for-MySQL
2) it comes with Percona server (Percona server is mysql with many custom enhancements and extras) http://www.percona.com/software/percona-server/
Some parts of the installation document will be relevant for both cases,
https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/installation.en.txt
For it to be accessible through a language (Perl, PHP), you’ll need to download the client for that as well, links below.
Configuration settings (done in My.cnf)
The ports
9999 = read and write
9998 = read
9998 is faster for reads, about 20-30%. Of course it is also secures read only environments.
loose_handlersocket_port = 9998 loose_handlersocket_port_wr = 9999
Authorization
Very simple to use and optional, set plain text passwords for each port.
handlersocket_plain_secret=<secret_for_reads> handlersocket_plain_secret_wr=<secret_for_readwrites>
Ways to access HandlerSocket
* Natively through telnet or nc
* C++ -> libhsclient folder at https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL
* Perl -> perl-Net-HandlerSocket folder at https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL
3rd party
PHP
Java
Python
- http://pypi.python.org/pypi/python-handler-socket
- https://code.launchpad.net/~songofacandy/+junk/pyhandlersocket
Ruby
