5 ms·

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 CP

by 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

by taffer 8y ago

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