MySQL/HandlerSocket and VoltDB: Contenders to NoSQL

NoSQL systems are considered by some as performing better than traditional SQL ones. Two SQL solutions, one based on MySQL plus a NoSQL layer used as a plug-in and VoltDB claim SQL still is a viable solution for large applications with high scalability needs.

MySQL with HandlerSocket

Yoshinori Matsunobu,  a former MySQL employee working for Sun/Oracle and currently Principal Database and Infrastructure Architect at DeNA, has implemented a solution for MySQL/InnoDB using a plug-in that raised the number of queries per second (qps) to +750,000 on a 2.53GHz 8-cores Nehalem server with 32GB of RAM. On the same server, MySQL does around 100,000 qps without the plug-in, number improved to around 400,000 if memcached is used. Upon profiling the RDBMS, Matsunobu realized that most of the time was spent on SQL overhead, on calls like MYSQLparse(), MYSQLlex(), make_join_statistics() and JOIN::optimize(). He wrote:

It's obvious that performance drops were caused by mostly SQL layer, not by "InnoDB(storage)" layer. MySQL has to do a lot of things … while memcached/NoSQL do not need to do.

The SQL layer includes functionality such as parsing SQL statements, opening/locking/unlocking/closing tables, solving concurrency issues, etc. Matsunobu’s solution was to circumvent this layer by adding an extra NoSQL layer:

We thought that the best approach was implementing a NoSQL network server inside MySQL. That is, writing a network server as a MySQL plugin (daemon plugin) which listens on specific ports, accepting NoSQL protocols/APIs, then accessing to InnoDB directly by using MySQL internal storage engine APIs. This approach is similar to NDBAPI, but it can talk with InnoDB.

His team added the HandlerSocket plug-in, which makes MySQL look like a NoSQL database by listening on a separate ports and receiving simple queries like PK lookups, index scans, insert/update/delete, bypassing the SQL layer. This change helped raise the performance of the system to +750K qps coming from remote hosts. The regular SQL port can be used to receive then process complex queries, the system remaining an SQL database at its core. DeNA uses the mixed SQL/NoSQL solution in production with success, according to Matsunobu, and it got rid of several memcached and MySQL slave servers in the same time.

VoltDB

Another promising SQL solution is VoltDB, an in-memory distributed open source OLTP SQL DBMS ensuring transactional data integrity (ACID). VoltDB was designed by Mike Stonebraker known for his role as architect for Ingres and Postgres. Some of the main features of the system are:

• Data is held in memory (instead of on disk) for maximum throughput, which eliminates buffer management.
• VoltDB distributes data—and a SQL engine to process it—to each CPU core in the server cluster.
• Each single-threaded partition operates autonomously, eliminating the need for locking and latching.
• Data is automatically replicated for intra-cluster high availability, which eliminates logging.
• VoltDB performance scales out near linearly by simply adding additional servers to the cluster.

The performance of the system, as advertised by its creators, makes it a contender to NoSQL solutions:

• VoltDB processed 53,000 transactions per second (TPS) on a single node versus the other DBMS processing just 1,155 TPS on identical hardware. VoltDB scalability was near linear—running the same test on a 12-node VoltDB cluster processed 560,000 TPS.
• A customer’s on-line gaming benchmark processed 1.3 million TPS on a 12-node VoltDB cluster.
• VoltDB has also been benchmarked against a NoSQL key-value store, and VoltDB executed a variety of key-value store workloads with equal or better performance.

Besides its performance, VoltDB’s merit is primarily the fact that it can talk to SQL clients which is a good asset.

The VoltDB Enterprise Edition was announced recently, containing a browser-based database management system for provisioning, managing and monitoring the DB clusters. Besides the free community edition, the Enterprise Edition comes with support.