13 ms·

MySQL Challenge: 100k Connections

200 points by PeterZaitsev 8y ago

penagwin 8y ago
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.
zepearl 8y ago
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...).
- paulryanrogers 8y ago
  A query time out is probably a better approach
  - tyingq 8y ago
    Exponential back off based on number of retries helps a lot. I don't know why that sort of thing isn't included in client libraries, at least as an option.
    - tracker1 8y ago
      That would be nice, though complex over a lot of client libraries... have had to do it manually for ETL pipelines before, not fun.
- avar 8y ago
  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.
  - smueller1234 8y ago
    InnoDB as a service!
- orestes910 8y ago
  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.
- andyjpb 8y ago
  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 8y ago
    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 8y ago
      > 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 8y ago
        > 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.
- threeseed 8y ago
  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 ?
paulryanrogers 8y ago
I'd like to see this with PostgreSQL
- justryry 8y ago
  Look at the numbers for MySQL without the thread-pooling and that will probably be close enough.
  - PeterZaitsev 8y ago
    PostgreSQL uses processes not threads which tend to be heavier.
- paulddraper 8y ago
  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.
  - dcplogic 8y ago
    > since it uses process-per-connection What? In 2019? I guess I'll stick with mysql then.
  - mrtweetyhack 8y ago
    that's racist. Haha.
- anarazel 8y ago
  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.
- gnulinux 8y ago
  That'll be like CGI. Not scalable. You're supposed to use something like PgBouncer.
PeterZaitsev 8y ago
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 8y ago
  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.
  - anarazel 8y ago
    > 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...
  - ioltas 8y ago
    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=md1zgxPkjH6CWDnTDft4gBi=+P9SnoC+Wy3pKdA@mail.gmail.com https://www.postgresql.org/message-id/CA+CSw_tEpJ=md1zgxPkjH...
stevebmark 8y ago
Neat. Is there a reason not to tune MySQL to this for all instances?
- scottlamb 8y ago
  > 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.
taffer 8y ago
Relevant discussion on this topic: https://news.ycombinator.com/item?id=18425923 https://news.ycombinator.com/item?id=18425923
anikdas 8y ago
> 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
Animats 8y ago
Nice. All the queries are SELECT operations from the same table, so there are no locking issues. No writes.
Blubberlutsch 8y ago
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 8y ago
  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.
Thermolabile 8y ago
next time try jmeter or tsung instead of sysbench
z3t4 8y ago
Is the response time the query time !? I think over one second would be considered unacceptable in most deployments.
Halluxfboy009 8y ago
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.

200 points by PeterZaitsev 8y ago

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.

zepearl 8y ago

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

paulryanrogers 8y ago
A query time out is probably a better approach
- tyingq 8y ago
  Exponential back off based on number of retries helps a lot. I don't know why that sort of thing isn't included in client libraries, at least as an option.
  - tracker1 8y ago
    That would be nice, though complex over a lot of client libraries... have had to do it manually for ETL pipelines before, not fun.
avar 8y ago
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.
- smueller1234 8y ago
  InnoDB as a service!
orestes910 8y ago
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.
andyjpb 8y ago
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 8y ago
  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 8y ago
    > 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 8y ago
      > 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.
threeseed 8y ago
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 ?

paulryanrogers 8y ago

I'd like to see this with PostgreSQL

justryry 8y ago
Look at the numbers for MySQL without the thread-pooling and that will probably be close enough.
- PeterZaitsev 8y ago
  PostgreSQL uses processes not threads which tend to be heavier.
paulddraper 8y ago
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.
- dcplogic 8y ago
  > since it uses process-per-connection What? In 2019? I guess I'll stick with mysql then.
- mrtweetyhack 8y ago
  that's racist. Haha.
anarazel 8y ago
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.
gnulinux 8y ago
That'll be like CGI. Not scalable. You're supposed to use something like PgBouncer.

PeterZaitsev 8y ago

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 8y ago
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.
- anarazel 8y ago
  > 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...
- ioltas 8y ago
  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=md1zgxPkjH6CWDnTDft4gBi=+P9SnoC+Wy3pKdA@mail.gmail.com https://www.postgresql.org/message-id/CA+CSw_tEpJ=md1zgxPkjH...

stevebmark 8y ago

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

scottlamb 8y ago
> 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.

taffer 8y ago

Relevant discussion on this topic: https://news.ycombinator.com/item?id=18425923 https://news.ycombinator.com/item?id=18425923

anikdas 8y ago

> 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

Animats 8y ago

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

Blubberlutsch 8y ago

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 8y ago
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.

Thermolabile 8y ago

next time try jmeter or tsung instead of sysbench

z3t4 8y ago

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

Halluxfboy009 8y ago

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.