Does idle sessions will consume more cpu and ram? If yes,how to control them

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?

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

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.

č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.

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.

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