Just yesterday we released Percona Server 5.1.52-12.3 which includes HandlerSocket. This is third-party plugin, developed by Akira Higuci, DeNA Co., Ltd and explained in Yoshinori Matsunobu’s blog post.
What is so special about it:
- It provides NOSQL-like requests to data stored in XtraDB. So in the same time you can access your data in SQL and NOSQL ways. This is first open source solution which allows that.
- It has persistent storage (XtraDB is persistent)
- It handles really high load. In my tests using 2 dedicated web servers ( using perl clients) I reached 200,000 req/sec and the clients were real bottleneck, while Percona Server was busy only 5-7%. I did not have more clients in my lab to put more load, but I have no doubts we have handle 1,000,000 req/sec with 5 separate web applications. The tests were done with Percona Server installed on Cisco UCS C250 server with 12cores/24threads and 380GB of RAM .
How it can be used:
- To provide high requests rate for simple requests like: PK-lookup, index-lookup or index-range
- To replace caching layer (or remove it at all). With declared access rate, there is no needs to have memcached.
This also solves cache management and data invalidation problems
So give it a try and provide us feedback.
HandlerSocket is a big win for MySQL but the name needs an overhaul…
What about MySQL/no or MyNoSQL or MySQLnoSQL ??
^_^’
Do writes to HandlerSocket get written to the binary log?
awesome++
Steve: According to Yoshinori’s blog posting
“Of course. Slaves execute queries that are written in binlogs. Binlog events written by HandlerSocket will be “Begin; multiple DMLs(row format); COMMIT;”. “
@Raine: I think possibly just “My”. That will especially make any future Perl bindings maximally-awesome.
is there a reason that the box is using cfq scheduler?
dchen,
The only reason is that just default in CentOS 5
Actual tests on this box I do using PCI-E SSD cards, and they are not affected by scheduler.
Vadim: this is great! I love you guys, btw!
Ah.. Just last week I was wondering about how cool would it be to have a noSQL storage engine for MySQL.
Are you considering commiting this to MariaDB as well?
Congrats on being the first to include this awesome technology!
I believe history will show that our Japanese brethren have with this small piece of code essentially obviated an entire class of database technology: all the key-value NoSQL stores (beginning with memcached). With the performance demonstrated by you and DeNA, I don’t see why anyone would use some of the simpler and less mature NoSQL solutions. (A graph engine or MapReduce is of course a different matter.) And Percona was the first to distribute it, nice!
Correcting one thing in the post: MySQL Cluster (NDB) was the first MySQL solution that allowed non-SQL access to the same data that was available via the MySQL interface – even long before the term “NoSQL” was born. However, HandlerSocket is the first to expose InnoDB (or any storage engine) tables in this way. (In fact, HandlerSocket was inspired by MySQL Cluster.)
Can I forbid access to
mysqlor any other table for handlersocket plugin?Even in the NoSQL world there is OrientDB [1] which mixes SQL, NoSQL and Graph ideas in one package. Nonetheless this sounds very cool.
[1] http://www.orientechnologies.com/orient-db.htm
Another thing that would be nice is getting sequential IO to the tables so you can do map reduce over them.
Very nice work Vadim. This looks like a big win for anyone trying to increase hardware utilization. I’m personally quite interested in how this can be used to speed up replication.
I just want to mention another bottle neck this magnificent feature introduce which is replication lag, how will the single slave SQL thread will manage to keep up with the changes ?
Today the limited write rate is regulated by engine operations ( SQL Parsing, Query analyzer etc.. ), but while using handler socket to have massive concurrent writes thus bypassing all those operations while the slave using traditional replication I suspect that replication slave will never catch up with the master.
You guys should develop a way to implement replication via HandlerSocket which might kill once and for all the slave inability to catch with the master on heavy write masters, for example if the master is using 16 core machine to process requests with the traditional mysql socket but slave process the binary log using HandlerSocket it might be helpful to reduce the replication lag if not to eliminate it totaly.
I imagine the master write the binary log in HandlerSocket protocol instead in the current sql queries format, while the slave will read the HSbinlog straight into handler socket write socket :).
I am sure it’s not that easy, you will have to implement a new sql thread logic and new master binlog format but it’s seems it can solve the replication lag once and for all.
Cheers
Golan
Golan: You raise a good point. Without knowing too many details, it seems possible that the current row-based replication could be adapted to use HandlerSocket and receive at least some benefit out of it.
…actually, if I remember correctly, the SQL thread in row based replication uses also the Handler statement to apply the replicated rows, so in theory it should be equally fast than HandlerSocket. But I know it sure isn’t 🙂 (One could perhaps try to enhance it by copying HandlerSocket code into the SQL thread???)
The OurSQL podcast discussed HandlerSocket at length: http://technocation.org/content/oursql-episode-27%3A-sql-or-no%5Btto%5Dsql,-question
They refer to my comment #17 here interpreting it as saying that row-based replication could catch up with HandlerSocket speed. That is NOT what I meant to say! I’m well aware with replication being slow and I cannot imagine it coming anywhere close to these speeds.
What I was trying to say was that it would be interesting to investigate why replication is so slow, since in essence replication and HandlerSocket do the same thing: Single thread using the Handler interface.
henrik
Vadim, thats really greate news but where I can find tutorial about Percona NoSQL solution?