Message-ID: <0c8fdad2-e14b-8415-95b2-9e82dc28b2bd@enterprisedb.com>
Date: Wed, 1 Mar 2023 11:40:51 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.8.0
Subject: Re: Memory leak from ExecutorState context?
To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
 Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-hackers@lists.postgresql.org
References: <20230228190643.1e368315@karst>
 <20230228182508.GA30529@telsasoft.com> <20230301104612.7799b105@karst>
Content-Language: en-US
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <20230301104612.7799b105@karst>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/0c8fdad2-e14b-8415-95b2-9e82dc28b2bd%40enterprisedb.com>
Precedence: bulk


On 3/1/23 10:46, Jehan-Guillaume de Rorthais wrote:
> Hi Justin,
> 
> On Tue, 28 Feb 2023 12:25:08 -0600
> Justin Pryzby <pryzby@telsasoft.com> wrote:
> 
>> On Tue, Feb 28, 2023 at 07:06:43PM +0100, Jehan-Guillaume de Rorthais wrote:
>>> Hello all,
>>>
>>> A customer is facing out of memory query which looks similar to this
>>> situation:
>>>
>>>   https://www.postgresql.org/message-id/flat/12064.1555298699%40sss.pgh.pa.us#eb519865575bbc549007878a5fb7219b
>>>
>>> This PostgreSQL version is 11.18. Some settings:  
>>
>> hash joins could exceed work_mem until v13:
> 
> Yes, I am aware of this. But as far as I understand Tom Lane explanations from
> the discussion mentioned up thread, it should not be ExecutorState.
> ExecutorState (13GB) is at least ten times bigger than any other context,
> including HashBatchContext (1.4GB) or HashTableContext (16MB). So maybe some
> aggregate is walking toward the wall because of bad estimation, but something
> else is racing way faster to the wall. And presently it might be something
> related to some JOIN node.
> 

I still don't understand why would this be due to a hash aggregate. That
should not allocate memory in ExecutorState at all. And HashBatchContext
(which is the one bloated) is used by hashjoin, so the issue is likely
somewhere in that area.

> About your other points, you are right, there's numerous things we could do to
> improve this query, and our customer is considering it as well. It's just a
> matter of time now.
> 
> But in the meantime, we are facing a query with a memory behavior that seemed
> suspect. Following the 4 years old thread I mentioned, my goal is to inspect
> and provide all possible information to make sure it's a "normal" behavior or
> something that might/should be fixed.
> 

It'd be interesting to see if the gdb stuff I suggested yesterday yields
some interesting info.

Furthermore, I realized the plan you posted yesterday may not be the
case used for the failing query. It'd be interesting to see what plan is
used for the case that actually fails. Can you do at least explain on
it? Or alternatively, if the query is already running and eating a lot
of memory, attach gdb and print the plan in ExecutorStart

  set print elements 0
  p nodeToString(queryDesc->plannedstmt->planTree)

Thinking about this, I have one suspicion. Hashjoins try to fit into
work_mem by increasing the number of batches - when a batch gets too
large, we double the number of batches (and split the batch into two, to
reduce the size). But if there's a lot of tuples for a particular key
(or at least the hash value), we quickly run into work_mem and keep
adding more and more batches.

The problem with this theory is that the batches are allocated in
HashTableContext, and that doesn't grow very much. And the 1.4GB
HashBatchContext is used for buckets - but we should not allocate that
many, because we cap that to nbuckets_optimal (see 30d7ae3c76). And it
does not explain the ExecutorState bloat either.

Nevertheless, it'd be interesting to see the hashtable parameters:

  p *hashtable


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company