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