Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Measuring the Memory Overhead of a Postgres Connection

blog.anarazel.de

Post reply on HN
Print view View original

11–20 of 30 posts

Re: Measuring the Memory Overhead of a Postgres Connection

#11

Post by anarazel »

eloff wrote: post #9

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 mis…

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

Re: Measuring the Memory Overhead of a Postgres Connection

#12

Post by andrewf »

anarazel wrote: post #8
ezekiel68 wrote: post #7

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:…

> 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 benchmar…

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.

Re: Measuring the Memory Overhead of a Postgres Connection

#13

Post by GauntletWizard »

anarazel wrote: post #5
aeyes wrote: post #3

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.

> 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 signif…

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.

Re: Measuring the Memory Overhead of a Postgres Connection

#14

Post by anarazel »

andrewf wrote: post #12
anarazel wrote: post #8

Earlier quoted context omitted.

> 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 benchmar…

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…

Pooling has a huge effect in this regard when using statement level pooling. Which isn't suitable for all that many workloads. With transaction level pooling it's still significant, but latency / processing effects become significant. And even tx pooling has a lot of issues - lack of proper prepared statements support being the biggest.

The last issue really could be solved, but to my knowledge none of the common poolers do.

Imo the medium-long term solution here is so separate out connection from process / thread. Neither deals well with many realistic and important workloads (with the overhead being smaller but significant with threads). It's a huge project with lots of prerequisites though (aio, threading, non recursive query execution)...

Edit: s/poolling/pooling/

Re: Measuring the Memory Overhead of a Postgres Connection

#15

Post by andrewf »

anarazel wrote: post #14
andrewf wrote: post #12

Earlier quoted context omitted.

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…

Pooling has a huge effect in this regard when using statement level pooling. Which isn't suitable for all that many workloads. With transaction level pooling it's still significant, but latency / processing effects become significant. And even tx pooling has a lot of issues - lack of proper prepared statements support being the biggest. The last issue really could be solved, but to my knowledge none of the common poo…

You're right, most of my experience here is using statement level pooling.

Re: Measuring the Memory Overhead of a Postgres Connection

#16

Post by anarazel »

GauntletWizard wrote: post #13
anarazel wrote: post #5

Earlier quoted context omitted.

> 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 signif…

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 o…

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.

Re: Measuring the Memory Overhead of a Postgres Connection

#17

Post by GauntletWizard »

anarazel wrote: post #16
GauntletWizard wrote: post #13

Earlier quoted context omitted.

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 o…

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 tha…

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.

Re: Measuring the Memory Overhead of a Postgres Connection

#18

Post by etaioinshrdlu »

aeyes wrote: post #3

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.

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

Re: Measuring the Memory Overhead of a Postgres Connection

#20

Post by scurvy »

aeyes wrote: post #3

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.

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.
Post reply on HN