
MySQL Challenge: 100k Connections - PeterZaitsev
https://www.percona.com/blog/2019/02/25/mysql-challenge-100k-connections/
======
zepearl
Personally I would say that the base design of the app is wrong if you have
100k direct connections => I would implement some kind of intermediate layer
that caches and/or schedules DB connections by some sort of priority queue...
.

And "re-connect and repeat if the query is too slow" is probably a killer
(basically a negative "feedback loop" which makes a system that has
performance problems become even slower and slower and slower...).

~~~
andyjpb
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?

~~~
taffer
I don't know how MySQL behaves in this regard, but in Postgres the number of
transactions the system can run concurrently is roughly limited by the number
of CPUs you have available. At some point adding more sessions just means that
you will end up with more lock contention, context switches and RAM usage.

The typical way to solve such problems, i.e. many app servers with hundreds of
connections, is to use a dedicated connection pooler such as PgBouncer.

~~~
anarazel
> I don't know how MySQL behaves in this regard, but in Postgres the number of
> transactions the system can run concurrently is roughly limited by the
> number of CPUs you have available. At some point adding more sessions just
> means that you will just end up with more lock contention, context switches
> and RAM usage.

That's only true if your transactions are all busy most of the time. Which in
a lot of OLTPish workloads isn't actually the case. Both latency and the fact
that applications need time to process such queries ensure that. IOW, I think
this is better phrased as concurrently running queries, rather than concurrent
transactions.

~~~
taffer
> That's only true if your transactions are all busy most of the time.

And this is actually what you want from a performance point of view: Reduce
the number of roundtrips and keep your transactions as short as possible.

Once a transaction locks a row or table, the lock persists regardless of
whether the transaction is active or inactive, resulting in resource
contention. The same applies to memory: if each connection has 10 MB of
memory, 10 GB of memory is wasted for every 100 unused connections that could
otherwise have been used for caching.

~~~
anarazel
> And this is actually what you want from a performance point of view: Reduce
> the number of roundtrips and keep your transactions as short as possible.

Uhm, obviously. But it turns out there's other constraints too. Like whether
optimizing for that to a significant degree in each application is worthwhile
and/or realistically being done. And latency is a thing either way. It's
extremely rare to not have briefly idle connections in OLTP context, even more
so if the app code isn't heavily optimized for it (but even if).

------
PeterZaitsev
There is Proxy/Connector approaches - ProxySQL, pgPool etc solve this problem
in a different way and often is best approach but I still think it is very
interesting to see how much the database itself can handle.

~~~
Diggsey
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.

~~~
ioltas
There is a range of interesting problems here for Postgres. One is related to
the costly way snapshots are built which needs to scan all entries in PGPROC
to get back a list of transaction IDs. So even idle connections come at a
cost. There have been proposed patches to improve the scalability in this
area, one being called CSN (Commit Sequence Number) which I think is rather
promising: [https://www.postgresql.org/message-
id/CA+CSw_tEpJ=md1zgxPkjH...](https://www.postgresql.org/message-
id/CA+CSw_tEpJ=md1zgxPkjH6CWDnTDft4gBi=+P9SnoC+Wy3pKdA@mail.gmail.com)

~~~
Diggsey
Interesting, but surely snapshots are only needed for connections with active
transactions?

My understanding was that the difficulty here was Postgres's currently very
simple forked-process-per-connection model. In order for idle connections to
take minimal resource, connections can no longer be mapped 1:1 with processes.
Instead, active connections will have to be balanced across a process or
thread pool, whilst idle connections' states will have to be stored in a
master process.

Not only that, but it must be possible to transmit a full connection state
between processes, and also to queue-up idle connections transitioning to the
active state if there is a sudden burst exceeding the size of the process
pool.

~~~
anarazel
> Interesting, but surely snapshots are only needed for connections with
> active transactions?

Right, but the problem is that the cost to compute them essentially scales
with the number of active connections (and to some degree with the number of
allowed connections). Which means if your system actually does things, all
those idle connections cost. We can, and should, make the constant factor for
computing this considerably smaller. But obviously larger architectural issues
need to be addressed too, at some point..

Edit: expand.

------
anikdas
> Using multiple IP addresses on the server box (one IP address per
> approximately 60k connections)

Please correct me if I am wrong. I thought the '64K' limit is _per client per
server port_. So the correct statement would be _Using multiple IP addresses
on the server box (one IP address per approximately 60k connections per
client)_. Also, it's not necessary to have multiple interfaces unless it is
required to achieve more than '64k' connection per client.

Edit: updated text formatting

------
penagwin
Interesting write up, although I'm sure there are some overlooked variables as
there often are that others here will likely point out.

I'm very curious as to what the system resource usage looked like during these
tests.

------
Blubberlutsch
Are the connections and operations really done concurrent?

Some time ago I stress tested a consul server and while I was happy that it
suited our needs I found out that only about ~300 connections/requests were
concurrently handled instead of the ~5000 our benchmark originally reported
(was some time ago so numbers are as I remember them).

It was due to some TCP connections were taking a long time to actually be
established, during which others were already done and served. This resulted
in a wide spread of measured latencies for requests.

~~~
gtowey
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.

------
Halluxfboy009
For this number of connections you want to disable JIT in sysbench with the
–luajit-cmd=off option. The option is available in 1.1 prereleases. In 1.0 the
same could be achieved by adding a single line (“jit.off()”) to
oltp_common.lua.

------
taffer
Relevant discussion on this topic:
[https://news.ycombinator.com/item?id=18425923](https://news.ycombinator.com/item?id=18425923)

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

~~~
scottlamb
> 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.

------
z3t4
Is the response time the query time !? I think over one second would be
considered unacceptable in most deployments.

------
Animats
Nice. All the queries are SELECT operations from the same table, so there are
no locking issues. No writes.

------
paulryanrogers
I'd like to see this with PostgreSQL

~~~
paulddraper
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.

------
Thermolabile
next time try jmeter or tsung instead of sysbench

