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
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.
Link | December 23rd, 2010 at 2:49 pm