HandlerSocket + Ruby
What is HandlerSocket (HS)
- a plugin for MySQL
- which allows you to read/write to MySQL
- and gives you a separate connection to MySQL
- and doesn’t allow you to run SQL queries
- but allows to run simple CRUD queries only using indexes
HandlerSocket query language is very simple (I’d even say it’s primitive), but it’s much faster than MySQL’s one. Though, of course, there are some limitations. Interested?
You already have it if you are using Percona Server or MariaDB. If not, install it from the source.
To activate the plugin, run:
INSTALL PLUGIN handlersocket SONAME 'handlersocket.so';
My configuration is the following:
# [mysqld] section # the port number to bind to for read requests loose_handlersocket_port = 9998 # the port number to bind to for write requests loose_handlersocket_port_wr = 9999 # the number of worker threads for read requests loose_handlersocket_threads = 16 # the number of worker threads for write requests loose_handlersocket_threads_wr = 1 open_files_limit = 65535
You can find a detailed documentation of all available configuration options here
Restart your MySQL server and run:
You should see a lot of rows like:
Id: 1 User: system user Host: connecting host db: NULL Command: Connect Time: NULL State: handlersocket: mode=rd, 0 conns, 0 active Info: NULL Rows_sent: 0 Rows_examined: 0
which means that HS daemon is up and running.
You can test it locally using
$ telnet 0.0.0.0 9999 Trying 0.0.0.0... Connected to 0.0.0.0. Escape character is '^]'.
P -> 0 -> your_database -> your_table -> PRIMARY -> id,some_column (where
-> is Tab). And press Enter. It should return
0 -> 1.
This protocol looks ugly, but it may save you a lot of network usage. It’s very compact, and parsing doesn’t require any CPU usage.
If you don’t have too much queries per second, probably you don’t need HS. It doesn’t optimize queries, but you may save some time on request parsing + some network. You may find it interesting if you have a lot of simple queries, like simple
SELECT‘s by primary key.
Here goes my Ruby for HandlerSocket protocol. You can find it here.
It has two implementations inside:
Ruby implementation is very slow, it’s there mainly to explain the protocol. C-based is quite fast.
To require a specific implementation, run
# For slow pure Ruby implementation require 'handlersocket/pure' # For fast C implementation require 'handlersocket/ext'
To create a connection, run
hs = Handlersocket.new('0.0.0.0', 9999)
Both pure Ruby and C implementations have the same API, so the
require place is the only difference.
To open an index, run
hs.open_index('0', 'hs_test', 'users', 'PRIMARY', ['id', 'email'])
To read the data from that index, run
hs.find('0', '=', ['12'], ['100]']) # Which is equal to # SELECT id, name FROM hs_test.users WHERE id = 12 LIMIT 100
Other commands like auth/insert/update/delete are not there yet. But it’s not that difficult to add them, check out this file, implementation of other methods also takes ~2 lines of code.
The most interesting part. To run benchmarks locally, clone the gem repository on github and run
rake benchmark. It compares Mysql2 gem to Ruby-based and C-based implementations. Here are my results:
Calculating ------------------------------------- pure HS 2.000 i/100ms ext HS 3.149k i/100ms mysql2 669.000 i/100ms ------------------------------------------------- pure HS 49.979 (± 2.0%) i/s - 250.000 ext HS 110.369M (±15.7%) i/s - 467.793M mysql2 5.218M (±16.3%) i/s - 23.869M Comparison: ext HS: 110369437.2 i/s mysql2: 5218120.6 i/s - 21.15x slower pure HS: 50.0 i/s - 2208314.91x slower
I’ve run these benchmarks on 4 cores server with 4GB ram on Percona server 5.6. On both small and huge (30 millions records) datasets, with enabled and disabled query cache, with a small and a big value of
There’s a 20-30x performance difference between Mysql2 and a C-based version of HandlerSocket because:
- almost no time is taken for request parsing
- mysql2 is just a way more complex than my gem
And I was really disappointed by performance of Ruby-based implementation. It’s 2 millions times slower than C-based. Why? There’s a magical number
50.0 i/s, but I cannot find what does it mean. If you have an answer, please, ping me on Twitter.
The gem mostly works, but there are some points that should be refined. Currently when network goes down there’s no way to reconnect because HS protocol is stateful. There’s no history tracking in HS objects, so if you open an index and then reconnect, you lose your opened index. I’m not sure if it should be implemented on a low level of abstraction in HS gem, probably it’s better to make a separated high-level gem for AR that does this job.
Once again, HandlerSocket saves your time on query parsing, buliding a query plan, it’s more compact, but is very limited. If you don’t have too many requests, don’t even think about using it.
Ruby gem for HandlerSocket