Using MySQL as a NoSQL - A story for exceeding 750,000 qps on a commodity server

http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html

by davecardwell • 16 years ago

169 31 16 years ago

yoshinorimatsunobu.blogspot.com

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...

davecardwell2• 16 years ago

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

So maybe?

steveklabnik• 16 years ago

I don't know if they're using a particular feature, but Reddit is basically using Postgres as a KVS.

They said it was actually faster than other KVSes they tried. This was also a while ago.

edanm• 16 years ago

I actually just looked up the video where they talk about this yesterday.

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.)

quassnoi6• 16 years ago

InnoDB was initially designed as a KVS. MySQL only added the SQL frontend to it.

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.

morgo2• 16 years ago

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.

jdub• 16 years ago

The latest release of PosgreSQL includes hstore:

http://www.postgresql.org/docs/current/static/hstore.html

quassnoi6• 16 years ago

This is a single-value KVS within a field, not a replacement for SQL. You still need to write an SQL statement to access it.

jtdowney• 16 years ago

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.

mike-cardwell3• 16 years ago

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."

jamaicahest• 16 years ago

The whole point of the article is to measure QPS, so I think it's fair to want numbers to back up that statement instead of speculating.

mhd2• 16 years ago

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?

quassnoi6• 16 years ago

So, if you're bypassing most of the bits of an RDBMS, what do you actually gain?

Speed.

Is the InnoDB storage engine that much superior compared to other products?

InnoDB is faster than MySQL over InnoDB.

mhd2• 16 years ago

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.

cdavid• 16 years ago

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.

tszming• 16 years ago

>> Is the InnoDB storage engine that much superior compared to other products?

Definitely yes, the author of the article already proved - web scale but reliable.

VladRussian• 16 years ago

they still are able to do complex SQL when needed

Devilboy• 16 years ago

Lots of people already run MySQL so this would make sense, no need to install another data store.

robinhouston• 16 years ago

There was a little discussion when this article was posted here three weeks ago: http://news.ycombinator.com/item?id=1807527

(OT: under what circumstances are duplicate submissions allowed? There's nothing in the FAQ or the Guidelines.)

davecardwell2• 16 years ago

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.

51Cards• 16 years ago

I think this is what should happen, or at least the duplication should be noted to the poster before they commit their new entry.

tmountain• 16 years ago

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.

bluesnowmonkey• 16 years ago

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.

quassnoi6• 16 years ago

"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".

icey2• 16 years ago

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.

alexro• 16 years ago

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.

morgo2• 16 years ago

"No benefit for HDD bound 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.

Using MySQL as a NoSQL - A story for exceeding 750,000 qps on a commodity server

Related Stories