Re: checkpoint process use too much memory

hello,
we are seeing this issue again, the checkpoint process is eating up all the memory,
based on the htop, the checkpointer process has
VIRT: 103G
RES: 103G
SHR: 4099M
MEM%: 41%

I use ps_mem.py to check the process,
https://github.com/pixelb/ps_mem
=================================
Private + Shared = RAM used Program

99.5 GiB + 37.5 MiB = 99.5 GiB postgres
---------------------------------
99.5 GiB
=================================
my pg version is 10.3 , shared_buffer size is 4GB. work_mem 64MB. there are about 120 connections on the db side. most of them are idle.
does anyone have idea how to troubleshooting this kind of issue?
thank you
James
________________________________
From: jian xu <jamesxu(at)outlook(dot)com>
Sent: Tuesday, April 21, 2020 16:07
To: MichaelDBA <MichaelDBA(at)sqlexec(dot)com>; Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
Cc: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; pgsql-admin(at)postgresql(dot)org <pgsql-admin(at)postgresql(dot)org>
Subject: Re: checkpoint process use too much memory

Good Point, Thanks Michael. Next time I will check the memory usage of other working processes.
looks like it is hard to troubleshooting the postgresql memory usage problem.
Some memory are shared, some are private, some are physical memory, some are virtual memory.
and the ps command shows the misleading memory usage of postgresql process,
is there any tool show memory usage for each postgresql process more clearly?
thanks,
James
________________________________
From: MichaelDBA <MichaelDBA(at)sqlexec(dot)com>
Sent: Monday, April 20, 2020 15:37
To: Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
Cc: jian xu <jamesxu(at)outlook(dot)com>; Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; pgsql-admin(at)postgresql(dot)org <pgsql-admin(at)postgresql(dot)org>
Subject: Re: checkpoint process use too much memory

You said: "the work_mem is 128MB, so they could use mostly 25GB memory,"

But work_mem is sometimes very deceiving based on types of queries executed. You may need many work_mem buffers for a single query. So 8 buffers used = 1GB. So it could conceivable surpass 25GB easily. I would do some explaining on your SQL workload.

Regards,
Michael Vitale

Scott Ribe wrote on 4/20/2020 3:23 PM:

On Apr 20, 2020, at 1:13 PM, jian xu <jamesxu(at)outlook(dot)com><mailto:jamesxu(at)outlook(dot)com> wrote:

Do you mean the file cache will be counted in postgres checkpointer memory usage?

No, it won't be counted as PG memory. I just mean that free memory low is not a sign of a problem, when it's file cache.