Home

NGINX MySQL HandlerSocket module

What is NGINX?

NGINX is an extremely fast singlethreaded proxy server. Mostly used for proxying HTTP and serving static content.

What is HandlerSocket?

HandlerSocket is a simple protocol for accessing MySQL databases.

No matter if you use MyISAM or InnoDB the usual way of working with MySQL is through SQL language. However with HanderSocket plugin MySQL can be used as NoSQL service.

MySQL has its native protocol and native library libmysqlclient which speaks it. It passes SQL queries to server and parses replies. It turned out recently that parsing SQL on server can become a significant performance issue especially when actual data is all in memory. That was the case with InnoDB tables which fit InnoDB cache. To address this issue HandlerSocket protocol was designed as well as MySQL server plugin. The plugin serves requests in a singlethreaded manner and passes them directly to InnoDB engine. No SQL-like parsing is required in this case.

Benchmarks have shown MySQL/HandlerSocket combination to be extremely fast and even faster than Memcache (remember HandlerSocket is a gateway to an InnoDB table which is persistent unlike Memcache)

MySQL HandlerSocket Plugin

How to access MySQL/InnoDB tables from NGINX?

nginx-mysql-hsock module is used for that. Module supports 4 common operations: SELECT, UPDATE, INSERT, DELETE. HandlerSocket being a NoSQL gate to InnoDB misses many SQL features, but keeps most commonly used ones. For any HandlerSocket operation an index is specified (usually the default 'PRIMARY' index is used).

The module output format is simple. Data is returned line-by-line. The first line is errorcode (zero is OK). The second is the number of following data lines. See examples section for more details.

Note this module is a true asynchronous NGINX upstream module. No blocking operations or worker threads.

Why access database from NGINX?

There are many situations where accessing database from NGINX in a non-blocking way gives a great improvement over other approaches. Imagine you have a highload web service with lots of backends and a table where a backend (and possibly any other information) is specified for each user. With this module proxying to backends can be done in NGINX in a non-blocking way.

Compatibility

NGINX >= 1.1.10

Install

# download nginx-mysql-hsock module
wget -O nginx-mysql-hsock.tar.gz 'https://github.com/arut/nginx-mysql-hsock-module/tarball/master'
tar xzf nginx-mysql-hsock.tar.gz

#download & install NGINX
wget 'http://nginx.org/download/nginx-1.1.10.tar.gz'
tar xzf nginx-1.1.10.tar.gz
cd nginx-1.1.10
./configure --add-module=$PATH_TO_THE_MODULE
make
make install

Apply to MySQL HandlerSocket Plugin page for HandlerSocket plugin install manual.

Config examples

These are the examples of nginx.conf for accessing MySQL/InnoDB tables from NGINX

First, you need a keepalive connection with HandlerSocket server.

upstream hsock_srv {
    server 127.0.0.1:9999;
    keepalive 1024;
}

Create select location

location ~ /select.* {

    hsock_pass   hsock_srv;
    hsock_db     mydbname;
    hsock_table  mytable;

    # Yes, you should copy variables like this
    # if you need to use arg_, http_ etc variables.
    # They are not accessed directly so far.

    set $key $arg_key; 

    hsock_select field1 field2 field3;
    hsock_key    $key;
}

Insert location

location ~ /insert.* {

    hsock_pass   hsock_srv;
    hsock_db     mydbname;
    hsock_table  mytable;

    set $key $arg_key;
    set $field1 $arg_field1;
    set $field2 $arg_field2;
    set $field3 $arg_field3;

    hsock_insert key $key
            field1 $field1
            field2 $field2
            field3 $field3;
}

Update location

location ~ /update.* {

    hsock_pass   hsock_srv;
    hsock_db     mydbname;
    hsock_table  mytable;

    set $key $arg_key;
    set $field1 $arg_field1;
    set $field2 $arg_field2;
    set $field3 $arg_field3;

    hsock_update
            field1 $field1
            field2 $field2
            field3 $field3;

    hsock_key $key;
}

Delete location

location ~ /delete.* {

    hsock_pass   hsock_srv;
    hsock_db     mydbname;
    hsock_table  mytable;

    set $key $arg_key;

    hsock_delete;
    hsock_key $key;
}

After server is started simple queries can be used to apply to database.

Insert record

curl 'http://localhost/insert?user=roman&field1=1&field2=abc&field3=100500'
0
1

reply explained: 0=no error, 1=ignore

Look what we have

curl 'http://localhost/select?user=roman'
0
3
1
abc
100500

0=no error, 3=number of data lines, 1=1st column value, abc=2nd column value, 100500=3rd column value

Update data

curl 'http://localhost/update?user=roman&field1=2&field2=cde&field3=100501'
0
1
1

0=no error, 1=number of data lines, 1=1 row updated

Delete record

curl 'http://localhost/delete?user=roman'
0
1
1

0=noerror, 1=number data lines 1=1 row deleted