Support purge operations for st2 Workflows and TaskExecution

[srimandaleeka01]

SUMMARY

We are looking for options and potential solutions on an issue in stack storm stack where 2 collections with-in mongodb bloat quite a bit - st2.task_execution_d_b, st2.workflow_execution_d_b.

We have a cron job to purge old data from mongo which uses st2-purge-executions, st2-purge-trigger-instances, curious if we have any flags or scripts to purge st2.task_execution_d_b and st2.workflow_execution_d_b collections(tables) explicitly ?

Our current approach to the customer is to drop the entire st2 db which is not ideal.

I looked at the current st2 purge operations and took a similar approach here is the PR : #4924 , please let me know if the approach makes sense.

STACKSTORM VERSION

st2 --version : st2 3.1.0, on Python 2.7.15

OS, environment, install method

A Centos VM : centos-release-7-8.2003.0.el7.centos.x86_64

Install method: Custom install - rpm.

Steps to reproduce the problem

The issue is caused with many workflows running over time and bloating the mongoDB. Here is the snapshot of the mongoDB

Here's a snapshot of the collections ( format : <collection_name>: ( ) )

st2.task_execution_d_b: 10.2 GB (1.9 GB)
st2.workflow_execution_d_b: 10.2 GB (1.8 GB)
st2.trace_d_b: 1.1 GB (334.0 MB)
st2.action_execution_d_b: 752.7 MB (165.2 MB)
st2.trigger_instance_d_b: 426.1 MB (107.1 MB)
st2.live_action_d_b: 292.4 MB (72.8 MB)
st2.rule_enforcement_d_b: 279.1 MB (82.5 MB)
st2.token_d_b: 219.1 MB (65.4 MB)
st2.action_execution_output_d_b: 18.8 MB (5.2 MB)
st2.action_d_b: 99.6 kB (48.0 kB)
st2.rule_d_b: 44.9 kB (28.0 kB)
st2.runner_type_d_b: 33.3 kB (24.0 kB)
st2.pack_d_b: 23.9 kB (20.0 kB)
st2.trigger_type_d_b: 16.1 kB (20.0 kB)
st2.sensor_type_d_b: 7.3 kB (16.0 kB)

Expected Results

What did you expect to happen when running the steps above?
Expect a support for purging workflow executions and task executions entries in the mongo DB.

Actual Results

What happened? What output did you get?

Making sure to follow these steps will guarantee the quickest resolution possible.

Thanks!

[srimandaleeka01]

I looked at the current st2 purge operations and took a similar approach here is the PR : #4924 , please let me know if the approach makes sense.

[nmaludy]

Until this is fixed, here is a bash script that cleans up old executions:

#!/bin/bash

RETENTION_TTL_DAYS=30

# https://stackoverflow.com/questions/49399984/parsing-ini-file-in-bash
# How it works
#   /\[/{prefix=$0; next}
# If any line begins with [, we save the line in the variable prefix and then we skip the rest of the commands and jump to the next line.
#   $1{print prefix $0}
# If the current line is not empty, we print the prefix followed by the current line.
ST2_CONF=$(awk '/\[/{prefix=$0; next} $1{print prefix $0}' /etc/st2/st2.conf)
ST2_DB_USERNAME=$(echo "$ST2_CONF" | grep "\[database\]username" | awk '{ print $3 }')
ST2_DB_PASSWORD=$(echo "$ST2_CONF" | grep "\[database\]password" | awk '{ print $3 }')

# generate timestamp
RETENTION_TIME_MICROSECONDS=$(date -d "$RETENTION_TTL_DAYS days ago" +%s000000)
echo "Deleting workflow + task executions older than $RETENTION_TTL_DAYS days [$RETENTION_TIME_MICROSECONDS]"

# run mongo cleanup
mongo st2 --username "$ST2_DB_USERNAME"  --password "$ST2_DB_PASSWORD" --quiet --eval "\
print('Deleting workflow executions...');
printjson(db.workflow_execution_d_b.deleteMany({'start_timestamp': { \$lt: NumberLong(\"$RETENTION_TIME_MICROSECONDS\") }}));\
print('Deleting task executions...');\
printjson(db.task_execution_d_b.deleteMany({'start_timestamp': { \$lt: NumberLong(\"$RETENTION_TIME_MICROSECONDS\") }}));"

[guzzijones]

@nmaludy i swear i see your name every time i find a solution. 👍

[arm4b]

Considering there wasn't any prod ready solution accompanied by the PR in st2 core until this moment, I'm removing this item from the v3.3.0 roadmap.

@StackStorm/maintainers WDYT? Do we have any capacity to bring the existing WIP PR #4924 to completion state?

[LindsayHill]

I think it's fine to remove from the v3.3.0 roadmap. It's not a ship-blocker, and the script can be used as a workaround.

[cognifloyd]

I added #4924 to v3.6.0 roadmap. Looks like all it needs is a rebase and register the config options. Hopefully that can be done before 3.6.0.

[guzzijones]

bump