Re: How to monitor Postgres real memory usage

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(at)gmail(dot)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) .."
> 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(at)telsasoft(dot)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
>>
>