« Perl のテスト用に PostgreSQL のインスタンスを自動で構築するモジュール Test::postgresql を書いた | Main | Picoev: a tiny event loop for network applications, faster than libevent or libev »

August 26, 2009

Mycached: memcached protocol support for MySQL

It is a well-known fact that the bottlenecks of MySQL does not exist in its storage engines, but rather in the core, for example, its parser and execution planner.  Last weekend I started to wonder how fast MySQL could be if those bottlenecks were skipped.  Not being able to stop my curiousity, I started  adding memcached proctol support to MySQL as a UDF.  And that is Mycached.

From what I understand, there are two advantages of using mycached (or the memcached protocol, in general) over using SQL.  One is faster access.  The QPS (queries per second) of mycached is roughly 2x compared to using SQL.  The other is higher concurrency.  As can be seen in the chart below, mycached can handle thousands of connections simultaneously.

And the lack of the described advantages has been the reasons for large-scale web applications to use memcached, even though it requires application developers to take care of keeping consistency between mysql and memcached.  In other words, mycached could become a good alternative here.

Mycached is still in very early stage of development.  I would never recommend using current version in a production environment, but if you are interested, the details of how to use it is as follows.

Source code of mycached is available at coderepos.org/share/browser/platform/mysql/mycached.  There is no Makefile or configure script yet, use GCC directly to create the UDF.  Source code of mysql is needed and should be set as include directories (you need to run make on the source code of mysql prior to compiling mycached).

% g++ -DMYCACHED_USE_EPOLL=1 -shared -fPIC -Wall -g -O2 -I ~/test/mysql-5.1.37/src/include -I ~/test/mysql-5.1.37/src/sql -I ~/test/mysql-5.1.37/src/regex mycached_as_udf.cc -o mycached_as_udf.so

Once the compilation succeeds, copy the shared library into the plugins directory of the lib directory of mysql, and use the CREATE FUNCTION statements to activate the necessary UDFs.  And to start mycached, call mycached_start.  The function takes three arguments: host, port, and number of worker threads to be used for handling requests.  To stop the mycached, call mycached_stop().

mysql> CREATE FUNCTION mycached_start RETURNS INT SONAME 'mycached_as_udf.so';
mysql> CREATE FUNCTION mycached_stop RETURNS INT SONAME 'mycached_as_udf.so';
mysql> SELECT mycached_start(0, 11211, 4);

The only memcached command currently supported by mycached is get (but you can do other things using SQL :-p).  As the following example shows, specify more than one db_name.table_name.primary_key to fetch a row of a table, separated by a whitespace.  The returned value will be a repetition of columns, each of them in the format of column_name:value_length:value.  It is also possible to specify JSON as the response format by adding ":JSON", however it will be slower, since the server needs to convert the values of the columns to UTF8 and to escape them.

$ telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.
get test.t1.1
VALUE test.t1.1 0 68
id:1:1message:51:hello world! hello world! hello world! hello world!
END
get test.t1.1:json
VALUE test.t1.1 0 72
{"id":1,"message":"hello world! hello world! hello world! hello world!"}
END

Thank you for reading.  If you have any ideas or suggestions, please leave a comment.

TrackBack

TrackBack URL for this entry:
http://bb.lekumo.jp/t/trackback/404050/21250255

Listed below are links to weblogs that reference Mycached: memcached protocol support for MySQL:

Comments

This is very interesting.

Do you have benchmarking results on the following conditions?

1. get operation vs SELECT on disk i/o bound conditions (database size is much bigger than innodb_buffer_pool_size)

2. set operation vs INSERT/UPDATE (innodb_flush_log_at_trx_commit=2or0)

I assume this UDF + MySQL might work very well as a durable key-value store, for example storing session data, last access timestamp etc.

i haven't learn about using udf,waiting for your good news !

This is great work. If you are willing to give up on SQL, then use embedded InnoDB with the memcached prototol.

Thank you all for your comments.

Mark, I agree that a memcached-protocol-compatible server using embedded InnoDB as a backend would be an excellent durable KVS. However I cannot live without SQL and my current intention is to add a faster alternative access route to MySQL.

Yoshinori, Mycached is designed to return "structured" values as defined in the DDL of MySQL. And once I add support for "set" operation to mycached, it will only accept structured values. This "schema-first" approach is inevitable since I want to access the same table using both SQL and memcached protocol, but might be an obstacle for using mycached as a session store, etc.

BTW, I haven't yet done any benchmarks other than the chart shown in the entry.

This is very interesting - thank you for doing this.
If you intend to access the same underlying table, it would be interesting to see a performance chart of equivalent operations using MySQL and their network protocol. I'm curious what the max concurrent requests are and the queries per second.

it seems a very interesting approach but what's the difference from this one http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html ? they seem similar, so it would be interesting to see which one performs better on the same machine.

Woh Everyone loves you , bookmarked ! My partner and i take issue in your last point.

I saw your blog awhile back and saved it to my bookmarks. Only recently have I got a chance to reading it and have to say great work.

While I enjoy that the actual analysis part of your smart cell phone market posts, you often sound for instance an Embittered Old Fart Apple haterboy.

It's the best time to make some plans for the longer term and it is time to be happy. I have read this publish and if I could I desire to suggest you few attention-grabbing issues or tips. Maybe you could write subsequent articles regarding this article. I wish to read even more issues about it!

I enjoy looking through a post that will make people think. Also, thank you for permitting me to comment!

Post a comment