Mirth Community

Mirth Community (http://www.mirthcorp.com/community/forums/index.php)
-   Support (http://www.mirthcorp.com/community/forums/forumdisplay.php?f=6)
-   -   Database Reader Thread Count (http://www.mirthcorp.com/community/forums/showthread.php?t=217107)

collinsmj 04-20-2017 01:54 PM

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.

narupley 04-20-2017 03:12 PM

1 Attachment(s)
Quote:

Originally Posted by collinsmj (Post 259272)
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 (Post 259272)
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 (Post 259272)
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.

collinsmj 04-21-2017 08:42 AM

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.

narupley 04-21-2017 09:46 AM

Quote:

Originally Posted by collinsmj (Post 259280)
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.

collinsmj 04-21-2017 11:07 AM

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?

narupley 04-21-2017 11:53 AM

Quote:

Originally Posted by collinsmj (Post 259283)
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.

collinsmj 05-03-2017 05:41 AM

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.


All times are GMT -8. The time now is 11:17 AM.

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