From: | Andreas Seltenreich <andreas(dot)seltenreich(at)credativ(dot)de> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Excessive memory usage in multi-statement queries w/ partitioning |
Date: | 2019-05-22 19:15:40 |
Message-ID: | 87ftp6l2qr.fsf@credativ.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
a customer reported excessive memory usage and out-of-memory ERRORs
after introducing native partitioning in one of their databases. We
could narrow it down to the overhead introduced by the partitioning when
issuing multiple statements in a single query. I could reduce the
problem to the following recipe:
--8<---------------cut here---------------start------------->8---
#!/bin/bash
# create 100 partitions
psql -c 'create table t(c int primary key) partition by range(c)'
for i in {1..100}; do
psql -e -c "create table t$i partition of t for values
from ($(((i-1)*100))) to ($((i*100-1))) "
done
# artificially limit per-process memory by setting a resource limit for
# the postmaster to 256MB
prlimit -d$((256*1024*1024)) -p $POSTMASTER_PID
--8<---------------cut here---------------end--------------->8---
Now, updates to a partition are fine with 4000 update statements:
,----
| $ psql -c "$(yes update t2 set c=c where c=6 \; | head -n 4000)"
| UPDATE 0
`----
…but when doing it on the parent relation, even 100 statements are
enough to exceed the limit:
,----
| $ psql -c "$(yes update t set c=c where c=6 \; | head -n 100)"
| FEHLER: Speicher aufgebraucht
| DETAIL: Failed on request of size 200 in memory context "MessageContext".
`----
The memory context dump shows plausible values except for the MessageContext:
TopMemoryContext: 124336 total in 8 blocks; 18456 free (11 chunks); 105880 used
[...]
MessageContext: 264241152 total in 42 blocks; 264 free (0 chunks); 264240888 used
[...]
Maybe some tactically placed pfrees or avoiding putting redundant stuff
into MessageContext can relax the situation?
regards,
Andreas
From | Date | Subject | |
---|---|---|---|
Next Message | Akim Demaille | 2019-05-22 19:20:31 | Re: Remove useless associativity/precedence from parsers |
Previous Message | Peter Eisentraut | 2019-05-22 19:13:04 | Re: pgindent run next week? |