Message Pruner

[twest-mirthconnect]

Coming in the next release is an error message pruner. Current functionality is at the "edit channel" level only. We have met the initial design and intention of the community request.

Now, I would like to know what can improve based on the solution in the product. Hope to hear from a few folks about this added functionality.

Thanks,
Travis

[jonbartels]

"Prune Errored Messages" should be OFF by default. Retaining errors seems like the safest default behavior to me.

[twest-mirthconnect]

Yes, it is set to 'off' by default. Good eye...

[tonygermano]

If we're talking about general pruner improvements, I know it's been requested to specify the pruning threshold in time units other than days (6 hours, 36 hours, etc...,) though I can't find a ticket for it now.

This issue #3030 was already "closed-due-to-inactivity" but it is the one that would be resolved by what you are proposing in this discussion. I'd recommend reopening it so it can be closed properly.

There are plenty of other pruner related issues that were "pruned" in the same way. Many of them previously marked with the Future Planned milestone, so those would probably be a good place to start looking for ideas.

https://github.com/nextgenhealthcare/connect/issues?q=is%3Aissue+pruner+is%3Aclosed+label%3Aclosed-due-to-inactivity

[twest-mirthconnect]

Thanks Tony. I will go read through them and adjust.

[jonbartels]

@twest-mirthconnect - Are general pruner improvements in scope for what you're doing here? I'm not sure if you hope to focus on the prune-errors or more broadly mock up pruner improvements.

I don't want to clutter a feature addition with noise if they're not in scope. Conversely "if you're in there anyway" may also apply. LMK if you want a brain dump or not please.

[twest-mirthconnect]

I will take the brain dump. It wouldn't be included in the next release AND would need to be prioritized but yes, let's get it all out on the table to take a look.

Thanks Jon.

[jonbartels]

The quick list:

1. Allow the pruner to run more frequently - I've run into some situations for high volume channels or bursty traffic patterns where hourly is not frequent enough. This is load and use case dependent. Advisory text may be prudent to help guide users to use many fast prunes or one big prune.

2. Related to Cannot import channel #1 - Allowing a pruning interval in the channel of increments less than 1 day may be valuable. A use case is needing to see messages and content for live traffic, "on-the-phone" debugging, etc.; if pruning is set to 1 day, that can be a lot of messages to keep; if "remove content on completion" is set then there is no message or map data to see to do a quick triage; having a prune content option down to an hour or 15 minutes would give engineers some traffic to triage issues

3. The postgres-messages.xml has a somewhat inefficient WHERE IN clause that doesn't work well with large batch sizes. (working from memory here, please give me some grace on the details) . If the batch size is ~>= 100 Postgres does a less efficient query, keeping the batch size down to 90-99 is a workaround. Generating a TEMP table and joining to it or a VALUES statement could be more efficient. Of course many smaller batches has an impact on the transaction log.

[twest-mirthconnect]

Thanks, Jon and TOny for the feedback.

It looks like we will take all of the archived and your comments into consideration for the next update for the pruner. The current build is just a few weeks out so these items won't make the cut in time.

[jonbartels]

I was able to find my notes on that slow WHERE IN clause.

When MC deletes with the pruner it generates statements like this DELETE FROM D_MC116 M WHERE MESSAGE_ID IN ( 216704, 216705, 216706, 216707, 216708, 216709, 216710, 216711, 216712, 216713, 216714, 216715, 216716, 216717, 216718, 216719, 216720, 216721, 216722, 216723, 216724, 216725, 216726, 216727, 216728, 216729, 216730, 216731, 216732, 216733, 216734, 216735, 216736, 216737, 216738, 216739, 216740, 216741, 216742, 216743, 216744, 216745, 216746, 216747, 216748, 216749, 216750, 216751, 216752, 216753, 216754, 216755, 216756, 216757, 216758, 216759, 216760, 216761, 216762, 216763, 216764, 216765, 216766, 216767, 216768, 216769, 216770, 216771, 216772, 216773, 216774, 216775, 216776, 216777, 216778, 216779, 216780, 216781, 216782, 216783, 216784, 216785, 216786, 216787, 216788, 216789, 216790, 216791, 216792, 216793, 216794, 216795, 216796, 216797, 216798, 216799, 216800, 216801, 216803 );

And the optimization is discussed at https://dba.stackexchange.com/questions/91247/optimizing-a-postgres-query-with-a-large-in/91539#91539 and citing https://postgres.cz/wiki/PostgreSQL_SQL_Tricks_I#Predicate_IN_optimalization and more formally documented at https://www.postgresql.org/docs/12/queries-values.html

This is postgres specific. I have not traced it down sufficiently to know if just updating the SQL mapping XML files is the only change nor if it applies to other database engines. Updating the pruneWhere mapping might work. I have not tested this:

		<where>
			<if test="includeMessageList != null">
				MESSAGE_ID IN ( VALUES
				<foreach collection="includeMessageList" item="messageIdToDelete" separator=",">
					(messageIdToDelete)
				</foreach>
				)
			</if>```