8 ms·
MySQL 5.7: over 1M queries/sec on a single machine
- shocks 13y ago<sarcasm> Pfft, GenericNoSQLDB has always been fast and totally mature and production ready, honest. </sarcasm>
- eonil 13y agoRead-only performance for unknown kind of query with unknown size of dataset using memory-cache plugin. Hmmm… I don't see any meaningful information. Should I be impressed? Am I missing something?
- arekanderu 13y agoI think i agree with you.
- eonil 13y agoThanks for explicit agreement. It's hard to keep my opinion because I am usually minority.
- corresation 13y agoYes, you should be impressed. Historically the overhead of SQL put a low ceiling on the potential performance of simple queries. Which is exactly why KV databases appeared in the first place, because a scalar value get in most RDBMS systems barely hit 1000 results per second, and that is being optimistic. That overhead was never optimized away because the classic use of the RDBMS was query conversations where each interaction was very expensive (e.g. large, complex queries), so optimizing it wasn't important. But, thanks to competition, most database systems have gotten fantastically more performant. Even using the TDS interface, I can get close to Redis performance with SQL Server now, while it was beaten by one if not two orders of magnitude a few versions ago. And in this case MySQL added the ability to use a much simpler, lighter memcached API in from of MySQL, for obvious reason -- the underlying database system is fast enough that it is of value. I should add that almost everyone who has commented thus far completely misunderstands what this is taking about. It is the Memcached API atop MySQL. Meaning you can talk to your database server through that API from any client programmed to talk to a memcached server. And that same data that you access via memcached (reading or writing) can be accessed in your normal SQL. Which is pretty cool.
- jacabado 13y agoCould you elaborate on SQL Server vs Redis claim? Or throw some references? I am not doubting but just honestly asking. I've been discussing a possible Redis solution for complementing our current SQL Server. Our problem isn't that big, we need to serve 10k req/sec of a simple query, can SQL Server do that? Isn't the connection pool the bottleneck to handle loads of this scale?
- corresation 13y agowe need to serve 10k req/sec of a simple query, can SQL Server do that Several years ago with SQL Server 2008 R2 I achieved 200,000+ simple queries per second (http://bit.ly/IlH2id http://bit.ly/IlH2id -- this is not a regimented benchmark by any measure of the imagination, but is only saying "validate before assuming" because you might find your install performs far better than you anticipate) using the standard TDS query interface. This was on pretty beefy hardware, and is obviously enormously contingent on the data being in memory (which you can force with 2012), however it blew me away and completely undermined an initiative we had to implement AppFabric / Redis or other solutions.
- bsg75 13y ago> contingent on the data being in memory (which you can force with 2012) What is the mechanism to do this in MSSQL 2012?
- jacabado 13y agoI haven't seen it on the new features of 2012, but I have seen it on some demos of 2014 Beta: http://stevenormrod.com/2013/09/an-overview-of-sql-server-2014-in-memory-oltp-hekaton/ http://stevenormrod.com/2013/09/an-overview-of-sql-server-20...
- corresation 13y agoWhoops, I meant to say 2014.
- epo 13y agoNo, you shouldn't be impressed. It only shows that some cacheing scheme can deliver data this quickly. One byte reads possibly, perhaps the same byte read one million times. Almost nothing to do with MySQL performance in fact.
- smoyer 13y agoI think I can safely claim that (with the right team) I can scale any database to match that rate. The real problem with scaling is that until you hit a limit, you don't generally know where the limit is ... and at the limit, you need the system to do something reasonable (in this case, I'd say drop read and/or write requests that couldn't be done consistently). I also wonder if you couldn't perform the same feat with only memcached (wink wink). Systems that behave erratically when you hit a limit should be avoided, and I often wonder if people publish articles like this because they themselves are amazed it was possible. For a well-behaved system, there's no need for fanfare as you grow, but when you hit a limit you have to engineer a reasonable solution. As an aside, it's pretty amazing (to some who's first computer had 2K of RAM) how big you can scale an individual server these days. The competition I'd like to see is when another team accomplishes the same feat but with half the memory, or half the number of cores or a result set that's twice as large - and then publishes how they've done it. A little friendly competition might result in some amazing discoveries.
- lelf 13y agoInnoDB API on top of memcached. Read-only. On the same “biggest HW box we have in the LAB” machine. over 1,000,000 Query/sec Cool. Carry on guys.
- martinml 13y agoWell, the performance under the same conditions was 500KQPS before, so definitely there was an improvement!
- hahainternet 13y agoRight but if someone showed a similar improvement by adding enough RAM to prevent swapping, would you be impressed? This is what is going on here. They have very much successfully proven that RAM is faster than HDD.
- nailk 13y agoThey have proven that SQL-based storage is faster than NOSQL databases. The poster of the top comment doesn't understand what this topic is about. This is actually a benchmark of InnoDB table access. You can query it using usual SQL-commands. But if you want faster access, you can use memcache API. "Read-only" is also misleading here. This is just a benchmark of get command.
- deleted 13y ago[deleted]
- Xorlev 13y ago"They have proven that SQL-based storage is faster than NOSQL databases." Too broad of a statement. 'NoSQL' is an umbrella term, and thus likely has several contenders for a raw QPS metric. The additional step of parsing the query is obviated by statement caching. You're right though, it's simply a benchmark of how fast the query path is to the InnoDB buffer pool. The work is to optimize internal contention. Very impressive results though.
- 13y ago
- CSDude 13y ago1M queries of what? Group by, 50 table joins or simple select count(*)?
- buremba 13y ago1M queries doesn't make sense if most of them actually use Memcached as backend. You can run more than 1M queries like "select 1", it does the same thing. The magic is Memcached.
- baq 13y agotrue but offtopic. from the docs: ==== 8< ==== MySQL 5.7 includes a NoSQL interface, using an integrated memcached daemon that can automatically store data and retrieve it from InnoDB tables, turning the MySQL server into a fast “key-value store” for single-row insert, update, or delete operations. You can still also access the same tables through SQL for convenience, complex queries, bulk operations, application compatibility, and other strengths of traditional database software. With this NoSQL interface, you use the familiar memcached API to speed up database operations, letting InnoDB handle memory caching using its buffer pool mechanism. Data modified through memcached operations such as ADD, SET, INCR are stored to disk, using the familiar InnoDB mechanisms such as change buffering, the doublewrite buffer, and crash recovery. ==== 8< ==== this is what is being benchmarked here, not pure memcached.
- buremba 13y agoYes but this doesn't change the fact that the queries use Memcached as the backend. If all of 1M queries hit the InnoDB tables, then I would be impressed. Also note that it's read-only performance, I think MySQL acts like a bridge here for most of the queries. (Correct me if I'm wrong.)
- cpleppert 13y ago>> If all of 1M queries hit the InnoDB tables, then I would be impressed. And you should be because that is exactly what is happening :)
- NickNameNick 13y agoAre these in memory innoDB tables durable?
- chris_wot 13y agoA benchmark is of absolutely no use whatsoever unless you give others a chance to reproduce it.
- JohnTHaller 13y agoBut is it web scale?
- wooptoo 13y agoNo, everybody knows Mongo is web scale.
- eonil 13y agoEverybody also knows Mongo has no transaction. Not a comparable product. Ah maybe comparable in this case, because this is read-only test.
- stonewhite 13y agoTry TokuMX. It mongodb with better engineering decisions and has ACID transactions, mvcc, no collection locks, no index fragmentation.
- MichaelGG 13y agoTransactions apply to read-only workloads. Getting a consistent read across multiple records can be very important.
- eonil 13y agoThat's only true when we have any write operation. If all dataset are purely read-only, it's always consistent. And I believe this article is the case.
- jwheeler79 13y agoHN is becoming more like reddit everyday. Ugh
- SkyMarshal 13y agoJust downvote these and move on.
- morgo 13y agoFor context, the first NoSQL interface for MySQL was: http://yoshinorimatsunobu.blogspot.ca/2010/10/using-mysql-as-nosql-story-for.html http://yoshinorimatsunobu.blogspot.ca/2010/10/using-mysql-as... (It's appeared on HN a few times). The MySQL team decided to run with a more standard memcached interface, and MySQL actually embeds memcached to do this (with MySQL as backend storage). Despite being read-only, the over 1M number highlights a lot work thats been done for multi-core scalability. This test was on a 48 score machine.. that was a distant dream a few years ago on MySQL 5.1.
- Mahn 13y ago> Read-Only > Memcached Well duh.
- jeltz 13y agoIt is read-only, InnoDB, and memcached. InnoDB is the storage engine used here for memcached.
- jeffdavis 13y agoFor comparison, here's a scalability benchmark for postgres (also read-only) from a year and a half ago: http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-about-64.html http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-abo... It's hard for me to tell what the MySQL benchmark was really doing, but the postgres one seems a bit more "normal" (ordinary SQL queries from postgres's ordinary simple benchmark tool). So, not apples-to-apples, but still interesting.
- jeltz 13y agoI would have loved if they had benchmarked it against doing them same thing in MySQL with SQL so we could see how much overhead is added by the SQL layer for simple selects. PostgreSQL manages to reach a third of their QPS in Haas's benchmark.