HandlerSocket: The NoSQL MySQL & Ruby »

Created at: 14.01.2011 18:49, source: igvita.com, tagged: databases handlersocket mysql nosql

The end of an architectural era, time for a complete rewrite? Is it really the case that by attempting to be a "one size fits all", the RDBMS "systems of the past" excel at nothing? The Cambrian explosion of alternative database engines certainly lends some credibility to that view. However, amidst all the hype, it is also easy to overlook the fundamentals: normalized or not, or with or without a "structured query language" (SQL), a B-Tree is still one of the best performing data structures when it comes to indexing data.

With that in mind, Yoshinori Matsunobu and a few of his collaborators at DeNA (one of the largest social game platform providers in Japan), decided to build the literal "NoSQL" directly into MySQL! The HandlerSocket plugin, which they have released to the public can be installed into any existing MySQL server to provide an optimized protocol for reading and writing data directly from the underlying storage engine (such as InnoDB) without any SQL overhead. And the results are stunning: faster than memcached, more flexible, and no cache coherency problems.

HandlerSocket: Under the Hood

The core insight behind HandlerSocket is that for in-memory workloads where data is accessed via an index, the overhead imposed by SQL parsing, locks and concurrency controls has nothing to do with the reading or writing of data from the underlying storage engine. In other words, if all you need is direct access to the index, then you can bypass the SQL layer altogether - that is exactly what HandlerSocket provides.

The plugin is a daemon which can be loaded into any MySQL server, and which opens additional ports on the server to accept direct reads and writes to the underlying storage engine. The protocol is incredibly simple, and most importantly, HandlerSocket runs inside of your existing MySQL server, accessing the same underlying data. This means that you have the full expressive power of SQL, the persistence and error recovery of the underlying engine, and an optimized read/write protocol for where you need it.

Benchmarks are a subjective sport, but Yoshi's tests show that direct access to InnoDB via the HandlerSocket protocol yields a significant improvement even when compared to a raw memcached connection! Perhaps it is memcached we should be optimizing, not running away from our RDBMS engines?

Hands on with HandlerSocket

To get started, follow the installation instructions provided in the HandlerSocket repo, or simply pickup one of the latest builds of the Percona Server - it now ships with HandlerSocket built in! Once you're up and running, you can tweak your settings for the plugin in your my.cnf:

[mysqld]
plugin-load=handlersocket.so # or mysql> install plugin handlersocket soname 'handlersocket.so';
loose_handlersocket_port = 9998
loose_handlersocket_port_wr = 9999
loose_handlersocket_threads = 16
loose_handlersocket_threads_wr = 1
open_files_limit = 65535

If all is well, launch your mysql CLI, and do a "show processlist" to see the open HandlerSocket connections. Now, if you are curious, you can telnet directly to the read or write port and issue some queries, or pick one of the existing clients (PHP, Java, Python, Node.js, Ruby) and do it from the comfort of your favorite language.

Ruby & HandlerSocket

HandlerSocket ships with a C++ library (libhsclient) for which there are several Ruby wrappers: ruby-handlersocket and handlersocket. If native extensions are not a problem, then "gem install handlersocket":

require 'handlersocket'
 
h = HandlerSocket.new(:host => '127.0.0.1', :port => '9998')
 
# open PRIMARY index on widgets.user table, assign it ID #1
h.open_index(1, 'widgets', 'user', 'PRIMARY', 'user_name,user_email,created')
 
# fetch record from index ID 1, where PRIMARY key is equal to 1
p h.execute_single(1, '=', [1])
# > [0, [["Ilya", "ilya@igvita.com", "2010-01-01 00:00:00"]]]
 
# open 'id_created' index on widgets.user table, assign it ID #2
p h.open_index(2, 'widgets', 'user', 'id_created', 'user_name,user_email,created')
 
# fetch record from index ID 2, where id >= 2, and date >= 2010-01-03
p h.execute_single(2, '>=', [2, '2010-01-03'], 10)
# > [0, [["Bob", "bob@example.com", "2010-01-03 00:00:00"]]]
 

Alternatively, if you are looking for a non-blocking version, you can also use em-handlersocket which does not require any native extensions. Non-blocking reactor, combined with the fact that HandlerSocket allows pipelined execution, makes a for a very fast API! A simple example of connecting to a MySQL server and doing a range scan on a composite InnoDB index:

EM.run {
  c = EM::HandlerSocket.new
  idx = {:id => 0, :db => 'widgets', :table => 'user', :index_name => 'id_created', :columns => 'user_name'}
 
  d = c.open_index(idx)
  d.callback do |s|
 
    # Query index 0 for records where id >= 2, and created_at >= 2010-01-03
    d = c.query(:id => 0, :op => '>=', :key => ['2', '2010-01-03'])
    d.callback do |data|
      p [:received, data]
    end
  end
}
 

Download

EM-HandlerSocket (EventMachine HandlerSocket client)

Downloads: 64 File Size: 0.0 KB

Stop the revolution! Hold the rewrite!

If HandlerSocket can fetch data faster than memcached, then this is a game changer. Stop the revolution! Hold the rewrite! After all, caching data in another datastore leads to data duplication, cache coherency problems and additional operational complexity. Perhaps it is too early to simply throw away the billions of dollars we have invested into developing and optimizing the underlying RDBMS engines!


more »

Double Shot #660 »

Created at: 03.03.2010 14:20, source: A Fresh Cup, tagged: Double Shot nosql sinatra

Real viruses are as much of a nuisance to the work schedule as computer ones.


more »

Schema-Free MySQL vs NoSQL »

Created at: 01.03.2010 20:55, source: igvita.com, tagged: databases eventmachine mysql nosql

Amidst the cambrian explosion of alternative database engines (aka, NoSQL) it is almost too easy to lose sight of the fact that the more established solutions, such as relational databases, still have a lot to offer: stable and proven code base, drivers and tools for every conceivable language, and more features than any DBA cares to learn about. Not to mention that relational or not, they often times perform just as well as any other single instance key-value store when faced with large datasets - hence the reason why Riak, Voldemort and others use InnoDB as their data stores. Granted, the “feature bloat” is also the reason why a rewrite can be a good idea, but it also feels like this gray zone is too often overlooked in the NoSQL community - just because you are “NoSQL” does not mean you have to throw away years of work put into relational databases.

Setting aside the fact that we are yet to define what “NoSQL” actually is, some of the attributes that we commonly glob under this label are: document based, schema-free, distributed and “scalable”. The fact that being distributed and being scalable are not one and the same is a subject for another post, instead let’s take a closer look at what schema-free and document-based actually means. In fact, let me jump ahead: I am genuinely surprised that we are yet to see a schema-free engine built on top of MySQL. I know, I know, but suspend you disbelief for a second, because it is not as outrageous as it sounds.

Document Based: a Double Edged Sword

The original reason for and the benefit of the relational model is that by constraining the data schema (read, eliminating structural complexity of the data, or decomposing it into relations), you actually gain power and flexibility in the types of queries you can execute against your database. Said another way, normalized data design allows us to have a general-purpose query language, which allows for queries whose parameters we do not even know at design time, whereas denormalized designs do not. What we loose in flexibility of our data structures, we gain in our ability to interact with the data. Hence, in theory, if you have no way to anticipate the types of queries in the future, a relation model is your best bet. Lose some, win some, chose your poison.

At the same time, we all know that “no join is faster than no join”. The inherent disadvantage of decomposing your data is the required assembly. If you are looking for “speed” or “scalability”, then denormalizing your data is usually the first step. The disadvantage? Now you have introduced a number of potential anomalies into your data: updates, inserts, and deletes can cause data inconsistencies unless you keep careful accounting of all duplication. One-to-One, and One-to-Many relations are usually easy to manage, but Many-to-Many in denormalized schemas are nothing but a recipe for disaster. That is, if you care about consistency.

Finally, since you lose the power of a general purpose query language (SQL), you are now at a mercy of the DSL provided by your new database. Mongo, Couch and many others had to introduce their own query language constructs alongside "map-reduce" functionality to address the problem of querying arbitrarily deep records. Now, I am a fan of both, but frankly, none I have worked with so far are as clean, or as easy to understand as SQL (case in point) - with the downside of making me learn yet another query language.

Schema-free != Document Based

Document based and schema-free are often used interchangeably, but there is an important difference: schema-free does not necessarily imply nested data structures. Likewise, just because MySQL is “relational” does not mean that it must be fixed to a predefined schema - at create time, maybe, but not at runtime. Intersect the two statements, and it means that there is absolutely no reason why we cannot have a schema-free engine in MySQL:

> schema-free.sql 

mysql> USE noschema;
mysql> CREATE TABLE widgets;  /* look ma, no schema! */
mysql> INSERT INTO widgets (id, name) VALUES("a", "apple");
mysql> INSERT INTO widgets (id, name, type) VALUES("b", "blackberry", "phone");
 
mysql> SELECT * FROM widgets WHERE id = "a";
+---------+---------------+
| id      | name          |
+---------+---------------+
| a       | apple         |
+---------+---------------+
 
mysql> SELECT * FROM widgets;
+---------+---------------+--------+
| id      | name          | type   |
+---------+---------------+--------+
| a       | apple         | NULL   |
| b       | blackberry    | phone  |
+---------+---------------+--------+
 

As long as we avoid nested data structures, then there is no reason why we should be limited by the columns defined in our tables because we can compose and decompose any relation at runtime. Not only would this mean no migrations or need to store null values, but you could also keep all the tools, drivers, and the SQL query language while adding the full flexibility of being schema-free.

Schema-free DB on top of MySQL

Not able to find any project that would give me this behavior, I ended up prototyping it myself over the weekend, and believe it or not, it works just fine. In fact, the output above is from a real console session with MySQL. All it took is an em-proxy server with a little low-level protocol and query rewriting, and all of the sudden, my MySQL forgot that it requires a schema. Take it for a test-drive yourself (you will need Ruby 1.9):

git clone git://github.com/igrigorik/em-proxy.git && cd em-proxy
ruby examples/schemaless-mysql/mysql_interceptor.rb
mysql -h localhost -P 3307 --protocol=tcp

> schema-free-mysql.rb 

# snip ... 
# build the select statements, hide the tables behind each attribute
join = "select #{table}.id as id "
tables.each do |column|
  join += " , #{table}_#{column}.value as #{column} "
end
 
# add the joins to stich it all together
join += " FROM #{table} "
tables.each do |column|
  join += " LEFT OUTER JOIN #{table}_#{column} ON #{table}_#{column}.id = #{table}.id "
end
 
join += " WHERE #{table}.id = '#{key}' " if key
 

Download

mysql_interceptor.rb (MySQL Proxy in Ruby)

Downloads: 4 File Size: 0.0 KB

Of course, this is nothing but a cute code example nor does it even cover all the different use cases, but let us look at the feature set: driver support for every language (you can point Rails + ActiveRecord, JDBC, etc. at it out the box, no problem), tool support (GUI and command line), replication that works, basically impossible to corrupt, transactions, and so on. Not bad for half a day of hacking with a simple data model in the background:

Instead of defining columns on a table, each attribute has its own table (new tables are created on the fly), which means that we can add and remove attributes at will. In turn, performing a select simply means joining all of the tables on that individual key. To the client this is completely transparent, and while the proxy server does the actual work, this functionality could be easily extracted into a proper MySQL engine - I’m just surprised that no one has done so already. For a closer look, check out the proxy code itself, there are plenty of comments, which explain how it is all pieced together.

The gray zone of SQL vs NoSQL

So what is the point of all this? Well, I hope someone actually writes such an engine, because I believe there is a market for it. There is a lot to be said for a drop in, SQL compatible, schema-free engine, and unlike what the NoSQL propaganda may say, there is absolutely no reason why we can’t have many of the benefits of “NoSQL” within MySQL itself. There is no one clear winner for a database engine or model, so put some thought into your decision up front. Just because Mongo, TC, or Couch are 'document-oriented' or 'schema-free' does not mean they are necessarily better for your application. In the meantime, don't get me wrong, I am still rooting for all the NoSQL projects, as well as have high expectations for Drizzle - they are all doing fantastic work.


more »

Double Shot #639 »

Created at: 02.02.2010 13:13, source: A Fresh Cup, tagged: Double Shot friendly nosql rails sass virtualbox webiva

Early morning coding is the most peaceful time of day.


more »

Double Shot #620 »

Created at: 06.01.2010 14:55, source: A Fresh Cup, tagged: Double Shot gems nosql phone puppet

Looks like the first snow of the winter is about to arrive. My kids are considerably more overjoyed with this than I am.


more »

bgurl
ипотпал
аквичоп


Tag cloud

Categories

Links

Blogs: 43
Posts: 2516