pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcheckpoint process use too much memory
8+ messages / 4 participants
[nested] [flat]
* checkpoint process use too much memory
@ 2020-04-19 13:56 jian xu <jamesxu@outlook.com>
2020-04-20 09:06 ` Re: checkpoint process use too much memory Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 8+ messages in thread
From: jian xu @ 2020-04-19 13:56 UTC (permalink / raw)
To: pgsql-admin
Hello,
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.
Thanks for your help!
James
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: checkpoint process use too much memory
2020-04-19 13:56 checkpoint process use too much memory jian xu <jamesxu@outlook.com>
@ 2020-04-20 09:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-04-20 17:52 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2020-04-20 09:06 UTC (permalink / raw)
To: jian xu <jamesxu@outlook.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: checkpoint process use too much memory
2020-04-19 13:56 checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 09:06 ` Re: checkpoint process use too much memory Laurenz Albe <laurenz.albe@cybertec.at>
@ 2020-04-20 17:52 ` jian xu <jamesxu@outlook.com>
2020-04-20 18:25 ` Re: checkpoint process use too much memory Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: jian xu @ 2020-04-20 17:52 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
Thanks Laurenz. that' very helpful!
when we saw this issue, we restarted the pg service. so we lost the process info right now.
However, we saw the server run out of the memory at that time, the free memory size was very low.
As you said, if this was a red herring, how could pg use so much memory?
the shared_buffer is 4GB, there were 200 user connections , the work_mem is 128MB, so they could use mostly 25GB memory,
so shared_buffer(4GB) + user connections(25GB) = 29GB. why did htop RES show 171GB?
I am also wondering if this issue recurs, what information shall I collect, how to figure out which part of pg use most of the memory?
any hint or tool is available?
thanks for your help
James
________________________________
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Monday, April 20, 2020 5:06
To: jian xu <jamesxu@outlook.com>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: 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 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: checkpoint process use too much memory
2020-04-19 13:56 checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 09:06 ` Re: checkpoint process use too much memory Laurenz Albe <laurenz.albe@cybertec.at>
2020-04-20 17:52 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
@ 2020-04-20 18:25 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-04-20 19:13 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2020-04-20 18:25 UTC (permalink / raw)
To: jian xu <jamesxu@outlook.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: checkpoint process use too much memory
2020-04-19 13:56 checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 09:06 ` Re: checkpoint process use too much memory Laurenz Albe <laurenz.albe@cybertec.at>
2020-04-20 17:52 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 18:25 ` Re: checkpoint process use too much memory Scott Ribe <scott_ribe@elevated-dev.com>
@ 2020-04-20 19:13 ` jian xu <jamesxu@outlook.com>
2020-04-20 19:23 ` Re: checkpoint process use too much memory Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: jian xu @ 2020-04-20 19:13 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
Thanks Scott. Do you mean the file cache will be counted in postgres checkpointer memory usage?
The htop RES is 171GB, do you mean most of the memory are used by file cache, and counted as htop RES size?
thanks
James
________________________________
From: Scott Ribe <scott_ribe@elevated-dev.com>
Sent: Monday, April 20, 2020 14:25
To: jian xu <jamesxu@outlook.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: checkpoint process use too much memory
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: checkpoint process use too much memory
2020-04-19 13:56 checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 09:06 ` Re: checkpoint process use too much memory Laurenz Albe <laurenz.albe@cybertec.at>
2020-04-20 17:52 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 18:25 ` Re: checkpoint process use too much memory Scott Ribe <scott_ribe@elevated-dev.com>
2020-04-20 19:13 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
@ 2020-04-20 19:23 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-04-20 19:37 ` Re: checkpoint process use too much memory MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2020-04-20 19:23 UTC (permalink / raw)
To: jian xu <jamesxu@outlook.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: checkpoint process use too much memory
2020-04-19 13:56 checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 09:06 ` Re: checkpoint process use too much memory Laurenz Albe <laurenz.albe@cybertec.at>
2020-04-20 17:52 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 18:25 ` Re: checkpoint process use too much memory Scott Ribe <scott_ribe@elevated-dev.com>
2020-04-20 19:13 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 19:23 ` Re: checkpoint process use too much memory Scott Ribe <scott_ribe@elevated-dev.com>
@ 2020-04-20 19:37 ` MichaelDBA <MichaelDBA@sqlexec.com>
2020-04-21 20:07 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: MichaelDBA @ 2020-04-20 19:37 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: jian xu <jamesxu@outlook.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
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@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.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: checkpoint process use too much memory
2020-04-19 13:56 checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 09:06 ` Re: checkpoint process use too much memory Laurenz Albe <laurenz.albe@cybertec.at>
2020-04-20 17:52 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 18:25 ` Re: checkpoint process use too much memory Scott Ribe <scott_ribe@elevated-dev.com>
2020-04-20 19:13 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 19:23 ` Re: checkpoint process use too much memory Scott Ribe <scott_ribe@elevated-dev.com>
2020-04-20 19:37 ` Re: checkpoint process use too much memory MichaelDBA <MichaelDBA@sqlexec.com>
@ 2020-04-21 20:07 ` jian xu <jamesxu@outlook.com>
0 siblings, 0 replies; 8+ messages in thread
From: jian xu @ 2020-04-21 20:07 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
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@sqlexec.com>
Sent: Monday, April 20, 2020 15:37
To: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: jian xu <jamesxu@outlook.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.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@outlook.com><mailto: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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2020-04-21 20:07 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-19 13:56 checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-04-20 09:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-04-20 17:52 ` jian xu <jamesxu@outlook.com>
2020-04-20 18:25 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-04-20 19:13 ` jian xu <jamesxu@outlook.com>
2020-04-20 19:23 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-04-20 19:37 ` MichaelDBA <MichaelDBA@sqlexec.com>
2020-04-21 20:07 ` jian xu <jamesxu@outlook.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox