From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: Thomas Munro <thomas.munro@gmail.com>
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Cc: a.rybakina <a.rybakina@postgrespro.ru>
Subject: Re: BUG #16925: ERROR: invalid DSA memory alloc request size 1073741824 CONTEXT: parallel worker
Date: Wed, 6 Dec 2023 11:46:08 +0700
Message-ID: <9277414b-dbce-4a32-8aff-642e399e23e5@postgrespro.ru> (raw)
In-Reply-To: <CA+hUKGLYXpGmbWVKjsCJuWahynGORy1Afwra3ZJyk6YWQ16Vaw@mail.gmail.com>
References: <16925-ec96d83529d0d629@postgresql.org>
<CA+hUKGKo5N65N2WG7ywO0Wf7p1gBduB-UJO1QzpQTN1xSxoROg@mail.gmail.com>
<CA+hUKGKYggD1k4Btd4S7HFfXdGJfmvxhv15LtoXpKAxm-GFYDg@mail.gmail.com>
<CA+hUKGJWKafbqQ3+uByP-Ydb4poX+0DPJCTh3bbcneJkbLKV1w@mail.gmail.com>
<CA+hUKGLYXpGmbWVKjsCJuWahynGORy1Afwra3ZJyk6YWQ16Vaw@mail.gmail.com>
On 18/3/2021 16:21, Thomas Munro wrote:
> ===8<===
> shared_buffers=2GB
> fsync=off
> max_wal_size=10GB
> min_dynamic_shared_memory=2GB
> ===8<===
> create table bigger_than_it_looks as
> select generate_series(1, 256000000) as id;
> alter table bigger_than_it_looks set (autovacuum_enabled = 'false');
> alter table bigger_than_it_looks set (parallel_workers = 1);
> analyze bigger_than_it_looks;
> update pg_class set reltuples = 5000000 where relname = 'bigger_than_it_looks';
> ===8<===
> postgres=# set work_mem = '4.5GB';
> SET
> postgres=# explain analyze select count(*) from bigger_than_it_looks
> t1 join bigger_than_it_looks t2 using (id);
> ERROR: invalid DSA memory alloc request size 1073741824
> CONTEXT: parallel worker
> ===8<===
This bug still annoyingly interrupts the queries of some clients. Maybe
complete this work?
It is stable and reproduces on all PG versions. The case:
work_mem = '2GB'
test table:
-----------
CREATE TABLE bigger_than_it_looks AS
SELECT generate_series(1, 512E6) AS id;
ALTER TABLE bigger_than_it_looks SET (autovacuum_enabled = 'false');
ALTER TABLE bigger_than_it_looks SET (parallel_workers = 1);
ANALYZE bigger_than_it_looks;
UPDATE pg_class SET reltuples = 5000000
WHERE relname = 'bigger_than_it_looks';
The parallel workers number impacts size of the allowed memory under the
hash table and in that sense correlates with the work_mem value, needed
for the bug reproduction (keep in mind also that hash_mem_multiplier has
been changed recently).
Query:
SELECT sum(a.id)
FROM bigger_than_it_looks a
JOIN bigger_than_it_looks b ON a.id =b.id
LEFT JOIN bigger_than_it_looks c ON b.id = c.id;
Any query that needs Parallel Hash Join can be found here. The case here
is as follows.
The first batch contains a lot of tuples (on increment, it has about
67mln tuples.). We calculate the number of buckets needed, approximately
134 mln (134217728). Remember, the size of dsa_pointer_atomic is 8 in my
case, and it ends up with an overflow of the max number of DSA, which
can be allocated (1073741823 bytes).
See the new patch in the attachment.
--
regards,
Andrei Lepikhov
Postgres Professional
Attachments:0001-Bugfix.-Guard-total-number-of-hash-table-buckets.patch (text/plain, 1.4K) download
From 9fb610126c607a6ced971d774127f2255ad0bbf8 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Date: Wed, 6 Dec 2023 10:52:05 +0700
Subject: [PATCH] Bugfix. Guard total number of hash table buckets.
It must be arranged with an max number, corresponding to maximum possible
allocated size of DSA memory.
---
src/backend/executor/nodeHash.c | 6 ++++--
1 file changed, 4 insertions(+), 2 deletions(-)
diff --git a/src/backend/executor/nodeHash.c b/src/backend/executor/nodeHash.cindex e72f0986c2..fdba7c85d3 100644--- a/src/backend/executor/nodeHash.c+++ b/src/backend/executor/nodeHash.c@@ -1155,6 +1155,9 @@ ExecParallelHashIncreaseNumBatches(HashJoinTable hashtable)
double dtuples;
double dbuckets;
int new_nbuckets;
+ const uint32 max_buckets =+ pg_prevpower2_32((uint32)+ (MaxAllocSize / sizeof(dsa_pointer_atomic)));
/*
* We probably also need a smaller bucket array. How many
@@ -1168,8 +1171,7 @@ ExecParallelHashIncreaseNumBatches(HashJoinTable hashtable)
*/
dtuples = (old_batch0->ntuples * 2.0) / new_nbatch;
dbuckets = ceil(dtuples / NTUP_PER_BUCKET);
- dbuckets = Min(dbuckets,- MaxAllocSize / sizeof(dsa_pointer_atomic));+ dbuckets = Min(dbuckets, max_buckets);
new_nbuckets = (int) dbuckets;
new_nbuckets = Max(new_nbuckets, 1024);
new_nbuckets = pg_nextpower2_32(new_nbuckets);
--
2.43.0
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: a.lepikhov@postgrespro.ru, thomas.munro@gmail.com, pgsql-bugs@lists.postgresql.org, a.rybakina@postgrespro.ru
Subject: Re: BUG #16925: ERROR: invalid DSA memory alloc request size 1073741824 CONTEXT: parallel worker
In-Reply-To: <9277414b-dbce-4a32-8aff-642e399e23e5@postgrespro.ru>
* 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