BUG #17914: walsenders taking up all memory
The following bug has been logged on the website:
Bug reference: 17914
Logged by: Stan S
Email address: stashukny@gmail.com
PostgreSQL version: 14.3
Operating system: AWS RDS - (Red Hat 7.3.1-12), 64-bit
Description:
Seeing the same issue as reported here:
/messages/by-id/CAMnUB3oYugXCBLSkih+qNsWQPciEwos6g_AMbnz_peNoxfHwyw@mail.gmail.com
In our case, logical_decoding_work_mem is set to 64 MB, yet walsenders can
each take up 30 GB of memory, crashing our database.
Able to reproduce it locally in docker, using Postgres 14.7 (latest minor
version of 14) following the same instructions as mentioned in
/messages/by-id/CAMnUB3oYugXCBLSkih+qNsWQPciEwos6g_AMbnz_peNoxfHwyw@mail.gmail.com
which is basically running parallelized update statements on one table on
the publisher, updating it's json column like this:
UPDATE tab1 SET data = data || '{"test_0": "1", "test_1": "1", "test_2":
"1",
"test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1",
"test_8":
"1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d":
"1",
"test_e": "1", "test_f": "1"}' #- '{test_0}'
The end result is each walsender's memory usage jumping up many times the
logical_decoding_work_mem limit.
Hi,
On Tue, May 2, 2023 at 5:56 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 17914
Logged by: Stan S
Email address: stashukny@gmail.com
PostgreSQL version: 14.3
Operating system: AWS RDS - (Red Hat 7.3.1-12), 64-bit
Description:Seeing the same issue as reported here:
/messages/by-id/CAMnUB3oYugXCBLSkih+qNsWQPciEwos6g_AMbnz_peNoxfHwyw@mail.gmail.comIn our case, logical_decoding_work_mem is set to 64 MB, yet walsenders can
each take up 30 GB of memory, crashing our database.
Thank you for reporting this issue. I have two questions:
* Did you use streaming = off/on on the subscriber?
* How did you confirm the walsender used 30GB of memory?
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Hello,
We use Kafka Connect debezium connectors, which translates to streaming =
OFF. We can tell that streaming is off by querying:
select *
from pg_stat_replication_slots;
And seeing that stream_count is 0.
The way we can tell that walsenders are jumping to 30GB and beyond is
checking in RDS --> select instance --> Go to Monitoring --> Select OS
Process list
Attaching an example screenshot below:
[image: image.png]
Thank you for looking into this!
On Wed, May 3, 2023 at 3:30 AM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:
Show quoted text
Hi,
On Tue, May 2, 2023 at 5:56 AM PG Bug reporting form
<noreply@postgresql.org> wrote:The following bug has been logged on the website:
Bug reference: 17914
Logged by: Stan S
Email address: stashukny@gmail.com
PostgreSQL version: 14.3
Operating system: AWS RDS - (Red Hat 7.3.1-12), 64-bit
Description:Seeing the same issue as reported here:
/messages/by-id/CAMnUB3oYugXCBLSkih+qNsWQPciEwos6g_AMbnz_peNoxfHwyw@mail.gmail.com
In our case, logical_decoding_work_mem is set to 64 MB, yet walsenders
can
each take up 30 GB of memory, crashing our database.
Thank you for reporting this issue. I have two questions:
* Did you use streaming = off/on on the subscriber?
* How did you confirm the walsender used 30GB of memory?Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Attachments:
image.pngimage/png; name=image.pngDownload+2-0
On Tue, May 2, 2023 at 2:26 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 17914
Logged by: Stan S
Email address: stashukny@gmail.com
PostgreSQL version: 14.3
Operating system: AWS RDS - (Red Hat 7.3.1-12), 64-bit
Description:Seeing the same issue as reported here:
/messages/by-id/CAMnUB3oYugXCBLSkih+qNsWQPciEwos6g_AMbnz_peNoxfHwyw@mail.gmail.comIn our case, logical_decoding_work_mem is set to 64 MB, yet walsenders can
each take up 30 GB of memory, crashing our database.
I think we have previously also seen a similar complaint. Can you
please once check the analysis related to memory context in email [1]/messages/by-id/CAMnUB3pwknqoe5s-bGuRD8nX1bWkZRbFF=jWNLTWbm_etFigkA@mail.gmail.com
and see if this is a similar problem?
[1]: /messages/by-id/CAMnUB3pwknqoe5s-bGuRD8nX1bWkZRbFF=jWNLTWbm_etFigkA@mail.gmail.com
--
With Regards,
Amit Kapila.
Hi Amit,
Yes, I believe it's a similar issue. However, we can't patch it up the same
way Alex did because we use Postgres via AWS RDS.
On Wed, May 3, 2023 at 11:36 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
Show quoted text
On Tue, May 2, 2023 at 2:26 AM PG Bug reporting form
<noreply@postgresql.org> wrote:The following bug has been logged on the website:
Bug reference: 17914
Logged by: Stan S
Email address: stashukny@gmail.com
PostgreSQL version: 14.3
Operating system: AWS RDS - (Red Hat 7.3.1-12), 64-bit
Description:Seeing the same issue as reported here:
/messages/by-id/CAMnUB3oYugXCBLSkih+qNsWQPciEwos6g_AMbnz_peNoxfHwyw@mail.gmail.com
In our case, logical_decoding_work_mem is set to 64 MB, yet walsenders
can
each take up 30 GB of memory, crashing our database.
I think we have previously also seen a similar complaint. Can you
please once check the analysis related to memory context in email [1]
and see if this is a similar problem?[1] -
/messages/by-id/CAMnUB3pwknqoe5s-bGuRD8nX1bWkZRbFF=jWNLTWbm_etFigkA@mail.gmail.com--
With Regards,
Amit Kapila.