Thread: BUG #17269: Why is virtual memory usage of PostgreSQL growing constantly?

The following bug has been logged on the website:

Bug reference:      17269
Logged by:          Yu Kikuchi
Email address:      y.kikuchi0714@gmail.com
PostgreSQL version: 9.6.17
Operating system:   Windows Server 2016
Description:

Hi, our team uses the PostgreSQL 9.6 database.
A few days ago, we changed memory size because the ratio of memory was about
70-80%.
Accordingly, we changed sharred_buffers in postgresql.conf.

Before
・memory:64GB
・shared_buffers:16GB

After
・memory:128GB
・shared_buffers:32GB

The radio of memory is about 20% after we changed memory size, but
PostgreSQL uses virtual memory constantly.
PostgreSQL finally ran out of virtual memory a few hours later. 
Why did PostgreSQL use virtual memory although there is an available memory?

At Wed, 03 Nov 2021 11:11:24 +0000, PG Bug reporting form <noreply@postgresql.org> wrote in 
> The following bug has been logged on the website:
> 
> Bug reference:      17269
> Logged by:          Yu Kikuchi
> Email address:      y.kikuchi0714@gmail.com
> PostgreSQL version: 9.6.17
> Operating system:   Windows Server 2016
> Description:        
> 
> Hi, our team uses the PostgreSQL 9.6 database.
> A few days ago, we changed memory size because the ratio of memory was about
> 70-80%.
> Accordingly, we changed sharred_buffers in postgresql.conf.
> 
> Before
> ・memory:64GB
> ・shared_buffers:16GB
> 
> After
> ・memory:128GB
> ・shared_buffers:32GB
> 
> The radio of memory is about 20% after we changed memory size, but
> PostgreSQL uses virtual memory constantly.
> PostgreSQL finally ran out of virtual memory a few hours later. 
> Why did PostgreSQL use virtual memory although there is an available memory?

Could you show us some settings?  Maybe max_connections and work_mem
could be the starter.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

At Fri, 5 Nov 2021 18:15:01 +0900, 菊池祐 <y.kikuchi0714@gmail.com> wrote in 
> I attached postgresql.conf file.

> max_connections = 10000            # (change requires restart)

I think the moderate setting of max_connection is 100 to 200 or 300 on
a 128GB box. Depending on workload but it seems that you need to
reduce it to the same range.  1000 might be possible for super-light
weight workload but it is almost definite that 10000 don't fit 128GB
memory, or on CPUs with 16-32 cores.

I'm sure the clients are executing queries with quite low
frequency. Maybe you want to use connection pooler such like Pgpool-II
or pgbouncer, yandex/odyssey if the clients need to maintain their
connections to the server for a long time.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

At Tue, 9 Nov 2021 15:52:51 +0900, 菊池祐 <y.kikuchi0714@gmail.com> wrote in 
> Thank you for the useful information.
> I checked the number of connections, and I found there are about 3,000
> connections from clients now.
> I try to reduce it.
> 
> Let me check additionally.
> 1. This event that the virtual memory usage of PostgreSQL grows is due to a
> large number of connections.

I suspect so.  Since shared_buffers is 32GB, I suppose the remaining
96GB is consumed by backend processes.  By a quite-coarse estimation,
96GB per 3000 clients is 32MB per client. It looks like a bit large
but it depends on workload.  We could investigate the memory usage by
the clients but I'm not sure how to examine the memory usage of as
many as 3000 processes on Windows..

> 2. I didn't know that the moderate setting of max_connection is 100 to 200
> or 300 on
> a 128GB box. Where is it written in the PostgreSQL manual?

You can read a report from PGECons.  It is an annual report on a
benchmarking of PostgreSQL. It is not about Windows, but Linux on
128GB box. And not memory usage but performance against the number of
clients. But I expect it informative for you.

https://www.pgecons.org/wp-content/uploads/2021/05/1d9c4e24e6906bcf785905f243a62608.pdf

For example, with a simple workload made by pgbench, a server is
saturaged with 192 clients or 8000 tps of referenceing workload.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center