From: Andy Fan <zhihuifan1213@163.com>
To: Tomas Vondra <tomas@vondra.me>
Cc: DBA <ecountdba@ecounterp.co.kr>,  "pgsql-bugs@lists.postgresql.org"
 <pgsql-bugs@lists.postgresql.org>
Subject: Re: what are the things that occupy the session memory.
In-Reply-To: <6962bb55-37fd-4b8d-83ca-822d2087b1a2@vondra.me> (Tomas Vondra's
	message of "Wed, 2 Oct 2024 10:47:42 +0200")
References: <20240930023236.9EC5610AA47@wsmtpa1.ecounterp.com>
	<6962bb55-37fd-4b8d-83ca-822d2087b1a2@vondra.me>
Date: Sun, 06 Oct 2024 12:19:46 +0800
Message-ID: <87zfnhkgm5.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/87zfnhkgm5.fsf%40163.com>
Precedence: bulk

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