First experience with Handler Socket & php_handlersocket

The article “ MySQL as NoSQL” turned my head around a little bit - A story about how to reach 750,000 queries per second (Translated by my friend Vadim). There are other materials on this topic. And now they got their hands on the experiments.

Three different clients have been developed for PHP:
extension code.google.com/p/php-handlersocket
PEAR openpear.org/package/Net_HandlerSocket
PHP native github.com/tz-lom/HSPHP
')
Below are my impressions of the first experiments.

I will not retell the article about the installation . If you installed MySQL from source, you should have no problems.

After installing the handlersocket and executing the SHOW PROCESSLIST command; There should be something like this:



In the loaded state, the picture will be approximately the same


by the number of loaded processes we can see the handlersocket load. The image shows that three connections are open, one of them is active (running).

The number of working threads can be adjusted in my.conf parameters:
loose_handlersocket_threads = 16
loose_handlersocket_threads_wr = 1


If you have collected at least one extension for PHP, then with the installation of php-hanlpersocket there should be no problems. A little confused by the lack of documentation. There are no particular complaints about the choice of example and clarity. Tested on both MyISAM and InnoDb.

The sample for PRIMARY KEY (by example) was a great success. An example is in the documentation for php-handlersocket.
Sampling is carried out as for the operation is equal to "=", operations are also possible:
more '>',
less '<'
greater than or equal to '> ='
less than or equal to '<='

With a sample of symbolic indices, the encoding must be considered. If necessary, do the conversion using iconv, otherwise you will not find anything and lose a lot of time.

An example of fetching by key name:

CREATE TABLE `test`.`cities` (
`id_city` int (10) unsigned NOT NULL AUTO_INCREMENT,
`id_region` int (10) unsigned NOT NULL ,
`id_country` mediumint(8) unsigned NOT NULL ,
`city_name` varchar (255) NOT NULL ,
`city_order` int (10) unsigned NOT NULL ,
PRIMARY KEY (`id_city`),
KEY `id_region` (`id_region`),
KEY `name` (`city_name`)
) ENGINE=MyISAM

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

if (!($hs->openIndex(0, $dbname, $table, 'name', 'city_name,id_city,id_region')))
{
echo $hs->getError(), PHP_EOL;
die( 'error open index');
}

$retval = $hs->executeSingle(0, '=', array(''), 10);



Answer:
array(1) {
[0]=> array(2) {
[0]=> string(6) ""
[1]=> string(4) "1976"
[2]=> string(1) "72"
}
}


You can sample the entire region (id_region = 1):

if (!($hs->openIndex(0, $dbname, $table, 'id_region', 'city_name,id_city,id_region')))
{
die($hs->getError());
}
$retval = $hs->executeSingle(0, '=', array(1), 100, $off);


Constant 100 - number of selective records, $ off - offset, similarly LIMIT, OFFSET in SELECT.
The response will be an array of 100 or less elements, similar to executing SQL: SELECT 'id_region', 'city_name, id_city, id_region' FROM cityes WHERE id_region = 1 LIMIT 100, $ off;

For example, it is convenient, when implementing an autocomplex, using the operations (signs) '> =' or '<=' you can select the required number of words beginning with certain letters. Tracking the range with standard means is impossible, but if the data is taken in chunks and checked for satisfaction with the second condition, then in principle any simple sampling can be performed.

Protocol. It should be noted that in the development of its knowledge is very helpful in debugging. Requests at first, especially when something does not work, constantly have to compare with the results obtained through the implementation of telnet. Protocol description lies in the file protokol.en.txt or its translation in the article “Introduction to HandlerSocket: protocol description and php-handlersocket extensions”

Using the Protocol is very simple: In telnet we connect on port 9998 to the host database.
telnet localhost 9998
P 0 test cities name city_name,id_city
// TAB (\t), NULL, TAB
0 1 ---> 0 1
///
0 > 1 a 10
/// (int), (<>= ...) - , [, Limit, Offset]
0 2 A Baa 10564 A Barqueira 10565 A Corua 10566 A da Beja 15510 A dos Arcos 15511 A dos Bispos 15512 A dos Cunhados 15513 A dos Francos 15514 A Merca 11120 A Nario 13257
// =0 (), (city_name,id_city)
// , TAB, BK(\n)


In conclusion, I would like to add that you can make queries on composite keys using arrays and multi-queries, i.e. multiple requests for one connection:

$hs->openIndex(0, $dbname, $table, 'PRIMARY' , 'city_name,id_city,id_region' );
$hs->openIndex(1, $dbname, $table, 'id_region' , 'city_name,id_city,id_region' );
$retval = $hs->executeMulti(
array(array(0, '=' , array( '23' ), 1, 0),
array(1, '=' , array( '3' ), 10, 0)));


* This source code was highlighted with Source Code Highlighter .

Behind a review side there were operations INSERT / UPDATE / DELETE. I hope in the near future to give a more detailed review.

Handlersocket is well shown in combination with sphinx. With the help of the Sphinx, we search for the required document IDs, and with the help of the handlersocket we instantly select the necessary information.

Customer Performance Comparison
top line PHPHS
bottom php-handlersocket
time is dirty in microseconds, taking into account the classes and instances
0.005791
0.001404
---
0.007095
0.001383
---
0.00456
0.002563
---
0.006104
0.001384


Time without inclusions and instances, purely one sample by PK
$t1 = microtime();
$retval = $hs->executeSingle(2, '=', array('60187'));
echo microtime()-$t1, PHP_EOL;
$t1 = microtime();
$res = $rs->select('=','60187');
echo microtime()-$t1, PHP_EOL;

0.000451
0.000632
---
0.00039400000000001
0.00020700000000007
---
0.00040000000000007
0.00021399999999994
---
0.00053999999999998
0.002058
---
0.002926
0.0002089999999999
---
0.000386
0.00021100000000002


Revealed bug
When debugged, I dropped telnet on kill -9, which caused the socket to hang.
Subsequent restarts of the script caused it to hang (the socket was waiting for reading).

SHOW PROCESSLIST handlersocket processes not shown

Cured by restarting muscle.