From: Justin Pryzby <pryzbyj@telsasoft.com>
Subject: [PATCH v1] account for size of BatchFile structure in hashJoin
Date: Sun, 21 Apr 2019 05:04:09 -0500
Avoid runaway number of Batches, overhead of which was previously not counted.
The size of BatchFiles is now included in instrumentation for output of explain
analyze.
Analyzed-By: Tom Lane, Tomas Vondra, Justin Pryzby
Discussion: bc138e9f-c89e-9147-5395-61d51a757b3b@gusw.net
---
src/backend/executor/nodeHash.c | 29 +++++++++++++++++++++++++++--
1 file changed, 27 insertions(+), 2 deletions(-)
diff --git a/src/backend/executor/nodeHash.c b/src/backend/executor/nodeHash.cindex ee93030..9a87265 100644--- a/src/backend/executor/nodeHash.c+++ b/src/backend/executor/nodeHash.c@@ -1670,10 +1670,33 @@ ExecHashTableInsert(HashJoinTable hashtable,
hashtable->spaceUsed += hashTupleSize;
if (hashtable->spaceUsed > hashtable->spacePeak)
hashtable->spacePeak = hashtable->spaceUsed;
++ /*+ * If addition of 2x as many batch files would cause the structure itself+ * (without the hashtable) to exceed spaceAllowed, then do not grow...unless the+ * spaceUsed has already exceeded Allowed by more than 2x...+ * Note, spaceUsed doesn't include the overhead of BatchFile structure.+ */
if (hashtable->spaceUsed +
hashtable->nbuckets_optimal * sizeof(HashJoinTuple)
- > hashtable->spaceAllowed)- ExecHashIncreaseNumBatches(hashtable);+ // + hashtable->nbatch * sizeof(PGAlignedBlock)+ > hashtable->spaceAllowed) {+ if (hashtable->nbatch*sizeof(PGAlignedBlock) < hashtable->spaceAllowed) {+ ExecHashIncreaseNumBatches(hashtable);+ } else if (hashtable->spaceUsed/2 >= hashtable->spaceAllowed) {+ /* Exceeded spaceAllowed by 2x, so we'll save RAM by allowing nbatches to increase */+ /* I think this branch would be hit almost same as below branch */+ ExecHashIncreaseNumBatches(hashtable);+ } else if (hashtable->nbatch*sizeof(PGAlignedBlock) < hashtable->spaceUsed) {+ /*+ * spaceUsed is so large that it will save RAM to allow the BatchFile structures themselves to exceed work_mem.+ * Increasing batches and exceeding work mem will use less space and be less bad than not increasing...+ */+ elog(WARNING, "exceeding work mem: used:%ld allowed:%ld add:%ld",+ hashtable->spaceUsed, hashtable->spaceAllowed, hashtable->nbatch*sizeof(PGAlignedBlock));+ ExecHashIncreaseNumBatches(hashtable);+ }+ }
}
else
{
@@ -2693,6 +2716,8 @@ ExecHashGetInstrumentation(HashInstrumentation *instrument,
instrument->nbatch = hashtable->nbatch;
instrument->nbatch_original = hashtable->nbatch_original;
instrument->space_peak = hashtable->spacePeak;
+ /* Finally, account for size of structure: */+ instrument->space_peak += hashtable->nbatch * sizeof(PGAlignedBlock);
}
/*
--
2.7.4
--WChQLJJJfbwij+9x--
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-performance@postgresql.org
Cc: pryzbyj@telsasoft.com
Subject: Re: [PATCH v1] account for size of BatchFile structure in hashJoin
In-Reply-To: <no-message-id-5656@localhost>
* 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