Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to monitor Postgres real memory usage

How to monitor Postgres real memory usage

From
徐志宇徐
Date:
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.

图片.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,00000,"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,00000,"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,00000,"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,00000,"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,00000,"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,00000,"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,00000,"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,00000,"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,00000,"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,00000,"could not fork autovacuum worker process: Cannot allocate memory",,,,,,,,,""

Attachment

Re: How to monitor Postgres real memory usage

From
Justin Pryzby
Date:
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

Re: How to monitor Postgres real memory usage

From
徐志宇徐
Date:
Hi Justin

   Thanks for your update.

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

What OS are you using ?

>  I am using Centos 7.5.

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

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

> I unable to connect to database server. I have to kill some process to release memory. Then I could connect it. 

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


> Please reference my attachment.

You can check memory use of an individual query with "explain (analyze,buffers) .."

Thanks for your update.  This memory allocation failed issue impact the whole database running. not a slow query.
Is there any commands or method could get totally Postgres memory utilization ? Thanks .

Justin Pryzby <pryzby@telsasoft.com> 于2022年5月25日周三 01:40写道：
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
Attachment

Re: How to monitor Postgres real memory usage

From
徐志宇徐
Date:
Hi Justin

 I list the server configuration for your reference.

postgres=# SELECT name, current_setting(name), source
postgres-#   FROM pg_settings
postgres-#   WHERE source NOT IN ('default', 'override');
              name               |           current_setting           |        source
---------------------------------+-------------------------------------+----------------------
 application_name                | psql                                | client
 archive_command                 | cp %p /data/postgres/archive_log/%f | configuration file
 archive_mode                    | on                                  | configuration file
 auto_explain.log_min_duration   | 10s                                 | configuration file
 autovacuum_analyze_scale_factor | 1e-05                               | configuration file
 autovacuum_analyze_threshold    | 5                                   | configuration file
 autovacuum_max_workers          | 20                                  | configuration file
 autovacuum_vacuum_scale_factor  | 0.0002                              | configuration file
 autovacuum_vacuum_threshold     | 5                                   | configuration file
 bgwriter_delay                  | 20ms                                | configuration file
 bgwriter_lru_maxpages           | 400                                 | configuration file
 client_encoding                 | UTF8                                | client
 DateStyle                       | ISO, MDY                            | configuration file
 default_text_search_config      | pg_catalog.english                  | configuration file
 dynamic_shared_memory_type      | posix                               | configuration file
 enable_seqscan                  | off                                 | configuration file
 lc_messages                     | en_US.UTF-8                         | configuration file
 lc_monetary                     | en_US.UTF-8                         | configuration file
 lc_numeric                      | en_US.UTF-8                         | configuration file
 lc_time                         | en_US.UTF-8                         | configuration file
 listen_addresses                | *                                   | configuration file
 lock_timeout                    | 5min                                | configuration file
 log_connections                 | on                                  | configuration file
 log_destination                 | csvlog                              | configuration file
 log_directory                   | log                                 | configuration file
 log_lock_waits                  | on                                  | configuration file
 log_min_duration_statement      | 10s                                 | configuration file
 log_rotation_size               | 30MB                                | configuration file
 log_statement                   | ddl                                 | configuration file
 log_timezone                    | PRC                                 | configuration file
 log_truncate_on_rotation        | on                                  | configuration file
 logging_collector               | on                                  | configuration file
 maintenance_work_mem            | 64MB                                | configuration file
 max_connections                 | 1000                                | configuration file
 max_parallel_workers_per_gather | 4                                   | configuration file
 max_stack_depth                 | 2MB                                 | environment variable
 max_wal_size                    | 4GB                                 | configuration file
 max_worker_processes            | 4                                   | configuration file
 min_wal_size                    | 320MB                               | configuration file
 pg_stat_statements.max          | 1000                                | configuration file
 pg_stat_statements.track        | all                                 | configuration file
 port                            | 5432                                | configuration file
 shared_buffers                  | 6352MB                              | configuration file
 shared_preload_libraries        | pg_stat_statements,auto_explain     | configuration file
 temp_buffers                    | 32MB                                | configuration file
 TimeZone                        | PRC                                 | configuration file
 track_activities                | on                                  | configuration file
 track_commit_timestamp          | off                                 | configuration file
 track_counts                    | on                                  | configuration file
 track_functions                 | all                                 | configuration file
 track_io_timing                 | on                                  | configuration file
 vacuum_cost_limit               | 2000                                | configuration file
 wal_compression                 | on                                  | configuration file
 wal_keep_segments               | 128                                 | configuration file
 wal_level                       | replica                             | configuration file
 work_mem                        | 40MB                                | configuration file
(56 rows)

徐志宇徐 <xuzhiyuster@gmail.com> 于2022年5月26日周四 23:36写道：
Hi Justin

   Thanks for your update.

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

What OS are you using ?

>  I am using Centos 7.5.

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

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

> I unable to connect to database server. I have to kill some process to release memory. Then I could connect it. 

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


> Please reference my attachment.

You can check memory use of an individual query with "explain (analyze,buffers) .."

Thanks for your update.  This memory allocation failed issue impact the whole database running. not a slow query.
Is there any commands or method could get totally Postgres memory utilization ? Thanks .

Justin Pryzby <pryzby@telsasoft.com> 于2022年5月25日周三 01:40写道：
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

Re: How to monitor Postgres real memory usage

From
Justin Pryzby
Date:
> enable_seqscan = 'off'

Why is this here ?  I think when people set this, it's because they "want to
use more index scans to make things faster".  But index scans aren't
necessarily faster, and this tries to force their use even when it will be
slower.  It's better to address the queries that are slow (or encourage index
scans by decreasing random_page_cost).

> maintenance_work_mem = '64MB'
> autovacuum_max_workers = '20'
> vacuum_cost_limit = '2000'
> autovacuum_vacuum_scale_factor = '0.0002'
> autovacuum_analyze_scale_factor = '0.00001'

This means you're going to use up to 20 processes simultaneously running vacuum
(each of which may use 64MB memory).  What kind of storage does the server
have?  Can it support 20 background processes reading from disk, in addition to
other processs ?

Justin Pryzby <pryzby@telsasoft.com> 于2022年5月25日周三 01:40写道：
> > What postgres version ?
> > How was it installed ?  From souce?  From a package ?

What about this ?

I'm not sure how/if this would affect memory allocation, but if the server is
slow, processes will be waiting longer, rather than completing quickly, and
using their RAM for a longer period...

Does the postgres user have any rlimits set ?

Check:
ps -fu postgres
# then:
sudo cat /proc/2948/limits

Re: How to monitor Postgres real memory usage

From
徐志宇徐
Date:
Hi Justin

  Thanks for you explaination.

  > > What postgres version ?
  > > How was it installed ?  From souce?  From a package ?
  I am using Postgres 11.1 .It's installed by package.

Check:
ps -fu postgres
# then:
sudo cat /proc/2948/limits

root@bl4n3icpms ~]# sudo cat /proc/21731/limits
Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             unlimited            unlimited            bytes
Max stack size            8388608              unlimited            bytes
Max core file size        0                    unlimited            bytes
Max resident set          unlimited            unlimited            bytes
Max processes             4096                 63445                processes
Max open files            65536                65536                files
Max locked memory         65536                65536                bytes
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       63445                63445                signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited            us

 
>enable_seqscan = 'off'
> maintenance_work_mem = '64MB'
> autovacuum_max_workers = '20'
> vacuum_cost_limit = '2000'
> autovacuum_vacuum_scale_factor = '0.0002'
> autovacuum_analyze_scale_factor = '0.00001'

Your are correct.

I will adjust those parameter .
enable_seqscan = 'on'
reduce autovacuum number .

Justin Pryzby <pryzby@telsasoft.com> 于2022年5月27日周五 00:05写道：
> enable_seqscan = 'off'

Why is this here ?  I think when people set this, it's because they "want to
use more index scans to make things faster".  But index scans aren't
necessarily faster, and this tries to force their use even when it will be
slower.  It's better to address the queries that are slow (or encourage index
scans by decreasing random_page_cost).

> maintenance_work_mem = '64MB'
> autovacuum_max_workers = '20'
> vacuum_cost_limit = '2000'
> autovacuum_vacuum_scale_factor = '0.0002'
> autovacuum_analyze_scale_factor = '0.00001'

This means you're going to use up to 20 processes simultaneously running vacuum
(each of which may use 64MB memory).  What kind of storage does the server
have?  Can it support 20 background processes reading from disk, in addition to
other processs ?

Justin Pryzby <pryzby@telsasoft.com> 于2022年5月25日周三 01:40写道：
> > What postgres version ?
> > How was it installed ?  From souce?  From a package ?

What about this ?

I'm not sure how/if this would affect memory allocation, but if the server is
slow, processes will be waiting longer, rather than completing quickly, and
using their RAM for a longer period...

Does the postgres user have any rlimits set ?

Check:
ps -fu postgres
# then:
sudo cat /proc/2948/limits

Re: How to monitor Postgres real memory usage

From
Justin Pryzby
Date:
On Fri, May 27, 2022 at 01:39:15AM +0800, 徐志宇徐 wrote:
> Hi Justin
> 
>   Thanks for you explaination.
> 
>   > > What postgres version ?
>   > > How was it installed ?  From souce?  From a package ?
>   I am using Postgres 11.1 .It's installed by package.

This is quite old, and missing ~4 years of bugfixes.

What's the output of these commands?
tail /proc/sys/vm/overcommit_*
tail /proc/sys/vm/nr_*hugepages /proc/cmdline
cat /proc/meminfo
uname -a

-- 
Justin

Re: How to monitor Postgres real memory usage

From
徐志宇徐
Date:
Hi Justin

  Thanks for your update. I collect those logs for your reference.
  I also collect this file /etc/sysctl.conf and PG logs on the attachment. (memory_issue.tar.gz).
 
  when the memory issue occur. The log directory will generate a new log file.

 postgresql-2022-05-27_000000.log. Usually only one file to record log information. 
 For example:postgresql-2022-05-27_000000.csv

 The log which named with *.log will contain a lot of infomation about memory detail.


What's the output of these commands?
tail /proc/sys/vm/overcommit_*

[root@bl4n3icpms vm]# tail /proc/sys/vm/overcommit_*
==> /proc/sys/vm/overcommit_kbytes <==
0

==> /proc/sys/vm/overcommit_memory <==
2

==> /proc/sys/vm/overcommit_ratio <==
60



tail /proc/sys/vm/nr_*hugepages /proc/cmdline

==> /proc/sys/vm/nr_hugepages <==
0

==> /proc/sys/vm/nr_overcommit_hugepages <==
0

==> /proc/cmdline <==
BOOT_IMAGE=/vmlinuz-3.10.0-957.27.2.el7.x86_64 root=/dev/mapper/rootvg-lv_root ro crashkernel=auto rd.lvm.lv=rootvg/lv_root rd.lvm.lv=rootvg/lv_swap rhgb quiet LANG=en_US.UTF-8


cat /proc/meminfo

[root@bl4n3icpms vm]# cat /proc/meminfo
MemTotal:       16266368 kB
MemFree:          203364 kB
MemAvailable:    7823244 kB
Buffers:            3272 kB
Cached:         12978488 kB
SwapCached:            0 kB
Active:         10456284 kB
Inactive:        5042108 kB
Active(anon):    6008156 kB
Inactive(anon):  1738892 kB
Active(file):    4448128 kB
Inactive(file):  3303216 kB
Unevictable:       11292 kB
Mlocked:           11292 kB
SwapTotal:       2097148 kB
SwapFree:        2097148 kB
Dirty:                20 kB
Writeback:             0 kB
AnonPages:       2527924 kB
Mapped:          4527276 kB
Shmem:           5226728 kB
Slab:             268304 kB
SReclaimable:     206876 kB
SUnreclaim:        61428 kB
KernelStack:        4576 kB
PageTables:       106924 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    11856968 kB
Committed_AS:   10488212 kB
VmallocTotal:   34359738367 kB
VmallocUsed:       37468 kB
VmallocChunk:   34359695100 kB
HardwareCorrupted:     0 kB
AnonHugePages:    124928 kB
CmaTotal:              0 kB
CmaFree:               0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:      143208 kB
DirectMap2M:     6148096 kB
DirectMap1G:    12582912 kB



uname -a

Linux bl4n3icpms.lenovo.com 3.10.0-957.27.2.el7.x86_64 #1 SMP Mon Jul 29 17:46:05 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux


[root@bl4n3icpms vm]# cat /etc/sysctl.conf
# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.conf.all.accept_redirects = 0
# edit for pg database used#
kernel.shmmax=16656777216
kernel.shmall=4066596
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.sem = 250     32000   32      128
kernel.pid_max=131072
vm.overcommit_memory=2
vm.overcommit_ratio=60
vm.swappiness=0
# edit for pg database used#



Justin Pryzby <pryzby@telsasoft.com> 于2022年5月27日周五 07:35写道：
On Fri, May 27, 2022 at 01:39:15AM +0800, 徐志宇徐 wrote:
> Hi Justin
>
>   Thanks for you explaination.
>
>   > > What postgres version ?
>   > > How was it installed ?  From souce?  From a package ?
>   I am using Postgres 11.1 .It's installed by package.

This is quite old, and missing ~4 years of bugfixes.

What's the output of these commands?
tail /proc/sys/vm/overcommit_*
tail /proc/sys/vm/nr_*hugepages /proc/cmdline
cat /proc/meminfo
uname -a

--
Justin
Attachment

Re: How to monitor Postgres real memory usage

From
Justin Pryzby
Date:
On Sat, May 28, 2022 at 01:40:14AM +0800, 徐志宇徐 wrote:
> vm.swappiness=0

I think this is related to the problem.

swappiness=0 means to *never* use swap, even if that means that processes are
killed.

If you really wanted that, you should remove the swap space.

Swap is extremely slow and worth avoiding, but this doesn't let you use it at
all.  You can't even look at your swap usage as a diagnostic measure to tell if
things had been paged out at some point.

I *suspect* the problem will go away if you set swappiness=1 in /proc (and in
sysctl.conf).

-- 
Justin