Neat. I didn't know you could do anything like that with MySQL. Does Postgres have an equivalent? I wouldn't move from an SQL DB to a NoSQL key/value store without a significant performance improvement. Seems like you can tune MySQL far more than I realised...
There are a couple of comments on the original article that mention this:
PostgreSQL supports C-based UDF so it might not be impossible, but I'm not sure whether PostgreSQL has a standard/documented/safe way to access to internal data structures from UDFs.
…and…
Yoshinori-san, very interesting! We use a similar technique for our site together Postgresql. We have our own Software ODBA that writes SQL, marshals the data and puts the marshaled Data into the PGsql-DB. All of our data is always in the memory if the Application is running. ODBA is written in Ruby. You can find it here: http://scm.ywesee.com/?p=odba/.git;a=summary
It's called "Lessons Learned at Reddit", and mostly talks about building and deploying large-scale web applications (the technical, not business side of things.) Well worth watching (there's also a transcript.)
Yes, InnoDB was standalone. But it had it's own SQL parser and things like Stored Procedures as well ;) It was rudimentary, but I don't know if KVS is the best description.
How MySQL and InnoDB talk is very much a K<->V / row based relationship.
Interesting that bypassing the SQL part of MySQL would increase performance by that much. In the comments he says that prepared statements would still be slower but it would be useful to see some numbers on his hardware to compare with.
I agree that the figures would be useful. His response in the comments was reasonable though:
"Prepared Statement is still expensive in MySQL. It avoids SQL parsing, but it still has to open/lock/unlock/close tables, make query execution plans, etc."
So, if you're bypassing most of the bits of an RDBMS, what do you actually gain? Is the InnoDB storage engine that much superior compared to other products? Or is it about the management and admin features, e.g. sharding, replication?
Sorry about the awkwardly worded question. What I meant is, what do you actually get from using MySQL? I can understand that a lot of people aren't willing to make a switch to some dedicated KVS, but going through all that effort would seem to require the same amount of time, if not more. So there has to be something about using MySQL that makes it worth using even in this stripped down configuration.
I think the idea is that you store your data in a relational model and access it using SQL. But for things that need to be looked up a lot, and very quickly, rather than tagging on a KV store in front of MySQL, use the method he describes instead.
This way you don't end up duplicating the cache, it is shared between the two different types of lookups. You also don't end up with data inconsistancies. And yet you get the speed that a dedicated KV store would have got you anyway.
Convenience of the complex queries. If you need anything more than a simple key-value access, you'll in fact need to implement the query engine yourself if you decide to use NoSQL.
Note that the author specifically mentioned that they use HandlerSocket for fast key retrievals and SQL for complex queries, and both methods work together on the very same database.
I don't have much experience with mysql, but I can think of at least of few reasons why it would make sense to me. First, you get a relatively solid, well known storage backend, with > decade of bug fixes. Writing things reliably to disk is surprisingly hard. Then, everybody knows mysql, it is packaged and tested in any OS you would use for production. Finally, there are a huge number of tools, wrappers, etc... the chance of using a language without mysql is near 0, for example, whereas you often need to roll your own, or use a very new one (untested) for nosql solutions.
Interesting… I didn't bother searching for a previous discussion as I was under the impression that when you submit a URL that was already submitted you were simply taken to that page and upvoted it.
Pretty cool. It seems this kind of hack would also integrate well into the Drizzle project as it's designed for light weight pluggable storage. I'm curious if this has any impact on thread buffers and the number of clients MySQL can accept. It's great to be able to perform 750k QPS, but if you're still limited to a few thousand clients, other solutions may be preferable.
Cool project. It's great to see people doing things like this. On the other hand, I don't know that it would be useful for many people. Note this caveat near the end.
> No benefit for HDD bound workloads
Which is most workloads.
The author cites the expense of memory as the drawback to memcache. It's true, a large pool of dedicated memcache machines can be quite expensive. However, the given technology only works when the database fits in memory. If the database fits, then a cached subset of it certainly will fit as well. So memcache will only use a single server. One server is not a major expense compared to overhauling an application to marry it to a custom MySQL extension.
Also consider what happens if the database ever does grow beyond memory capacity. If you're using memcache, you might just need to add more cache servers. With HandlerSocket you'd have to... switch to memcache. Then add more cache servers.
"HDD bound" here means that not all data fits into the cache.
Given the average RAM size on even an entry-level server (some 16 or maybe even 8 GB) and the average size of a database (same 8GB on http://musicbrainz.org/, the largest online music database), I wouldn't claim that it's "most workloads".
If we're comparing anecdotes, the smallest database I deal with on any given day is 50GB. Enterprise applications trap lots of data. I don't know that most of our queries are HDD bound, but a significant portion of them are.
I think while the most performance-critical portion of your data fits in memory you should be alright with this method. The rest of the data can be consumed using normal SQL approach.
This means no benefit over regular MySQL releases, not to say that MySQL doesn't have some nice features.
InnoDB can read/write in multiple threads - which is useful on RAID controllers which require concurrency to exploit all their throughput. Newer SSDs also like concurrent writes. Compare this to at least MongoDB which uses a single reader/writer thread.
32 comments
[ 3.4 ms ] story [ 81.1 ms ] threadPostgreSQL supports C-based UDF so it might not be impossible, but I'm not sure whether PostgreSQL has a standard/documented/safe way to access to internal data structures from UDFs.
…and…
Yoshinori-san, very interesting! We use a similar technique for our site together Postgresql. We have our own Software ODBA that writes SQL, marshals the data and puts the marshaled Data into the PGsql-DB. All of our data is always in the memory if the Application is running. ODBA is written in Ruby. You can find it here: http://scm.ywesee.com/?p=odba/.git;a=summary
So maybe?
They said it was actually faster than other KVSes they tried. This was also a while ago.
Here it is: http://thinkvitamin.com/code/steve-huffman-on-lessons-learne...
It's called "Lessons Learned at Reddit", and mostly talks about building and deploying large-scale web applications (the technical, not business side of things.) Well worth watching (there's also a transcript.)
Initially, MySQL also supported BerkeleyDB, another KVS, but as for now they dropped the support.
From InnoDB's point of view, MySQL is just a client, as is HandlerSocket the author is using.
PostgreSQL's storage engine is much more closely integrated into the system and has no low-level API easily available from the outside.
How MySQL and InnoDB talk is very much a K<->V / row based relationship.
http://www.postgresql.org/docs/current/static/hstore.html
"Prepared Statement is still expensive in MySQL. It avoids SQL parsing, but it still has to open/lock/unlock/close tables, make query execution plans, etc."
Speed.
Is the InnoDB storage engine that much superior compared to other products?
InnoDB is faster than MySQL over InnoDB.
This way you don't end up duplicating the cache, it is shared between the two different types of lookups. You also don't end up with data inconsistancies. And yet you get the speed that a dedicated KV store would have got you anyway.
Convenience of the complex queries. If you need anything more than a simple key-value access, you'll in fact need to implement the query engine yourself if you decide to use NoSQL.
Note that the author specifically mentioned that they use HandlerSocket for fast key retrievals and SQL for complex queries, and both methods work together on the very same database.
Definitely yes, the author of the article already proved - web scale but reliable.
(OT: under what circumstances are duplicate submissions allowed? There's nothing in the FAQ or the Guidelines.)
> No benefit for HDD bound workloads
Which is most workloads.
The author cites the expense of memory as the drawback to memcache. It's true, a large pool of dedicated memcache machines can be quite expensive. However, the given technology only works when the database fits in memory. If the database fits, then a cached subset of it certainly will fit as well. So memcache will only use a single server. One server is not a major expense compared to overhauling an application to marry it to a custom MySQL extension.
Also consider what happens if the database ever does grow beyond memory capacity. If you're using memcache, you might just need to add more cache servers. With HandlerSocket you'd have to... switch to memcache. Then add more cache servers.
Given the average RAM size on even an entry-level server (some 16 or maybe even 8 GB) and the average size of a database (same 8GB on http://musicbrainz.org/, the largest online music database), I wouldn't claim that it's "most workloads".
This means no benefit over regular MySQL releases, not to say that MySQL doesn't have some nice features.
InnoDB can read/write in multiple threads - which is useful on RAID controllers which require concurrency to exploit all their throughput. Newer SSDs also like concurrent writes. Compare this to at least MongoDB which uses a single reader/writer thread.