4 ms·

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 ea

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.
    - anarazel 8y ago
      > 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).
- threeseed 8y ago
  That maybe a typical way for PostgreSQL but many other databases don't need a connection pooler.
  - taffer 8y ago
    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 https://www.youtube.com/watch?v=xNDnVOCdvQ0
geocar 8y ago
> 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...

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.
  - anarazel 8y ago
    > 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).
threeseed 8y ago
That maybe a typical way for PostgreSQL but many other databases don't need a connection pooler.
- taffer 8y ago
  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 https://www.youtube.com/watch?v=xNDnVOCdvQ0

geocar 8y ago

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