Message-ID: <5193b00d175f2e06a171ba375f1ef723e1700de4.camel@cybertec.at>
Subject: Re: checkpoint process use too much memory
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: jian xu <jamesxu@outlook.com>, "pgsql-admin@postgresql.org"
	 <pgsql-admin@postgresql.org>
Date: Mon, 20 Apr 2020 11:06:50 +0200
In-Reply-To: 
 <MN2PR05MB68322593E34D4BF78AD45137A1D70@MN2PR05MB6832.namprd05.prod.outlook.com>
References: 
	<MN2PR05MB68322593E34D4BF78AD45137A1D70@MN2PR05MB6832.namprd05.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.32.5 (3.32.5-1.fc30) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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 in checkpointer 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