4 ms·

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 copyin

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