From: Andy Fan <zhihuifan1213@163.com>
To: Tomas Vondra <tomas@vondra.me>
Cc: DBA <ecountdba@ecounterp.co.kr>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: what are the things that occupy the session memory.
Date: Sun, 06 Oct 2024 12:19:46 +0800
Message-ID: <87zfnhkgm5.fsf@163.com> (raw)
In-Reply-To: <6962bb55-37fd-4b8d-83ca-822d2087b1a2@vondra.me>
References: <20240930023236.9EC5610AA47@wsmtpa1.ecounterp.com>
<6962bb55-37fd-4b8d-83ca-822d2087b1a2@vondra.me>
Tomas Vondra <tomas@vondra.me> writes:
> Anyway, I agree shared buffers 70GB is way too high - I'd reduce that to
> maybe 8GB and only increase that if cache hit ratio is below 0.95 or
> so.
I'm always confused how should shared_buffers be set initially (saying
monitor and adjust them later is not free.) Some places say 25% of
physical memory but I don't know why is that. I'm not sure my question
can have a answer, but just have a try. The known drawback of big shared
buffer from me includes: a). Seize the memory from other
component which may contribute to OOM. b). make some operation
slower. e.g. drop / truncate table.
> If not, try reducing work_mem. If you're doing OLTP queries, those
> likely don't need wm=300MB (why did you set this value?). For OLAP
> queries 300MB might make sense, but then maybe you shouldn't have 300 of
> them.
I want to have a metion of autovacuum_work_mem/maintenance_work_mem for
a OLTP workload. In OLTP workload, user probably doesn't need a bigger
work_mem but if user set it to a bigger value, it should not cause a big
issue IIUC, since it just set a upper bound. However in OLTP workload, it
probably has lots of updates/delete, and if they have lots of tables, it
probably use up to {autovacuum_work_mem} memory per vacuum worker, which
is more likely cause an issue.
--
Best Regards
Andy Fan
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-bugs@postgresql.org
Cc: zhihuifan1213@163.com, tomas@vondra.me, ecountdba@ecounterp.co.kr, pgsql-bugs@lists.postgresql.org
Subject: Re: what are the things that occupy the session memory.
In-Reply-To: <87zfnhkgm5.fsf@163.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