Extend capabilities to clean up old executions

[do3meli]

to avoid having a crowded mistral database and therefore slower mysql queries we try to delete successfully finished workflow executions. we currently achieve this by running the command "openstack workflow execution list" with certain filters followed by "openstack workflow execution delete". this unfortunately is not very efficient and fails from time to time due to workflow dependencies that got deleted earlier in the loop process of our script. nothing that we couldn't workaround separately but i suggest to have a more mainstream solution:

add a cleanup command for mistral-db-manage that allows to "archive" or "delete" successfully executed workflows. something like nova implements with "nova-manage db archive_deleted_rows".

[Dougal Matthews (d0ugal)]

Do you use the execution expiration policy? https://docs.openstack.org/mistral/latest/main_features.html#execution-expiration-policy

That is designed to solve the problem you have. However, maybe it is missing something?

To avoid the execution deletion errors, I think you may want to only delete root workflow executions (and not sub-workflows, they are deleted when the root is deleted).

[do3meli (d-info-e)]

we currently do not use execution expiration policies as we can not filter the executions which are supposed to be deleted. so far we only automatically delete successful workflows and keep the once with error for further manual investigation.

with this idea in mind we have to use the approach as described above and there seems to be only 2 solutions to this in my view:

1. add an option to "openstack workflow execution list" and/or "openstack workflow execution delete" that allows to only return root workflows.

2. create a new "mistral-db-manage" command that does allow the same filtering as in 1.)

please let me know if that now makes more sense to you or if you need some more information/discussion about the topic.

[Dougal Matthews (d0ugal)]

Maybe we should expand the expiration policy. Giving the option to not delete workflows in an error state (or maybe having a longer expiration for error state etc.)

I think option 1 would be useful. Possibly also option 2, but maybe we can avoid a new command by improving the expiration policy?

[do3meli (d-info-e)]

Thinking about it again i realized it's maybe better to improve existing tools rather than creating new once. So with this in mind i think the following should be done here:

1. add an option to "openstack workflow execution list" and/or "openstack workflow execution delete" that allows to only return root workflows.

2. expand expiration policy to be able to filter out workflows in an error state.

does this sounds like a good idea?

[Renat Akhmerov (rakhmerov)]

Your last suggestion looks good to me.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/686929

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/686929
Committed: https://git.openstack.org/cgit/openstack/mistral/commit/?id=6b862e625e0b59cb7d3806a119fdd223227c06c2
Submitter: Zuul
Branch: master

commit 6b862e625e0b59cb7d3806a119fdd223227c06c2
Author: ali <email address hidden>
Date: Sun Oct 6 09:07:19 2019 +0000

    Extend capabilities to clean up old executions

    * Added a configuration option to the expiration policy
      to filter out workflow states.

    Closes-Bug: #1796627
    Change-Id: Ife49e6da1d7d52a3f50f1628d808d4c65a22cad9

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/694737

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/694737
Committed: https://git.openstack.org/cgit/openstack/python-mistralclient/commit/?id=7cea381bfe6a5bd7213d2cd990ffbc34a8ba2273
Submitter: Zuul
Branch: master

commit 7cea381bfe6a5bd7213d2cd990ffbc34a8ba2273
Author: ali <email address hidden>
Date: Mon Nov 18 09:53:27 2019 +0000

    Roots only option for executions-list

    * added an option to execution-list to return
      root workflow executions only (--rootsonly).

    Change-Id: I4b9c997f2e9969b050ac73af1d13e2eda7a29f18
    Closes-Bug: #1796627
    Depends-On: I78fbf993519beb63ee9aef7058bdcb40f0a12ec3

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/mistral 10.0.0.0b1 development milestone.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/python-mistralclient 4.0.0 release.