PostgreSQL processes use large amount of private memory on Windows

From: Øystein Kolsrud <kolsrud(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: PostgreSQL processes use large amount of private memory on Windows
Date: 2020-09-17 09:19:07
Message-ID: CAH_oh=wk5i36whfUKhPvnTfR6KsU_CzRTXecNaAtY86HWb57YQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a product that uses PostgreSQL (v9.6) as database on Windows, and we
often observe that the "shared working set" memory reported for the
individual connection processes is quite high (around 200MB), but the
"private working set" is relatively low (around 5 MB). I assume this is due
to the processes utilizing the shared buffers of Postgres, and in these
cases everything works fine as the total memory consumption on the system
is quite low. But for some deployments we have observed that the "private
working set" is around 200 MB as well, and in these cases the server
quickly runs out of memory when the number of connections rises. I have
never been able to reproduce this behavior myself.

So my question is: When does a postgres process forked for a connection use
private memory instead of shared, and what can I do to avoid this?

The reference documentation (
https://www.postgresql.org/docs/9.6/runtime-config-resource.html) describes
the settings affecting memory usage including the "shared_buffers" setting
which I can see has a clear impact on the "shared working set" utilization,
but I find no mention of potential "private working set" consumers. This
page also gives clues about performance tuning, but again no mention of
private memory:
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

All settings typically referred to (like "work_mem" and "temp_buffers") are
at default settings, and I have a very hard time seeing how those settings
could add up to memory in the magnitude of 200MB. The "max_connections"
settings is quite high (>1000), but there are nowhere near that many
connections active in the system. The only way I have been able to trigger
growth of processes in my tests is to extract large amounts of data, but
when I do that, it is always the "shared working set" that grows. "private
working set" remains stable at around 5 MB.
--
Best regards, Øystein Kolsrud

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Yessica Brinkmann 2020-09-17 10:46:05 Re: Problems with MemoryContextSwitchTo ()
Previous Message Pavel Stehule 2020-09-17 06:03:57 Re: Problems with MemoryContextSwitchTo ()