pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to monitor Postgres real memory usage
9+ messages / 2 participants
[nested] [flat]
* How to monitor Postgres real memory usage
@ 2022-05-24 16:25 徐志宇徐 <xuzhiyuster@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: 徐志宇徐 @ 2022-05-24 16:25 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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.
[image: 图片.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",,,,,,,,,""
Attachments:
[image/png] 图片.png (53.4K, ../../CAOCebi+i=HPofBSdvs1RftcZpAHg81R9jqmr5xTJm0dGSDUMNQ@mail.gmail.com/3-%E5%9B%BE%E7%89%87.png)
download | view image
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to monitor Postgres real memory usage
@ 2022-05-24 17:40 Justin Pryzby <pryzby@telsasoft.com>
parent: 徐志宇徐 <xuzhiyuster@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Justin Pryzby @ 2022-05-24 17:40 UTC (permalink / raw)
To: 徐志宇徐 <xuzhiyuster@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to monitor Postgres real memory usage
@ 2022-05-26 15:36 徐志宇徐 <xuzhiyuster@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 9+ messages in thread
From: 徐志宇徐 @ 2022-05-26 15:36 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
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@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
>
Attachments:
[application/octet-stream] postgresql.auto.conf (1.2K, ../../CAOCebiLgKWe1Erbt-mqWrg6fhv_bBF-O4atZvWQaYoi+rTDvEQ@mail.gmail.com/3-postgresql.auto.conf)
download
[application/octet-stream] postgresql.conf (23.2K, ../../CAOCebiLgKWe1Erbt-mqWrg6fhv_bBF-O4atZvWQaYoi+rTDvEQ@mail.gmail.com/4-postgresql.conf)
download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to monitor Postgres real memory usage
@ 2022-05-26 15:47 徐志宇徐 <xuzhiyuster@gmail.com>
parent: 徐志宇徐 <xuzhiyuster@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: 徐志宇徐 @ 2022-05-26 15:47 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
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) .."
> 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@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
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to monitor Postgres real memory usage
@ 2022-05-26 16:05 Justin Pryzby <pryzby@telsasoft.com>
parent: 徐志宇徐 <xuzhiyuster@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Justin Pryzby @ 2022-05-26 16:05 UTC (permalink / raw)
To: 徐志宇徐 <xuzhiyuster@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to monitor Postgres real memory usage
@ 2022-05-26 17:39 徐志宇徐 <xuzhiyuster@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 9+ messages in thread
From: 徐志宇徐 @ 2022-05-26 17:39 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to monitor Postgres real memory usage
@ 2022-05-26 23:35 Justin Pryzby <pryzby@telsasoft.com>
parent: 徐志宇徐 <xuzhiyuster@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Justin Pryzby @ 2022-05-26 23:35 UTC (permalink / raw)
To: 徐志宇徐 <xuzhiyuster@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to monitor Postgres real memory usage
@ 2022-05-27 17:56 Justin Pryzby <pryzby@telsasoft.com>
parent: 徐志宇徐 <xuzhiyuster@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Justin Pryzby @ 2022-05-27 17:56 UTC (permalink / raw)
To: 徐志宇徐 <xuzhiyuster@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
1 further message in this thread omitted from this page (render size limit).
Use the per-message pages, or the t.mbox.gz byte stream, for the complete thread.
end of thread, other threads:[~2022-05-27 17:56 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-24 16:25 How to monitor Postgres real memory usage 徐志宇徐 <xuzhiyuster@gmail.com>
2022-05-24 17:40 ` Justin Pryzby <pryzby@telsasoft.com>
2022-05-26 15:36 ` 徐志宇徐 <xuzhiyuster@gmail.com>
2022-05-26 15:47 ` 徐志宇徐 <xuzhiyuster@gmail.com>
2022-05-26 16:05 ` Justin Pryzby <pryzby@telsasoft.com>
2022-05-26 17:39 ` 徐志宇徐 <xuzhiyuster@gmail.com>
2022-05-26 23:35 ` Justin Pryzby <pryzby@telsasoft.com>
2022-05-27 17:40 ` 徐志宇徐 <xuzhiyuster@gmail.com>
2022-05-27 17:56 ` Justin Pryzby <pryzby@telsasoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox