4 ms·

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

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

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.