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...).
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.
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?
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.
> 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.
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).
> 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...
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.
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.
> 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 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.
> 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..
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!
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.
> 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.
> 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.
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.
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.
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.
46 comments
[ 2.8 ms ] story [ 106 ms ] threadI'm very curious as to what the system resource usage looked like during these tests.
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...).
I don't know why that sort of thing isn't included in client libraries, at least as an option.
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.
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?
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.
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.
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.
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).
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...
Why can't it just be built into the database so you don't have another layer to deploy and manage ?
That said, it sounds like off-roading in a Corvette. You're gonna want to use PgBouncer or PgPool or something else altogether.
What?
In 2019? I guess I'll stick with mysql then.
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'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...
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.
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.
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...
In any case, DBMS can usually scale vertically relatively well (lots of CPU, Ram, NVME etc).
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
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.
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.