Hello,

i have a problem with my connection-pooling in my sa-12 server. In my programm is often open and close connections (for nearly every query). After a while, i get the error message that the maximum of poolsize is reached. if i take a look in Sybase Central, there are a lot of Connections with the type: "CONNECTPOOLCACHE".

If i add CPOOL=No to my connection-string, nothing changed. There are also a lot of connections.

Does anybody else had this problem and got a solution?

Thank you!

asked 18 Feb '14, 03:09

BenEg's gravatar image

BenEg
71338
accept rate: 0%


What API are you using? I'm asking since the .NET Data Provider does its own connection pooling (client-side) by default and does not use the SQL Anywhere connection pooling facility which can be used via the CPOOL connection parameter.

Note: Both ways of connection pooling allow to specify the maximum amount of pooled connections, cf. "Max Pool Size=" for .NET and the "MaxCached=" modifier for the CPOOL parameter.

So what kind of connection pooling do you use?

permanent link

answered 18 Feb '14, 03:25

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 18 Feb '14, 03:32

Perfect, the .Net Connection-Pooling was the problem. What pooling would you prefer? Do you have an idea why: POOLING=TRUE;Max Pool Size=3; is not working? The program keeps more than 3 connections in the pool.

(18 Feb '14, 05:30) BenEg
Replies hidden

Sorry, I can't tell any preferance. Note that .NET connection pooling may use several separate pools (AFAIK, based on distinct connection parameters) - are you sure you are using only one?

(18 Feb '14, 06:02) Volker Barth

Yes, because if i set POOLING=FALSE, the pool is always empty.

(18 Feb '14, 07:26) BenEg

Setting POOLING=FALSE does not tell you whether you have unique connection strings. For example, these two connection strings create 2 pools because the string is not identical.

UID=user1;PWD=user1;Max Pool Size=5;Pooling=true;ConnectionName=POOL 1

UID=user1;PWD=user1;Max Pool Size=5;Pooling=true;ConnectionName=POOL 2

(18 Feb '14, 12:36) JBSchueler
Replies hidden

Re: error message that the maximum of poolsize is reached

It might help to know what the actual message text was.

(18 Feb '14, 12:38) JBSchueler

So the rules for connection pooling are different for .NET pooling and SQL Anywhere pooling? - I'm asking as for the latter, a difference in CON name only is said to allow a connection to be re-used. To cite the 12.0.1 docs:

The connection name can be unique each time, but all other connection parameters must be identical for a cached connection to be reused.

(18 Feb '14, 15:41) Volker Barth
showing 4 of 6 show all flat view

If your message says this: Timeout expired. The timeout period elapsed before obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

If your pool size is 3, then you have attempted to open a 4th connection using the same connection string while the other 3 connections were still in use.

What is your Connect Timeout/Connection Timeout? The default is 15 seconds and the maximum is 60 seconds.

There are three things you could do.

  1. Increase the connect timeout value in order to give your existing connections time to get their work done.
  2. Increase the pool size so that a new connection can be made when the others are all in use.
  3. Make sure that you close() your connections when you are done; otherwise they remain open.
permanent link

answered 18 Feb '14, 13:11

JBSchueler's gravatar image

JBSchueler
2.1k2837
accept rate: 15%

edited 18 Feb '14, 13:13

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×415
×133
×13

question asked: 18 Feb '14, 03:09

question was seen: 1,762 times

last updated: 18 Feb '14, 15:41