Re: what are the things that occupy the session memory.

Tomas Vondra <tomas(at)vondra(dot)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