| Author |
Message
|
| jonasb |
 Posted: Mon Oct 04, 2010 5:49 am Post subject: connection pooling? |
 |
|
Apprentice
Joined: 20 Dec 2006
Posts: 49
Location: Sweden
|
Hi,
We are having troubles with many concurrent connections to the database.
Is there a way of limiting the number of connection (through connection pooling?) per execution group or broker, even though we are using esql nodes?
(we could always set the maxappls parameter in db2 to a big enough number, but we would prefer to limit the connection count on the client side, if possible)
thanks
_________________
contact admin |
|
| Back to top |
|
 |
| JLRowe |
| Posted: Mon Oct 04, 2010 7:48 am Post subject: |
|
|
Yatiri
Joined: 25 May 2002
Posts: 664
Location: South East London
|
| The merant odbc drivers do have a pooling feature, I'm not sure if this is documented in the MB docs, but it certainly is in the merant docs. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Oct 04, 2010 9:07 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
|
| Back to top |
|
|
| jonasb |
| Posted: Tue Oct 05, 2010 4:48 am Post subject: |
|
|
Apprentice
Joined: 20 Dec 2006
Posts: 49
Location: Sweden
|
mqjeff,
Since you posted a link (which I already had read of course) that does not mention connection pooling or any other means of limiting the number of connections from the broker side, I conclude that there is no such support in the broker itself.
JLRowe: Thank you for your input, I'll check the merant documentation.
_________________
contact admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Oct 05, 2010 5:00 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| contact admin wrote: |
| other means of limiting the number of connections from the broker side, |
Not at all.
That link tells you specifically when Broker allocates a new connection. If you are using too many connections, you can use the information on that page to control the number of connections in use.
If you read it again... 
But if, having read that page again, you decide that you will impact performance unduely... then you need to understand that imposing a connection pool in the DataDirect drivers will also impose a performance impact. |
|
| Back to top |
|
|
| jonasb |
| Posted: Tue Oct 05, 2010 6:32 am Post subject: |
|
|
Apprentice
Joined: 20 Dec 2006
Posts: 49
Location: Sweden
|
Hi Jeff,
Well, I have now read the page a few more times...
I'm well familiar with how and when database connection are created.
I still cannot see a way to limit the number of connection (in total) that the broker can use to the database. You can set maxappls parameter on *db2*, but thats not what I'm looking for, from my original post:
| Quote: |
(we could always set the maxappls parameter in db2 to a big enough number, but we would prefer to limit the connection count on the client side, if possible)
|
Maybe if I examplify:
We have several hundred flows spread out in over 20 execution groups. On occasion "everyone" are sending us messages causing a massive number of database connection (in total) being used. I would like to limit this (knowning that the db connections then would be a limited resource that could affect performance).
If parameters in the odbc.ini file could be used to configure a connection pool, that would be great (as hinted by JLRowe).
_________________
contact admin |
|
| Back to top |
|
|
| jonasb |
| Posted: Tue Oct 05, 2010 6:43 am Post subject: |
|
|
Apprentice
Joined: 20 Dec 2006
Posts: 49
Location: Sweden
|
And allowing only x number of flows to run at the same time in the broker is not a solution. (if thats what you mean).
That mechanism is something you then would have to build yourself, and is not something that the broker provides. (see my original post)
I know connection pooling is an option when you use the java node, and I just wanted to know if there was something similar that could be done when you are using the ESQL node.
_________________
contact admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Oct 05, 2010 6:50 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| contact admin wrote: |
| And allowing only x number of flows to run at the same time in the broker is not a solution. (if thats what you mean). |
Not quite. I meant only allowing X total instances of all message flows, that would then only use X connections simultaneously.
Which gets back to my hint about performance impact.
If you have Y message flow instances across the Broker, each trying to use one or more database connections simultaneously, but you have put in a connection pool such that there are only X connections available, then when X < Y, you are delaying Y-X flows while they wait for a connection to be available.
So you need to consider how willing you are to balance resource usage against performance implications. |
|
| Back to top |
|
|
| JLRowe |
| Posted: Tue Oct 05, 2010 7:33 am Post subject: |
|
|
Yatiri
Joined: 25 May 2002
Posts: 664
Location: South East London
|
Jeff - that's exactly what he wants to do. This is a very common scenario in the app server world. Throughput is improved by limiting the number of database connections at the expense of some message flow threads waiting for a connection to become available.
contact admin - here are the parameters configurable in the odbc ini file for merant 6.1 (not sure which datadirect version is in use by your version of broker):
http://media.datadirect.com/download/docs/odbc/allodbc/userguide/wwhelp/wwhimpl/common/html/wwhelp.htm#context=ODBC&file=oraclewp.14.04.html#233459
A problem is that broker also passes parameters on the connection string, and the whole question of broker <> odbc interaction is fairly opaque because the broker documentation does not really go into much detail. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Oct 05, 2010 7:41 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| JLRowe wrote: |
| Jeff - that's exactly what he wants to do. |
That's exactly what he's asking about how to do. It may or may not be what he wants to do.
| JLRowe wrote: |
| This is a very common scenario in the app server world. |
Yes, I've sat in that chair.
| JLRowe wrote: |
| Throughput is improved by limiting the number of database connections at the expense of some message flow threads waiting for a connection to become available. |
It is not necessarily true that throughput is improved by connection pooling. Throughput is improved by opening a connection at startup so that a thread does not have to wait for it to be established, but can merely use it. Connection pooling is all about resource utilization - we scale the pool to a given size to balance out the time that a thread has to wait for a free connection against the amount of network and server resources (memory and etc.) that are needed for each connection to the DB.
And, as I said, one simple easy way to do this is to limit the number of available instances of each flow such that it does not contribute to the total number of connections in use more than is deemed necessary. Database connections should always be accounted for when trying to determine how many instances to allocate to a given flow.
| JLRowe wrote: |
| contact admin - here are the parameters configurable in the odbc ini file for merant 6.1 (not sure which datadirect version is in use by your version of broker) |
Broker v6.1 and 6.0 use DD version 5.3. Broker v7 uses DD v6.0. |
|
| Back to top |
|
|
| jonasb |
| Posted: Tue Oct 05, 2010 7:42 am Post subject: |
|
|
Apprentice
Joined: 20 Dec 2006
Posts: 49
Location: Sweden
|
Ok, it boils down to this:
I do not want to *build* a feature that limits the number of concurrent flow instances across the broker. (I always knew this was an option.)
My question is:
I'm willing to set up a connection pool (for esql nodes) and limit the max number of concurrent db connections (in total) from the broker. I cannot find any documentation on this in the information center.
From JLRowe excellent post I can read that the following parameters can be used with merant ODBC driver:
MaxPoolSize , MinPoolSize , Pooling
Can I use these with Message Broker? (and still get support if I get into trouble)
_________________
contact admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Oct 05, 2010 7:53 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
[quote="contact admin"]I do not want to *build* a feature that limits the number of concurrent flow instances across the broker. (I always knew this was an option.)
| Quote: |
Right, I agree. And I've not been suggesting that. I've been suggesting that you manage your deployments such that you know exactly how many concurrent flow instances can ever exist, and then balance that against the number of db connections you want to support.
[quote="contact admin"]My question is:
I'm willing to set up a connection pool (for esql nodes) and limit the max number of concurrent db connections (in total) from the broker. I cannot find any documentation on this in the information center. |
I don't believe you can do this.
| contact admin wrote: |
From JLRowe excellent post I can read that the following parameters can be used with merant ODBC driver:
MaxPoolSize , MinPoolSize , Pooling
Can I use these with Message Broker? (and still get support if I get into trouble) |
If the Merant/DataDirect driver supports those, then you should be able to use them in the Broker's odbc.ini or in the properties on the ODBC connection in Windows.
Please take note again that JLRowe's link was to DataDirect version 6.1 and the latest release that any version of Broker is using is 6.0.
I can not give you an official answer on whether that will put you out of support or not.
You will have to ask IBM that question if you are sufficiently worried about it. If if were me, I might try it first and see if it causes any obvious problems, and then decide how to phase it into production. |
|
| Back to top |
|
|
|
|
