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

21–30 of 30 posts

Re: Measuring the Memory Overhead of a Postgres Connection

#21

Post by anarazel »

GauntletWizard wrote: post #17
anarazel wrote: post #16

Earlier quoted context omitted.

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

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

Re: Measuring the Memory Overhead of a Postgres Connection

#22

Post by nullsense »

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…

>Few real world scenarios have clients sending queries back-to-back without any gaps in between.

I would have thought so too until I saw our app in production run 1000s of queries in a single domain operation.

It's not the highest quality codebase I've ever worked on to say the least.

Re: Measuring the Memory Overhead of a Postgres Connection

#23

Post by ahachete »

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…

I agree. I normally recommend multiplying the number of cores by a 2-5x factor, and then dividing all that by a subjective "% utilization of the connection", which is obviously a value lower than 1 and results in an increased value for max_connections.

This % of utilization accounts for factors like the session not sending back-to-back queries, the io_wait on the server, which is turn potentially derived by the speed of your I/O subsystem, etc.

However, I'd also advise to only look at the TPS peak as the optimal point. That is indeed typically achieved at high connection numbers. Postgres is reasonably good at managing large number of connections, in terms of throughput. However, latency may go to the roof.

So to find the peak more latency than tps needs to be considered IMO (or, obviously, both).

Re: Measuring the Memory Overhead of a Postgres Connection

#24

Post by eternalban »

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…

> Imo the medium-long term solution here is so separate out connection from process / thread.

I thought process forking was a fundamental aspect of Posgres' architecture. Is it correct to read above as effectively rearchitecting Postgres?

Re: Measuring the Memory Overhead of a Postgres Connection

#25

Post by aeyes »

etaioinshrdlu wrote: post #18
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.

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.

Re: Measuring the Memory Overhead of a Postgres Connection

#26

Post by anarazel »

eternalban wrote: post #24
anarazel wrote: post #14

Earlier quoted context omitted.

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…

> Imo the medium-long term solution here is so separate out connection from process / thread. I thought process forking was a fundamental aspect of Posgres' architecture. Is it correct to read above as effectively rearchitecting Postgres?

> I thought process forking was a fundamental aspect of Posgres' architecture. Is it correct to read above as effectively rearchitecting Postgres?

Is it a small change? Certainly not. But it'd also not touch the majority of the code.

There have been prototypes for just going to threading by liberally sprinkling thread-local markers around - it works, but is fairly ugly. But more incrementally working towards that is quite doable.

Re: Measuring the Memory Overhead of a Postgres Connection

#27

Post by eloff »

anarazel wrote: post #11
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…

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

Re: Measuring the Memory Overhead of a Postgres Connection

#28

Post by anarazel »

eloff wrote: post #27
anarazel wrote: post #11

Earlier quoted context omitted.

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…

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

> must be crazy if they're not aggressively using pgbouncer to keep the number of connections down.

With the limitations around PG poolers that's not always that easy :(

I've worked on a number of very large postgres instances - the backend memory usage wasn't usually a major issue for the very large ones (i.e. not schema sharded ones, where it's a large issue). The TLB miss ratio however was a major bottleneck on them, even with huge pages (perhaps even because of huge pages, because the TLB for huge pages used to be so small).

> 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". [...] > > To Tom Lane: I told you so :)

The project is bigger than Tom Lane ;)

I think it's been pretty clear that threads have more advantages than disadvantages (which are substantial - hello mmap_sem) for quite a while. But that doesn't necessarily mean that we should have changed it a couple years back - as you say, it's not a small change, and it'll cause some disruption. There arguably were (and perhaps are) more crucial issues.

> That's going to be one hell of a job to change now though.

Yea. But it's doable. Personally I think the PG internal changes not the hardest parts - that's having to deal with all the extension out there. Both to ensure that they are adapted, but also managing the pain of having to deal with all the API evolution.

Re: Measuring the Memory Overhead of a Postgres Connection

#29

Post by aeyes »

darrenpergf wrote: post #6
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.

Is there another framework/paradigm to overcome a)?

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

Re: Measuring the Memory Overhead of a Postgres Connection

#30

Post by anarazel »

aeyes wrote: post #29
darrenpergf wrote: post #6

Earlier quoted context omitted.

Is there another framework/paradigm to overcome a)?

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

That doesn't actually address the "overhead of an idle connection" bit meaningfully:

https://techcommunity.microsoft.com/t5/azure-database-for-po...

Post reply on HN