Re: PostgreSQL consuming high memory causing restart

Hi

There might be a couple of potential reasons for the high memory
consumption you're experiencing

1) If the memory-related settings are not optimally tuned, it can lead to
high memory usage. For instance, if system resources are limited but memory
settings are configured with larger values, it could cause this issue.
Please double-check your memory-related settings, specifically
*shared_buffers*, *work_mem*, *maintenance_work_mem*, and
*effective_cache_size*.

2) The warning you encountered may also be linked to long-running
transactions, which can block autovacuum operations and cause delays. You
can use the following query to identify any long-running transactions

SELECT pid, age(clock_timestamp(), query_start), usename, query FROM
pg_stat_activity WHERE state != 'idle' ORDER BY age(clock_timestamp(),
query_start) DESC;

Thanks and regards
Semab

On Mon, Sep 16, 2024 at 6:24 PM Sagar Popade <spopade(at)rbbn(dot)com> wrote:

> Hi team,
>
> We are facing postgresql memory consuming issue which causes postgresql
> restart.
>
> A description of what you are trying to achieve and what results you expect.: PostgreSQL is consuming high memory causes restart
>
> PostgreSQL version number you are running: psql (PostgreSQL) 15.8
>
> How you installed PostgreSQL: in containerised environment with patroni
>
> Changes made to the settings in the postgresql.conf file*: autovacuum_max_workers: 4, autovacuum_vacuum_cost_limit: 200, autovacuum_vacuum_scale_factor: 0.2*
>
> Operating system and version: RHEL 9.4
>
> What program you're using to connect to PostgreSQL: python
>
> Is there anything relevant or unusual in the PostgreSQL server logs?:
> 2024-09-12 02:44:47.231 IST [1057] WARNING: autovacuum worker took too long to start; canceled
> 2024-09-12 02:44:47.421 IST [1095741] WARNING: autovacuum worker started without a worker entry
> 2024-09-12 02:46:01.043 IST [1095743] LOG: could not send data to client: Broken pipe
> 2024-09-12 02:46:01.050 IST [1095743] FATAL: connection to client lost
> 2024-09-12 02:46:28.696 IST [1050] LOG: received fast shutdown request
> Aborts all idle connections and recovery
>
> 2024-09-12 02:46:53.909 IST [1095835] LOG: checkpoint starting: force
> 2024-09-12 02:46:53.925 IST [1095832] LOG: database system is ready to accept connections
>
> For questions about any kind of error:
>
> What you were doing when the error happened / how to cause the error: no specific operation , sytem is running for few days
>
> The EXACT TEXT of the error message you're getting, if there is one: (Copy and paste the message to the email, do not send a screenshot)
>
> Regards,
> Sagar Popade
>
>
> *Disclaimer*
>
> This e-mail together with any attachments may contain information of
> Ribbon Communications Inc. and its Affiliates that is confidential and/or
> proprietary for the sole use of the intended recipient. Any review,
> disclosure, reliance or distribution by others or forwarding without
> express permission is strictly prohibited. If you are not the intended
> recipient, please notify the sender immediately and then delete all copies,
> including any attachments.
>