Using HandlerSocket Plugin for MySQL with Perl

December 23, 2010

In my last post I installed the HandlerSocket plugin into MariaDB and tested it.  Like the last post these examples are done with Linux CentOS 5.5.

HandlerSocket some with Perl code for the Net:: module group. When you build and install the HandlerSocket plugin it does not build the Perl module. I looked and it is not included in CPAN.

If you have already downloaded (git) and installed the plugin, you can install the Perl module by:

cd perl-Net-HandlerSocket
perl Makefile.PL
make test
make install

Most of the sample apps I’ve found do little more the prove it works.  I found the protocol (API) is very simple.  This makes it fast but may give you some trouble coding for it.  I created test table and my own version of the example code.

 `user_id` int(10) unsigned NOT NULL,
 `user_name` varchar(50),
 `user_email` varchar(255),
 `created` datetime DEFAULT NULL,
 PRIMARY KEY (`user_id`),
 KEY `NAME` (`user_name`)

use strict;
use warnings;
use Net::HandlerSocket;

# 1. establishing a connection
my $args = { host => 'localhost', port => 9998 };
my $hs = new Net::HandlerSocket($args);

# 2. initializing an index so that we can use in main logics.
#    MySQL tables will be opened here (if not opened)
my $res = $hs->open_index(0, 'test', 'user', 'NAME', 'user_name,user_email,created');
 die $hs->get_error() if $res != 0;

# 3. main logic
#    fetching rows by name
$res = $hs->execute_single(0, '=', [ 'foo' ], 1, 0);
 die $hs->get_error() if $res->[0] != 0;

my $row = 1;

while ( $res->[0] )
 $res = $hs->execute_single(0, '=', [ 'foo' ], $row, $row - 1);
 die $hs->get_error() if $res->[0] != 0;

# 4. closing the connection

What I found is, although you can return more then one record, there is no way to know how many records are returned.  The “Getting Data” section of the HandlerSocket protocol documentation describes how this is done.

  • <indexid> is a number. This number must be an specified by a ‘open_index’ request executed previously on the same connection.
  • <op> specifies the comparison operation to use. The current version of HandlerSocket supports ‘=’, ‘>’, ‘>=’, ‘<’, and ‘<=’.
  • <vlen> indicates the length of the trailing parameters <v1> … . This must be smaller than or equal to the number of index columns specified by specified by the corresponding ‘open_index’ request.
  • <v1> … specify the index column values to fetch.
  • <limit> and <offset> are numbers. These parameters can be omitted. When omitted, it works as if 1 and 0 are specified.

I connected to HandlerSocket and tested the query protocol.  The user ‘foo’ has three records with different email addresses of  test, foo and bar.

telnet localhost 9998
Connected to localhost.localdomain (
Escape character is '^]'.
P       0       test    user    NAME    user_name,user_email
0       1
0       =       1       foo
0       2       foo
0       =       1       foo    10
0       2       foo     foo     foo
0       =       1       foo    1       0
0       2       foo
      =       1       foo    2       1
0       2       foo
0       =       1       foo    3       2
0       2       foo

You see (in blue) where I requested up to 10 records and all thee emails where returned.  In my example program, I first tried using this and found it hard to loop through the records until I reached the end. (It can be done). I then changed the program to request one record at a time until I receive no result.  This creates more network traffic but simplified the code. I’m interested in how the two methods effect performance.

Retrieving multiple records has another issue. In the configuration options documentation, it describes a “maximum socket send buffer” and a “maximum socket receive buffer”.  I’m  worried the number of records a query may return could exceed this buffer.  The documentation doesn’t explain.

How the configuration options effect the applications and performance should also be explored.


It has bee show HandlerSocket is very fast. It is understandable why given the simplicity of both the API and the code itself.  I expect there will be a lot of talk about including this type of capability into MySQL now that it has been show what it can do.  Although it has some faults, I hope the improvements don’t bloat the code and make it slow.


tags: , , ,
posted in Commentary, HOW TO by mark

Follow comments via the RSS Feed | Leave a comment | Trackback URL

1 Comment to "Using HandlerSocket Plugin for MySQL with Perl"

  1. Justin Swanhart wrote:

    Returning the number of records in the resultset would be difficult, since it is simply reading from an index.

    It also needs atomic operations to be really useful, and it is missing those.

    It would be more convenient if a ‘start of row’ or ‘end of row’ marker was sent in the stream.

Leave Your Comment


Powered by Wordpress and MySQL. Theme by Shlomi Noach,
Creative Commons License
MySQL Fan Boy by Mark Grennan is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.