MySQL Challenge: 100k Connections(percona.com)
percona.com
MySQL Challenge: 100k Connections
https://www.percona.com/blog/2019/02/25/mysql-challenge-100k-connections/
46 comments
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.
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.
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.
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.
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.
Relevant discussion on this topic: https://news.ycombinator.com/item?id=18425923
Is the response time the query time !? I think over one second would be considered unacceptable in most deployments.
Nice. All the queries are SELECT operations from the same table, so there are no locking issues. No writes.
next time try jmeter or tsung instead of sysbench
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.
I'm very curious as to what the system resource usage looked like during these tests.
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...).
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...).
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?
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?
> What worries you about the 100K number and what would a better number be?
I can't imagine the point.
The whole architecture just sounds ridiculous.
> 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.
Even 100 app servers is stupid. 4 second response time is stupid. Just build two big ones. I can handle (low) tens of trillions of queries per day on a single box (low µs response time, pipelined) -- and the only reason I even need two is because one might die for uncontrollable hardware reasons.
The only reason I ever buy more servers than two is because I want geographic diversity, and I can cover the world with ads with around 10 pops. I can't imagine having 100k pops sending MySQL queries to a single place.
So yeah: I would really want to hear the use-case here.
One time I met a company who insisted they were sending tens of TB of data per day and would need multi-PB per year storage compressed. Took one look at the data: All json, all GUIDS and bools. If we just pre-parse it, the entire dataset for a year fits in a few 100s GB uncompressed -- literally could fit on a macbook air for most of the year. Absolutely insane the kind of build-out people do to "win" at numbers that don't matter...
I can't imagine the point.
The whole architecture just sounds ridiculous.
> 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.
Even 100 app servers is stupid. 4 second response time is stupid. Just build two big ones. I can handle (low) tens of trillions of queries per day on a single box (low µs response time, pipelined) -- and the only reason I even need two is because one might die for uncontrollable hardware reasons.
The only reason I ever buy more servers than two is because I want geographic diversity, and I can cover the world with ads with around 10 pops. I can't imagine having 100k pops sending MySQL queries to a single place.
So yeah: I would really want to hear the use-case here.
One time I met a company who insisted they were sending tens of TB of data per day and would need multi-PB per year storage compressed. Took one look at the data: All json, all GUIDS and bools. If we just pre-parse it, the entire dataset for a year fits in a few 100s GB uncompressed -- literally could fit on a macbook air for most of the year. Absolutely insane the kind of build-out people do to "win" at numbers that don't matter...
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.
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.
> 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.
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.
> 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.
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.
> 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).
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).
That maybe a typical way for PostgreSQL but many other databases don't need a connection pooler.
The notion that the connection pool should be kept small also applies to other databases, such as Oracle: https://www.youtube.com/watch?v=xNDnVOCdvQ0
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 ?
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.
InnoDB as a service!
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.
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
I'd like to see this with PostgreSQL
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 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.
PostgreSQL uses processes not threads which tend to be heavier.
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.
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.
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.
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...
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.
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.
> 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.
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.
FWIW, I don't think CSN actually gets us that far, because the data structures to map xids to CSNs aren't that cheap to maintain either (which is needed for MVCC visibility tests).
Prepared statements actually work fine with PGBouncer. Anonymous prepared statements via PqExecParams work completely without issue. No need to go back to escaping and putting params in the query string!
Named prepared statements via PREPARE also work, but the client needs to use deterministic names and rescue if the query isn’t prepared: https://github.com/diesel-rs/diesel/issues/1028#issuecomment...
Named prepared statements via PREPARE also work, but the client needs to use deterministic names and rescue if the query isn’t prepared: https://github.com/diesel-rs/diesel/issues/1028#issuecomment...
I was just about to comment the same thing. We use PgBouncer for some of our production databases and we make heavy use of prepared statements, stored procedures, etc.
> 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.
There's several PG developers aware of this, and hope to work on this. Getting the resources to work on it isn't trivial, but looks like it might happen. Obviously that's not going to help you short term...
There's several PG developers aware of this, and hope to work on this. Getting the resources to work on it isn't trivial, but looks like it might happen. Obviously that's not going to help you short term...
[deleted]
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).
In any case, DBMS can usually scale vertically relatively well (lots of CPU, Ram, NVME etc).
Neat. Is there a reason not to tune MySQL to this for all instances?
> 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.
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.
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