web stats
Database Reader Thread Count - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 04-20-2017, 01:54 PM
collinsmj collinsmj is offline
OBX.1 Kenobi
 
Join Date: Jun 2014
Location: Southern USA
Posts: 27
collinsmj is on a distinguished road
Default Database Reader Thread Count

Issue at hand: I need to send hundreds of thousands of records from a database over an MLLP network connection to a remote server. We have to convert each DB record into a HL7 v2.6 message prior to sending it over. My first 3 attempts that this take days at a time to process.

Mirth Version: 3.4.2
database: SQL SERVER

Connectors
------------------

Source Connector DB Reader:

Set to queue, respond before transformer, and no response.

I have a db reader polling every 10 seconds for rows of data where a single flag is set.

I currently have Max Processing Thread set to just 1.

Post Process SQL:
After each message is processed the flag is set to complete so this message will not process again.

Destination Connector: MLLP
There is only 1 connector

One of the transformer steps queries for more information to form repeating DG1 segments for a given encounter. This doesn't seem like a choke point but I want to mention this for completeness in case I am not understanding a vital point.

Connection is kept open.

Destination set to queue automatically and the number of threads are set to 10.



Questions
-----------
1.In dashboard view my destination queue is always reporting it is at 9. Source will be 0 to 3. How does that work? I have expected the source to be setting at my queue buffer size or have destination threads queue instead of each thread having 1 message waiting.

2. How does increasing the MAX Processing Threads on the source queue assist with overall channel through put. Please see this mirth connect thread from last year. I have toyed with this setting but see no difference.

3.How does having multiple destination queues help with throughput? I only have 1 destination over the network. I only ever see one message being processed in the dashboard messages view. I do not see 3 or 4 messages processing at a destination the same time.
Reply With Quote
  #2  
Old 04-20-2017, 03:12 PM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,123
narupley is on a distinguished road
Default

Quote:
Originally Posted by collinsmj View Post
1.In dashboard view my destination queue is always reporting it is at 9. Source will be 0 to 3. How does that work? I have expected the source to be setting at my queue buffer size or have destination threads queue instead of each thread having 1 message waiting.
That probably just means your destination queue is never growing beyond 9 or 10. If your queued count is staying more or less static, it means that the rate at which messages are being added to the destination queue is about the same as the rate at which messages are being dispatched outbound. Since your max processing threads is set to 1, the source queue thread will process one message at a time through your channel, up until the point at which it drops it into the destination queue. The transformer logic you have in your channel likely factors into this. To increase throughput, you can increase your Max Processing Threads so that multiple consumer threads will simultaneously poll and process messages through your channel (and therefore, messages will be added to the destination queue more rapidly). That is of course assuming that the source of messages (source connector or originating client system) is sending you messages quickly enough for it to make a difference.

Quote:
Originally Posted by collinsmj View Post
2. How does increasing the MAX Processing Threads on the source queue assist with overall channel through put. Please see this mirth connect thread from last year. I have toyed with this setting but see no difference.
When the source queue is enabled, multiple processing threads essentially means multiple source queue threads, all consuming from the same source queue simultaneously. If you increase this value and see no difference in throughput, it could mean that the source connector or originating system isn't adding messages to the source queue quickly enough for it to make a difference. For a Database Reader source connector, it could mean that the database server itself is just not fast enough, or there is some connection latency bottleneck. Or, you may be running into a bottleneck with the I/O write speeds on the underlying storage system you're using for the Mirth Connect database. As with most high volume applications, these things work best when you're using fast SSD storage, not spinning disks.

Quote:
Originally Posted by collinsmj View Post
3.How does having multiple destination queues help with throughput? I only have 1 destination over the network. I only ever see one message being processed in the dashboard messages view. I do not see 3 or 4 messages processing at a destination the same time.
If you have multiple destination queue threads, then again they will all consume from the same destination queue simultaneously. When you search by the QUEUED status in the message browser, are you saying you only ever see one message? It could be because the rate at which messages are being added to the destination queue is slower than the rate the destination queue is dispatching messages outbound.


Overall, it sounds like you're running into a performance bottleneck somewhere else, not related to the channel configuration per se. Try proving it one way or another by blasting multiple messages simultaneously at the channel. You can use a separate JavaScript Reader channel with a polling interval of something low like 100 ms, and 10 processing threads. I'll attach a channel to show what I mean. When blasting messages, try it when your main channel is using 1 processing thread, and then try it again when your main channel is using 10 processing threads.
Attached Files
File Type: xml Example - Blast Messages.xml (10.3 KB, 24 views)
__________________
Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

Nicholas Rupley
Work: 949-237-6069
Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


- How do I foo?
- You just bar.

Last edited by narupley; 04-20-2017 at 03:14 PM.
Reply With Quote
  #3  
Old 04-21-2017, 08:42 AM
collinsmj collinsmj is offline
OBX.1 Kenobi
 
Join Date: Jun 2014
Location: Southern USA
Posts: 27
collinsmj is on a distinguished road
Default

I have taken your advice and checked the query from the SQL server directly. They query returns up to 10k records before a second can pass. The speed of the DB server isn't an issue. Blasting the channel doesn't seem to cause issue either. I have altered the query and interval polling though.

DB reader is now polling every 10 seconds.
The Queue Buffer Size is limited to 2500
The fetch size for the cursor is 1000 records
I limited the query to TOP 1000.

The MLLP destination now has a retry interval of 2 seconds instead of 10.
I regenerate the template. I have taken a guess that the network/end point may be the issue. I limited the destination to 5 threads so they would not be fighting over the network connections to the end point server.
The Queue buffer size is 1k.


I now see messages queue up to 2400 in the source queue. So, yay?

I now only see 4 or 5 messages queued in the destination side.

In the message view I am seeing entries for messages like the following.

Connector -- Status ---- Received Date ---------- Response Date
Source - Transformed - 2017-04-21 10:31:16:254 --
Dest 1 - SENT - 2017-04-21 11:02:76:148 -- 2017-04-21 11:02:76:748


There is 30 minutes difference between when the source received the message and when the destination received it. I assume this is due to the size of the source queue and how quickly the destinations can process the message over the network.

I am receiving a number of timeout waiting for response errors on messages even though I lowered the total number of destination queues. Should I select ignore response and/or increase the timeout? It is set to 5 seconds.
Reply With Quote
  #4  
Old 04-21-2017, 09:46 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,123
narupley is on a distinguished road
Default

Quote:
Originally Posted by collinsmj View Post
Blasting the channel doesn't seem to cause issue either.
I don't understand what you're trying to say here. Did you follow my instructions? If so, what were the actual results?

If you're seeing a lot of errors on your destinations due to response timeouts, then a-ha, that's likely why your outbound throughput is so low. So it's not an issue with Mirth Connect or your channel per se, and more an issue with the external system you're trying to send messages to.

If your response timeout is 5 seconds and you're seeing messages timeout, then yes try increasing that, maybe 30 seconds. Then tune the number of destination queue threads as high as you want to get the throughput you're looking for, assuming the external system can handle the load.

We know right now that it takes at least 5 seconds to dispatch a single message, because that's your response timeout right now, and messages are timing out. Let's say that, assuming you increase that timeout, on average it takes 10 seconds to dispatch a single message. If you have 10 queue threads on your destination, that would be:
10 messages / 10 seconds
Or overall, a rate of:
1 message / second
If you then increase the queue threads to 100, then (ignoring other factors for the moment) you'll have 10 msg/sec.

You can continue to increase your queue threads, but at some point you'll begin to see a point of diminishing returns. That's typically because the external system you're blasting messages at simply can't process that many at once. Or it could be because of I/O write times for the underlying storage system you're using for the Mirth Connect database. Or it could be one of many other networking issues.
__________________
Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

Nicholas Rupley
Work: 949-237-6069
Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


- How do I foo?
- You just bar.
Reply With Quote
  #5  
Old 04-21-2017, 11:07 AM
collinsmj collinsmj is offline
OBX.1 Kenobi
 
Join Date: Jun 2014
Location: Southern USA
Posts: 27
collinsmj is on a distinguished road
Default

I increased the number of destination queues to 20 and increased the time out from 5 seconds to 9 seconds. The result was horrible. I started getting receiving the response, response timeout, connection refused, client refused connection errors. I am now trying to reduce the number of threads and parallel connections. I have had to increase the response timeout to 30 full seconds.

You seem to be correct in that the bottle neck is somewhere else. I am thinking destination system. The network response/message processing seems monstrously slow. But that is with multiple threads running.

Putting that aside, please clarify what i need to do to properly test the message blast. So far, I have downloaded and imported the channel you attached a few posts ago. I changed the <result/> XML Node to contain a message that would have come from the database. This will always be the same record. I used the same naming scheme the message format would have if it came out of the database. Is this all I need to do?
Reply With Quote
  #6  
Old 04-21-2017, 11:53 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,123
narupley is on a distinguished road
Default

Quote:
Originally Posted by collinsmj View Post
I increased the number of destination queues to 20 and increased the time out from 5 seconds to 9 seconds. The result was horrible. I started getting receiving the response, response timeout, connection refused, client refused connection errors. I am now trying to reduce the number of threads and parallel connections. I have had to increase the response timeout to 30 full seconds.

You seem to be correct in that the bottle neck is somewhere else. I am thinking destination system. The network response/message processing seems monstrously slow. But that is with multiple threads running.

Putting that aside, please clarify what i need to do to properly test the message blast. So far, I have downloaded and imported the channel you attached a few posts ago. I changed the <result/> XML Node to contain a message that would have come from the database. This will always be the same record. I used the same naming scheme the message format would have if it came out of the database. Is this all I need to do?
Yeah it certainly sounds like the outbound system you're interfacing with just cannot handle high volumes of messages.

Regarding the blaster channel, that sounds correct. I was just wondering if you had tested that out when your main channel had 1 processing thread, and tested it when your main channel had 10 processing threads. If you saw a significant increase in the rate the source queue grows, but did not see the same increase when you used the Database Reader, then it would have pointed towards a possible bottleneck with the connection to your external database, or the speed at which your database can read and return data. But it sounds like you've already shown that the issue isn't with your source database, but rather with the outbound system.
__________________
Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

Nicholas Rupley
Work: 949-237-6069
Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


- How do I foo?
- You just bar.
Reply With Quote
  #7  
Old 05-03-2017, 05:41 AM
collinsmj collinsmj is offline
OBX.1 Kenobi
 
Join Date: Jun 2014
Location: Southern USA
Posts: 27
collinsmj is on a distinguished road
Thumbs up

Wanted to update a final post to this in case my discoveries can help anyone else. We are moving to EPIC as a HIS and the interface we are battling is the incoming patient info channel. We have made changes to the channel and now have over 1 million patient visits loading into the environment within just a few days.

For each visit sent over the interface the HIS needs to scan the visit record with in turn grows with each new visit. This scanning causes the interface to run slower than say the Materials Item master input or Orders feed.

We have the database reader source (queue turned on, 1 processing thread) and 1 destination running(queue turned on, always queue message, 3 threads). Using this setup we have gotten to about 100 messages per minute. This input takes ~5 days. Absolutely all the Filters/transformers are on the source connector. The destination is just a bare bones MLLP connection. Turn the message level down to RAW.


Thanks to Nick Rupley for the quick replies and excellent suggestions. The first time we tired this push it took over 12 days. We have seen just over a 50% reduction in the time it took.
Reply With Quote
Reply

Tags
database reader, parallel, threads

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 09:53 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation