Thread: Does idle sessions will consume more cpu and ram? If yes,how to control them
Does idle sessions will consume more cpu and ram? If yes,how to control them
From
Raghavendra Rao J S V
Date:
In my application, the idle sessions are consuming cpu and ram. refer the ps command output.
How idle session will consume more ram/cpu?
How to control it?
We are using Postgresql 9.2 with Centos 6 os. Please guide me.
Regards,
Raghavendra Rao J S V
Mobile- 8861161425
Raghavendra Rao J S V
Mobile- 8861161425
Attachment
Re: Does idle sessions will consume more cpu and ram? If yes,how tocontrol them
From
Joshua White
Date:
In my application, the idle sessions are consuming cpu and ram. refer the ps command output.
If you connect to the database, does select * from pg_stat_activity() show a lot of idle connections?
Re: Does idle sessions will consume more cpu and ram? If yes,how tocontrol them
From
Michael Paquier
Date:
On Thu, Dec 20, 2018 at 11:32:22AM +1100, Joshua White wrote: >> In my application, the idle sessions are consuming cpu and ram. refer the >> ps command output. >> > > If you connect to the database, does select * from pg_stat_activity() show > a lot of idle connections? Each backend stores its own copy of the relation cache, so if you have idle connections which have been used for other work in the past then the memory of those caches is still around. Idle connections also have a CPU cost in Postgres when building snapshots for example, and their entries need to be scanned from a wider array, but usually the relation cache bloat is a wider problem. This can be countered with pgbouncer as connection pooling. -- Michael
Attachment
On 12/19/18 7:27 PM, Michael Paquier wrote: [snip] > Each backend stores its own copy of the relation cache, so if you have > idle connections which have been used for other work in the past then > the memory of those caches is still around. Idle connections also have > a CPU cost in Postgres when building snapshots for example, and their > entries need to be scanned from a wider array, but usually the relation > cache bloat is a wider problem. So it's best to kill connections that have been idle for a while? -- Angular momentum makes the world go 'round.
Re: Does idle sessions will consume more cpu and ram? If yes,how tocontrol them
From
Pavel Stehule
Date:
čt 20. 12. 2018 v 2:41 odesílatel Ron <ronljohnsonjr@gmail.com> napsal:
On 12/19/18 7:27 PM, Michael Paquier wrote:
[snip]
> Each backend stores its own copy of the relation cache, so if you have
> idle connections which have been used for other work in the past then
> the memory of those caches is still around. Idle connections also have
> a CPU cost in Postgres when building snapshots for example, and their
> entries need to be scanned from a wider array, but usually the relation
> cache bloat is a wider problem.
So it's best to kill connections that have been idle for a while?
sure - one hour idle connection is too old.
--
Angular momentum makes the world go 'round.
Re: Does idle sessions will consume more cpu and ram? If yes,how tocontrol them
From
Joshua White
Date:
On Thu, 20 Dec 2018 at 14:35, Pavel Stehule <pavel.stehule@gmail.com> wrote:
čt 20. 12. 2018 v 2:41 odesílatel Ron <ronljohnsonjr@gmail.com> napsal:On 12/19/18 7:27 PM, Michael Paquier wrote:
[snip]
> Each backend stores its own copy of the relation cache, so if you have
> idle connections which have been used for other work in the past then
> the memory of those caches is still around. Idle connections also have
> a CPU cost in Postgres when building snapshots for example, and their
> entries need to be scanned from a wider array, but usually the relation
> cache bloat is a wider problem.
So it's best to kill connections that have been idle for a while?sure - one hour idle connection is too old.
I'd also assess closing the connection from the client end once its task is done - that would reduce the number of idle connections in the first place.
Re: Does idle sessions will consume more cpu and ram? If yes,how to control them
From
Tom Lane
Date:
Joshua White <joshua.white@monash.edu> writes:
> On Thu, 20 Dec 2018 at 14:35, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> čt 20. 12. 2018 v 2:41 odesílatel Ron <ronljohnsonjr@gmail.com> napsal:
>>> So it's best to kill connections that have been idle for a while?
>> sure - one hour idle connection is too old.
> I'd also assess closing the connection from the client end once its task is
> done - that would reduce the number of idle connections in the first place.
IMO, "has it been idle a long time" is the wrong question. The right
question is "how likely is it to start doing something useful soon".
Certainly, leaving sessions sit doing nothing for a long time isn't
helpful. They consume RAM, they have to be accounted for by other
sessions, and if you're doing any DDL, they consume CPU time
maintaining their own caches in response to catalog changes. But it's
also true that starting a new session has a lot of overhead --- so you
don't want to kill a session that was just about to start doing some
useful work.
regards, tom lane