| Author |
Message
|
| pcelari |
 Posted: Mon Apr 02, 2007 7:33 am Post subject: does broker use DB connection pool |
 |
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
I have two msgflows, one calls remote DB2 stored procedure directly, the other pass the requests on to a webservice that call the SP and returns resultset as XML msg.
Since the direct SP call avoid the XML back and forth transformation and has shorter route, I anticipate its performance should be better. However, the opposite turns out to be true. While 100 calls over webservice take only 40 seconds, direct calls take twice as long.
My question is: does broker use DB connection pool as the webservice does? If everytime a call comes in, a new connection has to be established, it certainly will perform badly.
Any insight on this?
thanks,
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
 |
| vk |
| Posted: Mon Apr 02, 2007 9:39 am Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
As far as I know, the execution group establishes a connection to a database when any of the flows deployed under it needs to connect to the database for the first time (The connection will not be established when the flow is initially deployed, but will be established when the flow processes the first message).
After a connection is established, the broker never relinquishes the connection unless the broker is stopped or the execution group is restarted using mqsireload command. For every subsequent message, the broker will check if the already existing connection is a valid one. If it is valid, then the same connection will be re-used. If it is found to be invalid, then the connection is closed and a new connection is established.
I have not experienced any delays with normal SQL queries to databases. Check if the stored procedure is causing any delays.
Regards,
VK. |
|
| Back to top |
|
|
| pcelari |
| Posted: Mon Apr 02, 2007 11:49 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
thanks for sharing your knowledge.
I couldn't understand the low performance comparing to using webservice. Under broker, things are far more simple, at least seems to be so.
I access the same Stored Procedure in the same db. performance won't even improve after I added another CPU and doubled memory.
Anyone has some insight on possible bottleneck?
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Apr 02, 2007 11:50 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Maybe your direct flow is poorly written.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| pcelari |
| Posted: Tue Apr 03, 2007 5:38 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
| jefflowrey wrote: |
| Maybe your direct flow is poorly written. |
I hope it were, then I can at least improve it.
But no, it contains only one MQInput, one compute, and one MQOutput. The code to call the SP can't be simpler. Just a few lines.
I wonder maybe the internal ESQL parsing of resultSet into tree structure has low performance? I'll try to code the same in JavaCompute node.
Would itl ikely be a lot faster?
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Apr 03, 2007 6:08 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Is it possible that the web service calls the database locally, rather than remotely?
Also, you can use User Trace at Debug level to at least identify where the broker flow is spending the majority of it's time, to understand where the real bottleneck is. It might not be the database interaction at all.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| pcelari |
| Posted: Tue Apr 03, 2007 6:21 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
thank you so much for the great idea to use trace log's time stamp to locate bottleneck. I'll check it this way.
No, both call the remote MF db2.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Apr 03, 2007 6:31 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
There's some good information about how and when broker establishes and releases database connections in the Info Center.
http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/index.jsp?topic=/com.ibm.etools.mft.doc/ac00406_.htm
If you're only invoking your flow once every couple of minutes, it looks like it will always establish a new connection.
There might be an undocumented environment variable you can alter to change that timeout - if so you'd have to get it from IBM Support.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| pcelari |
| Posted: Tue Apr 03, 2007 1:27 pm Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
thanks Grand Pooh so much for the insight.
it turned out a CLI tracing started a while back was still running. turning it off resulted in a huge performance jump.
Now it takes less than 40 seconds to process 250 SP calls, instead of nearly 3 minutes for 50 before.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
|
|
