Secondary broker logins (increasing maximum number of connec

Hi David,

>> Is there a way to increase -Mpb or -Ma on the fly?

No.

>> It is possible that the connections are being kept in a connection pool or something like that?

Connection pooling is not supported by ODBC Progress OpenEdge driver (though it is supported by JDBC driver).  So, I suspect something else could be the issue?

I am sure that, you verified that, there are several open connections (around 800) to secondary broker using promon or some other utility.  

Link to KB article which says, connection pooling for ODBC for OE is not supported.

knowledgebase.progress.com/.../000038726

>> Is there an easy way to stop the secondary broker and restart?  Can someone please point me to a KB?

We cannot just stop and start secondary broker. Here are the related KB articles.

knowledgebase.progress.com/.../P18888

knowledgebase.progress.com/.../000050574

Thanks,

Akthar.

Out of interest:

a.  What are the startup parameters on your Primary Login Broker?

b.  What does the following look like: PROMON  > R&D > 1. Status Displays > 17. Servers By Broker

c.  What are these ODBC clients?

@makthar

Thanks for the KB articles and the confirmation that you cannot change -Mpb or -Ma on the fly.  That is very helpful.

I'm discovering that the ODBC connection pooling on windows is managed by the ODBC driver manager itself, and not by the driver/provider.  I suspect that there is no way to manipulate the connection pooling behavior from a connection string.  Here is a link that explains there are three ways to control ODBC connection pooling, but its not via the connection string, and its not from the ADO.Net API: www.oreilly.com/.../ch01.html

*Using the ODBC Data Source Administrator to enable or disable pooling for the entire driver...

*Using the ODBC API to control pooling options from an ODBC ...

*Editing the registry settings described previously.

@[mention:1c8915b597de418d841a613811ccf0bc:e9ed411860ed4f2ba0265705b8793d05]

The primary login broker looks like this (can never have too many startup parameters, right?)

-B 1500000 -B2 250000 -L 400000 -n 1500 -bibufs 200 -aibufs 200 -lkrela -spin 64000 -S MYPRIMARY -DBService replserv -pica 32768 -tablerangesize 1050 -indexrangesize 4200 -omsize 5200 -semsets 12 -bithold 32768 -lruskips 200 -aiarcdir /aiarchive -aiarcinterval 3600 -prefetchDelay -prefetchPriority 100 -refetchNumRecs 100 -Nmsgwait 2 -Mm 32000 -Mn 62 -Mpb 40 -Ma 5 -Mi 1"

Servers By Broker => When I had looked at this, each server was filled to capacity with 40 connections

The ODBC clients in question are coming from a front-end user interface application on Windows.  The user processes are opening direct connections to the database.  I found out afterwards that the connections are in fact being timed-out by the driver manager but not as fast as I would like.  In theory the odbc driver manager is supposed to be closing connections after a (default) 60 second connection pool timeout.  But in practice it seems to be taking 2 or 3 minutes.  

(As a side... It is possible that our custom code is part of the problem.  The custom ODBC (ADO.Net) code is not releasing connections properly or in a timely fashion.  There are some programming patterns in ADO.Net - like "try/finally" or "using" blocks - that are supposed to be used to ensure that connections are closed and disposed, but it doesn't look like the code is rigorously following those patterns.  Otherwise I think the driver-manager's pooling behavior would work better.)

Since Progress doesn't have a managed .net provider for connecting to the SQL92 engine, I guess I have to become more familiar with OdbcConnection in ADO.Net.  It is unfortunate that connection pooling isn't available in the driver itself (except via the ODBC driver-manager).  While the OdbcConnection won't support connection strings to manipulate the connection pooling, I did find a method (ReleaseObjectPool - docs.microsoft.com/.../system.data.odbc.odbcconnection.releaseobjectpool )  .. and this may allow us to explicitly close our pooled connections if we need to.  Aside from using a method like that, it seems that all .Net applications on the same client machine are impacted by the connection pooling that is provided by the driver-manager.

> -B 1500000 -B2 250000 -L 400000 -n 1500 -bibufs 200 -aibufs 200 -lkrela -spin 64000 -S MYPRIMARY -DBService replserv -pica 32768 -tablerangesize 1050 -indexrangesize 4200 -omsize 5200 -semsets 12 -bithold 32768 -lruskips 200 -aiarcdir /aiarchive -aiarcinterval 3600 -prefetchDelay -prefetchPriority 100 -refetchNumRecs 100 -Nmsgwait 2 -Mm 32000 -Mn 62 -Mpb 40 -Ma 5 -Mi 1

Unrelated to ODBC...

- you have a typo in what should be -prefetchNumRecs 100

- either the *rangesizes are a fair bit higher than they need to be (which is fine) or omsize isn't high enough; do you have fewer than 5200 _storageobject records?

- I'm curious about your reason for using -lkrela; I view it as a backward-compatibility param to use as a work-around if you encountered errors with the "new" (10.1B) lock-release algorithm, with the downside that it can impact performance, especially with a large -L.  Did you encounter such errors?

- since you are using -B2 and -lruskips, it's not a bad idea to add -lru2skips as well, in the event that the LRU2 replacement policy becomes enabled (i.e. you fill the ABP to capacity)

I wanted to see your Mn, Mpb, n, Ma were balanced .. which they are :)

You might want to add -PendConnTime 10 (the default is 30 seconds on SQL servers)

We've got a code example on ReleaseObjectPool:

--  the Close() method is used to disconnect from a connected database.

--  with CP, OdbcConnection.ReleaseObjectPool() method should also be called to release the ODBC Driver Manager environment handle when the last underlying connection is released. Otherwise the connection to the database will be be held by the ODBC Driver Manager.

000067957 - .NET ODBC Connection not terminating immediately

knowledgebase.progress.com/.../NET-ODBC-Connection-not-terminating-immediately

Thanks Ruanne,

It would be nice if the disabling of pooling could be done in an app-specific way without messing with the driver in the ODBC administration.  Simply adding "Pooling=false" to a connection string would be wonderful for troubleshooting purposes. (And it would also be helpful to use that within services that don't need any pooling).

While searching google, I saw a lot references to the configuring of pooling in connection strings.  But the context wasn't normally related to ODBC connections.  It was usually a managed provider of some kind.  There are very few ODBC providers that seem to support the configuration of pooling via connection strings .

The ReleaseObjectPool is something we can use in a pinch.  It is pretty ugly to do on a regular basis.