4 ms·

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 ca

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

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 ?