ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » WMB V8.0.0.2 slow performance when calling remote SQL DB.

Post new topic  Reply to topic
 WMB V8.0.0.2 slow performance when calling remote SQL DB. « View previous topic :: View next topic » 
Author Message
sloaneuys
PostPosted: Mon Jun 24, 2013 1:11 am    Post subject: WMB V8.0.0.2 slow performance when calling remote SQL DB. Reply with quote

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
View user's profile Send private message
lancelotlinc
PostPosted: Mon Jun 24, 2013 3:15 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
sloaneuys
PostPosted: Mon Jun 24, 2013 5:54 am    Post subject: Reply with quote

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
View user's profile Send private message
lancelotlinc
PostPosted: Mon Jun 24, 2013 6:00 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
mb01mqbrkrs
PostPosted: Mon Jun 24, 2013 8:00 am    Post subject: Reply with quote

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
View user's profile Send private message
vikas.bhu
PostPosted: Tue Jun 25, 2013 4:15 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
lancelotlinc
PostPosted: Tue Jun 25, 2013 4:38 am    Post subject: Reply with quote

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

vikas.bhu wrote:
"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.


(IIRC) In March 2010, WMB 7.0.0.2 on IBM AIX POWER7 780 frame, divided into four LPARs. MQInput -> Trace -> Compute (DB2 In-Memory Database) -> MQOutput {YMMV} Four EGs per LPAR, 22 Additional Instances per flow.

http://www.informationweek.com/software/information-management/ibm-adds-high-speed-in-memory-option-to/208402402
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER
Back to top
View user's profile Send private message Send e-mail
smdavies99
PostPosted: Tue Jun 25, 2013 5:06 am    Post subject: Reply with quote

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
View user's profile Send private message
lancelotlinc
PostPosted: Tue Jun 25, 2013 5:19 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » WMB V8.0.0.2 slow performance when calling remote SQL DB.
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.