Handlersocket is MyNoSQL

Some years back, I was trying to solve a session storage problem. The session data was already in MySQL but it’s slowing down the whole user experience. Needed to find a solution which introduced me to Handlersocket. Although in the end I decided to not use Handlersocket but the learning helped me to pick up performance in other parts of the website.

HandlerSocket is a NoSQL plugin for MySQL, working as a daemon inside the mysqld process, to accept tcp connections, and execute requests from clients. HandlerSocket does not support SQL queries; instead it supports simple CRUD operations on tables.

In short, it’s MySQL without SQL and Redis with SQL.

Go ahead and have a read at https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL

To follow through the article make sure you have these:

1. MariaDB 10.1.x
2. Python 2.7.x
3. PIP

Let’s enable Handlersocket in MariaDB following the MariaDB KB from here. If you’re successful, query SHOW PROCESSLIST and you will see the result below.

Handlersocket active in MariaDB

For this article, let’s create a table like below.

Next, let’s add some data to it.

User table content

Now let’s do retrieve some data using Handlersocket with Python.

The example above retrieved a user by using the user’s ID on the database fun. As you can see, we determine which columns we want from each query. However, the get method is only usable if we want to query the PRIMARY KEY, what if we want to query the user_name column?

Let’s add another index and see how the query goes.

In case you have a composite index then you should remember that the order of the indexed columns must match the order of the column values you want to query.

So to cut things short, let’s benchmark! Let’s measure request/second when using Handlersocket VS MySQL with the codes below.

The results on my laptop are in:

DISCLAIMER: the codes are ran on a single core meaning Python & MySQL are racing for CPU time.

During the benchmark, I watched the CPU by doing `$ top -F -R -o cpu` on my Macbook. On the first Handlersocket run, MySQL used ~35% CPU and the rest for Python while on the second MySQL run, MySQL used ~65%. This is expected since the SQL layer of MySQL got to work on the MySQL run.

With Handlersocket we get a ~130% more queries/second compared to MySQL raw query with almost half the MySQL cpu time needed.

Our next bite sized blog post will try and create a web session storage based on Handlersocket. Until next time!