Measuring the Memory Overhead of a Postgres Connection
blog.anarazel.de
Measuring the Memory Overhead of a Postgres Connection
1–10 of 30 posts
Re: Measuring the Memory Overhead of a Postgres Connection
#2If 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...
Re: Measuring the Memory Overhead of a Postgres Connection
#3Let'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.
Re: Measuring the Memory Overhead of a Postgres Connection
#4Let'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.
Using a process per connection instead of a thread is another reason. MySQL uses threads and seems to spin up connections with lower overhead.
Re: Measuring the Memory Overhead of a Postgres Connection
#5Let'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.
> 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-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.
Re: Measuring the Memory Overhead of a Postgres Connection
#6Let'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.
Is there another framework/paradigm to overcome a)?
Re: Measuring the Memory Overhead of a Postgres Connection
#7I 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
[1] https://momjian.us/main/blogs/pgblog/2018.html#December_7_20...
Re: Measuring the Memory Overhead of a Postgres Connection
#8I 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:…
> 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 clientserver 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, 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.
Re: Measuring the Memory Overhead of a Postgres Connection
#9I 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.
Re: Measuring the Memory Overhead of a Postgres Connection
#10I 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 mis…
[deleted]