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
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.

CREATE TABLE `user` (
 `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`)
 ) ENGINE=InnoDB

#!/usr/bin/perl

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;
shift(@$res);

my $row = 1;

while ( $res->[0] )
{
 printf("%s\t%s\t%s\n",$res->[0],$res->[1],$res->[2]);
 $row++;
 $res = $hs->execute_single(0, '=', [ 'foo' ], $row, $row - 1);
 die $hs->get_error() if $res->[0] != 0;
 shift(@$res);
}

# 4. closing the connection
$hs->close();

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
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.
P       0       test    user    NAME    user_name,user_email
0       1
0       =       1       foo
0       2       foo    foo@www.mysqlfanboy.com
0       =       1       foo    10
0       2       foo    foo@www.mysqlfanboy.com     foo    bar@www.mysqlfanboy.com     foo     test@www.mysqlfanboy.com
0       =       1       foo    1       0
0       2       foo    foo@www.mysqlfanboy.com
0       =       1       foo    2       1
0       2       foo    bar@www.mysqlfanboy.com
0       =       1       foo    3       2
0       2       foo    test@www.mysqlfanboy.com

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.

Conclusion:

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.

Tweet