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 » Deadlock & performance issue

Post new topic  Reply 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: 18

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: 17447

wrap the esql call to the stored procedure in an atomic block.
_________________
chmod -R ugo-wx /
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: 18

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

Grand Master

Joined: 25 Aug 2015
Posts: 1280

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: 18

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 High Poobah

Joined: 18 Nov 2003
Posts: 20696
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
pavraj
PostPosted: Mon Aug 07, 2017 7:46 am    Post subject: Reply with quote

Novice

Joined: 06 Mar 2017
Posts: 18

mqjeff wrote:
wrap the esql call to the stored procedure in an atomic block.


We have removed the stored procedure calls and make inserts using passthru statements. In our Quality environment, we have not seen any issues while additional instances even set as 5. But, in our prod environment if set additional instances as 2 also, it is throwing db locks errors.

We compare the two databases, and found only below difference-
SqlServer 2012 SP3 PCU3 (11.0.6520) - Quality
SqlServer 2012 SP3 CU3   (11.0.6540) - Prod

Isolation level etc. everything is same.
Even we wrap atomic block around PASSTHRU statements, in Quality everything seems working fine. But, in Prod, not able to insert even single message. So, we moved atomic blocks and flows are running.
But, we are not able to understand why we are facing deadlocks especially in Prod environment. Please help with your valuable inputs.
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Mon Aug 07, 2017 8:04 am    Post subject: Reply with quote

Grand High Poobah

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

OK so you're running SQL server and you have a heavy load on it.
You need to make sure that the locking is done right. If you can set the locking to row locking and not page locking (the default?). This should reduce the number of locks. Also make sure that none of the selects locks the DB... and you may have to use passthrough and dbhints to achieve that...
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
pavraj
PostPosted: Mon Aug 07, 2017 8:12 am    Post subject: Reply with quote

Novice

Joined: 06 Mar 2017
Posts: 18

Hi fjb_saper

We set the locking to row locking only. And they are no selects in our code. All are insert statements.
Back to top
View user's profile Send private message
timber
PostPosted: Mon Aug 07, 2017 10:29 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

Quote:
In our Quality environment, we have not seen any issues while additional instances even set as 5. But, in Prod, not able to insert even single message. So, we moved atomic blocks and flows are running.
So you have a reliable difference in behaviour between the Quality and Prod environments. I suggest that you mine that for information - start by looking carefully at the logs and errors messages in Prod. There must be *some* errors to explain why the messages were never committed to the DB.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Deadlock & 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.