By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

My Postgres RDS Database is constantly reastarting suddenly due to heavyconsumption of memory. Not sure why it is happening suddenly.

0

My Postgres RDS Database is constantly reastarting suddenly due to heavyconsumption of memory. Not sure why it is happening suddenly.
please help . some times freeable memory goes up to 30gb and suddenly comes to 8gb. not sure why. Below are logs

The database process was killed by the OS due to excessive memory consumption. It is recommended to tune your database workload and/or parameter usage to reduce memory consumption.

2022-03-07 12:17:57.302 GMT [9230] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 12:17:57.302 GMT [9230] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 12:17:57.302 GMT [9230] LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf"
2022-03-07 12:17:57 UTC::@:[9230]:WARNING: unrecognized configuration parameter "rds.adaptive_autovacuum"
2022-03-07 12:17:57 UTC::@:[9230]:WARNING: unrecognized configuration parameter "rds.enable_plan_management"
2022-03-07 12:17:57 UTC::@:[9230]:LOG: database system is shut down
Postgres Shared Memory Value: 23067312128 bytes
2022-03-07 12:17:59.685 GMT [9858] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 12:17:59.686 GMT [9858] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 12:17:59.686 GMT [9858] LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf"
2022-03-07 12:17:59 UTC::@:[9858]:WARNING: unrecognized configuration parameter "rds.adaptive_autovacuum"
2022-03-07 12:17:59 UTC::@:[9858]:WARNING: unrecognized configuration parameter "rds.enable_plan_management"
2022-03-07 12:17:59 UTC::@:[9858]:LOG: starting PostgreSQL 12.4 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 7.4.0, 64-bit
2022-03-07 12:17:59 UTC::@:[9858]:LOG: listening on IPv4 address "0.0.0.0", port 5434
2022-03-07 12:17:59 UTC::@:[9858]:LOG: listening on IPv6 address "::", port 5434
2022-03-07 12:17:59 UTC::@:[9858]:LOG: listening on Unix socket "/tmp/.s.PGSQL.5434"
2022-03-07 12:17:59 UTC::@:[9858]:LOG: could not write file "pg_stat_tmp/pgss_query_texts.stat": No such file or directory
2022-03-07 12:17:59 UTC::@:[9858]:LOG: redirecting log output to logging collector process
2022-03-07 12:17:59 UTC::@:[9858]:HINT: Future log output will appear in directory "/rdsdbdata/log/error".
2022-03-07 12:20:10 UTC::@:[9859]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 12:20:10 UTC::@:[9859]:LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf"
2022-03-07 12:20:30 UTC::@:[9859]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 12:20:30 UTC::@:[9859]:LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf"
2022-03-07 12:26:50 UTC::@:[9859]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 12:26:50 UTC::@:[9859]:LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf"
2022-03-07 13:43:53.670 GMT [22533] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 13:43:53.670 GMT [22533] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 13:43:53.670 GMT [22533] LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf"
2022-03-07 13:43:53 UTC::@:[22533]:WARNING: unrecognized configuration parameter "rds.adaptive_autovacuum"
2022-03-07 13:43:53 UTC::@:[22533]:WARNING: unrecognized configuration parameter "rds.enable_plan_management"
2022-03-07 13:43:53 UTC::@:[22533]:LOG: database system is shut down
Postgres Shared Memory Value: 23067312128 bytes
2022-03-07 13:43:54.922 GMT [22660] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 13:43:54.922 GMT [22660] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 13:43:54.922 GMT [22660] LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf"
2022-03-07 13:43:54 UTC::@:[22660]:WARNING: unrecognized configuration parameter "rds.adaptive_autovacuum"
2022-03-07 13:43:54 UTC::@:[22660]:WARNING: unrecognized configuration parameter "rds.enable_plan_management"
2022-03-07 13:43:54 UTC::@:[22660]:LOG: starting PostgreSQL 12.4 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 7.4.0, 64-bit
2022-03-07 13:43:54 UTC::@:[22660]:LOG: listening on IPv4 address "0.0.0.0", port 5434
2022-03-07 13:43:54 UTC::@:[22660]:LOG: listening on IPv6 address "::", port 5434
2022-03-07 13:43:54 UTC::@:[22660]:LOG: listening on Unix socket "/tmp/.s.PGSQL.5434"
2022-03-07 13:43:55 UTC::@:[22660]:LOG: redirecting log output to logging collector process
2022-03-07 13:43:55 UTC::@:[22660]:HINT: Future log output will appear in directory "/rdsdbdata/log/error".
2022-03-07 13:45:11 UTC::@:[22732]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 13:45:11 UTC::@:[22732]:LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf"
2022-03-07 13:45:30 UTC::@:[22732]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 13:45:30 UTC::@:[22732]:LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf"
2022-03-07 13:49:25.588 GMT [8568] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 13:49:25.588 GMT [8568] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 13:49:25.590 GMT [8568] LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf"
2022-03-07 13:49:25 UTC::@:[8568]:WARNING: unrecognized configuration parameter "rds.adaptive_autovacuum"
2022-03-07 13:49:25 UTC::@:[8568]:WARNING: unrecognized configuration parameter "rds.enable_plan_management"
2022-03-07 13:49:25 UTC::@:[8568]:LOG: database system is shut down
Postgres Shared Memory Value: 23067312128 bytes
2022-03-07 13:49:26.803 GMT [8674] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 13:49:26.803 GMT [8674] LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf"
2022-03-07 13:49:26.803 GMT [8674] LOG: skipping missing configuration file "/rdsdbdata/db/postgresql.auto.conf"
2022-03-07 13:49:26 UTC::@:[8674]:WARNING: unrecognized configuration parameter "rds.adaptive_autovacuum"
2022-03-07 13:49:26 UTC::@:[8674]:WARNING: unrecognized configuration parameter "rds.enable_plan_management"
2022-03-07 13:49:26 UTC::@:[8674]:LOG: starting PostgreSQL 12.4 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 7.4.0, 64-bit
2022-03-07 13:49:26 UTC::@:[8674]:LOG: listening on IPv4 address "0.0.0.0", port 5434
2022-03-07 13:49:26 UTC::@:[8674]:LOG: listening on IPv6 address "::", port 5434
2022-03-07 13:49:26 UTC::@:[8674]:LOG: listening on Unix socket "/tmp/.s.PGSQL.5434"
2022-03-07 13:49:26 UTC::@:[8674]:LOG: redirecting log output to logging collector process
2022-03-07 13:49:26 UTC::@:[8674]:HINT: Future log output will appear in directory "/rdsdbdata/log/error".
----------------------- END OF LOG ----------------------
Topics
Database
Tags
Amazon RDS on VMWare
Language
English
AWS-User-0815207
asked 2 years ago8490 views
2 Answers
3

Thank you for reaching out. I understand you are observed an Out of Memory issue in the RDS PostgreSQL instance.

As per the event messages, instance was under memory pressure and thus the host was unable to communicate with monitoring system. Thus the instance was rebooted to take it out of its memory pressure. Generally, the amount of the memory assigned to the underlying host is being used by Operating System Process, RDS Process, Monitoring Process, Database Engine Process. Whenever there is a requirement of the memory for the customer workload, the engine process gets precedence than the rest of the process running on the host. Thus due to the low freeable memory the monitoring agent was unable to send heartbeat information to the Monitoring Application. Since the Monitoring Application didn't received any heartbeat information from the Monitoring Agent hosted in the same RDS host, it suspects there are some issues and it initiates a reboot in the process.

Preventative measures :

  1. Check for the workload/queries consuming more memory and work on further Query tuning from Performance Insight tool.
  2. Check for the RDS PostgreSQL memory related parameters and tune them if needed.
  3. Please refer to the link which will guide you to set up cloudwatch alarms in RDS Metrics and pro-actively monitor those. You can create a CloudWatch Alarm to monitor and send alerts when the RDS metric goes beyond a certain threshold limit.CloudWatch uses Amazon SNS to send an email.
  4. You can scale up the RDS instance class to next higher level so that it can have more memory. You need to perform load testing on the Non-Prod instance first before working on the Prod as we are not sure of the impact of the new workloads on the instance

The above information will provide you with details to triage the issue. However, Please note this is general guidance only. In order to understand the issue fully, we require details that are non-public information. Please open a support case with AWS using the following link.

As always, Happy Cloud Computing.

AWS
SUPPORT ENGINEER
Arnab_S
answered 2 years ago
0

I understand need to tune parameters but how do I find which parameters. The logs doesn't indicate any.

nagakalyan
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content