HandlerSocket mySQL's NoSQL, PHP and Webscale

HandlerSocket is a server plug-in that interfaces with InnoDB directly bypassing for the most part the core mySQL server all together. Using Handler socket you do not connect to the traditional port 3306 or use the mySQL protocol to communicate with the mySQL server, you use 9998 reads, 9999 writes and talks a different protocol (much smaller) to an epoll-based service. This awesome addition means that you can have many many connections with very little overhead. The core mySQL server does a good job, but can do better. I'm sure the reason why epoll is not in the core engine is probably due to licensing issues or some other reason I am not aware of, like it would be a big deal to add it.

Why bypass the server? Really bypassing the sql parser is what was desired. The sql parser ads a significant amount of slowdown at a huge transaction rate, additionally mutex contention is involved prior to reaching the storage engine with malloc overhead (although Monty has fixed this in MariaDB). Even though you can easily get 45-70K selects a second through the mySQL SQL parser layer for InnoDB primary key lookups, this number falls short of Memcache's 600K Gets per second or various other NoSQL solutions that keep data in memory.

HandlerSocket just like, Memcache, Cassandra, MongoDB accesses the data by a key, with the fastest access on Primary Key since that's how InnoDB structures it data, sorted by the primary key. I've seen benchmarks that show Handler socket doing 750K Transactions per second on a single server. I'm now in the process of benching it and let me say I think I can get a better number. This by the way blows away ALL OTHER NoSQL benchmarks with the added benefit of an ACID compliant DATABASE. These numbers and durability really shows the power of InnoDB this is why I believe it's the best Storage engine in the world. (Plus the code is clean).


Percona released a XTRADB server version with HandlerSocket a plug-in created by the engineers of Dena and immediately I started testing it.

Installation of HandlerSocket is detailed here in case you want to have HandlerSocket on a vanilla mySQL source base. The latest version prior to the version above of XTRADB, I ran into a big stall and had to roll back to a vanilla install of mySQL to get rid of it. When I have some time I'll try to duplicate the stall in a controlled environment and use PMP to track down where the stall is. The versions that I dealt with are drastically different so it may just be an new InnoDB stall or something else.


The PHP Client that I am using is php-handlersocket which is a PECL type version (C driver with exposure to PHP). It does the job but needs some work that I'm doing now.


Here is some rough code and output of data.

#
# make a php connection
#

$hs = new HandlerSocket($host, $port);
if (!($hs->openIndex(1, $dbname, $table, HandlerSocket::PRIMARY, 'facebook_id,shard_id,shard_lock'))){
    die($hs->getError() . "\n");
    
}

#
# execute a query on the primary key and return the columns from the 5th parameter on openIndex
#

$retval1 = $hs->executeSingle(1, '=', array($uid),1,0);
init_funcs_log_info("HandlerSocket", "ExecuteSingle:" . var_export($retval1,1), 'socket_handler_query');


#
# prepare a friend query
#

$socket_handler_commands = array();

foreach($list_of_users as $userid){
    $socket_handler_commands[] = array(1, '=', array($userid));

}

#
# execute the friend query
#

$retval2 = $hs->executeMulti($socket_handler_commands);

init_funcs_log_info("HandlerSocket", "ExecuteMulti:count(" .count($list_of_users) . ")\n" . var_export($retval2,1), 'socket_handler_query');