Обсуждение: 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: https://www.postgresql.org/message-id/CAMnUB3oYugXCBLSkih%2BqNsWQPciEwos6g_AMbnz_peNoxfHwyw%40mail.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 https://www.postgresql.org/message-id/CAMnUB3oYugXCBLSkih%2BqNsWQPciEwos6g_AMbnz_peNoxfHwyw%40mail.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: > https://www.postgresql.org/message-id/CAMnUB3oYugXCBLSkih%2BqNsWQPciEwos6g_AMbnz_peNoxfHwyw%40mail.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
Hello,
![image.png](/media/maillist_attaches/pgsql-bugs/2023/05/3/CAHcftXry2h728CTH4KAqjkNb_RfEQnVNTOoimKo4VwKzMCsOfA@mail.gmail.com/image.png)
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;
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.png](/media/maillist_attaches/pgsql-bugs/2023/05/3/CAHcftXry2h728CTH4KAqjkNb_RfEQnVNTOoimKo4VwKzMCsOfA@mail.gmail.com/image.png)
Thank you for looking into this!
On Wed, May 3, 2023 at 3:30 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
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:
> https://www.postgresql.org/message-id/CAMnUB3oYugXCBLSkih%2BqNsWQPciEwos6g_AMbnz_peNoxfHwyw%40mail.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
Вложения
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: > https://www.postgresql.org/message-id/CAMnUB3oYugXCBLSkih%2BqNsWQPciEwos6g_AMbnz_peNoxfHwyw%40mail.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] - https://www.postgresql.org/message-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:
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:
> https://www.postgresql.org/message-id/CAMnUB3oYugXCBLSkih%2BqNsWQPciEwos6g_AMbnz_peNoxfHwyw%40mail.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] - https://www.postgresql.org/message-id/CAMnUB3pwknqoe5s-bGuRD8nX1bWkZRbFF=jWNLTWbm_etFigkA@mail.gmail.com
--
With Regards,
Amit Kapila.