I’ve presented at two different venues about HandlerSocket recently and the number one question that always arises is:
Why hasn’t HandlerSocket become more popular than it is?
Considering how fast and awesome HandlerSocket is, it’s not seeing as rapid adoption as some might expect. I theorize that there are five reasons for this:
Bugs, Bugs, Bugs
Up until the beginning of the year, HandlerSocket had a couple of bugs that a lot of people considered deal-breakers, and it’s not widely known that these issues have been fixed.
- Write operations do not invalidate the query cache – Exactly what it sounds like, if you performed a write operation via HandlerSocket, you could potentially read stale data from the MySQL interface.
- No support for auto increment – Exactly as it sounds, you couldn’t have auto-incrementing columns get an auto increment value on insert.
Distribution
Lots of organizations simply use what is provided to them. This was the case with MyISAM in standard distributions of MySQL for the longest time; many people were on MyISAM simply because it was the default. Even today, many organizations using recent versions of MySQL 5.1 are not using the InnoDB plugin because it is not enabled by default! If people cannot be bothered to add a couple of lines to my.cnf to enable the InnoDB plugin, they certainly cannot be expected to build the HandlerSocket plugin from source.
This is being changed with Percona-Server. The HandlerSocket plugin is now included by default and it simply takes a couple of steps to set it up. Hopefully we’ll see HandlerSocket be included in other places in the MySQL community.
No releases
There are no real releases or release schedules that are easy to digest. There is active development, bugs are being fixed, and the documentation is improving, but that does not translate into people knowing that their bugs are fixed and there is new code available.
No popular use cases
This is sort of a chicken-and-egg problem where people need to see use cases or endorsements of well-known companies in order to adopt a technology. Generally speaking, engineers are more inclined to remember a use case for a particular technology when it is well documented.
Not enough benchmarks
Finally, the benchmarks are not thorough enough. Nobody has yet taken the time to show diverse workloads on a variety of data sets. The existing work has focused almost entirely on primary-key-select workloads because that is where HandlerSocket truly shines; but people still want to know how the rest of the functionality performs. Also, there is an inherent distrust of benchmarks when done only by parties who have an interest in the product; there needs to be a wide variety of benchmarks from an even wider variety of industries and companies.
Summary
Most of these “issues” will work themselves out naturally as HandlerSocket matures. More people will publish benchmarks and use cases. As companies upgrade their Percona-Server installations, HandlerSocket will become available and easily accessible to them. Finally, as the code matures and the community grows, we’ll see lots more third-party involvement with bug reports, feature requests, and patches. I see lots of interest in HandlerSocket and am tremendously optimistic about it’s future.
Thanks for the update.
I guess it time for me to try it and reveiw HandlerSocket again. I talked about these issue in my blog (www.MysqlFanBoy.com) called NoSQL/SQL interactions.
We could really use this in my office.
I’ll offer my own reason why I (unfortunately) can’t find a way to use handlersockets in my environment: no driver.
We’re a java/mysql shop and don’t use the c-language libraries at all. Without support in the JDBC driver, its not real useful for my current employer.
I’m not as familiar with other development environments as I used to be, but if you’re on PERL, you’d need DBD:mysql to support handlersockets, if you’re on PHP you’d want support in PHP_MYSQLND, etc.
Or would handler sockets just work with those drivers (looser abstraction layer)?
Naturally, the problem is that I’m not even sure how you’d map the HandlerSockets API onto a JDBC grammer.
— Pat
@Mark I’d be curious to see how it works out for you. I’m always looking for use-cases to refer people to!
@Patrick I’m not a Java guy, but according to https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL there are two java client libraries for HandlerSocket:
http://code.google.com/p/hs4j/
http://code.google.com/p/handlersocketforjava/
I haven’t tried either, so YMMV. Also, the protocol (https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/protocol.en.txt) is incredibly simple.
I think it is because most people do not need performance. They need convenience.
@Tobi What could possibly be more convenient than not having to introduce another layer into your application stack? Would you posit that introducing Redis or Cassandra is easier than installing a MySQL PlugIn? That’s another entire application that must be administered, backed up, monitored, etc. With HandlerSocket, you get the benefits of NoSQL access, without having to complicate your application.
It doesn’t get much more convenient than that.
Tobi, I need performance as much as the next guy (probably more than most, depending on who we decide is standing next to me), but I also need a maintainable system. Right now the primary system I work on is a Java — JDBC — SQL — MYSQL (or oracle) stack.
Its well understood from a development and operations perspective and it largely works for us. Given different technologies available 5 years ago we’d probably have made different engineering choices, but we are where we are today and we know what we know today.
Based on that, I’d need an incredibly compelling reason to introduce a new technology (even if it is really just a new data access path) into our stack. The operational risks to changing something up are significant, and the operations costs of supporting two different access paths are non-trivial.
For me, the “killer” feature would be if you could make selects by primary key perform analogously to handlersockets. Then my existing code and code base would run faster and the development staff who knows how to write SQL wouldn’t need to turn around and learn another API to fetch/set data.
Note that this isn’t me saying there isn’t a use case for handlersockets (there is one), or that I wouldn’t consider it on a greenfield project (I would). My point is that for at least one existing project (mine), its different enough that its not something I’m inclined to consider right now.
Am I typical of the community at large? I’ve no idea, but it is my data point.
Ryan,
Which version of HandlerSocket is included in Percona Server?
As you mentioned a few important improvements to HandlerSocket are only available recently. So is there any way to:
1) Find out which version of HandlerSocket is included in Percona Server?
2) In case the version of HandlerSocket included in Percona Server isn’t recent enough, upgrade to the most recent version easily?
By convenience I mean that my existing application continues to work unmodified which is not the case with handler socket. I also mean that all existing technologies, libs and tutorials work (not the case). The move towards nosql is driven primarily by performance but only few need it.
And if you need performance HandlerSocket is an excellent choice.
Ryan,
Considering how coarse query cache in It would be strange for me to see someone using Handler Socket actually looking to use query cache at the same time. Query Cache is all about global mutexes and HandlerSocket is a lot about avoiding them.
Though It would be good it is fixed some way, whenever it is disabling query cache if Handler Socket is enabled or actually invalidating items is enabled is both OK with me.
Lack of security (authentication) is a show stopper for us.
@Andy: The current version we ship in 5.5 and 5.1 is the version up to commit 4cfc84066931841f3209, which was commited on Nov 20, 2010.
We’re planning to update this to a much more recent commit in our upcoming 5.5.10-rc and 5.1.56 releases.
About having an easy way to tell which version of HandlerSocket we include, unfortunately there isn’t one at the moment (you’ve got to look at our source code), but we plan to solve this right away by including a version string in the server.
On updating the component by yourself — we do include the HandlerSocket code in our sources (and we don’t patch it, we just ./configure it with the appropiate flags), so you could update it and try our usual build scripts on it. However, we cannot ensure this would work without modification.
We appreciate your interest!
for me , the problems are :
1. if tables more than 1000 in a single schema , I’ll get a out of memory err . it may because Handlersocket use one THD to deal with all client requests…
2. HandlerSocket reads many requests from clients and executes their
requests in bulk, just likt batch operation. but it will return OK ACK to client as soon as it recive the request from the client, but at that time , the update still not be committed to the innodb engint. so it will lose some update . it may cause some problem and not safe .
3. innodb only have page buffer. this cause the leak of memory .
4. when the data are not in the memory, we have to find them in the disk. the performance of the handlersocket fall down quickly. it because the handlersocket will execute the read requests in bulk.if one of the request is slow , the whole requests queue will wait for return . ..
@guillaume Authentication is supported, although it’s only plaintext and it’s undocumented (probably because it’s been added as the result of a pull request to satisfy a single-use case). For those interested, you just need to add the following variables to your MySQL config (where your HandlerSocket config is):
handlersocket_plain_secret = SECRET_PASS
handlersocket_plain_secret_wr = SECRET_PASS
And the raw command for doing authentication is:
A 1 SECRET_PASS
This will return “0 1” for a successful authentication attempt. Any requests to HandlerSocket performed before the authentication will result in HandlerSocket returning “3 1 unauth”.
This was committed back on the 21st November 2010, so I guess there’s a reason it’s not been popularised since. Note also that any clients that don’t support this (as far as I know the majority of them don’t) won’t work unless they’re updated to support the authentication command.
@Matthew Thank You!!! Despite the fact that the authentication is plaintext, this is still great news. I’ve posted a bit more of a discussion at http://www.pablowe.net/2011/03/handlersockets-secret-authentication-mechanism/
FYI, handlersocket is included in Debian unstable as of today, and will be available in Ubuntu 11.04, releasing later this month.
Why are my comments not being published? its very annoying to sit down and write a long comment only to find it piped to /dev/null….
Steve,
I’m sorry about that. Could be some bug with comment handling. I will check with responsible person at once
Well it seems to work again now.
I will write again from what I can remember from the former comment.
I think the slow uptake of handlersocket is due to the lack of concrete use cases out on the tubes. I have seen plenty of small example code snippets and benchmarks showing the raw potential of HandlerSocket, but as yet no use-cases which your average developer can relate to. The problem is explaining HandlerSocket to devs who have been using joins for since Uni in a way that makes them understand the benefits of moving joins into app logic. This unfortunately means that only a small percentage of people actually can visualize the benefits, those who like to be on bleeding edge..
I have decided to finally write my master thesis, and I am considering changing subject to cover scalable user-centric web applications, of which I intend to use HandlerSocket in the practical example I will prototype for PoC. I promise to link to this as soon as its done – though this will take a while as I am doing it on evenings after work 🙁
In the mean time, I think some better use-cases / papers online would really help.
There is a packaged distribution: Percona Server. No need to package and deploy your own, just enable the plugin.
I think it’s probably a bit early in the development process to be worrying about things like SOX compliancy; it’s more a kind of technical demo to allow MySQL to keep pace with the plethora of NoSQL solutions being developed. I’m sure those features will be added at some stage, but we’re mostly talking about raw performance for now, not about whether mission critical applications that substitute performance for security can use it.
You can make HandlerSocket read-only; as it has different ports for reading and writing, you simply disable the write port in the config by setting it to 0.
Replication works because HandlerSocket interacts with InnoDB above the binlog layer, so all your commands in HandlerSocket still get written to the binlog and therefore replicated on any slaves.
These are my notions gleaned from my limited experimentation:
HandlerSocket has some great use cases, but from a DBA perspective, it
is a dangerous toy.
1.) It allows software developers to slice through any MySQL
security/ACL scheme you may have setup for corporate applications.
2.) Storing any passwords in clear text is not an option in a
Sarbanes/Oxley compliant environment.
3.) Disabling query cache to maintain consistent reads is not a huge
issue, but sucks nonetheless if your environment leans on it (aka don’t
use memcached).
4.) ACID transactions managed at the developer level? You must work in a
methodical and rigid, integrity conscious environment where the
developers prefer to utilize stored procedures whenever possible and
changes have an approval cycle.
5.) I have to package and deploy my own build. Meh. Bothersome.
6.) To my knowledge, there’s no ‘read-only’ flag.
7.) I have not tested replication so I won’t comment.
However, if I could switch on a read-only flag, it would be highly
desirable and a bit more secure. The performance appears to be
unmatched. I guess I’ll stay tuned.
As an update to my previous comment, I’ve had some changes to libhsclient that support the authentication command merged in to trunk, so HandlerSocket clients that use libhsclient should start to see authentication support soon (and I’ve also posted a support issue for an update to php-handlersocket that adds a function to support these changes).
Well it seems to work again now.
I will write again from what I can remember from the former comment.
I think the slow uptake of handlersocket is due to the lack of concrete use cases out on the tubes. I have seen plenty of small example code snippets and benchmarks showing the raw potential of HandlerSocket, but as yet no use-cases which your average developer can relate to. The problem is explaining HandlerSocket to devs who have been using joins for since Uni in a way that makes them understand the benefits of moving joins into app logic. This unfortunately means that only a small percentage of people actually can visualize the benefits, those who like to be on bleeding edge..
I have decided to finally write my master thesis, and I am considering changing subject to cover scalable user-centric web applications, of which I intend to use HandlerSocket in the practical example I will prototype for PoC. I promise to link to this as soon as its done – though this will take a while as I am doing it on evenings after work
In the mean time, I think some better use-cases / papers online would really help.
Steve,
I’m sorry about that. Could be some bug with comment handling. I will check with responsible person at once
Why are my comments not being published? its very annoying to sit down and write a long comment only to find it piped to /dev/null….
FYI, handlersocket is included in Debian unstable as of today, and will be available in Ubuntu 11.04, releasing later this month.
@Matthew Thank You!!! Despite the fact that the authentication is plaintext, this is still great news. I’ve posted a bit more of a discussion at http://www.pablowe.net/2011/03/handlersockets-secret-authentication-mechanism/
@guillaume Authentication is supported, although it’s only plaintext and it’s undocumented (probably because it’s been added as the result of a pull request to satisfy a single-use case). For those interested, you just need to add the following variables to your MySQL config (where your HandlerSocket config is):
handlersocket_plain_secret = SECRET_PASS
handlersocket_plain_secret_wr = SECRET_PASS
And the raw command for doing authentication is:
A 1 SECRET_PASS
This will return “0 1″ for a successful authentication attempt. Any requests to HandlerSocket performed before the authentication will result in HandlerSocket returning “3 1 unauth”.
This was committed back on the 21st November 2010, so I guess there’s a reason it’s not been popularised since. Note also that any clients that don’t support this (as far as I know the majority of them don’t) won’t work unless they’re updated to support the authentication command.
for me , the problems are :
1. if tables more than 1000 in a single schema , I’ll get a out of memory err . it may because Handlersocket use one THD to deal with all client requests…
2. HandlerSocket reads many requests from clients and executes their
requests in bulk, just likt batch operation. but it will return OK ACK to client as soon as it recive the request from the client, but at that time , the update still not be committed to the innodb engint. so it will lose some update . it may cause some problem and not safe .
3. innodb only have page buffer. this cause the leak of memory .
4. when the data are not in the memory, we have to find them in the disk. the performance of the handlersocket fall down quickly. it because the handlersocket will execute the read requests in bulk.if one of the request is slow , the whole requests queue will wait for return . ..
@Andy: The current version we ship in 5.5 and 5.1 is the version up to commit 4cfc84066931841f3209, which was commited on Nov 20, 2010.
We’re planning to update this to a much more recent commit in our upcoming 5.5.10-rc and 5.1.56 releases.
About having an easy way to tell which version of HandlerSocket we include, unfortunately there isn’t one at the moment (you’ve got to look at our source code), but we plan to solve this right away by including a version string in the server.
On updating the component by yourself — we do include the HandlerSocket code in our sources (and we don’t patch it, we just ./configure it with the appropiate flags), so you could update it and try our usual build scripts on it. However, we cannot ensure this would work without modification.
We appreciate your interest!
Lack of security (authentication) is a show stopper for us.
Ryan,
Considering how coarse query cache in It would be strange for me to see someone using Handler Socket actually looking to use query cache at the same time. Query Cache is all about global mutexes and HandlerSocket is a lot about avoiding them.
Though It would be good it is fixed some way, whenever it is disabling query cache if Handler Socket is enabled or actually invalidating items is enabled is both OK with me.
And if you need performance HandlerSocket is an excellent choice.
By convenience I mean that my existing application continues to work unmodified which is not the case with handler socket. I also mean that all existing technologies, libs and tutorials work (not the case). The move towards nosql is driven primarily by performance but only few need it.
Ryan,
Which version of HandlerSocket is included in Percona Server?
As you mentioned a few important improvements to HandlerSocket are only available recently. So is there any way to:
1) Find out which version of HandlerSocket is included in Percona Server?
2) In case the version of HandlerSocket included in Percona Server isn’t recent enough, upgrade to the most recent version easily?
Tobi, I need performance as much as the next guy (probably more than most, depending on who we decide is standing next to me), but I also need a maintainable system. Right now the primary system I work on is a Java — JDBC — SQL — MYSQL (or oracle) stack.
Its well understood from a development and operations perspective and it largely works for us. Given different technologies available 5 years ago we’d probably have made different engineering choices, but we are where we are today and we know what we know today.
Based on that, I’d need an incredibly compelling reason to introduce a new technology (even if it is really just a new data access path) into our stack. The operational risks to changing something up are significant, and the operations costs of supporting two different access paths are non-trivial.
For me, the “killer” feature would be if you could make selects by primary key perform analogously to handlersockets. Then my existing code and code base would run faster and the development staff who knows how to write SQL wouldn’t need to turn around and learn another API to fetch/set data.
Note that this isn’t me saying there isn’t a use case for handlersockets (there is one), or that I wouldn’t consider it on a greenfield project (I would). My point is that for at least one existing project (mine), its different enough that its not something I’m inclined to consider right now.
Am I typical of the community at large? I’ve no idea, but it is my data point.
@Tobi What could possibly be more convenient than not having to introduce another layer into your application stack? Would you posit that introducing Redis or Cassandra is easier than installing a MySQL PlugIn? That’s another entire application that must be administered, backed up, monitored, etc. With HandlerSocket, you get the benefits of NoSQL access, without having to complicate your application.
It doesn’t get much more convenient than that.
I think it is because most people do not need performance. They need convenience.
@Mark I’d be curious to see how it works out for you. I’m always looking for use-cases to refer people to!
@Patrick I’m not a Java guy, but according to https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL there are two java client libraries for HandlerSocket:
http://code.google.com/p/hs4j/
http://code.google.com/p/handlersocketforjava/
I haven’t tried either, so YMMV. Also, the protocol (https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/protocol.en.txt) is incredibly simple.
I’ll offer my own reason why I (unfortunately) can’t find a way to use handlersockets in my environment: no driver.
We’re a java/mysql shop and don’t use the c-language libraries at all. Without support in the JDBC driver, its not real useful for my current employer.
I’m not as familiar with other development environments as I used to be, but if you’re on PERL, you’d need DBD:mysql to support handlersockets, if you’re on PHP you’d want support in PHP_MYSQLND, etc.
Or would handler sockets just work with those drivers (looser abstraction layer)?
Naturally, the problem is that I’m not even sure how you’d map the HandlerSockets API onto a JDBC grammer.
— Pat
Thanks for the update.
I guess it time for me to try it and reveiw HandlerSocket again. I talked about these issue in my blog (www.MysqlFanBoy.com) called NoSQL/SQL interactions.
We could really use this in my office.
AUTO_INCREMENT fields work great via handlersocket now.
Currently using handlersocket (and currently loving it).