Message-ID: <cc488d1c-6477-4e20-8bd0-cf152a2e59bf@gmail.com>
Date: Thu, 10 Oct 2024 10:59:13 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Reference to - BUG #18349: ERROR: invalid DSA memory alloc
 request size 1811939328, CONTEXT: parallel worker
To: Craig Milhiser <craig@milhiser.com>
Cc: Thomas Munro <thomas.munro@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 pgsql-bugs@lists.postgresql.org
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>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CA+wnhO2sHcUOfi6_gvaoPMk=2DsKUOWkDjdUtGxJOguQOXDvOQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/cc488d1c-6477-4e20-8bd0-cf152a2e59bf%40gmail.com>
Precedence: bulk

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