Date: Thu, 26 May 2022 11:05:19 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: =?utf-8?B?5b6Q5b+X5a6H5b6Q?= <xuzhiyuster@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: How to monitor Postgres real memory usage
Message-ID: <20220526160519.GB19626@telsasoft.com>
References: 
 <CAOCebi+i=HPofBSdvs1RftcZpAHg81R9jqmr5xTJm0dGSDUMNQ@mail.gmail.com>
 <20220524174050.GI19626@telsasoft.com>
 <CAOCebiLgKWe1Erbt-mqWrg6fhv_bBF-O4atZvWQaYoi+rTDvEQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAOCebiLgKWe1Erbt-mqWrg6fhv_bBF-O4atZvWQaYoi+rTDvEQ@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220526160519.GB19626%40telsasoft.com>
Precedence: bulk

> 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