Re: How to monitor Postgres real memory usage

[徐志宇徐 <xuzhiyuster@xxxxxxxxx>]

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