Continuous Looping And/Or Orphaned Workflows Using Conditional Runner Macro On Server

[MSchmekel1]

I have been using the crew macro pack and specifically the conditional runner for over a year to do more and more complex staging of ETL like jobs. As I've helped others within the company utilize this tactic we've noticed performance issues with the Alteryx server. As we investigate the constant issue is many background instances of the AlteryxRunner.exe that appear to be Orphaned from completed jobs. I'm wondering if anyone else has experienced this issue and how they may have solved it. Below is a picture with descriptions of how the workflow currently operates.

I have considered moving towards using the command line to add the next step as a immediately scheduled job, but would like to keep this as simple as possible as we have a mix of beginning and advanced users interacting with workflows like this regularly.

[KevinP]

@MSchmekel1 Typically the support team recommends not using the runner macros on server or desktop scheduler if possible. If it isn't possible to cut out their use entirely you should minimizing their use as much as possible. This is because we have seen a wide variety of issues that arise from using them in scheduled workflows. Most of the encountered issues are due to resource exhaustion or contention issues on the server. Something you need to keep in mind when using these macros is that they will start additional worker tasks that the server knows nothing about. This is because these tools call the engine process directly instead of adding the job to the servers queue. Since the server isn't aware that these additional tasks are running it will continue to give out tasks to available workers. This can and frequently does cause resource contention or exhaustion issues on the server especially if a large number of the scheduled tasks are using these macros. When these resource issues arise it can cause a number of symptoms including: jobs taking longer to complete, jobs being orphaned in the servers queue, stuck/orphaned processes, slow or failed responses from Gallery, communication failures between components, and unexpected service shutdowns. 

Usually, we recommend the following for workload capabilities regarding server. 1 simultaneous workflow per 2 CPU cores, and sort join memory of 1/2 total system memory divided by number of simultaneous workflows. As such a typical 4 core 16GB RAM server should be able to handle 2 simultaneous workflows with 4GB of sort/join memory. If your scheduled jobs regularly include runner macros you should consider either doubling the available resources (8 cores & 32GB RAM based on our example) to handle the same number of simultaneous workflows, or cutting the number of simultaneous workflows in half to help prevent resource issues. 

[MSchmekel1]

@KevinP Thank you for the information. I understood how the background processes worked and that the server does not see them as active tasks in the queue, but it is good to know that the use of the runner macros is discouraged. The workload suggestions are also very helpful.

Do you have any best practices on how to check for something like a status flag in a DB and then run a workflow? We have many processes built in Alteryx that are dependent on another job completing, or where we need to wait until a daily Database refresh is completed.

[KevinP · Accepted solution]

@MSchmekel1 This type of functionality is where the runner macros excel and is the use case for most people that are using them. Unfortunately, Workflow dependencies and checks for other jobs to complete before running a job is an area with limited options.

One option to trigger a dependent workflow is to use a workflow events to queue the next dependent job when the parent workflow successfully completes. You can do this by setting the event to run after the workflow completes without error. The event would then call the AlteryxService.exe with the addtoqueue parameter in order to define the new job and add it to the work queue. This will allow you to queue the next job(s) after its dependency completes without errors., but if the main job errors the jobs aren't queued until the next successful run.

Another option would be to have a job that runs periodically which checks the results collection in the server's database for the last time the dependent job completed. The workflow then would call a chained app that decides based on the last completion time if it should run the dependent jobs or not. This method is more complicated though, and also requires the chained app to run regularly even if the dependent jobs don't need to run. As such I personally believe the first option is better. 

The last option would be to continue using the runner macros, but to plan your server resources around their usage. As I previously stated this could mean increasing the resources available, or decreasing the number of simultaneous jobs to compensate.