8 ms·
Using MySQL as a NoSQL - A story for exceeding 750,000 qps on a commodity server
- mike-cardwell 16y agoNeat. 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...
- davecardwell 16y agoThere 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 http://scm.ywesee.com/?p=odba/.git;a=summary So maybe?
- steveklabnik 16y agoI 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 16y agoI actually just looked up the video where they talk about this yesterday. Here it is: http://thinkvitamin.com/code/steve-huffman-on-lessons-learned-at-reddit/ 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.)
- quassnoi 16y agoInnoDB 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.
- morgo 16y agoYes, 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 16y agoThe latest release of PosgreSQL includes hstore: http://www.postgresql.org/docs/current/static/hstore.html http://www.postgresql.org/docs/current/static/hstore.html
- quassnoi 16y agoThis 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 16y agoInteresting 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-cardwell 16y agoI 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 16y agoThe 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.
- mhd 16y agoSo, 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?
- quassnoi 16y agoSo, 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.
- mhd 16y agoSorry 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.
- mike-cardwell 16y agoI 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.
- quassnoi 16y agoWhat do you actually get from using MySQL? 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.
- robinhouston 16y agoThere was a little discussion when this article was posted here three weeks ago: http://news.ycombinator.com/item?id=1807527 http://news.ycombinator.com/item?id=1807527 (OT: under what circumstances are duplicate submissions allowed? There's nothing in the FAQ or the Guidelines.)
- davecardwell 16y agoInteresting… 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.
- deleted 16y ago[deleted]
- 51Cards 16y agoI think this is what should happen, or at least the duplication should be noted to the poster before they commit their new entry.
- tmountain 16y agoPretty 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 16y agoCool 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.
- quassnoi 16y 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/ http://musicbrainz.org/, the largest online music database), I wouldn't claim that it's "most workloads".
- icey 16y agoIf 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.