pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Andrei Lepikhov <lepihov@gmail.com>
To: Craig Milhiser <craig@milhiser.com>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Reference to - BUG #18349: ERROR: invalid DSA memory alloc request size 1811939328, CONTEXT: parallel worker
Date: Thu, 10 Oct 2024 10:59:13 +0700
Message-ID: <cc488d1c-6477-4e20-8bd0-cf152a2e59bf@gmail.com> (raw)
In-Reply-To: <CA+wnhO2sHcUOfi6_gvaoPMk=2DsKUOWkDjdUtGxJOguQOXDvOQ@mail.gmail.com>
References: <CA+wnhO1OfgXbmXgC4fv_uu=OxcDQuHvfoQ4k0DFeB0Qqd-X-rQ@mail.gmail.com>
	<2878480.1727017360@sss.pgh.pa.us>
	<CA+wnhO1zP13p5sn6d-top=-gmCnE+ymYEfkPbN2d=QKWMbp6Sg@mail.gmail.com>
	<CA+hUKGJo9H75w3QoZrZCVm3eGg7ifJ0UvZZMw6bz=HCpwg0gKw@mail.gmail.com>
	<CA+hUKG+YZpOyP-XXT65o9hx=oGF+9-BJzjqzRXcdvWV=ZrJbTA@mail.gmail.com>
	<CA+wnhO2NDZFWB1TrZga-uLzpfSDjKH5Axa2d2+1Q76JSsS7yMg@mail.gmail.com>
	<CA+wnhO0S2kxJ2_M57uJXRocMpcDtz6N4RkP-3Xy=pFs1wp7hmg@mail.gmail.com>
	<CA+hUKGLPKWamfKjtEkPLvRDSeOqoZZ0wDfU65d-hF-1upL8iGw@mail.gmail.com>
	<CA+wnhO3kT7mxbadbteppSZVcs3FRPvGL5xTY2DbrWSGohmhyVw@mail.gmail.com>
	<6785e9e8-4c64-41b0-9e72-c13d1822b599@gmail.com>
	<CA+wnhO0VdTP9OVdZP9bmkavma2WkE-pYpP1yW8yDwRrJ97j-2g@mail.gmail.com>
	<0c3b0e1b-55d1-42b6-8c3c-4392479187db@gmail.com>
	<CA+wnhO2sHcUOfi6_gvaoPMk=2DsKUOWkDjdUtGxJOguQOXDvOQ@mail.gmail.com>

On 10/9/24 23:28, Craig Milhiser wrote:
> On Oct 8 Andrei Lepikhov wrote
>  > I'm a bit confused: the thread subject named ' invalid DSA memory alloc
>  > request size ...', but you write about issue with OOM killer. It is two
>  > different issues, which one do you have exactly?
> 
> 
> I started with the Invalid DSA memory allocation error. I was asked to 
> try an experimental patch above. Then I got OOM with the patch only 
> running parallel. You will see below, there was an OOM but I do not 
> believe it is the query.
So, I think the patch works, but you found out one more issue at the 
same query. Awesome!

> I ran a new test on an instance with 512 GiB of memory.  After I applied 
> the patch, the Invalid DSA memory allocation message was not 
> replicated.  Running max_parallel_workers_per_gather = 0, the query took 
> ~9.5 seconds and used <1 GiB of memory.  With 
> max_parallel_workers_per_gather = 2 the query used  ~170 GiB of memory, 
> ~70 GB of temp files were written and the query ran for more than 1 hour 
> until I ran out of disk space.
It's fascinating. I have one user report like that, but they also didn't 
provide any synthetic test. I think it is almost impossible to create 
such reproduction without a minimal understanding of what's happening. I 
can imagine only a data skew or a logical bug in this part of the code. 
But without direct perf and gdb touch, it is hard to resolve the issue 
by just gazing into the code.
Additional actions can provide some food for thought:
1. If you remove aggregates (STRING_AGG, count) from the selection list, 
will the problem remain? What about OFFSET 0?
2. Can you build extended statistics on account_id,disabled,user_type_id 
and provide an explain (and explain analyse)?
3. Can you use pg_query_state (unfortunately, it needs a patch and 
re-compilation) and show us intermediate execution state snapshots?
4. I see a duplicate clause in the query: SGM.OBJECT_ID = U.OBJECT_ID. 
For what reason you have it here? can you remove it from the query?
5. One more wild guess: can you analyse how much NULLS contains column 
u.users_id at the moment when HashJoin evaluates clause (u.users_id = 
cf.user_id)?

[1] https://github.com/postgrespro/pg_query_state

-- 
regards, Andrei Lepikhov






view thread (27+ messages)  latest in thread

Message-ID: <cc488d1c-6477-4e20-8bd0-cf152a2e59bf@gmail.com>
Permalink:  ../cc488d1c-6477-4e20-8bd0-cf152a2e59bf@gmail.com/
Also on:    postgresql.org/message-id/cc488d1c-6477-4e20-8bd0-cf152a2e59bf@gmail.com

 · 

reply
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: lepihov@gmail.com, craig@milhiser.com, thomas.munro@gmail.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
  Subject: Re: Reference to - BUG #18349: ERROR: invalid DSA memory alloc request size 1811939328, CONTEXT: parallel worker
  In-Reply-To: <cc488d1c-6477-4e20-8bd0-cf152a2e59bf@gmail.com>

* 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