Re: How to monitor Postgres real memory usage
| От | 徐志宇徐 |
|---|---|
| Тема | Re: How to monitor Postgres real memory usage |
| Дата | |
| Msg-id | CAOCebiKogDcrJtCUZnQxvU4HLrHREAT=CpHObzcf03aFROpUGw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: How to monitor Postgres real memory usage (徐志宇徐 <xuzhiyuster@gmail.com>) |
| Список | pgsql-performance |
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)
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 JustinThanks 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
В списке pgsql-performance по дате отправления: