Thread: BUG #18675: Postgres is not realasing memory causing OOM

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

> On 28 Oct 2024, at 09:30, PG Bug reporting form <noreply@postgresql.org> wrote:

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

Are you running with or without JIT?

--
Daniel Gustafsson

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:        

or maybe
PostgreSQL should include garbage collection?

RAMforPG = shared_buffers + (temp_buffers + work_mem) * max_connections;

On 28 Oct 2024, at 09:30, PG Bug reporting form <noreply@postgresql.org> wrote:

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.

Are you running with or without JIT?

Maciej Jaros <eccenux@gmail.com> writes:
> Daniel Gustafsson (28.10.2024 14:34):
>> Are you running with or without JIT?

> Hi. Yes, JIT is enabled:
> ^                    name ^   value ^
> |                     jit |      on |

Does the behavior improve if you set jit = off?

We're really grasping at straws here: without a great deal more
information than you've provided, it's impossible for anyone else
to investigate this report.  Ideally we'd ask for a self-contained
reproducer.

            regards, tom lane

On 10/28/24 19:07, Maciej Jaros wrote:
> David G. Johnston (28.10.2024 14:42):
>> On Monday, October 28, 2024, PG Bug reporting form
>> <noreply@postgresql.org> wrote:
>>
>>     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:        
>>
>>
>>     or maybe
>>     PostgreSQL should include garbage collection?
>>
>>
>> Garbage collection is typically used in relation to a programming
>> language feature to make writing applications in those languages
>> easier.  Applications themselves don’t really implement garbage
>> collection.  And C, the language PostgreSQL, is written in, doesn’t
>> have garbage collection. To our knowledge, though, there are no
>> significant memory leaks in supported versions.
>>
>>
>>     RAMforPG = shared_buffers + (temp_buffers + work_mem) *
>>     max_connections;
>>
>>
>> The expression: work_mem * max_connections is incorrect.  See the doc
>> for work_mem for how it is used.
>>
>> There is so much more info needed to conclude there is a bug here -
>> which there probably is not.  Exploring the query and tuning the
>> system is better discussed on the -general mailing list.
>>
>> David J.
>>
> 
> Could you share what would be the correct expression to calculate or at
> least estimate max RAM usage then? I've checked and haven't found
> anything in the docs. I've found that expression in user space. I know
> autovac might need to be accounted for, but as said we are not using it.
> How would this estimation of 20GB go to 50GB?
> 

Unfortunately there's no universal formula, because it depends on what
queries you run. For example a query that needs to do 10 sorts may need
to use 10 x work_mem, and so on. Yes, this is unfortunate, we'd like to
have a per-session memory limit, but we don't have that. So the only
recommendation is to set these limits conservatively, not too close to
the available memory limit.

Also, if you really found a memory leak, these formulas are pointless. A
memory leak is usually about "breaking" such limits, and we may not even
know about all memory that gets allocated (for external libraries).

> There just seem to be no limit in RAM usage so it does seem like a
> memory leak. It just grows until there is no more RAM available an we
> restart the service. There are same operations, same connections
> (pooling on the Java side) and it just grows everyday. It seem to be a
> memory leak.It doesn't seem to have an end.
> 

The question is how you define a memory leak. All memory allocated by a
query (using "our" infrastructure) is tied to a "memory context" and
should be released at the end. It's possible for a query to allocate a
lot of memory, perhaps even not release right away, but it should be
released at the end of a query. I'm not going to rule out a bug that
breaks this (e.g. by using a long-lived memory context), but it's very
unlikely we'd not find that pretty soon.

Also, the memory leak seems to be permanent - in your chart the memory
usage grows over a week. Presumably your queries are shorter than that,
so that's not consistent with this type of memory leak.

What I think might be more likely is that you're using something that
allocates memory by directly calling malloc() - say, an extension using
some external library, etc. These allocations are completely outside our
control, and if not freed explicitly, would be a "normal" memory leak.

This is why people were asking about JIT earlier. JIT relies on llvm,
which allocates memory directly, and so a bug in LLVM could leak memory
like this. The first thing to do is to disable JIT, and see if the
memory leak goes away. If it does, then we know it's either a bug in
LLVM, or in how we use it (e.g. we may not trigger cleanup).

But all this is just a wild guess. We don't even know if you're using
some other extensions which might also leak memory, etc.


regards

-- 
Tomas Vondra

Maciej Jaros <eccenux@gmail.com> writes:
> So, that comment led me to suggest adding some kind of process. I called it
> a garbage collector, but maybe David is right; maybe that’s not accurate.
> Anyway, that process, in my view, could try to actually release memory to
> the system to prevent the OOM killer from doing its bidding. Is that
> possible?

We do not even know what is causing your problem, let alone know a
way that one process could command another one to stop doing it.
So no, this suggestion is not helpful.

            regards, tom lane

On 10/29/24 13:26, Maciej Jaros wrote:
> Thanks, Tomas. That helped me understand the problem and the comments a
> bit more. To answer some questions about our setup and possible causes
> of the issues:
> 
>   * We are not using any non-standard extensions. We use PL/pgSQL in
>     some maintenance scripts, but that extension is built-in, so I guess
>     you didn't mean that.
>   * This is mostly a default configuration (aside from memory and CPU
>     adjustments). More specifically all of the JIT options are in their
>     default states (none are set).

OK. Yesterday you posted this:

^                    name ^   value ^
|                     jit |      on |
|          jit_above_cost |  100000 |
|   jit_debugging_support |     off |
|        jit_dump_bitcode |     off |
|         jit_expressions |      on |
|   jit_inline_above_cost |  500000 |
| jit_optimize_above_cost |  500000 |
|   jit_profiling_support |     off |
|            jit_provider | llvmjit |
|     jit_tuple_deforming |      on |

Which means the JIT is enabled.

>   * For context about connections, we have 10 app servers (Tomcat, JDK
>     17, Hibernate 5) connecting to the database in question, each with
>     around 20 active connections in practice (pooling, though some
>     schedules might add a bit). There is also a data warehouse with
>     pooling and should not exceed 20 connections. So, in practice, we
>     have around 250 connections, not the 500 we have in |
>     max_connections| setting. Also most of the connections are idle most
>     of the time. So at least our |max_connections| is quite
>     conservative, I think.

Seems like that. You haven't shared any information about how much
memory is used by individual backends, but it might be interesting to
look at that, and check if the memory usage is high for some subset of
backends (say, those for the warehouse).

>   * We terminate all queries running longer than 30 minutes. Typical
>     queries are below 1 second, and Java responses are mostly limited to
>     20 seconds. Additionally, most queries have a limit of 25 (25 items
>     per page).

That just supports my speculation this is not the in-query memory leak
where we allocate memory in a memory context, because that'd be freed at
the end of a query. I'm not sure about what happens to memory allocated
by LLVM if a query gets interrupted because of a timeout. How often do
queries hit the 30-minute limit?

>   * The application is in use from 8 am to 6-7 pm, and it is mostly idle
>     at night. There is some maintenance at night (including vacuum and
>     vacuumlo). RAM availability stays flat at night, and I would expect
>     it to drop at some point around 7-8 pm. RAM usage on separate app
>     servers does drop after hours.
> 
> So, yes, that RAM usage is strange, and that’s why I reported it. It
> doesn’t seem like this is a problem unique to us. I found questions
> about memory usage on Stack Overflow, like this one, for example:
> HowtolimitthememoryavailableforPostgreSQLserver <https://
> stackoverflow.com/questions/28844170/how-to-limit-the-memory-that-is-
> available-for-postgresql-server>. There is a comment there that seems to
> describe what could be a bug (looks like a bug to me). Maybe not a bug-
> bug, but definitely an unwanted behavior:
> 
>     Note that even if postgres logically releases memory it has
>     allocated, it may not be returned to operating system depending on
>     the |malloc()|/|free()| implementation of your execution
>     environment. That may result in multiple PostgreSQL processes
>     getting over the limit due use of hash aggregation as described
>     above and the memory is never released to OS even though PostgreSQL
>     isn't actually using it either. This happens because technically |
>     malloc()| may use |brk()| behind the scenes and releasing memory
>     back to OS is only possible only in some special cases.
> 

That's not a bug, that's what glibc does for everything in user space.
Yes, it can interfere with overcommit if you have the limit set too llow
(but that's not your case), and most of the time it's not an issue
thanks to virtual memory etc. It also should not lead to indefinite
growth, the memory should be reused for future allocations.

For the "memory limit", it's true we don't have a way to do that, but
it's also not clear it'd actually help in any way. If you have a memory
leak in the JIT code, that's completely outside our control - we don't
even know how much memory LLVM allocated etc. so this would not be
covered by the limit.

> So, that comment led me to suggest adding some kind of process. I called
> it a garbage collector, but maybe David is right; maybe that’s not
> accurate. Anyway, that process, in my view, could try to actually
> release memory to the system to prevent the OOM killer from doing its
> bidding. Is that possible? I don’t know, don't know inner workings of
> PG. I also don’t understand why calling |free| would not release memory.
> I’m also not sure if that description of malloc/free is accurate, but it
> does seem to align with what I’m seeing.
> 

There are different ways to define garbage collection, but memory
contexts could be seen as doing that. Of course, that's only "our" side,
it has no impact on what happens in glibc. That's a different layer, we
have no visibility into that.

Anyway, people have already suggested you try disabling JIT by setting

jit = off

and see it that fixes the issue. If yes, that significantly narrows the
area where the bug could be.


regards

-- 
Tomas Vondra

> On 31 Oct 2024, at 13:21, Maciej Jaros <eccenux@gmail.com> wrote:

> >> We terminate all queries running longer than 30 minutes....
> > That just supports my speculation this is not the in-query memory leak where we allocate memory in a memory
context,because that'd be freed at the end of a query. I'm not sure about what happens to memory allocated by LLVM if a
querygets interrupted because of a timeout. How often do queries hit the 30-minute limit? 
>
> Very rarely. Aside from bad deployments, long-running query kills happen maybe once a week or even once a month. It
dependson the load (e.g. had more last month due to the beginning of the school year). 

The LLVMContextRef remains, currently for 100 queries as a heuristic since at
time ot of the OOM fix commit there was no way of interrogating LLVM for how
large the memory context was.

IIRC this memory leak (which is capped in 16.4) only happens when performing
inlining, raising the cost limit for inlining with jit_inline_above_cost and
re-running problematic queries could give interesting insights.

--
Daniel Gustafsson

> On 8 Nov 2024, at 11:29, Maciej Jaros <eccenux@gmail.com> wrote:
>
> I can now confirm that JIT is adding significant amount of memory on top of what PG SQL is using. Memory reserved by
PGstill grows every day, but doesn't grow as much as with JIT. So there is still something wrong with Postgres. 

Using memory isn't necessarily something wrong, it would be a lot worse if it
needed the memory and used swap instead.  Are you able to try with JIT inlining
turned off as I asked about upthread.

--
Daniel Gustafsson