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

On 9/30/24 04:32, DBA wrote:
> 
> 1. A description of what you are trying to achieve and what results you
> expect : 
> 
> 
> We set shared_buffer = 70GB, max_connections = 300, work_mem = 300MB.
> 
> 
> Our setup is as above. When monitored by TOP CPU, we confirmed that RES
> uses up to 500MB in one postgres session. 
> 
> The session memory was initially a low number and then gradually
> increased to 500MB.
> 
> We wonder what the contents of session memory have, and wonder if there
> is a way to solve this. 
> 
> Also, considering our server specification,  if things like
> shared_buffer or work_mem need to be adjusted.
> 
> I'm inquiring because I often use swap due to lack of memory due to a
> large amount of session memory.
> 

I think you may be confused about what RSS means. It is not "private"
session memory, dedicated to the single backend process. For example, as
the processes "touch" shared memory, that'll be counted in RSS too.

If you want to look closer, you can look at "smaps" for each process in
/proc/$PID/smaps, which has info about all the memory. For one of "my"
backends I see this entry:

  7f4fecf42000-7f50f7000000 rw-s 00000000 00:01 3126
                                                 /dev/zero (deleted)
  Size:            4358904 kB
  KernelPageSize:        4 kB
  MMUPageSize:           4 kB
  Rss:             1622120 kB
  Pss:              323476 kB
  Pss_Dirty:        323476 kB
  Shared_Clean:          0 kB
  Shared_Dirty:    1622120 kB
  Private_Clean:         0 kB
  Private_Dirty:         0 kB
  Referenced:      1622120 kB
  ...

which is clearly shared memory (shared buffers, actually), but it adds
1.6GB to RSS.

It is a bit weird / surprising, but that's what Linux does.

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.
Chances are this alone will fix the OOM.

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.

If this doesn't help, you need to investigate if there's one query using
too much memory, or if it's simply a the total memory usage. You can
either monitor the system, but the OOM killer should have also logged
stas about the killed process (how much memory it used etc.).

Also, check the memory overcommit setting.


regards

-- 
Tomas Vondra

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