pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: checkpoint process use too much memory
5+ messages / 3 participants
[nested] [flat]
* Re: checkpoint process use too much memory
@ 2020-07-20 21:33 jian xu <jamesxu@outlook.com>
2020-07-20 22:08 ` Re: checkpoint process use too much memory Alvaro Herrera <alvherre@2ndquadrant.com>
2020-07-20 22:34 ` Re: checkpoint process use too much memory Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: jian xu @ 2020-07-20 21:33 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
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@outlook.com>
Sent: Tuesday, April 21, 2020 16:07
To: MichaelDBA <MichaelDBA@sqlexec.com>; Scott Ribe <scott_ribe@elevated-dev.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.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@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] 5+ messages in thread
* Re: checkpoint process use too much memory
2020-07-20 21:33 Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
@ 2020-07-20 22:08 ` Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2020-07-20 22:08 UTC (permalink / raw)
To: jian xu <jamesxu@outlook.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Scott Ribe <scott_ribe@elevated-dev.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
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. most of 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: checkpoint process use too much memory
2020-07-20 21:33 Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
@ 2020-07-20 22:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-07-21 13:20 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2020-07-20 22:34 UTC (permalink / raw)
To: jian xu <jamesxu@outlook.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Scott Ribe <scott_ribe@elevated-dev.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: checkpoint process use too much memory
2020-07-20 21:33 Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-07-20 22:34 ` Re: checkpoint process use too much memory Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-07-21 13:20 ` jian xu <jamesxu@outlook.com>
2020-07-21 23:48 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
0 siblings, 1 reply; 5+ messages in thread
From: jian xu @ 2020-07-21 13:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Scott Ribe <scott_ribe@elevated-dev.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
Thanks Tom and Alvaro. I will upgrade it to 10.13 first.
James
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Monday, July 20, 2020 18:34
To: jian xu <jamesxu@outlook.com>
Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Scott Ribe <scott_ribe@elevated-dev.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: checkpoint process use too much memory
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: checkpoint process use too much memory
2020-07-20 21:33 Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-07-20 22:34 ` Re: checkpoint process use too much memory Tom Lane <tgl@sss.pgh.pa.us>
2020-07-21 13:20 ` Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
@ 2020-07-21 23:48 ` jian xu <jamesxu@outlook.com>
0 siblings, 0 replies; 5+ messages in thread
From: jian xu @ 2020-07-21 23:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Scott Ribe <scott_ribe@elevated-dev.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
btw, I checked /proc/<PID>/smaps. it looks like postgres process took most of the memory, the output is like:
===============================================================================
564415881000-56441588f000 rw-p 00857000 08:04 17682620 postgresql/10/3/dist/bin/postgres
Size: 56 kB
KernelPageSize: 4 kB
MMUPageSize: 4 kB
Rss: 56 kB
Pss: 56 kB
Shared_Clean: 0 kB
Shared_Dirty: 0 kB
Private_Clean: 0 kB
Private_Dirty: 56 kB
Referenced: 56 kB
Anonymous: 56 kB
LazyFree: 0 kB
AnonHugePages: 0 kB
ShmemPmdMapped: 0 kB
Shared_Hugetlb: 0 kB
Private_Hugetlb: 0 kB
Swap: 0 kB
SwapPss: 0 kB
Locked: 0 kB
VmFlags: rd wr mr mw me dw ac sd
5644162cf000-565d4c645000 rw-p 00000000 00:00 0 [heap]
Size: 105745880 kB
KernelPageSize: 4 kB
MMUPageSize: 4 kB
Rss: 105745692 kB
Pss: 105745692 kB
Shared_Clean: 0 kB
Shared_Dirty: 0 kB
Private_Clean: 0 kB
Private_Dirty: 105745692 kB
Referenced: 104768404 kB
Anonymous: 105745692 kB
LazyFree: 0 kB
AnonHugePages: 0 kB
ShmemPmdMapped: 0 kB
Shared_Hugetlb: 0 kB
Private_Hugetlb: 0 kB
Swap: 0 kB
SwapPss: 0 kB
Locked: 0 kB
VmFlags: rd wr mr mw me ac sd
===============================================================================
however, the shared_buffers size is only 4GB..
postgres=# show shared_buffers ;
shared_buffers
----------------
4GB
thanks,
James
________________________________
From: jian xu <jamesxu@outlook.com>
Sent: Tuesday, July 21, 2020 9:20
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Scott Ribe <scott_ribe@elevated-dev.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: checkpoint process use too much memory
Thanks Tom and Alvaro. I will upgrade it to 10.13 first.
James
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Monday, July 20, 2020 18:34
To: jian xu <jamesxu@outlook.com>
Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Scott Ribe <scott_ribe@elevated-dev.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: checkpoint process use too much memory
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-07-21 23:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-20 21:33 Re: checkpoint process use too much memory jian xu <jamesxu@outlook.com>
2020-07-20 22:08 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-07-20 22:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-07-21 13:20 ` jian xu <jamesxu@outlook.com>
2020-07-21 23:48 ` 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