Tutkiun

Most of the scalable application on web use MYSQL+memcached as the back end for their applications. Recently some of them shifted to NOSQL for one of the biggest reason ‘performance’. Certainly, NOSQL performs better than MYSQL for simple queries and primary key look-ups.

Most of the database queries of web applications are simple and related to primary key lookups. So, it seems to be intelligent decision of migrating to NOSQL from MYSQL.



NOSQL on MYSQL- Is it possible?


Yoshinori Matsunobu recently launched a MySQL plug-in ‘HandlerSocket’ which implements protocol for MySQL. The plug-in allows applications to communicate directly with MySQL storage engines, without the overhead associated with using SQL. Operations such as parsing and optimizing queries, as well as table handling operations (opening, locking, unlocking, closing) are also included in this plug-in.

As a result, HandlerSocket can provide much better performance for applications that using normal SQL application protocols for primary key lookups.



Better Performance?



Have a look at interesting benchmarks at Yoshinori’s blog, verified by MySQL Performance blog.






How it Works?



Whenever a query is fired on a table in MySQL, MySQL servers upper layer parses the query. Then it opens a table for which query is fired. After performing operations on data table is closed and results are returned. So, for every single query MySQL Engine opens a new instance of table and closes it after performing operations.

Opening and closing of tables is very costly as it causes mutex contensions and hampers performance. The biggest difference here in Handlersocket is, it doesn’t open/close tables everytime. It reuses the open table connections.

The working of HandlerSocket plug-in is best explained diagram below taken from Yoshinori’s blog.




Installation

• Download HandlerSocket plugin code from github
• Build HandlerSocket

Server Side-

$ ./autogen.sh
$ ./configure --with-mysql-source=/work/mysql-5.1.50 --with-mysql-bindir=/work/mysql-5.1.50-linux-x86_64-glibc23/bin --with-mysql-plugindir=/work/mysql-5.1.50-linux-x86_64-glibc23/lib/plugin
$ make
$ sudo make install


Client Side (For perl)-

$ ./autogen.sh
$ ./configure --disable-handlersocket-server
$ make
$ sudo make install
$ cd perl-Net-HandlerSocket
$ perl Makefile.PL
$ make
$ sudo make install



Note: Handlersocket uses daemon plug-in interface supported from MySQL 5.1, MySQL 5.1 or higher version is required. Also, make sure that you use identical MySQL version between MySQL source and MySQL binary. Otherwise you might encounter serious problems like server crash, etc.

• Installing into MySQL

Log in to mysql as root, and execute the following query.

mysql> install plugin handlersocket soname 'handlersocket.so';

If “handlersocket.so” is successfully installed, it starts accepting connections on port 9998 and 9999. Running show processlist’ should show handlersocket worker threads.



Advantages




• Supporting lots of query patterns
• Can handle lots of concurrent connections
• Extremely high performance
• Smaller network packets
• Running limited number of MySQL internal threads
• Grouping client requests
• Can reduce the number of fsync() calls
• Can reduce replication delay
• No duplicate cache
• No data inconsistency
• Crash-safe
• SQL can be used from mysql clients
• All operational benefits from MySQL
• No need to modify/rebuild MySQL
• Independent from storage engines




Limitations




• No Security
• Need to learn HandlerSocket APIs.



So, what do you think about HandlerSocket? Will you use it for your company’s databases? Share your thoughts in comments below.