pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16831: Idle postgres processes on Linux consumes huge amount of memory
3+ messages / 3 participants
[nested] [flat]

* BUG #16831: Idle postgres processes on Linux consumes huge amount of memory
@ 2021-01-20 21:04  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 3+ messages in thread

From: PG Bug reporting form @ 2021-01-20 21:04 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: yli@rsa.com

The following bug has been logged on the website:

Bug reference:      16831
Logged by:          Yaqing Li
Email address:      yli@rsa.com
PostgreSQL version: Unsupported/Unknown
Operating system:   Suse Linux
Description:        

We are still using postgresql 9.4.1, however, it looks the same issue as in
9.5 as someone reported:
https://www.postgresql.org/account/reset/NzAyMjY-5ng-2bb43dc39c9c2c644a7a/

We have some client side read only user run report against the database
using ADODB. On a 16GB system, when the memory setting for database is as
following.
    shared_buffers       = 3840MB
    effective_cache_size = 8GB
    max_connections      = 200
    work_mem             = 80MB
     maintenance_work_mem = 960MB
The memory consumption for each postgres process is like this:
    PID=11152 - Swap used: 3736 KB - (postgres)
   PID=3380 - Swap used: 1580 KB - (postgres)
   PID=11150 - Swap used: 1568 KB - (postgres)
   PID=11147 - Swap used: 1352 KB - (postgres)
    PID=3382 - Swap used: 660 KB - (postgres)

The system works fine with that,
But after we changed the system memory to 32GB, and adjusted the database
memory to the following:
   shared_buffers       = 7680MB
   effective_cache_size = 16GB
   max_connections      = 200
   work_mem             = 160MB
   maintenance_work_mem = 1920MB

The postgres process consume huge memory as following for the same database
size
   PID=6840 - Swap used: 145860 KB - (postgres)

   PID=6842 - Swap used: 142564 KB - (postgres)

    PID=6841 - Swap used: 141620 KB - (postgres)

So it looks to me there is a bug in PostgresSql about the memory allocation,
is it fixed in any version?



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #16831: Idle postgres processes on Linux consumes huge amount of memory
@ 2021-01-20 23:19  Tom Lane <tgl@sss.pgh.pa.us>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2021-01-20 23:19 UTC (permalink / raw)
  To: yli@rsa.com; +Cc: pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> writes:
> We are still using postgresql 9.4.1, however, it looks the same issue as in
> 9.5 as someone reported:
> https://www.postgresql.org/account/reset/NzAyMjY-5ng-2bb43dc39c9c2c644a7a/

That link's not too helpful: it leads to a "password reset" form.

> The postgres process consume huge memory as following for the same database
> size
>    PID=6840 - Swap used: 145860 KB - (postgres)
>    PID=6842 - Swap used: 142564 KB - (postgres)
>     PID=6841 - Swap used: 141620 KB - (postgres)

Almost invariably, when we hear reports like this, the true reason
is somebody misunderstanding the numbers they are looking at.
Many Unix tools such as "top" will count the shared memory segment
as memory consumed by each Postgres process, even though in reality
there's only one copy of the shared memory.

I don't know what these "Swap used" numbers are coming out of, but
it'd be wise to check into how the tool accounts for shared memory
before putting much faith in them.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: BUG #16831: Idle postgres processes on Linux consumes huge amount of memory
@ 2021-01-21 15:40  Li, Yaqing <yaqing.li@rsa.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Li, Yaqing @ 2021-01-21 15:40 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Thanks Tom!
Sorry, the link is this one: https://dba.stackexchange.com/questions/185880/analyze-memory-usage-of-postgresql-why-is-it-growing-...

The memory report is on the same system, the only difference is the memory setting. Even if on the same 32GB system,  I changed the database memory setting, the postgres memory consumption difference is unproportioned, just the same as I reported, what is the reason for that?

Yaqing

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us> 
Sent: Wednesday, January 20, 2021 6:19 PM
To: Li, Yaqing
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16831: Idle postgres processes on Linux consumes huge amount of memory


[EXTERNAL EMAIL] 

PG Bug reporting form <noreply@postgresql.org> writes:
> We are still using postgresql 9.4.1, however, it looks the same issue 
> as in
> 9.5 as someone reported:
> https://www.postgresql.org/account/reset/NzAyMjY-5ng-2bb43dc39c9c2c644
> a7a/

That link's not too helpful: it leads to a "password reset" form.

> The postgres process consume huge memory as following for the same 
> database size
>    PID=6840 - Swap used: 145860 KB - (postgres)
>    PID=6842 - Swap used: 142564 KB - (postgres)
>     PID=6841 - Swap used: 141620 KB - (postgres)

Almost invariably, when we hear reports like this, the true reason is somebody misunderstanding the numbers they are looking at.
Many Unix tools such as "top" will count the shared memory segment as memory consumed by each Postgres process, even though in reality there's only one copy of the shared memory.

I don't know what these "Swap used" numbers are coming out of, but it'd be wise to check into how the tool accounts for shared memory before putting much faith in them.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2021-01-21 15:40 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-20 21:04 BUG #16831: Idle postgres processes on Linux consumes huge amount of memory PG Bug reporting form <noreply@postgresql.org>
2021-01-20 23:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-01-21 15:40   ` Li, Yaqing <yaqing.li@rsa.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