| Author |
Message
|
| sloaneuys |
 Posted: Mon Jun 24, 2013 1:11 am Post subject: WMB V8.0.0.2 slow performance when calling remote SQL DB. |
 |
|
Newbie
Joined: 12 Jul 2006
Posts: 9
|
We are in the process of upgrading to Websphere Message Broker V8 from V6 on NT. We have adopted a fresh install approach on Windows 2008 R2 64bit servers. The MQ version is MQ V7. We have created a 64bit broker in our Development environment and redeployed all the flows after exporting the bar files from the V6 toolkit and importing it into the new V8 toolkit.
The architecture has changed from using local DB2 databases(shipped free with Message Broker V6) to using remote SQL databases to do the various static data look ups due to the licensing implications.
The V8 flows that access remote SQL databases for routing and other client data are performing much slower (up to 2 seconds per call) in comparison to the same flow in WMBV6 with a local DB2 database. We have created the DSN entries for the SQL databases.
Has anyone experienced similar issues or know what to look for in order to troubleshoot the root cause?
Thanks! |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Mon Jun 24, 2013 3:15 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
First off - congratulations on your upgrade.
I access a remote DB2 instance and my response time is 0.03 seconds. Have your DBA check indexing and other DB-related causes. WMB accesses databases well. Also look at how your flows are calling the databases and if there are any contention within the flows.
Keep in mind that if you have "additional instances" greater than zero, you need to multiply additional instances by number of database (compute) nodes to get your total client license quantity. If the number of db connections is greater than your client licenses, then you need more licenses and greater number of client connections may be one factor in your slowness.
You are also unclear about how you migrated. Did you follow the instructions?
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fbh23310_.htm
Did you rebuild your bar files from source code or just move bars from one place to the other?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| sloaneuys |
| Posted: Mon Jun 24, 2013 5:54 am Post subject: |
|
|
Newbie
Joined: 12 Jul 2006
Posts: 9
|
Hi Lance and thanks for your reply:
WRT the database interaction, for the test we are just running one flow with one compute node access the database from ESQL. Code sample:
SET EnvVar.temp[] = SELECT D.DESCRIPTION
FROM Database.GCS.GCSMAP as D
WHERE D.SYSTEM = XYZ SYSTEM'
AND D.CODESTRING = Codestring;
WRT migration. We installed WMB V8 onto the new hardware and OS. Therefore we created a new broker and redeployed all the flows in the respective execution groups. The flows were compiled by using the WMB V8 Toolkit and recreated the bar file using the source imported into WMB V8 Toolkit. Therefore the flow would comply with V8. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Jun 24, 2013 6:00 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
You might like to ask your DBA to turn on trace so you could see the plan being executed by the query optimizer.
I can vouch for WMB access to DB2, Oracle, SQLServer, Sybase, and Informix. If your query is slow, I would look first to the database engine. WMB performs exceptionally well. With proper tuning, I have been successful at 10,000 TPS.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mb01mqbrkrs |
| Posted: Mon Jun 24, 2013 8:00 am Post subject: |
|
|
Apprentice
Joined: 18 Nov 2011
Posts: 48
|
| lancelotlinc wrote: |
You might like to ask your DBA to turn on trace so you could see the plan being executed by the query optimizer.
|
Another interesting thing is to check the difference between how long the flow thinks its taking to run a query (using a flow trace) compared to how long the SQL server thinks the execution time is. They can be surprisingly different. |
|
| Back to top |
|
|
| vikas.bhu |
| Posted: Tue Jun 25, 2013 4:15 am Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
"With proper tuning, I have been successful at 10,000 TPS."
Wanted to know the hardware configuration and base element of flows.
Request you to please share. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Jun 25, 2013 4:38 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Jun 25, 2013 5:06 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
It is important to note that in the system described, the DB server was not connected to the Broker system by a thin bit of Cat-6 (or similar) ethernet cable.
The DB server was effectively running on the same H/W platform as Broker but in its own segregated environment. Hence the very nice TPS figures.
IF you are working on equipment that us mere mortals could afford (Eg X86 servers in separate boxes) there are many places where a bottleneck could be introduced that just might cause the performance figures you are seeing.
That said, the current system I'm working on uses HP DL380 servers and easily give me 100+ TPS and I have not go into network tuning yet simple because the idiots who run the network don't know one end of a router from another and every connection if Half-Duplex, auto-negociate. They want to charge us 500% more full duplex non auto-negociate connections. My thoughts on this are unprintable.
(Now where's Vitor's piece of wet fish when you need it?)
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Jun 25, 2013 5:19 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
You are correct smdavies, although similar TPS figures can be achieved without the DB2 in-memory database. There are open source equivalents or other lower cost equivalents to do the same. In fact, the DB2 product in-memory function was at one time an open-source product.
The need is not universal. Very few people need such performance. The use case here was a household name brand credit card network processing credit card transactions for all merchants worldwide on their backbone.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
|
|
