Re: How to monitor Postgres real memory usage

[徐志宇徐 <xuzhiyuster@xxxxxxxxx>]

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@xxxxxxxxx> 于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) .."

https://wiki.postgresql.org/wiki/Slow_Query_Questions

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@xxxxxxxxxxxxx> 于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