12 ms·
One million queries per second with MySQL
- leetrout 4y agoI know their devs (or the vitess devs) are on here... No foreign keys are supported - are there any other schema level constraints that are not supported that would also enable scaling out and achieving these numbers?
- andersonrkton 4y agoI would like to see a bench using the “common” setup and not the enterprise one… what are the limits on those?
- forgot_old_user 4y agosays this in the article at the end: >We ran this benchmark against a single-tenant environment, with levels of resources that we reserve for our enterprise customers.
- ijidak 4y agoVery good job. A few questions. What is the response payload size? How large is the key space? And how many unique keys? I may have missed those details in the write up. It's hard to judge queries per second without understanding those pieces. Genuinely curious.
- jonahberquist 4y agoWe used https://github.com/Percona-Lab/sysbench-tpcc https://github.com/Percona-Lab/sysbench-tpcc, which the folks at Percona did a good write up of here https://www.percona.com/blog/2018/03/05/tpcc-like-workload-sysbench-1-0/ https://www.percona.com/blog/2018/03/05/tpcc-like-workload-s.... For these tests, we ran with --tables=1 and --scale=1000.
- chreniuc 4y agoI'm curious about the latency between a backend app that runs on different cloud provider than aws(eg hetzner), because I've noticed that they run on aws(it shows up when choosing the region for your database). Has anyone used this and connected from a different cloud provider? Have you noticed big latencies?
- orware 4y agoYou will generally run into different types of latencies in this case since you have the general connection latency when those are getting established plus the regular physical/network latency between where the database is located and your own servers. For connections, since a TLS handshake is required, the impact of physical distances can have a greater impact on the connection time. The following article: https://sking7.github.io/articles/44961356.html https://sking7.github.io/articles/44961356.html actually provides a good 3.5x-4x figure which correlates with some connection tests I've completed. In other words, if an initial TCP packet takes ~100ms to get from the database to your server, then establishing a TLS connection to the database will probably be around 400ms. Once the connection is established, running queries over an open connection is generally going to be quicker, at least for simpler queries. More complex queries will still take whatever time they need to process on the database end before they can start sending results back so results will generally vary there. But going back to that 100ms example...if the amount of data being returned from a very simple query is minimal than the response time here would be very close to that 100ms figure over an already open connection and likely would go up from there depending on the complexity of the query and amount of data needing to be returned. Since the connection hostnames are publicly accessible and TLS is always required for connections you can easily test from your own provider's location. So long as the general physical location isn't too far away from a supported region, the latency overall shouldn't be unusable. I may have mangled some terminology/analogies above but hopefully that helps provide a bit of a ballpark for you. If you have specific to/from regions in mind I might be able to try and collect some specific numbers for you!
- toast0 4y agoI haven't spent time optimizing TLS between a database client and server, but in HTTPS, using TLS 1.3 without early data (or TLS 1.2 with somewhat optimistic handshake handling) gets you to one added roundtrip, TLS 1.3 early data gets you down to zero added round trips. Early data isn't always appropriate, because there's potential for replays, but the latency improvement might be worth considering for some selects.
- truth_seeker 4y agoCorrect me if my knowledge is outdated, Vitess deployment comes at a cost: 1. You can't have single database view of all shards 2. No distributed ACID 3. No distributed JOINs If I were to do it in PostgreSQL 14 natively without any other third party plugins, I can get help of Table Partitioning (say root server) + Tables on Foreign Servers (say leaf servers) through FDW. Parallel scans and JOINs are allowed. Also, PostgreSQL 15 (in beta now) will allow parallel commits to foreign server tables through FDW.
- harshitgangal 4y agoDisclaimer: I am a Vitess Maintainer. Please find my answer inline 1. You can't have single database view of all shards - Vitess does query planner and routing for the application so application does not have to know about shards. Yes, Vitess gives a view of a single database. 2. No distributed ACID - This has been talked a lot feature and it is in our roadmap to provide. Currently, we have a twoPC implementation which we do not recommend for production use. Right now the users of vitess either choose their sharding key in such a way that all the queries in a transaction goes to single shard or the application can handle best effort multi shard transaction. 3. No distributed JOINs - Vitess does support distributed joins, aggregation, subqueries and other complex queries. Vitess thrive to be 100% Mysql compatible. If anyone encouter such query, a github issue helps in knowing and prioritizing.
- truth_seeker 4y agoThanks for info.
- TedShiller 4y ago
- mattlord 4y ago> If I were to do it in PostgreSQL 14 natively without any other third party plugins, I can get help of Table Partitioning (say root server) + Tables on Foreign Servers (say leaf servers) through FDW. Parallel scans and JOINs are allowed. Also, PostgreSQL 15 (in beta now) will allow parallel commits to foreign server tables through FDW. I wish you all the best in operating that at scale and providing a reliable general purpose PostgreSQL DBaaS (even if only internally) around it. :-) PostgreSQL is great, not going to argue otherwise -- every solution has tradeoffs and if the tradeoffs of one solution fit your usage best then go with it. If you need to scale out and manage a large cluster of PostgreSQL instances as if it was a single logical database then I'd recommend something that's in a similar space as Vitess and PlanetScale -- Citus and Citus on Azure respectively. At the hand waving level it's always easy to "do this, and that", but it's a whole other matter when you develop a general purpose production-grade product that anyone can use and operate at virtually infinite scale. :-)
- deleted 4y ago[deleted]
- tiffanyh 4y agoIn 2018, folks were getting 4M queries per second using SQLite (BedrockDB) [0]. This was also accomplished from just 1 server, not needing 40 shards like blog post. [0] https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
- rbranson 4y agoApples to oranges comparison. The benchmark in the PlanetScale blog post is based on TPC-C.
- deleted 4y ago[deleted]
- deleted 4y ago[deleted]
- SnowHill9902 4y agoAnd with awk you can query 10M lines per second.
- bitobserver 4y ago"Granted, it’s not SQLite by itself. We’ve wrapped it in a custom distributed transaction layer ..."
- tiffanyh 4y agoHow is this any different than the blog post that uses Vitess, which is a custom wrapper around MySQL? BedrockDB is a custom wrapper around SQLite.
- mattlord 4y agoThe Expensify test was targeted at demonstrating the upper limits of SQLite itself. The hardware used: > 1TB of DDR4 RAM > 3TB of NVME SSD storage > 192 physical 2.7GHz cores (384 with hyperthreading) And a very simple read-only query w/o overlapping rows, with the full dataset already loaded in memory: > The query itself just sums a range of 10 rows randomly located inside 10B row (447GB) two column database of random integers, one of which is indexed and the other not. This means that the test is fully cached in RAM, and the database is precached prior to the test. No writes are done during this test, only reads. All that is good and fine for that purpose -- to demonstrate the limits of what SQLite can do in scaling UP. The purpose of the PlanetScale post was demonstrating linear scalability by scaling OUT using a realistic workload (TPC-C is a warehouse / ecommerce site simulation) on small commodity VMs (ec2 isntances). As noted, you're comparing two very different tests that had very different goals.
- hbrn 4y agoI'm unfamiliar with vitess, but what exactly is the achievement here? If you have a shared-nothing architecture, you can keep indefinitely adding nodes to get more throughput. You can easily do a billion qps if you wanted to. The downside is that you have to eliminate relations (or at least don't expect them to be performant/scalable). Am I missing something?
- samlambert 4y agoYou understand the point. It's a real point. Show me a modern relational database that can scale this predictably on the cloud with this level operability.
- tiffanyh 4y agoWhen you have to eliminate data relationships for it to scale, you no longer have a "relational" database.
- sroussey 4y agoYou would be surprised then. Most SaaS companies can easily shard by customer. All the customer data stays together, relational and all. Cross-customer queries will be somewhat slower.
- tiffanyh 4y agoSure, but what you are describing is no longer a multi-tenant application/database. It's essentially a single-tenant deployment of your tech stack per customer. Which is not very cost effective.
- deleted 4y ago[deleted]
- staticassertion 4y agoI would still consider it a multi-tenant system. It's a single database to manage, which distributes your customers using their identity as a partitioning key.
- 29athrowaway 4y agoNot all queries are the same.
- samsquire 4y agoHandlerSocket is an interesting approach to turn MySQL into a NoSQL database and get 700,000 requests per second on a single node. http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html?m=1 http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-a... When I benchmarked Redis on an Intel hexacore I got 800,000 requests a second on 8 terminal windows. So I assume the MYSQL data fit in RAM.
- maxdemarzi 4y agoWhat were the stats on each Shard? Was each shard running 4 cores? 8 cores? 32 cores? RAM? The blog post is light on details.
- nelsondev 4y agoHow much hardware did you need?
- loxias 4y agoIs a shard a server? If so, how powerful of a machine were they using? I'm deeply unimpressed at only 1M TPS on a _40 node_ cluster.
- obiwahn 4y agoWhy is that? It is very unlikely to answer more random requests with more nodes. You have to pay for the communication that is required in the cluster to locate the data. It will only be faster if you have some special knowledge and are able to partition the data in a smart way. Which will allow shortcuts when storing or receiving data.
- TedShiller 4y agoThis is cool but the problem is you're on MySQL
- osrec 4y agoWhy is that a problem?
- TedShiller 4y agoEveryone is moving away from MySQL to Postgres
- endisneigh 4y agoDo you have any data supporting this assertion?
- TedShiller 4y ago
- paxys 4y ago"Everyone" except all the largest and most successful companies in the world.
- samlambert 4y agoThis.
- TedShiller 4y ago
- bpodgursky 4y agoThe largest and mot successful companies in the world are still powered by MapReduce. It doesn't mean it's a great technology anymore... it just means it's incredibly hard to migrate tech stacks once you're a huge and successful company.
- didip 4y agoToo bad Vitess cannot be generalized to sit on top of PostgreSQL
- skyde 4y agoCheck out cirrusDB, its pretty much the same thing as Vitess but for PostgreSQL https://docs.microsoft.com/en-us/azure/postgresql/hyperscale/overview https://docs.microsoft.com/en-us/azure/postgresql/hyperscale...
- achanda358 4y agoSounds like you mean CitusDB
- skyde 4y agoyep sorry
- endisneigh 4y agoGiven the restrictions I’d be curious to hear if anyone has compared the two approaches: 1. Something similar to the article 2. Use a generic key value store, pipeline the data to a rdbms like SQLite or Postgres. Use the kv store for all simple queries, delegate to the rdbms for joins/complicated queries If you used a cluster of syclladb or foundationDB you could get similar read/write performance but those are kv stores. If you happen to shard by customer you could duplicate keys and values into separate databases per tenant. You could effectively reindex common queries to use kv directly and have others go to rdbms. —- More generally I wish there was a rdbms with easily pluggable storage engines or one where you can use a key value store as the storage. I’d trade some performance for the flexibility
- acomjean 4y agoDon't Postgres and mysql already have the Json datatype, that pretty much allows creating Key/Value stores? https://www.postgresql.org/docs/current/functions-json.html https://www.postgresql.org/docs/current/functions-json.html
- endisneigh 4y agoThe main reason to use a kv store is performance and shard-ability
- AdamProut 4y agokeep in mind TPC-C scales out in a trivial manner. It was designed before distributed databases existed. The workload itself is perfectly sharded on warehouse_id. All joins happen on this column and the query load is uniformly distributed on this key. This makes it perfect for distributed sql databases to scale out by sharding on warehouse_id (no hot spots and all joins and transactions will be local to one shard in the cluster) This is one of the big reasons TPC-E was developed as a successor to TPC-C. It’s much harder to scale it out.
- derekperkins 4y agoThat matches many, if not most most real world workloads. When hotspots happen, you just continue to shard, as they don't have to be equally sized, even if you shard a single warehouse id to a dedicated node and vertically scale it up.
- AdamProut 4y agoYep, lots of real world workloads shard very well. That wasn't my point. My point was that "most" distributed SQL Databases that do sharding will do very well on TPC-C. You can google search for equivalently impressive results for many distributed SQL databases. Each of them can keep adding more warehouses to the TPC-C workload along with more hosts and shards to the distributed SQL database to push the TPC-C TpmC results into the millions.
- jordanlewis 4y agoActually, the designers of TPC-C were ahead of their time - they deliberately designed TPC-C to not be trivially shardable. The new order transaction, which is the heart of the workload, is required to cause a read outside of a shard 1% of the time [0] for each of the 10 on average items in a new order. So, 10% of the time on average, the workload should emit a cross-shard transaction. A correctly implemented TPC-C workload does actually need to issue cross-shard transactions. There are a lot of "TPC-C-like" workloads that don't actually implement this behavior, though, along with other harder to handle attributes like foreign keys and sleep/wait time. TPC-C is really an enduring, interesting benchmark. [0]: See section 2.4.1.5 2 of the spec https://www.tpc.org/tpc_documents_current_versions/pdf/tpc-c_v5.11.0.pdf https://www.tpc.org/tpc_documents_current_versions/pdf/tpc-c...
- midislack 4y agoOn what hardware? We need a standard computer for these "benchmarks."
- nixgeek 4y agoApparently AWS DynamoDB peaked at 105 million queries per second for a single tenancy this year, delivering Amazon’s Prime Day. Apples to oranges to be sure but >100x this scale for a single tenant of a multi-tenant database system is super impressive, too! https://aws.amazon.com/blogs/aws/amazon-prime-day-2022-aws-for-the-win/ https://aws.amazon.com/blogs/aws/amazon-prime-day-2022-aws-f...
- redditor98654 4y agoMany (in fact I would bet most) other AWS services themselves use DynamoDB under the hood for various control-plane and data-plane uses-cases. It is not unimaginable that the actual traffic to DynamoDB service in total at regular daily peaks would be in the nine or ten digits per second. Granted, these are across different AWS accounts, across different tables but still delivered by a single logical service.
- petre 4y agoToo bad it doesn't support a whole bunch of MySQL features which probably makes it a non starer for us. Some of them are quite boring, like auto increment. https://vitess.io/docs/14.0/reference/compatibility/mysql-compatibility/ https://vitess.io/docs/14.0/reference/compatibility/mysql-co... It's more dissapointment on my part rather than criticism.
- tyre 4y agoThey document why auto_increment has issues across shards and the sequence solution that they’ve developed. https://vitess.io/docs/14.0/reference/features/vitess-sequences/ https://vitess.io/docs/14.0/reference/features/vitess-sequen... Is there a use case that sequences don’t handle for you?
- petre 4y agoYeah, we would need to redesign everything from the ground up. And sequences are only useful on unsharded keyspace.
- harshitgangal 4y agoSequences actual use is for sharded keyspace. For unsharded you can just continue to use auto_increment. Maybe your application has some special logic which is complex enough to unwind. I am just clarifying for the second statement.
- tuneloud0 4y ago[flagged]
- captain_coffee 4y ago
- PeterZaitsev 4y agoI think as headline "one million queries per second" is not particularly exciting these days. MySQL 8 had shown more than 2mil simple queries/sec few years ago and more than 1 million just IO bound queries https://www.mysql.com/why-mysql/benchmarks/mysql/ https://www.mysql.com/why-mysql/benchmarks/mysql/ The great thing with PlannetScale and Vitess is what it is horizontally scalable while MySQL itself is not, though you need higher headline number for things to get exciting.