11 ms·

Let's be realistic here: You need a connection pooler because: a) You can't hold 5000 open connections out of which 4900 are idle without performance impact.

by aeyes 6y ago

Let's be realistic here: You need a connection pooler because: a) You can't hold 5000 open connections out of which 4900 are idle without performance impact. b) Opening a new connection takes way longer than connecting through pgBouncer running on a differnt(!) box. It would be nice if memory was the only drawback.

paulryanrogers 6y ago
Using a process per connection instead of a thread is another reason. MySQL uses threads and seems to spin up connections with lower overhead.
anarazel 6y ago
> a) You can't hold 5000 open connections out of which 4900 are idle without performance impact. Author here. I agree. In fact this blog post only exists because a blog post on analyzing the various bottleneck around connection scalability in postgres was getting too long, so I split it out... That post has numbers showing the slowdowns: https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266# https://techcommunity.microsoft.com/t5/azure-database-for-po... There's a significant amount of improvements in Postgres' development branch addressing the - in my opinion - major source for the performance impact of lots of idle connections. > b) Opening a new connection takes way longer than connecting through pgBouncer running on a differnt(!) box. Hm. How long is way longer? And how high is shared buffers on that instance? In tests it's pretty easy to reach a few thousand connection establishments/second. That's not great, but also not terrible. If the instance is otherwise very busy (e.g. due to the above issue), it can make connecting very slow, due to contention on very important locks.
- GauntletWizard 6y ago
  I’ve personally seen pgbouncer do thousands of connection establishments per second without breaking a sweat and also handling immediate queries on many of those connections while running under a tight single-core constraint. That was caused by a thundering herd, and I don’t plan to repeat it, but unless your benchmark was also on a single-core machine, I’d say that’s “significant”; PGBouncer really has no overhead on connection startup, while simply copying the shared buffers table into a new process represents a real overhead for postgres proper. A TCP connection is really lightweight, when stripped down enough. Thousands of connection establishments per second per core is not actually gonna break a sweat.
  - anarazel 6y ago
    I really didn't want to give the impression that I think poolers are useless - I don't, I think they're often crucial. Sorry if I did so. > while simply copying the shared buffers table into a new process represents a real overhead for postgres proper. You mean the page table being large due to the large shared memory allocation? With huge pages that's not that large anymore (132KB for 16GB of s_b). My impression that with huge pages the problem moves to be primarily the locking around the huge pages when a lot of processes constantly fork and exit, than the actual amount of copying.
    - GauntletWizard 6y ago
      I didn’t get that impression at all. I just want to make clear the orders of magnitude we’re talking. I do 100:1 multiplexing with pgbouncer; There’s a few hundred connections open to our Postgres database, from application servers that have tens of thousands of connections open, all multiplexed through pgbouncer. You’re definitely right that hugepages make the allocation pretty small, but it still has to walk the page table to copy it; I’ve seen that be a real bottleneck in apps. Then again, I might be misremembering; The problems could have been simply from having a huge number of 4k pages to walk. What I’m trying to say is: A fork() call can be relatively cheap, but is rarely as cheap as an accept(). The biggest limiting factor in my experience is actually Work Mem. You can reduce it quite a bit from the default (which I was remembering as 10MB/connection, though apparently the default is 4MB; it might be AWS RDS Defaults that I had in my head). Even halving that, to 2MB, it still presents a significant overhead. A thousand connections takes two gigs of memory - Obnoxious, but workable. Ten thousand? Now you’re biting significantly into the size of your working set.
      - anarazel 6y ago
        > A fork() call can be relatively cheap, but is rarely as cheap as an accept(). Agreed, obviously. While I personally am not that concerned with the cost of connection establishment, and much more concerned with the context switches in a threaded / process model, the fix for the latter would also likely fix the former. I think there's a few higher priority issues in PG (some are prerequisites too), but it's somewhere in the top 5 issues > The biggest limiting factor in my experience is actually Work Mem. For me it hasn't been that big an issue in practice. It's transient memory usage, i.e. it's only used in the backends processing queries, not idle ones. And many - but not all! - cases where you have a huge number of connections most queries are simple, and use a good bit less than 2MB. There are a fair number of issues with work_mem, don't get me wrong. But more around it being used several times in more complex queries, than the simple fact of using some memory for query execution. And it being hard to limit the number of concurrent queries rather than the number of connections.
darrenpergf 6y ago
Is there another framework/paradigm to overcome a)?
- aeyes 6y ago
  Connect using Unix sockets. It is just shared memory so you have zero connection overhead. For example, connecting through sockets to pgBouncer saves 50% CPU. Unfortunately there are some kernel tuneables (buffer sizes) which you have to set at compile time to support massive data transfers on sockets. Obviously this only works if you are on the same machine and if you connect directly to Postgres this way, it will happily spawn a process for every query :(.
  - anarazel 6y ago
    That doesn't actually address the "overhead of an idle connection" bit meaningfully: https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266#snapshot-scalability https://techcommunity.microsoft.com/t5/azure-database-for-po...
etaioinshrdlu 6y ago
It kind of sounds like a connection pooler should be built-in to Postgres and turned on by default.
- aeyes 6y ago
  Exactly. Maybe we should look into compiling pgBouncer connection management directly into postmaster if the target platform permits it. I see no downside to doing this and you can probably get it done in <1000 lines of code, most pgBouncer features aren't needed.
scurvy 6y ago
Yet long-lived connections (processes) suffer tremendously on Postgresql servers with high cores (> 100) and a decent amount of memory (> 256GB). Connection pooling is good, but you gotta recycle those connections on a regular basis.

by aeyes 6y ago

paulryanrogers 6y ago

Using a process per connection instead of a thread is another reason. MySQL uses threads and seems to spin up connections with lower overhead.

anarazel 6y ago

> a) You can't hold 5000 open connections out of which 4900 are idle without performance impact. Author here. I agree. In fact this blog post only exists because a blog post on analyzing the various bottleneck around connection scalability in postgres was getting too long, so I split it out... That post has numbers showing the slowdowns: https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266# https://techcommunity.microsoft.com/t5/azure-database-for-po... There's a significant amount of improvements in Postgres' development branch addressing the - in my opinion - major source for the performance impact of lots of idle connections. > b) Opening a new connection takes way longer than connecting through pgBouncer running on a differnt(!) box. Hm. How long is way longer? And how high is shared buffers on that instance? In tests it's pretty easy to reach a few thousand connection establishments/second. That's not great, but also not terrible. If the instance is otherwise very busy (e.g. due to the above issue), it can make connecting very slow, due to contention on very important locks.

GauntletWizard 6y ago
I’ve personally seen pgbouncer do thousands of connection establishments per second without breaking a sweat and also handling immediate queries on many of those connections while running under a tight single-core constraint. That was caused by a thundering herd, and I don’t plan to repeat it, but unless your benchmark was also on a single-core machine, I’d say that’s “significant”; PGBouncer really has no overhead on connection startup, while simply copying the shared buffers table into a new process represents a real overhead for postgres proper. A TCP connection is really lightweight, when stripped down enough. Thousands of connection establishments per second per core is not actually gonna break a sweat.
- anarazel 6y ago
  I really didn't want to give the impression that I think poolers are useless - I don't, I think they're often crucial. Sorry if I did so. > while simply copying the shared buffers table into a new process represents a real overhead for postgres proper. You mean the page table being large due to the large shared memory allocation? With huge pages that's not that large anymore (132KB for 16GB of s_b). My impression that with huge pages the problem moves to be primarily the locking around the huge pages when a lot of processes constantly fork and exit, than the actual amount of copying.
  - GauntletWizard 6y ago
    I didn’t get that impression at all. I just want to make clear the orders of magnitude we’re talking. I do 100:1 multiplexing with pgbouncer; There’s a few hundred connections open to our Postgres database, from application servers that have tens of thousands of connections open, all multiplexed through pgbouncer. You’re definitely right that hugepages make the allocation pretty small, but it still has to walk the page table to copy it; I’ve seen that be a real bottleneck in apps. Then again, I might be misremembering; The problems could have been simply from having a huge number of 4k pages to walk. What I’m trying to say is: A fork() call can be relatively cheap, but is rarely as cheap as an accept(). The biggest limiting factor in my experience is actually Work Mem. You can reduce it quite a bit from the default (which I was remembering as 10MB/connection, though apparently the default is 4MB; it might be AWS RDS Defaults that I had in my head). Even halving that, to 2MB, it still presents a significant overhead. A thousand connections takes two gigs of memory - Obnoxious, but workable. Ten thousand? Now you’re biting significantly into the size of your working set.
    - anarazel 6y ago
      > A fork() call can be relatively cheap, but is rarely as cheap as an accept(). Agreed, obviously. While I personally am not that concerned with the cost of connection establishment, and much more concerned with the context switches in a threaded / process model, the fix for the latter would also likely fix the former. I think there's a few higher priority issues in PG (some are prerequisites too), but it's somewhere in the top 5 issues > The biggest limiting factor in my experience is actually Work Mem. For me it hasn't been that big an issue in practice. It's transient memory usage, i.e. it's only used in the backends processing queries, not idle ones. And many - but not all! - cases where you have a huge number of connections most queries are simple, and use a good bit less than 2MB. There are a fair number of issues with work_mem, don't get me wrong. But more around it being used several times in more complex queries, than the simple fact of using some memory for query execution. And it being hard to limit the number of concurrent queries rather than the number of connections.

darrenpergf 6y ago

Is there another framework/paradigm to overcome a)?

aeyes 6y ago
Connect using Unix sockets. It is just shared memory so you have zero connection overhead. For example, connecting through sockets to pgBouncer saves 50% CPU. Unfortunately there are some kernel tuneables (buffer sizes) which you have to set at compile time to support massive data transfers on sockets. Obviously this only works if you are on the same machine and if you connect directly to Postgres this way, it will happily spawn a process for every query :(.
- anarazel 6y ago
  That doesn't actually address the "overhead of an idle connection" bit meaningfully: https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266#snapshot-scalability https://techcommunity.microsoft.com/t5/azure-database-for-po...

etaioinshrdlu 6y ago

It kind of sounds like a connection pooler should be built-in to Postgres and turned on by default.

aeyes 6y ago
Exactly. Maybe we should look into compiling pgBouncer connection management directly into postmaster if the target platform permits it. I see no downside to doing this and you can probably get it done in <1000 lines of code, most pgBouncer features aren't needed.

scurvy 6y ago

Yet long-lived connections (processes) suffer tremendously on Postgresql servers with high cores (> 100) and a decent amount of memory (> 256GB). Connection pooling is good, but you gotta recycle those connections on a regular basis.