(originally published 07\/08\/13)<\/p>\n
other handlersocket entries:<\/p>\n
part II<\/strong> – handlersocket syntax Perl\/PHP<\/a><\/p>\n part III<\/strong> – handlersocket examples – Perl<\/a><\/p>\n ——————————–<\/p>\n 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.<\/p>\n Why would I want to use HandlerSocket?<\/strong><\/p>\n 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\u2019s 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.<\/p>\n For a great deal more information on HandlerSocket,<\/p>\n http:\/\/yoshinorimatsunobu.blogspot.com\/2010\/10\/using-mysql-as-nosql-story-for.html<\/a><\/p>\n Comparison to prepared statements<\/strong><\/p>\n 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.<\/p>\n You can find a detailed comparison on performance here,<\/p>\n http:\/\/www.mysqlperformanceblog.com\/2011\/03\/16\/where-does-handlersocket-really-save-you-time\/<\/a><\/p>\n I\u2019m not going to cover installation here, but there are two main ways,<\/p>\n 1) download and install separately https:\/\/github.com\/DeNADev\/HandlerSocket-Plugin-for-MySQL<\/a><\/p>\n 2) it comes with Percona server (Percona server is mysql with many custom enhancements and extras) http:\/\/www.percona.com\/software\/percona-server\/<\/a><\/p>\n Some parts of the installation document will be relevant for both cases,<\/p>\n https:\/\/github.com\/ahiguti\/HandlerSocket-Plugin-for-MySQL\/blob\/master\/docs-en\/installation.en.txt<\/a><\/p>\n For it to be accessible through a language (Perl, PHP), you\u2019ll need to download the client for that as well, links below.<\/p>\n Configuration settings<\/strong> (done in My.cnf)<\/p>\n The ports<\/p>\n 9999 = read and write 9998 is faster for reads, about 20-30%. Of course it is also secures read only environments.<\/p>\n Authorization<\/p>\n Very simple to use and optional, set plain text passwords for each port.<\/p>\n Ways to access HandlerSocket<\/strong><\/p>\n * Natively through telnet or nc<\/p>\n * C++ -> libhsclient folder at https:\/\/github.com\/ahiguti\/HandlerSocket-Plugin-for-MySQL<\/a><\/p>\n * Perl -> perl-Net-HandlerSocket folder at https:\/\/github.com\/ahiguti\/HandlerSocket-Plugin-for-MySQL<\/a><\/p>\n 3rd party<\/p>\n PHP<\/p>\n Java<\/p>\n Python<\/p>\n Ruby<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" (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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-5","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts\/5","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5"}],"version-history":[{"count":14,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts\/5\/revisions"}],"predecessor-version":[{"id":235,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts\/5\/revisions\/235"}],"wp:attachment":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nInstallation<\/strong><\/p>\n
\n9998 = read<\/p>\nloose_handlersocket_port = 9998\r\n loose_handlersocket_port_wr = 9999<\/pre>\n
handlersocket_plain_secret=<secret_for_reads>\r\n handlersocket_plain_secret_wr=<secret_for_readwrites><\/pre>\n
\n
\n
\n
\n