From: Tomas Vondra <tomas@vondra.me>
To: Cameron Vogt <cvogt@automaticcontrols.net>
To: Michael Paquier <michael@paquier.xyz>
To: Sean Massey <sean.f.massey@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: PostgreSQL 17 Segmentation Fault
Date: Fri, 4 Oct 2024 21:11:15 +0200
Message-ID: <82ffe6ad-a219-4255-9fea-daff619a1ca0@vondra.me> (raw)
In-Reply-To: <BY5PR16MB31442E65985FE0817C1D2C85AB712@BY5PR16MB3144.namprd16.prod.outlook.com>
References: <6aca4816-be12-4f54-9eba-8c9b55cdc801.094840f8-2c1e-4d5a-9984-8ac00be38080.9eae5fd3-5d56-4ffb-9d4c-3e272ee57cba@emailsignatures365.codetwo.com>
<DM6PR16MB314588EDCBF487F0C1EF7BCEAB702@DM6PR16MB3145.namprd16.prod.outlook.com>
<CAJfn5C0Kqn52wxVqRBaFSyTf4O6Avo6xoLoS76SWysRQWjx4Rw@mail.gmail.com>
<Zv3vkGRGKaCyMyva@paquier.xyz>
<BY5PR16MB31442E65985FE0817C1D2C85AB712@BY5PR16MB3144.namprd16.prod.outlook.com>
Hi,
Thanks for the provided information. Per the backtrace, the failure
happens in the LLVM JIT code in nestloop/seqscan, so it has to be in
this part of the plan:
-> Nested Loop (cost=0.42..6074.84 rows=117 width=641)
-> Parallel Seq Scan on tasks__projects (cost=0.00..2201.62
rows=745 width=16)
Filter: (gid = '1138791545416725'::text)
-> Index Scan using tasks_pkey on tasks tasks_1
(cost=0.42..5.20 rows=1 width=102)
Index Cond: (gid = tasks__projects._sdc_source_key_gid)
Filter: ((NOT completed) AND (name <> ''::text))
But it's not clear why this should consume a lot of memory, though. It's
possible the memory is consumed elsewhere, and this is simply the straw
that breaks the camel's back ...
Presumably it takes a while for the query to consume a lot of memory and
crash - can you attach a debugger to it after after it allocates a lot
of memory (but before the crash), and do this:
call MemoryContextStats(TopMemoryContext)
That should write memory context stats to the server log. Perhaps that
will tell us which part of the query allocates memory.
Next, try running the query with jit=off. If that resolves the problem,
maybe it's another JIT issue. But if it completes with lower shared
buffers, that doesn't seem likely.
The plan has a bunch of hash joins. I wonder if that might be causing
issues, because the hash tables may be kept until the end of the query,
and each may be up to 64MB (you have work_mem=32, but there's also 2x
multiplier since PG13). The row estimates are pretty low, but could it
be that the real row counts are much higher? Did you run analyze after
the upgrade? Maybe try with lower work_mem?
One last thing you should check is memory overcommit. Chances are it's
set just low enough for the query to hit it with SB=4GB, but not with
SB=3GB. In that case you may need to tune this a bit. See /proc/meminfo
and /proc/sys/vm/overcommit_*).
regards
--
Tomas Vondra
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: tomas@vondra.me, cvogt@automaticcontrols.net, michael@paquier.xyz, sean.f.massey@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: PostgreSQL 17 Segmentation Fault
In-Reply-To: <82ffe6ad-a219-4255-9fea-daff619a1ca0@vondra.me>
* 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