Re: How to monitor Postgres real memory usage
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 于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 > postgresql.auto.conf Description: Binary data postgresql.conf Description: Binary data
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 |
Re: How to monitor Postgres real memory usage
> 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.1' 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 于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
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 unlimitedunlimitedseconds Max file size unlimitedunlimitedbytes Max data size unlimitedunlimitedbytes Max stack size8388608 unlimitedbytes Max core file size0unlimitedbytes Max resident set unlimitedunlimitedbytes Max processes 4096 63445 processes Max open files6553665536files Max locked memory 6553665536bytes Max address space unlimitedunlimitedbytes Max file locksunlimitedunlimitedlocks Max pending signals 6344563445signals Max msgqueue size 819200 819200 bytes Max nice priority 00 Max realtime priority 00 Max realtime timeout unlimitedunlimitedus >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.1' Your are correct. I will adjust those parameter . enable_seqscan = 'on' reduce autovacuum number . Justin Pryzby 于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.1' > > 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 于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
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
