From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: eccenux@gmail.com
Subject: BUG #18675: Postgres is not realasing memory causing OOM
Date: Mon, 28 Oct 2024 08:30:05 +0000
Message-ID: <18675-39a45505fb472bf0@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18675
Logged by: Maciej Jaros
Email address: eccenux@gmail.com
PostgreSQL version: 16.4
Operating system: Ubuntu 22.04
Description:
Hi. We have a DB server running on a VM hosted on Proxmox (ZFS). We recently
upgraded from PG10 to PG16 and noticed an increase in load and, most
importantly, **uncontrolled** RAM usage growth. In my calculations RAM usage
should stay under 20GB. In practice, we found that even 50GB wasn’t enough
for PostgreSQL, as OOM killer killed the service. What’s strange is that
once OOM kills PostgreSQL, the memory drops to zero, indicating that nothing
else was using that memory. After the OOM, PostgreSQL runs fine again, but
requires intervention. So as a workaround, we’re manually restarting it from
time to time.
This is a bug, right? I mean, surely PostgreSQL shouldn't exceed 20GB, maybe
30GB if I missed something. I've searched the web and found posts asking how
to set an absolute memory limit for PostgreSQL, only to learn that no such
setting exists. Perhaps there should be an absolute limit, or maybe
PostgreSQL should include garbage collection?
RAM for PG calculation (autovac is disabled, cron runs at night instead):
```
shared_buffers = 12544
temp_buffers = 8
work_mem = 6422/1024
max_connections = 500
RAMforPG = shared_buffers + (temp_buffers + work_mem) * max_connections;
console.log({RAMforPG}); -> // 19679.74
```
One of SQL causing large tables to be scanned:
```
SELECT
this_.id as y0_,
this_.pfx as y1_,
this_.base as y2_,
this_.type as y3_,
lower(this_.pfx) as pfxLowerCol,
lower(this_.base) as baseLowerCol
from
"m6187".AuthorEntry this_
where
this_.bibliographicDatabaseId = 566757
and (
exists (
select
daee_.AUTHORENTRY_ID as y0_
from
"m6187".DOCUMENT_AUTHOR daee_
inner join "m6187".Document doc1_ on daee_.DOCUMENT_ID = doc1_.id
where
daee_.AUTHORENTRY_ID = this_.id
and not doc1_.hidden = '2'
)
or exists (
select
daee_.AUTHORENTRY_ID as y0_
from
"m6187".DOCUMENT_AUTHOR daee_
inner join "m6187".Document doc1_ on daee_.DOCUMENT_ID = doc1_.id
where
daee_.AUTHORENTRY_ID = this_.NORMALFORM_ID
and not doc1_.hidden = '2'
)
or exists (
select
daee_.AUTHORENTRY_ID as y0_
from
"m6187".DOCUMENT_AUTHOR daee_
inner join "m6187".AuthorEntry ae1_ on daee_.AUTHORENTRY_ID = ae1_.id
inner join "m6187".Document doc2_ on daee_.DOCUMENT_ID = doc2_.id
where
ae1_.NORMALFORM_ID = this_.id
and not doc2_.hidden = '2'
)
or exists (
select
daee_.AUTHORENTRY_ID as y0_
from
"m6187".DOCUMENT_AUTHOR daee_
inner join "m6187".AuthorEntry ae1_ on daee_.AUTHORENTRY_ID = ae1_.id
inner join "m6187".Document doc2_ on daee_.DOCUMENT_ID = doc2_.id
where
ae1_.NORMALFORM_ID = this_.NORMALFORM_ID
and not doc2_.hidden = '2'
)
)
order by
baseLowerCol asc,
pfxLowerCol asc
limit 25
```
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, eccenux@gmail.com
Subject: Re: BUG #18675: Postgres is not realasing memory causing OOM
In-Reply-To: <18675-39a45505fb472bf0@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox