One million queries per second with MySQL

https://planetscale.com/blog/one-million-queries-per-second-with-mysql

by aarondf • 4 years ago

213 103 4 years ago

leetrout• 4 years ago

I 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• 4 years ago

I would like to see a bench using the “common” setup and not the enterprise one… what are the limits on those?

forgot_old_user• 4 years ago

says 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• 4 years ago

Very 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• 4 years ago

We used 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-s.... For these tests, we ran with --tables=1 and --scale=1000.

chreniuc• 4 years ago

I'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?

orware2• 4 years ago

You 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 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• 4 years ago

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

minhazm• 4 years ago

It's depends on how far the two data centers are from one another. But the big cloud providers have data centers in similar locations since they usually want to be near an internet exchange[1]. For example most companies that provide cloud services have a data center in Virginia, USA. So if both data centers are in the same region then you can get numbers in the sub 5ms range. As long as you're not crossing the ocean the latency should be fine.

1. https://en.wikipedia.org/wiki/List_of_Internet_exchange_poin...

truth_seeker3• 4 years ago

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

harshitgangal2• 4 years ago

Disclaimer: 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_seeker3• 4 years ago

Thanks for info.

mattlord3• 4 years 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. :-)

paulryanrogers2• 4 years ago

There is also Aurora Postgres, though I've heard it still suffers from vacuum issues for some workloads.

In my limited experience DB scaling usually necessitates knowing some specific quirks of whatever implementation is chosen. Otherwise their side effects or sharp edges will cause problems.

gen220• 4 years ago

Postgres FDW [1] seems to me to be a crazy cool (i.e. unexpected and good) feature of Postgres, but I haven't seen much chatter about it on HN in the rdbms stories.

Has anyone here used it? What is/was your experience like?

[1] https://www.postgresql.org/docs/current/postgres-fdw.html

sroussey3• 4 years ago

That’s a great feature but not al all comparable for a deployment.

The closest thing for PostgreSQL would be Citus, I imagine.

aeyes2• 4 years ago

FDW is not nearly as usable as you think. Joins of even 2 or 3 remote tables with more than a few hundred rows in the join are enough to make simple queries take minutes to return even if you tune all the settings and have statistics of remote tables.

Also, how do you reshard without downtime?

truth_seeker3• 4 years ago

(Plz excuse the brevity. I am using my mobile phone to reply)

Have you read this page carefully especially the section "remote execution option"

https://www.postgresql.org/docs/current/postgres-fdw.html

If not give it a try.

Also this one might help too.

https://www.percona.com/blog/2021/06/02/postgres_fdw-enhance...

Now, about re-sharding:

I know this is not as care free as in NewSQL dbs like CockroachDB but its not that difficult either.

Choosing a shard key ultimately comes down to breaking down your ER diagram and inspecting the incoming write flow to certain tables which are bound to grow.

In any of the cases you don't have to let your user suffer from downtime. At least I have not. Even if you chose wrong shard key, You can gracefully transfer or re-distribute the data records by creating new tables and renaming then with old ones. The window of appearant downtime here is few milliseconds. This is not something which will happen frequently.

tiffanyh4• 4 years ago

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

rbranson• 4 years ago

Apples to oranges comparison. The benchmark in the PlanetScale blog post is based on TPC-C.

SnowHill9902• 4 years ago

And with awk you can query 10M lines per second.

bitobserver• 4 years ago

"Granted, it’s not SQLite by itself. We’ve wrapped it in a custom distributed transaction layer ..."

tiffanyh4• 4 years ago

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

mattlord3• 4 years ago

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

DiggyJohnson• 4 years ago

Genuinely appreciative of this comment. I feel like I learned/refreshed a lot of (should be) “common” knowledges about the subject of DBs.

throwusawayus• 4 years ago

i strongly dislike planetscale and even i think this is not a useful or fair comparison .. sqlite works with local files, much simpler, no network latency, no HA

kamikaz1k• 4 years ago

why do you dislike t?

marginalia_nu2• 4 years ago

i strongly dislike planetscale and even i think this is not a useful or fair comparison .. sqlite works with local files, much simpler, no network latency, no HA

To be fair, if you accomplish the same effect without needing distributed computing, then the solution gets much simpler, you cut out worrying about network latency and HA*

* the probability of node failure grows very rapidly with the number of nodes. Same reason you probably don't need RAID for 1 disk, but you definitely do for a dozen

samlambert4• 4 years ago

I respect your intellectual honesty here.

renewiltord• 4 years ago

My personal DB solution actually averages 45 GiB / second on my laptop on a single thread and scales 1-1 with threads. It's way faster than SQLite. This is painfully mediocre performance.

You have to make engineering trade-offs for this, though. In my case, you can only recover 1 KiB records. It doesn't support compression. And you can only write to /dev/null.

To read data from this DB (which I built with command-line tools any Unix has), run:

   dd if=/dev/zero of=/dev/null bs=1K count=${count}

You can vary the $count parameter to get more data.

obiwahn2• 4 years ago

Scales 1:1 with threads:-) Nice Trolling ;)

Thaxll2• 4 years ago

This is not SQLite though, also the test is trivial compared to TPC-C: https://github.com/Expensify/Bedrock/blob/dbarrett_perftest/...

Only read all in memory lol.

prescriptivist2• 4 years ago

No one is running their OLTP and batch processing workloads on sqlite (bedrock or not) at scale. There may be unique cases for specific situations but show me an org with large and broad concurrency demands running sqlite at scale and I'm happy to be proven wrong.

hansvm2• 4 years ago

That sounds like a leadership problem rather than an indictment of sqlite

zmxz• 4 years ago

So what you're saying is that the same type of queries were ran in expensify's case and in planetscale's case, therefore we should not be excited for running 1m queries per second using MySQL?

hbrn3• 4 years ago

I'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?

samlambert4• 4 years ago

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

tiffanyh4• 4 years ago

When you have to eliminate data relationships for it to scale, you no longer have a "relational" database.

hbrn3• 4 years ago

A relational database without relations is an oxymoron. As folks pointed out, you also have to throw ACID away. So what's left of the original database, SQL-dialect? I bet that gets limited too.

Look, I get it, you have to sell your product. Some folks want semi-infinitely scalable storage, and they don't understand that the only way to achieve it is turning their DB into a key-value store. As a side effect they would have to rewrite their whole application from scratch, but they would only realize it after they get vendor locked in.

You can advertise your solution as MySQL-compatible. And I can claim that it's dishonest.

29athrowaway• 4 years ago

Not all queries are the same.

samsquire• 4 years ago

HandlerSocket 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-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• 4 years ago

What 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• 4 years ago

How much hardware did you need?

loxias• 4 years ago

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

obiwahn2• 4 years ago

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

TedShiller4• 4 years ago

This is cool but the problem is you're on MySQL

osrec• 4 years ago

Why is that a problem?

TedShiller4• 4 years ago

Everyone is moving away from MySQL to Postgres

KronisLV• 4 years ago

Why is that a problem?

I feel like this will inevitably lead to some not-very-productive discussions, as it already seems to have started in some sibling comments.

Personally, I have to admit that there's definitely a trend of looking more in the direction of PostgreSQL, which is a pretty great choice - it follows SQL standards pretty well, has adequate performance, advanced procedural language with support for custom types and even nice projects like PostGIS, which let you work with geospatial data (previewing which is even integrated in pgAdmin). In addition, it has support for JSON (though personally I find the syntax uncomfortable) and other plugins as well, whilst being able to scale both up and down pretty far.

That said, I still think that MySQL/MariaDB is a sensible choice for lots of use simpler use cases, where you don't necessarily need custom types and advanced in-database processing with procedural languages (which in my opinion can be risky to begin with, mostly due to challenges in regards to testing and debugging such logic, outside of cases where you need to process a large amount of data and sending it over the network would be disastrous).

MySQL/MariaDB has a simpler structure (databases and users, instead of databases, schemas and users), good performance, very good driver support, excellent tooling (in my opinion, MySQL Workbench is ahead of pgAdmin, especially reverse/forward engineering and the whole ER visualization functionality), passable resource usage (a bit more RAM at idle and under load than PostgreSQL in my experience, at least with default container setup which you may/may not want to alter) and a nice ecosystem around it - be it clustering solutions or integrations with almost any language that you might want to use.

Also, projects like TiDB are interesting to behold, even though personally I wouldn't want to give up foreign key support for dynamic clustering.

At the end of the day, I think that either is a better choice than being stuck with Oracle which just feels more cumbersome and isn't as easy to run wherever you need it, licensing being a concern all of the sudden (and Oracle XE not letting you use certain features either, such as dynamic indices, which you might want to test on your dev environment/locally). Also, the modelling functionality on SQL Developer leaves a lot to be desired.

Cannot really comment on the SQL Server offering, because haven't worked a lot with it.

For anyone who's on the fence about what RDBMS to use, just pick whatever looks interesting from the top 10-20 here, do some benchmarks, read what others have said and come to your own conclusions for any given project: https://db-engines.com/en/ranking

It's hard to go absolutely wrong nowadays with any of the popular technologies (that have proven that they can survive for a decade or longer and still receive updates), unless you really misinterpret what would be a reasonable fit for a given workload.

chinabot• 4 years ago

I'm sure you could find some edge-case scenarios YOU dont like with MySQL but all round its a pretty sensible and stable choice if thats what you want to work with.

paulryanrogers2• 4 years ago

IDK that rename-table corruption bug was brutal around 8.14 or so. Configured properly (legacy defaults were kind of janky) and only running vetted versions is probably OK.

didip• 4 years ago

Too bad Vitess cannot be generalized to sit on top of PostgreSQL

skyde2• 4 years ago

Check out cirrusDB, its pretty much the same thing as Vitess but for PostgreSQL

https://docs.microsoft.com/en-us/azure/postgresql/hyperscale...

achanda358• 4 years ago

Sounds like you mean CitusDB

endisneigh3• 4 years ago

Given 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• 4 years ago

Don'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

endisneigh3• 4 years ago

The main reason to use a kv store is performance and shard-ability

AdamProut3• 4 years ago

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

derekperkins2• 4 years ago

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

AdamProut3• 4 years ago

Yep, 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• 4 years ago

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

AdamProut3• 4 years ago

That's true, I should have been a bit more specific.

Each individual query runs within a single shard as far I recall (its been a few years...). There maybe transactions (like new order) that do multiple queries such that the transaction does read and writes to more then one shard. This doesn't really change how easy the workload is to scale out too much though. Harder to scale workloads require a single query to run across many (often all) shards or require data reshuffling for joins (there is not one good shard key that keeps all joins shard local). In these cases adding more nodes or shards will make each query a bit slower.

midislack• 4 years ago

On what hardware? We need a standard computer for these "benchmarks."

nixgeek• 4 years ago

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

redditor98654• 4 years ago

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

petre2• 4 years ago

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

It's more dissapointment on my part rather than criticism.

tyre• 4 years ago

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

Is there a use case that sequences don’t handle for you?

petre2• 4 years ago

Yeah, we would need to redesign everything from the ground up. And sequences are only useful on unsharded keyspace.

PeterZaitsev• 4 years ago

I 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/

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.

One million queries per second with MySQL

Related Stories