Re: PostgreSQL processes use large amount of private memory on Windows - Mailing list pgsql-general
| From | Øystein Kolsrud |
|---|---|
| Subject | Re: PostgreSQL processes use large amount of private memory on Windows |
| Date | |
| Msg-id | CAH_oh=w0xGsVPGdMQEOBhOo=zu-s_+6pu+QXvTz82TPKSiLXqg@mail.gmail.com Whole thread Raw |
| In response to | Re: PostgreSQL processes use large amount of private memory on Windows ("Peter J. Holzer" <hjp-pgsql@hjp.at>) |
| List | pgsql-general |
On Thu, Sep 17, 2020 at 7:27 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
What I would do:
* Set log_statement to all (warning: that can be a lot of log messages.
It can also be a privacy/security hazard, depending on who has access
to the server and how sensitive queries are).
* Frequently (at least once per minute) record the size of all postgres
processes. Send an alert if one of them is "too large".
This should give you a good idea what the processes were doing at the
time they allocated that memory, so that you can reproduce the problem.
In fact I already did track the memory behavior over time and recorded memory utilization every 5 minutes. The memory consumption exhibits a surprisingly linear growth of about 400 MB per hour. And since I can't see any sharp points where the memory jumps I can't really identify any single query culprit that is causing this. It seems more like there are a large number of small queries that are causing the memory to stack up over time.
And again, the real mystery here is that we don't see this behavior in most deployments of the product which all utilize the database in the same way. This leads me to think that there is some configuration on the server side that is somehow affecting memory allocation. It's almost as if the shared memory allocation between processes is not working and that all processes are holding a copy of the shared memory instead of actually sharing.
--
Best regards, Øystein Kolsrud
On Thu, Sep 17, 2020 at 7:27 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
On 2020-09-17 11:19:07 +0200, Øystein Kolsrud wrote:
> 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.
[...]
> 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 work_mem setting is per operation (e.g. sort, hash join, etc.), so a
complex query may use a multiple of it. However, the default is just 4 MB,
so a query would have to be very complex to use 50 times as much. Also,
that memory is freed after the query (however "freed" does not
necessarily mean "returned to the OS", it can just mean "marked as
availiable for reuse" - that depends on the C library and the OS).
What I would do:
* Set log_statement to all (warning: that can be a lot of log messages.
It can also be a privacy/security hazard, depending on who has access
to the server and how sensitive queries are).
* Frequently (at least once per minute) record the size of all postgres
processes. Send an alert if one of them is "too large".
This should give you a good idea what the processes were doing at the
time they allocated that memory, so that you can reproduce the problem.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
--
Mvh Øystein Kolsrud
pgsql-general by date: