Available connections decreasing, not freed?

[Former Member]

Hi all,

according to SAP note 719778 I have increased the poolsize of our data servers to avoid timeout and performance problems.

Now I wonder why the "Number of available connections" gets lower and lower. Are there queries which keep the pool connections? Is it a problem if the there are only few connections left?

For example, I initialized a data source with poolsize 100. Now after 2 hours the connection status shows number of available connections 6 (used con = 0, max used con = 0). How should I interpret those values?

Michael

[jcgood25]

NW Data Source for the connection to the underlying NW DB or MII Data Server to an external database?

[Former Member]

Jeremy,

connection to external database, created in the Data Servers section of MII. I am watching the connection status. Two of the DB connections are currently reducing the count of avaiable connections. Initial value is 100.

Michael

[jcgood25]

OK, I was a bit confused since that note would only be of relevance on the NW datasource side.

I assume you've already looked at the server attributes for pooling on this page (http://help.sap.com/saphelp_xmii120/helpdata/en/45/22b06aaf3d18e9e10000000a11466f/frameset.htm)?

Initial Value of 100 meaning PoolSize or PoolMax ?

Perhaps you can provide a sanitized data server summary resultset in the forum?

[Former Member]

Jeremy,

jup, I've had a look at the help.

This is a view of the summary of one of the Data Servers:

Connector IDBC

ConnectorID

ConnectorType SQL

DatePrefix '

DateSuffix '

DaysRetention 7

Description ---

Enabled T

InitCommand

InternalDateFormat yyyy-MM-dd HH:mm:ss

JDBCDriver oracle.jdbc.driver.OracleDriver

MaxRetryCount 5

Name

PoolMax 100

PoolSize 100

RetryInterval 60000

ServerPackage com.sap.xmii.Illuminator.connectors.IDBC

ServerURL

Timeout 60

UseCount 256

UserName

ValidationQuery SELECT 'Validation' FROM dual

WaitTime 30

The status now looks like this:

Connector Type SQL

Status Running

No. of Used Connections 0

No. of Available Connections 89

Max. No. of Used Connections 100

Max. Wait Time 0.0

Michael

[jcgood25]

Raising your PoolSize to 100, which is the same as PoolMax doesn't make any sense. This just means that the initial creation of the connection pool itself will take longer. Having a bigger pipe of connections open like 10 or 20 if your Data Server is used heavily will be OK, but I don't see much value in bumping this up quite so high.

The pool will automatically throttle open and request a new connection in the pool if all available (maintained, but idle) connections are in use, up to the Pool Max. Each open available connection will get reused up to the Use Count, and the tiny Validation Query is used to make sure the connection has not been terminated on the database server side.

Were you experiencing issues? Were you getting large wait times for connections (obviously the actual query execution and response time largely depends upon Oracle).

Regards,

Jeremy

[Former Member]

Jeremy,

we lately had wuite a lot problems with performance and timeout errors, so I was looking for reasons. SAP colleagues gave that SAP note If have mentioned before, and it proposes the value 100, so I have tried it.

Now a hardware change (SAPS increased) improved the overall performance, but I was not sure about the poolsize / poolmax parameters, and why the available connections number goes down. I thought, the connections won't be held but freed after use.

I guess you are right to set a size like 20 for heavily used DBs and see what happens. To me this is some kind of try and error thing, and we are still circling around the middle.

Michael

[jcgood25]

Michael,

I still think there is a bit of confusion between the SAP Note for tuning the NetWeaver connection to it's Oracle DB, where the default setup is a bit smaller than desired. We have recently seen other 12.0 customers experience wait issues for db connections, but that has been the primary NW Datasource connection managed through Visual Admin. This primary NW DataSource is the one that MII would be using to access it's configuration and files stored in the underlying DB.

The Data Servers of MII are maintained connection pools on the MII side, supporting all of the associated Query Template requests (nothing to do with the NW DataSource). From my experiences Oracle is a bit sluggish when creating new connections, and namespace browsing Tables / Columns in the WB or Template Editor. I'm not sure if this is a JDBC driver issue or just Oracle, but in either case MII just makes the request and waits for the response.

Unless you have very long running queries and tons of concurrent user requests, I would assume that most of the available connections in the MII connection pool are just awaiting their next use. At some point (TimeOut setting) they do expire and the number will go back down, even if the Use Count has not been met, so in the case of heavily used MII data servers it may make sense to bump up the Pool Size to a larger number, but since it grows automatically up to the Pool Max there's no need to open it larger than really needed.

Regards,

Jeremy

[Former Member]

Jeremy,

thanks for clarification. We also experience wait issues on the MII "file system", if we store lots of files in it. I used to store XML files as a means to easily look them up or use them as input or for error investigation. However, as we produce many files, I even was at a point where the workbench did not show me any files in a directory anymore. Using the Publish assistant, I could see my files again. But this is another story. I will change the logging of XML contents to a DB.

Concerning the Data Servers, I will reduce the poolsize and wait what happens.

Next step will be refactoring of the queries and BLTs. We sometimes use repeaters with many small insert/update statements, maybe we can improve this.

Hope to get this stable before christmas

Michael

[Former Member]

I think Jeremy's onto it with the "cleanup" code. The connections will be released after a certain number of uses/time...and since you have so many already allocated, there's no reason to allocate more.

Another reason not to create too many ahead of time is in the case where your database server occasionally goes offline. If so, you'll end up with a bunch of "stale" connections (which will eventually be validated/recovered/replaced) but it could create occasional pauses/delays in execution while it tests/fails/re-establishes the connection.

Edited by: Rick Bullotta on Dec 10, 2008 11:01 AM