6 ms·

Measuring the Memory Overhead of a Postgres Connection

62 points by samokhvalov 6y ago

anarazel 6y ago
If anybody has a good idea of how to accurately subtract memory that is shared between processes... It's doable with /proc/[pid]/pagemap + /proc/kpagecount, but that's fairly expensive...
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.
- 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.
ezekiel68 6y ago
I have followed various snippets of community folklore advice on this topic over the years (many of them suggesting that the proper number of connections should relate somehow to the number of CPU cores). It was, therefore, refreshing to see one of the core developers, Bruce Momjian, publish modern advice[0] based on empirical observations he made while working on real client problems through Enterprise DB. Spoiler: on modern hardware, without an over-agressive shared_buffer config setting[1], up to 250 direct connections to the DB may be made without detrimental impact. [0] https://momjian.us/main/blogs/pgblog/2020.html#April_22_2020 https://momjian.us/main/blogs/pgblog/2020.html#April_22_2020 [1] https://momjian.us/main/blogs/pgblog/2018.html#December_7_2018 https://momjian.us/main/blogs/pgblog/2018.html#December_7_20...
- anarazel 6y ago
  > Oltp databases typically cannot process more than 5 x cpu cores without having to time-slice among cpus I think this is entirely wrong in reality. In most read-mostly OLTP workloads, due to client<->server latency and application processing times, my experience as well as measurements show that at that connection count the machine will not be utilized sufficiently. 5x may be around the peak throughput when a benchmark client runs on the same system and connects via unix socket or localhost tcp. But even just benchmarking over a fast local (10GBe, <10m cables, one switch) connection moves peak throughput quite a bit higher than that. Few real world scenarios have clients sending queries back-to-back without any gaps in between. They have to process the results e.g. go through some template engine, shuffle the data to the web server. I think for OLTP - and many other workloads - any guidance based on CPU cores is going to be so off for 90% of workloads to be more misleading than useful. The request patterns and thus bottlenecks vary far too much. For write heavy OLTP workloads on medium-high latency storage (cheaper SSDs, all network block stores, spinning disks) a larger number of connections also can actually be good from a cost perspective - due to group commit fewer iops will be needed than when the same workload were split over two instances.
  - andrewf 6y ago
    It's common to put a connection pooler such as pgbouncer in front of postgres, usually in the same datacenter/cloud region, sometimes on the same machine. In that case, the behavior you describe will happen between the clients and the pooler, then the pooler will mux the clients' queries onto a smaller number of connections to the database. In many configs, once all the pgbouncer -> postgres connections are used, any more incoming queries will be queued. Each time pgbouncer gets a result from postgres, it pulls another query off the queue, so if queuing is sustained you'll see back-to-back requests on the pgbouncer -> postgres connections without gaps in between. I agree there's no general CPU core guidance here. Gold standard is to representatively load test your app, safely beyond your target load, or to destruction if nobody can tell you what the target load is.
eloff 6y ago
I think these measurements include the page table entries in the kernel? It's my fuzzy understanding that page table entries are 64bit on amd64, so if you have 64GB of shared buffers (which is quite a lot) you'd have 128 mb overhead just to map that with 4kb pages. Which is to say the overhead is proportional to both the shared buffers size and the page size. It is not a constant factor. Saying it is about 2mb is misleading. It's possible I'm missing something special about postgres, but that's how shared memory between processes works on Unix systems in general. MySQL uses threads which are much lighter weight because they don't need their own page table entries, and they don't need to do as many TLB flushes when switching contexts between them. It's one of the architectural decisions they did right I think. Postgres went with processes so it's easier to kill a connection cleanly, without leaving a things in a bad state. I think there are other ways of solving that problem though.
- deleted 6y ago
  [deleted]
- anarazel 6y ago
  The < 2MB figure is with huge pages (2MB ones in this case) enabled. I used 16GB of shared_buffers. The measurement at the end shows how much of a difference in memory usage that makes: The page tables use 6.5MB per process without huge pages, 132KB with. So sure, you can have shared buffers set so large that it's more than 2MB per process - but there is a fair bit of headroom to 2MB. So I don't think it's particularly misleading. I think threads are the right call, and that we should move to threads at some point. Partially due to the overhead you mention, but more importantly because there's a lot of features that are much harder with separate processes. We have a fair bit of redundant code due to it: one version working with plain pointers, one with pointers differing between processes, do deal with dynamically allocated shared memory for things like parallel query.
  - eloff 6y ago
    It's a decent ballpark estimate, but it's definitely not a constant factor. People with really huge database deployments (like machines with 4TB of ram) must be crazy if they're not aggressively using pgbouncer to keep the number of connections down. I'm actually quite heartened to hear that the attitude among Postgres developers is changing. Back in the day whenever people would bring up threads vs processes they got shouted down because processes are clearly superior and threads were "just implemented as processes on linux anyway". That's going to be one hell of a job to change now though. To Tom Lane: I told you so :)
ramraj07 6y ago
Practical experience with AWS RDS postgres is that an m4.large instance chokes at 150 connections. We are currently planning to test RDSProxy, but does anyone have experience with comparing this service to running pgBouncer ourselves?

62 points by samokhvalov 6y ago

If anybody has a good idea of how to accurately subtract memory that is shared between processes... It's doable with /proc/[pid]/pagemap + /proc/kpagecount, but that's fairly expensive...

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

ezekiel68 6y ago

I have followed various snippets of community folklore advice on this topic over the years (many of them suggesting that the proper number of connections should relate somehow to the number of CPU cores). It was, therefore, refreshing to see one of the core developers, Bruce Momjian, publish modern advice[0] based on empirical observations he made while working on real client problems through Enterprise DB. Spoiler: on modern hardware, without an over-agressive shared_buffer config setting[1], up to 250 direct connections to the DB may be made without detrimental impact. [0] https://momjian.us/main/blogs/pgblog/2020.html#April_22_2020 https://momjian.us/main/blogs/pgblog/2020.html#April_22_2020 [1] https://momjian.us/main/blogs/pgblog/2018.html#December_7_2018 https://momjian.us/main/blogs/pgblog/2018.html#December_7_20...

anarazel 6y ago
> Oltp databases typically cannot process more than 5 x cpu cores without having to time-slice among cpus I think this is entirely wrong in reality. In most read-mostly OLTP workloads, due to client<->server latency and application processing times, my experience as well as measurements show that at that connection count the machine will not be utilized sufficiently. 5x may be around the peak throughput when a benchmark client runs on the same system and connects via unix socket or localhost tcp. But even just benchmarking over a fast local (10GBe, <10m cables, one switch) connection moves peak throughput quite a bit higher than that. Few real world scenarios have clients sending queries back-to-back without any gaps in between. They have to process the results e.g. go through some template engine, shuffle the data to the web server. I think for OLTP - and many other workloads - any guidance based on CPU cores is going to be so off for 90% of workloads to be more misleading than useful. The request patterns and thus bottlenecks vary far too much. For write heavy OLTP workloads on medium-high latency storage (cheaper SSDs, all network block stores, spinning disks) a larger number of connections also can actually be good from a cost perspective - due to group commit fewer iops will be needed than when the same workload were split over two instances.
- andrewf 6y ago
  It's common to put a connection pooler such as pgbouncer in front of postgres, usually in the same datacenter/cloud region, sometimes on the same machine. In that case, the behavior you describe will happen between the clients and the pooler, then the pooler will mux the clients' queries onto a smaller number of connections to the database. In many configs, once all the pgbouncer -> postgres connections are used, any more incoming queries will be queued. Each time pgbouncer gets a result from postgres, it pulls another query off the queue, so if queuing is sustained you'll see back-to-back requests on the pgbouncer -> postgres connections without gaps in between. I agree there's no general CPU core guidance here. Gold standard is to representatively load test your app, safely beyond your target load, or to destruction if nobody can tell you what the target load is.

eloff 6y ago

I think these measurements include the page table entries in the kernel? It's my fuzzy understanding that page table entries are 64bit on amd64, so if you have 64GB of shared buffers (which is quite a lot) you'd have 128 mb overhead just to map that with 4kb pages. Which is to say the overhead is proportional to both the shared buffers size and the page size. It is not a constant factor. Saying it is about 2mb is misleading. It's possible I'm missing something special about postgres, but that's how shared memory between processes works on Unix systems in general. MySQL uses threads which are much lighter weight because they don't need their own page table entries, and they don't need to do as many TLB flushes when switching contexts between them. It's one of the architectural decisions they did right I think. Postgres went with processes so it's easier to kill a connection cleanly, without leaving a things in a bad state. I think there are other ways of solving that problem though.

deleted 6y ago
[deleted]
anarazel 6y ago
The < 2MB figure is with huge pages (2MB ones in this case) enabled. I used 16GB of shared_buffers. The measurement at the end shows how much of a difference in memory usage that makes: The page tables use 6.5MB per process without huge pages, 132KB with. So sure, you can have shared buffers set so large that it's more than 2MB per process - but there is a fair bit of headroom to 2MB. So I don't think it's particularly misleading. I think threads are the right call, and that we should move to threads at some point. Partially due to the overhead you mention, but more importantly because there's a lot of features that are much harder with separate processes. We have a fair bit of redundant code due to it: one version working with plain pointers, one with pointers differing between processes, do deal with dynamically allocated shared memory for things like parallel query.
- eloff 6y ago
  It's a decent ballpark estimate, but it's definitely not a constant factor. People with really huge database deployments (like machines with 4TB of ram) must be crazy if they're not aggressively using pgbouncer to keep the number of connections down. I'm actually quite heartened to hear that the attitude among Postgres developers is changing. Back in the day whenever people would bring up threads vs processes they got shouted down because processes are clearly superior and threads were "just implemented as processes on linux anyway". That's going to be one hell of a job to change now though. To Tom Lane: I told you so :)

ramraj07 6y ago

Practical experience with AWS RDS postgres is that an m4.large instance chokes at 150 connections. We are currently planning to test RDSProxy, but does anyone have experience with comparing this service to running pgBouncer ourselves?