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 IndexWebSphere Message Broker SupportDeadlock & performance issue

Post new topicReply to topic
Deadlock & performance issue View previous topic :: View next topic
Author Message
pavraj
PostPosted: Thu Jul 06, 2017 5:29 am Post subject: Deadlock & performance issue Reply with quote

Novice

Joined: 06 Mar 2017
Posts: 10

Hi All,

We are receiving Idocs from SAP and we need to transform and insert into multiple SQL server tables.

As data from one Idoc to be inserted into multiple tables, and we are calling Stored Procedure from IIB flows to insert the records into various tables.
Daily we receiving huge number of Idocs from SAP, approx 15k Idocs. So, we increased the additional instances to 5 and we are seeing Idoc's got failed due to deadlock issues and it is really frustrating the customer.

Please let us know how to resolve the deadlock issues, without affecting the throughput.

And we are seeing performance issue also, some Idocs we are getting of size ranging from 10MB to 90MB and on an average IIB flows is taking 30min to 40min of processing time. We have seen some issue with MQInput node to parse incoming ALE Idocs so we have used RCD to parse the incoming Idoc. And we are using compute node for making database calls. If we take one 40MB Idoc, it makes 5k records to be inserted into 5 sql server tables.

Toolkit version: 9.0.0.4, Platform: Windows, Broker: 9.0.0.5

Please share your guidance to resolve deadlock & performance issue.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Thu Jul 06, 2017 5:31 am Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17267

wrap the esql call to the stored procedure in an atomic block.
_________________
Read, Think, Try, Repeat
Back to top
View user's profile Send private message
pavraj
PostPosted: Thu Jul 06, 2017 8:04 am Post subject: Reply with quote

Novice

Joined: 06 Mar 2017
Posts: 10

Thank you a lot jeff for your quick response.
I will try this option by placing stored procedure call in an atomic block.

Could you please guide me on faster processing of Idoc's also?
Back to top
View user's profile Send private message
timber
PostPosted: Thu Jul 06, 2017 8:45 am Post subject: Reply with quote

Yatiri

Joined: 25 Aug 2015
Posts: 630

Quote:
We have seen some issue with MQInput node to parse incoming ALE Idocs so we have used RCD to parse the incoming Idoc
That will make no difference at all. The RCD will call exactly the same parser as the MQInput node. There must be something that you have forgotten to mention.

Quote:
And we are seeing performance issue also, some Idocs we are getting of size ranging from 10MB to 90MB and on an average IIB flows is taking 30min to 40min of processing time.
You cannot expect anybody to give advice on this until you
a) tell us where the bottleneck is in the message flow (I assume that you have done some tests)
b) tell us what fixes you have already tried
Back to top
View user's profile Send private message
smdavies99
PostPosted: Thu Jul 06, 2017 10:49 pm Post subject: Reply with quote

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.

Make sure that the Stored Procedure is properly coded.
Any selects that are for reference and not update need to be made readonly/no locks.
This will help the deadlocks and also performance as the DB won't lock the data (what is and how much is locked depends upon the DB make. Oracle is different from SQLserver etc)
On one system I worked on, we had lots of deadlocks so we went through the whole thing and recoded the DB access to use no locking wherever it was appropriate. This solved the deadlocks AND gave us 20% more throughput on the same hardware.

Work with your DBA's to find the root cause of the deadlocks. They may have more details and can help you scale your solution properly.
_________________
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
pavraj
PostPosted: Fri Jul 07, 2017 8:08 am Post subject: Reply with quote

Novice

Joined: 06 Mar 2017
Posts: 10

timber wrote:
Quote:
We have seen some issue with MQInput node to parse incoming ALE Idocs so we have used RCD to parse the incoming Idoc
That will make no difference at all. The RCD will call exactly the same parser as the MQInput node. There must be something that you have forgotten to mention.

Quote:
And we are seeing performance issue also, some Idocs we are getting of size ranging from 10MB to 90MB and on an average IIB flows is taking 30min to 40min of processing time.
You cannot expect anybody to give advice on this until you
a) tell us where the bottleneck is in the message flow (I assume that you have done some tests)
b) tell us what fixes you have already tried


In the incoming Idoc we are getting some 3k to 4k segments and using mapping node we removed unwanted segments and passing only necessary segments to next compute node and inserting the records into the target table. Still to process a 70MB Idoc file, it is taking apporx. 50mins.
Kindly suggest for any alternative approach for faster processing and inserting.
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Fri Jul 07, 2017 9:02 am Post subject: Reply with quote

Grand Poobah

Joined: 18 Nov 2003
Posts: 19156
Location: LI,NY

pavraj wrote:

In the incoming Idoc we are getting some 3k to 4k segments and using mapping node we removed unwanted segments and passing only necessary segments to next compute node and inserting the records into the target table. Still to process a 70MB Idoc file, it is taking apporx. 50mins.
Kindly suggest for any alternative approach for faster processing and inserting.

You might want to separate the processing from the inserting so that you can measure the performance of both.
The first thing to check would be for usage of indexes and excessive tree walking. Use references instead. With 3 to 4 thousand segments on the IDOC the usage of indexes and excessive tree walking could cause some major slow down...
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportDeadlock & performance issue
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.