How expensive are idle connections?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Troels Arvin - 29 Jan 2007 14:01 GMT

Hello,

Some of our SAS users seem to initiate 20+ idle connections to DB2 every
time they start SAS. I'm inclined to tell them to cut down on their DB2
connection number, but then again, if the connections are very cheap why
bother.

Are idle DB2 connections cheap, from the DBMS' point of view?

(I fear that there's no easy answer to this question, but maybe someone
can comment on it.)

Signature

Regards,
Troels Arvin <troels@arvin.dk>
http://troels.arvin.dk/

Reply to this Message

shenanwei@gmail.com - 29 Jan 2007 14:58 GMT

I think it is because SAS is trying to do a connection pool.
How many application y have ? What is your max agent defined?
A connection will use around 2MB from the server side based on your
configuration about the agent.

> Hello,
>
[quoted text clipped - 11 lines]
> Regards,
> Troels Arvin <tro...@arvin.dk>http://troels.arvin.dk/

Reply to this Message

Troels Arvin - 30 Jan 2007 07:18 GMT

On Mon, 29 Jan 2007 06:58:45 -0800, shenanwei wrote:

> I think it is because SAS is trying to do a connection pool.

OK; then, it obviously fails ;-)
I wonder if anyone here has pointers to good documents about the SAS+DB2
combination, perhaps including how SAS can be told to pool database
connections between several LIBNAMEs which use a common database?

> How many application y have ? What is your max agent defined?

The other day, we hit our maximum of 400 connections; probably because
some users' SAS created 22 idle database connections to the same database
simply by starting SAS (not doing any analysis work at all).

> A connection will use around 2MB from the server side based on your
> configuration about the agent.

OK, thanks. Now, at least, I have a figure to calculate with.

Signature

Regards,
Troels Arvin <troels@arvin.dk>
http://troels.arvin.dk/

Reply to this Message

Mark A - 29 Jan 2007 19:09 GMT

> Hello,
>
[quoted text clipped - 7 lines]
> (I fear that there's no easy answer to this question, but maybe someone
> can comment on it.)

Reply to this Message

Mark A - 29 Jan 2007 19:12 GMT

> Hello,
>
[quoted text clipped - 7 lines]
> (I fear that there's no easy answer to this question, but maybe someone
> can comment on it.)

It is basically a memory issue. But 20 connections should not be a problem
in most cases. The default for a new instance is 400 agents (there is
usually one agent per connection unless queries or utilities are using
parallelism).

Reply to this Message