November 2013 – Scalemysql

November 12, 2013November 12, 2013

Handlersocket Part III / examples / Perl

(originally posted 07/18/12)

other handlersocket entries:

part I – introduction to handlersocket

part II – handlersocket syntax Perl/PHP

—————————————

Inserting Records

Single record

$res = $hs->execute_single(0, '+', ['mike','mike@101.com','2011-05-03']
, 1, 0);

 die $hs->get_error() if $res != 0;

Multiple records

let’s insert four more records as a ‘batch’

$res = $hs->execute_multi([
 [0, '+', ['102','bob','bob@102.com','2011-12-29'], 1, 0],
 [0, '+', ['103','john','john@103.com','2011-07-18'], 1, 0],
 [0, '+', ['104','jane','jane@104.com','2011-06-23'], 1, 0],
 [0, '+', ['105','dave','dave@105.com','2011-04-12'], 1, 0]
 ]);

for my $res (@$res) {
 die $hs->get_error() if $res->[0] != 0;
 shift(@$res);

note: Limit and offset do not affect the insert.

Reading Records

For reading records, you’ll want to additionally do something with the resulting array reference.

Something like this will work,

for (my $row = 0; $row < $ele_returned; $row+= $count_cols) {
   if($res->[$row]){
      my $user_name= $res->[$row + 0];
      my $user_email= $res->[$row + 1];
      my $created= $res->[$row + 2];
      print "$user_name | $user_email | $created\n";
   }
}

Exact values

a) Selecting on an exact single value

$res = $hs->execute_single(0, '=', ['102'], 1, 0);

b) selecting on two or more exact values (where col1=a AND col2=b)

$res = $hs->execute_single(0, '=', ['102', ‘mike’], 1, 0);

note: select on two keys must have the multiple index to support it

c) selecting on two or more exact values (where col1=a or col2=b)

$res = $hs->execute_multi(
[0, '=', ['102'], 1, 0],
[1, '=', ['mike'], 1, 0]
);

note the second array needed to use a different index
notes:

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.

d) Selecting on one or more values with (where col1 in (a, b, c….))

You can do this one of two ways,

$res = $hs->execute_multi(
 [0, '=', ['101'], 1, 0],
 [0, '=', ['102'], 1, 0],
 [0, '=', ['103'], 1, 0],
 [0, '=', ['105'], 1, 0]
);

$res = $hs->execute_single(0, '=',[''], 1, 0,
undef,undef,undef,0,['101','102','103','105']);

note with the second query, we can do more with it later on such as filtering while in the first query the arrays are independent of one another..

Limits and Offsets

These are very straight forward, we’ll start with a limit of 10,more than enough for our 5 records. (where col1=a limit x,y)

$res = $hs->execute_single(0, '>=', ['101'], 10, 0);

$res = $hs->execute_single(0, '>=', ['101'], 3, 0);

$res = $hs->execute_single(0, '>=', ['101'], 10, 2);

Range Searches

handlersocket is a little odd in that only one boundary of a range search can be specified in the initial operator cell

$res = $hs->execute_single(0, '>', ['102'], 10, 0);

two bounded range can indeed be done, it can be done with an additional filter function, (W for a stop, F for a filter of those records)

$res = $hs->execute_single(0, '>',['102'], 10, 0, undef, undef,
[['W','execute_single(0, '>',['102'], 10, 0, undef, undef,
[['F','!=', '0','104']]);

103 | john | john@103.com
105 | dave | dave@105.com

the filter can be a different column as well,

$res = $hs->execute_single(0, '>',['102'], 10, 0, undef, undef,
[['F','=', '1','dave']]);

105 | dave | dave@105.com

as you can see with the ability to adjust operations, filters and columns, there are lots of combinations

updating records

similar to the selects, the key difference being an update ‘U’ flag and array of columns are specified.

a single key

$res = $hs->execute_single(0, '=', ['103'], 1, 0, 'U',
['2015-12-12 00:00:00']);

a range

$res = $hs->execute_single(0, '>=', ['103'], 1, 0, 'U',
['2015-12-12 00:00:00']);

two keys

$res = $hs->execute_single(0, '=', ['102', 'mike'], 1, 0, 'U',
['2015-12-12 00:00:00']);

a range and filter

$res = $hs->execute_single(0, '>',['102'], 10, 0, 'U',
['2015-12-12 00:00:00'], ['F','=', '1','dave']);

This is not working correctly.

an ‘IN’ and filter

$res = $hs->execute_single(0, '=',[''], 10, 0, 'U',
['2015-12-12 00:00:00'],[['F','!=', 'mike']],0,
['101','102','103','104']);

it’s important the columns in the open_index call are exactly the same as in your update statement

Deleting Records

Single record

$res = $hs->execute_single(0, '=', ['103'], 1 , 0, 'D');

+———+———–+————–+———————+
| user_id | user_name | user_email | created |
+———+———–+————–+———————+
| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |
| 104 | jane | jane@104.com | 2015-12-12 00:00:00 |
| 105 | dave | dave@105.com | 2015-12-12 00:00:00 |
+———+———–+————–+———————+

multiple records

$res = $hs->execute_single(0, '>=', ['103'],10 , 0, 'D');

+———+———–+————–+———————+
| user_id | user_name | user_email | created |
+———+———–+————–+———————+
| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |
+———+———–+————–+———————+

$res = $hs->execute_single(0, '>=', ['103'], 2, 2 'D');

+———+———–+————–+———————+
| user_id | user_name | user_email | created |
+———+———–+————–+———————+
| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
| 104 | jane | jane@104.com | 2011-06-23 00:00:00 |
| 105 | dave | dave@105.com | 2011-04-12 00:00:00 |

You can not seem to use or even accept ‘!=’

$res = $hs->execute_single(0, '!=', ['103'], 10, 0, 'D');

however the filter can be made to do the same thing

$res = $hs->execute_single(0, '>=',['101'], 10, 0, 'D',
undef,[['F','!=', '1','bob']]);

| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |

filters and IN

$res = $hs->execute_single(0, '=',[''], $rows_returned, $offset,
'D', undef,[['F','!=', '1','bob']],0,['101','102','103','104']);

| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |
| 105 | dave | dave@105.com | 2011-04-12 00:00:00 |

bugs (tested around 02-15-12)

a recent fix was implemented for filtering and ‘IN’ in combination, however,

you CAN NOT seem to use a range, filter and ‘IN’ statement at the same time, such as,

$res = $hs->execute_single(0, '>',['101'], 10, 0, undef, undef,
[['F','!=', '1','bob']],0,['101','102','103','104']);

I had problems with the date field as well with just filtering and IN,

$res = $hs->execute_single(0, '=',[''], 10, 0, undef, undef,
[['F','>', '2','2011-07-18 00:00:00']],0,['101','102','103','104']);

as with the selects, all three (range, filters and IN) do not all work together

| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |

(missing 105 from the IN exclusion)

putting it all together

here is a sample script in perl

#!/usr/bin/perl

### this script is far from optimized, particularly for many returned rows, however you probably shouldn’t be using handlersocket for a lot of return data anyways,

use strict;

use warnings;

use Net::HandlerSocket;

#1. establishing a connection

####### port 9999 to write, 9998 to read

#my $args = { host => ‘127.0.0.1’, port => 9998 };

my $args = { host => ‘127.0.0.1’, port => 9999 };

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->auth(‘secretwr’);

die $hs->get_error() if $res != 0;

## no spaces or it will error

my $cols = “user_name,user_email,created”;

### used to get the count of the columns above dynamically, need for below

my @count_cols = split(/,/,$cols);

my $count_cols = scalar(@count_cols);

$res = $hs->open_index(1, ‘test’, ‘user’, ‘user_name’, $cols);

die $hs->get_error() if $res != 0;

#3. main logic my $rows_returned = 3;

my $offset = 0;

my $ele_returned = $rows_returned * $count_cols;

#select signs can be – ‘=’, ‘>’,'<‘,'<=’,’>=’, ‘!=’

$res = $hs->execute_single(1, ‘>=’, [‘101’], $rows_returned, $offset);

die $hs->get_error() if $res->[0] != 0; shift(@$res);

########### multi-select

#$res = $hs->execute_multi([ #[1, ‘=’, [‘mike’], 1, 0], [1, ‘=’, [‘jane’], 1, 0] #]);

## this begins the exec for a multi-statement

#for my $res (@$res) {

# die $hs->get_error() if $res->[0] != 0;

# shift(@$res); my $count_elements = scalar(@$res);

my $count_rows = $count_elements / $count_cols;

for (my $row = 0; $row < $ele_returned; $row+= $count_cols) {

if($res->[$row]){ my $user_name= $res->[$row + 0];

my $user_email= $res->[$row + 1];

my $created= $res->[$row + 2];

print “$user_name | $user_email | $created\n”;

}

print”elements: $count_elements\n”;

print “rows: $count_rows\n\n”;

### this is the closing bracket for a multi-statment

# }

#4. closing the connection

$hs->close();

November 12, 2013November 12, 2013

Handlersocket Part II / syntax Perl/PHP

(originally published 07/18/12)

other handlersocket entries:

part I – introduction to handlersocket

part III – handlersocket examples – Perl

————————-

Like the native access, you can use access all handlersocket functionality via HandlerSocket.pm, you can download the entire Handlersocket package at,
https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

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

The official documentation is at,

https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/perl-client.en.txt

Initiate a connection

perl

use Net::HandlerSocket;

my $args = { host => '127.0.0.1', port => 9999 };
 my $hs = new Net::HandlerSocket($args);

php

$hs = new HandlerSocket($host, $port_wr);

very simple, choose your host, choose your port (normally 9999 or 9998, more below on this).

Error Handling

After executing a call, you can print alerts and stop the program with the following,

perl

die $hs->get_error() if $res != 0;

Or for multiple rows,

for my $res (@$res) {
 die $hs->get_error() if $res->[0] != 0;
 shift(@$res);

php

if (! Whatever function) {
echo $hs->getError(), PHP_EOL;
 die(); 
}

Authorization

1) edit your my.cnf settings (see configuration settings above)

2) then in your code,

perl

my $res = $hs->auth('your_password');
 die $hs->get_error() if $res != 0;

php

if (!$hs->auth('your password')) {
 die('Fault auth');
 }

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 name
c – the table name
d – the name (defined in your table) of the index you are using
e(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,…’)

Data transactions

To see specific examples, go to Handlersocket examples. The general form of the call is the following,

perl

select, insert, update and delete can all be done with the general form below,

$res = $hs->execute_single(a, 'b',['c',’c2’, ‘c3’…], d, e, ‘f’, 
[g1,g2,g3,…],['h1','h2', 'h3','h4'],i,['j1','j2',’j3','j4']);

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

a – index id
b – the operation one of (>,=,!=, +)
c – the value for the index to query on OR the values for an insert
d – the limit of rows operated on like limit in mysql
e – the offset of the result like offset in mysql
f – if a delete or update then (‘D’, ‘U’) otherwise undef
g(1,2,3..) – if update, an array of values to use for the update
h – an array with filter values
h1 – ‘F’ (filter those values for =, !=), ‘W’ (stop once value is hit such as =)
h2 – filter operation (>,=,!=)
h3 – index id for the filter
h4 – value to filter on
i – key for IN statement
j(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

$retval = $hs->executeSingle(a, '=', array(‘c’), d, e);

$retval = $hs->executeMulti(
 array(array(a1, '=', array(‘c1'), d1, e1),
 array(a2, '=', array('c2'), d2, e2)));

$hs->executeInsert(a, array('c1', 'c2','c3')
 $hs->executeUpdate(a, 'b', array('c1'), array('c2'), d, e)
 $hs->executeDelete(a, 'b', array('c'))

Things to keep in mind (optimization, troubleshooting and bugs)

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.

November 12, 2013November 12, 2013

Introduction to Handlersocket

(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

http://code.google.com/p/handlersocketforjava/

Python

Ruby