handlersocket needs indexes to find results and like mysql, a multi-column index requires the leftmost prefix and index. Again, handlesocket does not do table scans, and so without it, you will not get any results at all.

if you already have Handlersocket via Percona Server installed, in order to get HandlerSocket.pm, you may still need to download the separate installation, then simply go to the perl-Net-HandlerSocket directory within it and build the perl module per the instruction within it.

What perl-Net-HandlerSocket can do:

Like the native functionality it can,

Select / insert /update/ delete on primary and secondary keys
Filter on primary and secondary keys
‘In’ type functionality
queries with more than one parameter

It will test both passwords and give appropriate permissions to the password that matches

Open an index

HandlerSocket completely relies on the indexes to get data, it can not get a record without one. (although it can certainly get the non-indexed data within the row you retrieve using the index). You must open at least one index for handlersocket to operate on to do a query.

perl

$res = $hs->open_index(a, 'b', 'c', 'd', ‘e1,e2,e3…’, 'f1,f2,f3…');

describing each position in the set above,

a – the index id, this can be any number, you’ll use it to differentiate all of your open indexes in your calls. Using the same number for a different index will overwrite the former opened index.b – the database namec – the table named – the name (defined in your table) of the index you are usinge(1,2,3…) – the columns you are using in the index. If it’s a multiple index, like mysql you must include the left most columns of the index (i.e. to use col2, you can not leave out col1 and use col2, you must include both).f(1,2,3…) – the columns for which you want data returned.

php

$hs->openIndex(a,’b’ , ‘c’, ‘d’, 'f1,f2,f3…')

exactly the same as above EXCEPT no defining partial indexes (no ‘e1, e2,…’)

Not all are necessarily relevant and depends on the operation you are doing.

a – index idb – the operation one of (>,=,!=, +)c – the value for the index to query on OR the values for an insertd – the limit of rows operated on like limit in mysqle – the offset of the result like offset in mysqlf – if a delete or update then (‘D’, ‘U’) otherwise undefg(1,2,3..) – if update, an array of values to use for the updateh – an array with filter valuesh1 – ‘F’ (filter those values for =, !=), ‘W’ (stop once value is hit such as =)h2 – filter operation (>,=,!=)h3 – index id for the filterh4 – value to filter oni – key for IN statementj(1,2,3..) – values for IN statement

the call itself can be execute_single or execute_multi. The latter will feed it as an array of the series above, there are a couple of examples coming up to demonstrate.
I’ve found many positions not requiring a value may still need a placeholder, where an empty ‘’ will not work for some of them. Use undef in this case.

php

Php is a little more flexible, you can use a similar executeSingle function for everything or use specific functions for insert, update, delete

a) execute_multi is supposed to be much faster for many statements than looping through execute_single.

b) Contrary to the documentation, I’ve found if you do not include definitions for the limit and offset, it will error. Using ‘undef’ for each place will work, but throw a ‘Use of uninitialized value in subroutine entry at handlersockettest.pl’ warning.

c) DO NOT leave spaces on either side of the commas in ‘open index’ call for (e) and (f) – it will error. I assume because these are multiple value and each is not enclosed by quotes has something to do with it.

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.

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.

about me

My name is Michael Morse, I’m the principal database & performance architect for Upsight Inc., the world’s largest independent analytics and mediation provider. I work primarily with our mediation platform architecting and managing our infrastructure with tools and technologies such as Percona server/toolkit, memcached, redis, galera and Mariadb columnstore to store and serve our data in a reliable and performant manner.