How to monitor Postgres real memory usage

[徐志宇徐]

Hi All

  I am a Database DBA. I focus on PostgreSQL and DB2.
  Recently. I experience some memory issue. The postgres unable allocate
memory. I don't know how to monitor Postgres memory usage.
 I try to search some  document. But not found any useful information.
 This server have 16G memory. On that time. The free command display only 3
G memory used. The share_buffers almost 6G.

 On that time. The server have 100 active applications.
 New connection failed. I have to kill some application by os command "kill
-9"
The checkpoint command execute very slow. almost need 5-10 seconds.

[image: 图片.png]


  Is there any useful command to summary PostgreSQL memory usage ?

  How to analyse this memory issue ? Thanks for your help.

2022-05-23 17:42:51.541 CST,,,21731,,6288963b.54e3,8055,,2022-05-21
15:35:23 CST,,0,LOG,0,"could not fork autovacuum worker process: Cannot
allocate memory",""
2022-05-23 17:42:51.627 CST,,,21731,,6288963b.54e3,8056,,2022-05-21
15:35:23 CST,,0,LOG,0,"could not fork new process for connection:
Cannot allocate memory",""
2022-05-23 17:42:51.627 CST,,,21731,,6288963b.54e3,8057,,2022-05-21
15:35:23 CST,,0,LOG,0,"could not fork new process for connection:
Cannot allocate memory",""
2022-05-23 17:42:51.628 CST,,,21731,,6288963b.54e3,8058,,2022-05-21
15:35:23 CST,,0,LOG,0,"could not fork new process for connection:
Cannot allocate memory",""
2022-05-23 17:42:51.628 CST,,,21731,,6288963b.54e3,8059,,2022-05-21
15:35:23 CST,,0,LOG,0,"could not fork new process for connection:
Cannot allocate memory",""
2022-05-23 17:42:52.130 CST,,,21731,,6288963b.54e3,8060,,2022-05-21
15:35:23 CST,,0,LOG,0,"could not fork new process for connection:
Cannot allocate memory",""
2022-05-23 17:42:52.130 CST,,,21731,,6288963b.54e3,8061,,2022-05-21
15:35:23 CST,,0,LOG,0,"could not fork new process for connection:
Cannot allocate memory",""
2022-05-23 17:42:52.131 CST,,,21731,,6288963b.54e3,8062,,2022-05-21
15:35:23 CST,,0,LOG,0,"could not fork new process for connection:
Cannot allocate memory",""
2022-05-23 17:42:52.131 CST,,,21731,,6288963b.54e3,8063,,2022-05-21
15:35:23 CST,,0,LOG,0,"could not fork new process for connection:
Cannot allocate memory",""
2022-05-23 17:42:52.543 CST,,,21731,,6288963b.54e3,8064,,2022-05-21
15:35:23 CST,,0,LOG,0,"could not fork autovacuum worker process: Cannot
allocate memory",""

Re: How to monitor Postgres real memory usage

[Justin Pryzby]

On Wed, May 25, 2022 at 12:25:28AM +0800, 徐志宇徐 wrote:
> Hi All
> 
>   I am a Database DBA. I focus on PostgreSQL and DB2.
>   Recently. I experience some memory issue. The postgres unable allocate
> memory. I don't know how to monitor Postgres memory usage.

Postgres is just an OS Process, so should be monitored like any other.

What OS are you using ?

Know that the OS may attribute "shared buffers" to different processes, or
multiple processes.

>  This server have 16G memory. On that time. The free command display only 3
> G memory used. The share_buffers almost 6G.
> 
>  On that time. The server have 100 active applications.
>  New connection failed. I have to kill some application by os command "kill 
> -9"

It's almost always a bad idea to kill postgres with kill -9.

> The checkpoint command execute very slow. almost need 5-10 seconds.

Do you mean an interactive checkpoint command ?
Or logs from log_checkpoint ?

>   Is there any useful command to summary PostgreSQL memory usage ?

You can check memory use of an individual query with "explain (analyze,buffers) 
.."
https://wiki.postgresql.org/wiki/Slow_Query_Questions

What settings have you used in postgres ?
https://wiki.postgresql.org/wiki/Server_Configuration

What postgres version ?
How was it installed ?  From souce?  From a package ?

-- 
Justin