| Author |
Message
|
| bags10 |
 Posted: Wed Aug 07, 2013 5:58 am Post subject: WMB Database Input Node can't Multi-Thread |
 |
|
Newbie
Joined: 06 Aug 2013
Posts: 2
Location: KCMO - USA
|
We are trying to make use of the Database Input node in our organization.
Proof of concept works flawlessly on a single thread running on one server and/or executionGroup... however, Architecture (team) requires that it function on a clusted server environment and unfortunately, we see contention as soon as a second flow/thread is activated.
Both flows will poll and pick up rows off the event table and begin to build their messages but when it comes time to delete from the event table contention-exception occurs since both broker flows are adding to and drawing from the same stack. And since each flow executes the following statement, both will ultimately attempt to delete a common row, or a row that has already been deleted by its brother...
DELETE FROM Database.EVT_TABLE
WHERE EVT_TABLE.EVT_ID = DispatchedEvent.Usr.EVT_ID;
We found that the delete is necesarry at each iteration (and not somewhere else downstream) in order to prevent looping. Broker wants to continuously build messages off the same row on the event table if not deleted.
IBM's response to ticket was that the 'DB Input node is single threaded'.
Any experience with this or suggestions on a workaround would be greatly appreciated? |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Wed Aug 07, 2013 6:05 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Off the top of my head and without knowing anything other that you seem to be working in an ACTIVE-ACTIVE scenario,
Use a simple flow that gets triggered at the DB Input node and THEN drops the various bits of data onto a clustered queue. Then the real processing is distributed over the nodes in the cluster.
Deploy to all nodes but only have it working on one. In the event of failure of that node, start the stopped copy on another node.
_________________
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 |
|
|
| Vitor |
| Posted: Wed Aug 07, 2013 6:06 am Post subject: Re: WMB Database Input Node can't Multi-Thread |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| bags10 wrote: |
Both flows will poll and pick up rows off the event table and begin to build their messages but when it comes time to delete from the event table contention-exception occurs since both broker flows are adding to and drawing from the same stack. And since each flow executes the following statement, both will ultimately attempt to delete a common row, or a row that has already been deleted by its brother...
DELETE FROM Database.EVT_TABLE
WHERE EVT_TABLE.EVT_ID = DispatchedEvent.Usr.EVT_ID;
We found that the delete is necesarry at each iteration (and not somewhere else downstream) in order to prevent looping. Broker wants to continuously build messages off the same row on the event table if not deleted. |
This seems at variance with the documented behavior given here:
| Quote: |
| After the message has been propagated to the message flow, EndEvent updates the event store to ensure that the event that has just been processed cannot be processed again |
This implies that the node attempts to remove the row from the event table.
| bags10 wrote: |
| IBM's response to ticket was that the 'DB Input node is single threaded'. |
Which is how it's documented. Why have you chosen to have multiple flows with a DatabaseInput node rather than additional instances as shown in the documentation Transaction & Scaling section?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Aug 07, 2013 6:06 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Selection criteria for a DatabaseInput node prevents it from being multi-threaded.
You can get around this by creating multiple temporary tables and having the database round-robin to the temp tables any changes you want to make, each database input node instance referring to a separate table.
You'll also need to create uniquely-named ODBC data sources in order to avoid contention on the ODBC driver.
Another workaround is having a Triggered-SP on your database feed MQ messages to an MQInput node, which can be multi-threaded.
Database operations are the Achilles-heal of message broker. It has never handled db ops well.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Aug 07, 2013 6:10 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| lancelotlinc wrote: |
| You can get around this by creating multiple temporary tables and having the database round-robin to the temp tables any changes you want to make, each database input node instance referring to a separate table. |
You can also get around this using the documented method.
| lancelotlinc wrote: |
| You'll also need to create uniquely-named ODBC data sources in order to avoid contention on the ODBC driver. |
By which you mean it will perform marginally better with multiple ODBC sources, not that it won't work at all I think.
| lancelotlinc wrote: |
| Another workaround is having a SP on your database feed MQ messages to an MQInput node, which can be multi-threaded. |
Indeed
| lancelotlinc wrote: |
| Database operations are the Achilles-heal of message broker. It has never handled db ops well. |
Ah, your other crusade to training. I've missed it. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| bags10 |
| Posted: Wed Aug 07, 2013 12:05 pm Post subject: |
|
|
Newbie
Joined: 06 Aug 2013
Posts: 2
Location: KCMO - USA
|
Thanks for the quick responses all!
This is all great stuff and currently under review... will post any developments as they happen.
Much appreciated... |
|
| Back to top |
|
|
| PeterPotkay |
| Posted: Fri Feb 07, 2014 7:23 am Post subject: Re: WMB Database Input Node can't Multi-Thread |
|
|
Poobah
Joined: 15 May 2001
Posts: 7722
|
| Vitor wrote: |
| bags10 wrote: |
| IBM's response to ticket was that the 'DB Input node is single threaded'. |
Which is how it's documented. Why have you chosen to have multiple flows with a DatabaseInput node rather than additional instances as shown in the documentation Transaction & Scaling section? |
We're starting to look at this node in WMB 8.0.0.3.
The Infocenter gives pointers on how to scale with Additional Instances. And that's it.
Does that imply you can scale on a single broker, but if you run multiple brokers for H.A. reasons that you try to keep configured identically, this won't fly?
_________________
Peter Potkay
Keep Calm and MQ On |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Feb 07, 2014 7:47 am Post subject: Re: WMB Database Input Node can't Multi-Thread |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| PeterPotkay wrote: |
| Does that imply you can scale on a single broker, but if you run multiple brokers for H.A. reasons that you try to keep configured identically, this won't fly? |
If you're running active/active then yes, AFAIK you're hosed.
Not tried it personally.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Feb 07, 2014 8:12 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
A DatabaseInput node really just acts like any other database application.
It periodically scans the contents of an event table, that are populated by database triggers when appropriate changes are made to the real table or tables. It then scans the real tables to gather the necessary information to create an event. Each event is then propagated out of the node for processing. Once the event is processed, then the related row in the event table is updated to indicate that it's done (or deleted).
If you have two copies of "the same" DatabaseInput node running, they are both going to scan the same event tables, and without some tricky fancy stuff and good row-level locking at the database level, they are going to contend against each other on the event table access and update. This could cause duplicated messages, or etc..
You could spend some time and effort customizing the ESQL that the DatabaseInput node uses to read the event table, to allow it to mark records, and this would give you some way of alleviating the contention between the two scans.
Or you could simplify the flow in the same way that one tends to simplify FileInput flows, and have it just dump each event onto a queue. Then you run a single DatabaseInput node, and it processes rows as fast as it can, and then MQ distributes the work for processing in parallel across multiple brokers. |
|
| Back to top |
|
|
|
|
