St2: Support purge operations for st2 Workflows and TaskExecution

Created on 6 May 2020 · 5Comments · Source: StackStorm/st2

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 : : ( ) )

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!

feature help wanted

Source

srimandaleeka01

Most helpful comment

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\") }}));"

nmaludy on 26 May 2020

👍5

All 5 comments

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.

srimandaleeka01 on 6 May 2020

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\") }}));"

nmaludy on 26 May 2020

👍5

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

guzzijones on 30 Jul 2020

👍1

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 https://github.com/StackStorm/st2/pull/4924 until completion state?