MySQL Challenge: 100k Connections

What worries you about the 100K number and what would a better number be?

As the article suggests, it's only 1,000 app servers with a connection pool of 100 each.

Alternatively it's 100 servers with a connection pool of 1,000 each. 100 app servers isn't excessive and if you're doing less than 10K concurrent on each of them then there's other profiling opportunities to be had there.

Scaling-up the database as far as it'll go is still a useful thing to do. DB technology over the last 10 years or so has made scaling-out the DB a more realistic prospect but it's still by far the hardest part of the stack to scale-out.

If you can get performance improvements elsewhere before the DB becomes the bottleneck, why not take them?

So instead of a database with 100k direct connections you have some intermediate service with 100k direct connections.

Why can't it just be built into the database so you don't have another layer to deploy and manage ?

You're making a lot of assumptions about how people use MySQL. For a lot of use-cases MySQL is that caching layer. E.g. when it's used to serve up flattened materialized data as essentially a glorified memcached implementation.

As someone who used Percona in production, its fantastic for a small/medium workload, (depending on your definition of those) but it has pretty hard limits on scaling without an intermediate layer, which then mostly defeats its purpose - dead simple clustering.

I agree completely that 100k directs is bad, but they're writing from a perspective where the alternative would likely mean switching to a different product.

A query time out is probably a better approach

Frustratingly "solutions" like pgBouncer and similar do not properly maintain session state in transaction pooling mode and so commonly used features like prepared statements cannot be used, which is a non-starter in many cases.

Meanwhile, session pooling mode provides almost no benefit unless you completely disable connection pooling in your application, which then means you incur the cost of setting up a new connection on each request.

MySQL's ability to handle many thousands of idle connections without breaking a sweat actually solves this problem, and does so in a very convenient way, particularly in a micro-services architecture, and I really wish Postgres supported it.

Or, if you're mostly-read and can separate read vs. write scenarios, then one master for writes and read replicas works pretty well too.

In any case, DBMS can usually scale vertically relatively well (lots of CPU, Ram, NVME etc).

No, that's not what's being tested here. It's testing how the server itself handles managing 100k clients connected simultaneously. Most of those connections are idle most of the time -- but that's still a situation that is not trivial to handle. If you think that alone isn't noteworthy, go ahead and build an application which handles that many connections where they all can do non-trivial work on shared resources.

In most web applications, even if you have 100k connections and are doing something on the order of 30-50k QPS on a single database instance, the number of actual queries executing concurrently at any instant in time is probably only about 10-15. This is because most queries are fast. You could have 1000 queries per second, and if they all take ~1 ms to execute, that could still be a theoretical concurrency of 0.

> Neat. Is there a reason not to tune MySQL to this for all instances?

Yeah, I'd think so. On more modest hardware (less RAM, no 10GbE, etc.), those high buffer sizes may waste RAM that's needed elsewhere. The high number of processes may let some death spiral get much farther into unresponsiveness before stopping it. etc.

And most people don't need it. 100k active queries is way too much for most webapps. That means queries average >=1 sec and/or you're peaking at >= 100 kqps. The former sounds painful from a user latency standpoint. The latter isn't the scale of Google, Facebook, Twitter [1], etc. but is still pretty significant. [2] Do you have enough active users that this rate seems reasonable? If not, your best optimization opportunity is figuring out why you have so much unexplained traffic and if you can make it go away...it could be as simple as buggy client-side Javascript in an aggressive retry loop.

[1] twitter in particular I think has a pretty high fan-out from user-facing requests to database operations. I imagine most latency is hidden from the user because it happens asynchronously (after writes) and/or in parallel.

[2] I'd be curious to see a ranking of public websites by estimated (average/peak/whatever) qps. Seems like something Alexa and such must have but I couldn't find a page with that raw data.

Not gonna happen that soon, sorry. There's a few easy enough changes to improve PG connection scaling considerably, but to get to that count more invasive changes are needed.

It assuredly won't do very well, since it uses process-per-connection.

That said, it sounds like off-roading in a Corvette. You're gonna want to use PgBouncer or PgPool or something else altogether.

That'll be like CGI. Not scalable. You're supposed to use something like PgBouncer.

Look at the numbers for MySQL without the thread-pooling and that will probably be close enough.