Обсуждение: checkpoint process use too much memory

On Sun, 2020-04-19 at 13:56 +0000, jian xu wrote:
> My pg version is 10.3, last week my user got error :
> 
> OperationalError: (psycopg2.OperationalError) could not fork new process for connection: Cannot allocate memory
> could not fork new process for connection: Cannot allocate memory
>  
> when I checked the database, I found checkpoint process used too much memory, htop showed
> VIRT: 171GB
> RES: 171GB
> SHR:4176MB
> MEM%: 70%
> Command: Posgres: checkpointer process
>  
> The postgresq.conf is
> Shared_buffers = 4GB
> Work_mem = 128MB
> Maintenance_work_mem = 2GB
> Wal_level = replica
> Wal_buffer = 16MB
> Checkpoint_completion_target = 0.7
>  
> How can I troubleshooting this error? Why checkpoint process used so much memory? it sounds like a memory leak issue
incheckpointer process. 
 

That is probably a red herring, because the memory reported for the checkpointer
is shared memory that all PostgreSQL processes access.  Utilities like "ps"
show shared memory for all processes that access it, so they don't show the truth.

A better value would be the sum of all the Private_* entries in /proc/<pid>/smaps,
which shows only the private memory.

Not that the error does not necessarily mean that you are out of memory.
It might as well indicate that you are out of open file descriptors for the user
(see /proc/<pid>/limits).

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

> On Apr 20, 2020, at 11:52 AM, jian xu <jamesxu@outlook.com> wrote:
>
> the free memory size was very low

Remember that the OS will fill memory with file cache until something else needs it.

> On Apr 20, 2020, at 1:13 PM, jian xu <jamesxu@outlook.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.

On Apr 20, 2020, at 1:13 PM, jian xu <jamesxu@outlook.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.

On 2020-Jul-20, jian xu wrote:

>       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.
mostof them are idle.
 
> does anyone have idea how to troubleshooting this kind of issue?

I see no reason for checkpointer to use that much memory.  Accounting
for the whole of shared_buffers is okay (as Laurenz explaijned), which
in other installations may account for several gigabytes of memory.  But
if shared_buffers is 4GB, then 99GB is clearly overboard.  I see no
explanation for checkpointer using 25x shared_buffers.

Maybe looking at /proc/<PID>/smaps would tell you where that memory is
going?  Have a look there are see if you can figure out some obviously
absurd entries.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

jian xu <jamesxu@outlook.com> writes:
>       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

hmmm ...

> my pg version is 10.3 ,

Really the *first* thing you ought to do is update to current (10.13)
and see if the problem is still there.  But if it is, maybe collecting
a memory context map in that process would be informative:

https://wiki.postgresql.org/wiki/Developer_FAQ#Examining_backend_memory_use

Note you may need a debug build for that recipe to work.

            regards, tom lane