PostgreSQL consuming high memory causing restart
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.
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@rbbn.com> wrote:
Show quoted text
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 recovery2024-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 connectionsFor 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.