From: Justin Pryzby <pryzby@telsasoft.com>
To: 徐志宇徐 <xuzhiyuster@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: How to monitor Postgres real memory usage
Date: Thu, 26 May 2022 11:05:19 -0500
Message-ID: <20220526160519.GB19626@telsasoft.com> (raw)
In-Reply-To: <CAOCebiLgKWe1Erbt-mqWrg6fhv_bBF-O4atZvWQaYoi+rTDvEQ@mail.gmail.com>
References: <CAOCebi+i=HPofBSdvs1RftcZpAHg81R9jqmr5xTJm0dGSDUMNQ@mail.gmail.com>
<20220524174050.GI19626@telsasoft.com>
<CAOCebiLgKWe1Erbt-mqWrg6fhv_bBF-O4atZvWQaYoi+rTDvEQ@mail.gmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, xuzhiyuster@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: How to monitor Postgres real memory usage
In-Reply-To: <20220526160519.GB19626@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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