-
Notifications
You must be signed in to change notification settings - Fork 10
Home
NGINX is an extremely fast singlethreaded proxy server. Mostly used for proxying HTTP and serving static content.
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)
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.
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.
NGINX >= 1.1.10
# 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.
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;
hsock_limit 10;
hsock_offset 10;
hsock_op >=;
}
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;
# increment fields
hsock_mop +;
}
Delete location
location ~ /delete.* {
hsock_pass hsock_srv;
hsock_db mydbname;
hsock_table mytable;
set $key $arg_key;
hsock_delete;
hsock_key $key;
}
Subrequests let you receive data from database and use it for proxying/sharding or immediate output as well as insert/update any data before doing something else. The subrequest has its own URI and recieves HTTP header from parent. The returned variable values match the output lines (see later) except for first two (status) lines.
location ~ /sharding.* {
hsock_subrequest /select?key=$arg_key $f1 $f2 $f3;
if ($f1 != 0) {
return 301; # it's just an example :)
}
proxy_pass http://backend$f1?a=$f2&b=$f3;
}
You should have correctly described upstreams (backend1, backend2, ...) in nginx.conf for this to work.
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