How does one monitor Postgres memory usage?

Question

We are running Postgres in Kubernetes. Our Prometheus pod monitoring always shows that Postgres fills up the entire pods memory in a shared or cached state.

free -h
              total        used        free      shared  buff/cache   available
Mem:            62G        1.7G         13G         11G         47G         48G


ps -u postgres o pid= | sed 's#.*#/proc/&/smaps#' | sed 's/ //g#/proc/&/smaps#' | \
    xargs grep -s ^Pss: | awk '{A+=$2} END{print "PSS: " A}' 
PSS: 13220013 kb

ps -u postgres o pid,rss:8,cmd | awk 'NR>1 {A+=$2} {print} END{print "RSS: " A}' | tail -1
RSS: 38794236 kb

Correct me if I am wrong, but since the memory displayed in top and ps(RSS/RES) is shared memory, this means that Posgres isnt using that memory, its only reserved for when it needs it and other processes can also use that memory. Some articles say that one needs to cat /proc/<PID>/smaps and check the PSS to find the actual memory usage of Postgres.

We recently got OOM errors, but we where unable to pick it up in our monitoring. Due to the fact that our pod memory monitoring is always displaying 90% usage as its only monitoring the RSS/RES memory which is includes the shared/cached memory as well. So we didnt see any increase in RAM when the OOM errors happened and our database went down. The error was caused by a new query we introduced to our backend which used large amounts of memory per query.

We have prometheus-postgres-exporter installed giving us good Postgres metrics, but this didnt show us that we had queries using large amounts memory, but maybe our Grafana graph is missing that graph?

Because we are running Postgres in Kubernetes exporting the PSS memory is a hassle. So it feels like I am missing something.

So how does one monitor the memory usage of Postgres ? How does one pick up on queries using too much memory or pick up on postgres using too much memory due to load?

Laurenz Albe · Accepted Answer · 2021-04-28 15:51:46Z

2

I recommend that you disable memory overcommit by setting vm.overcommit_memory = 2 in /etc/sysctl.conf and running sysctl -p. Don't forget to set vm.overcommit_ratio appropriately, based on the RAM and swap that you have.

Than should keep the OOM killer at bay.

Then you can examine /proc/meminfo to find if you are getting tight on memory:

CommitLimit shows how much memory the kernel is willing to hand out
Committed_AS shows how much memory is allocated by processes

Look at the kernel documentation for details.

answered Apr 28, 2021 at 15:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Narayana Over a year ago

Thanks this information is handy ! I guess for PSQL memory monitoring one has to monitor the /proc/meminfo ? To figure out if one will be running out of memory soon ?

Laurenz Albe Over a year ago

This is not specific to PostgreSQL, I think. If your monitoring system doesn't understand how shared memory works, it may have a problem with other software as well.

Collectives™ on Stack Overflow

How does one monitor Postgres memory usage?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related