nova-manage archive_deleted_rows date limit

[Jake Yip]

Description
===========

Currently we have a large number of rows in our nova databases, which will greatly benefit from `nova-manage archive_deleted_rows` (thanks!). What we would like to do is to archive all deleted records before a certain time (say >1 year ago). This will allow us to continue running reports on newly deleted instances, and allow `nova list --deleted` to work (up to a certain period).

Reading the code, however, reveals that there is no ability to do that. Currently, it has a --max-rows, but there are certain shortcomings with this option

1) related records are archived inconsistently. Due to foreign keys, it has to archive fk tables first. It will take up to `--max-rows` from the first table it encounters, working its way through all tables eventually reaching `instances` table last. What this means, is that instances is always archived last. An instance might have all of it's information in fk tables archived before itself is.

2) there is no ability to keep records up to certain timerange ago.

We are working on an in-house patch to achieve this. If this is of use to the community I'd be happy to work on this to be included upstream.

Environment
===========
We are running Newton Nova

[Surya Seetharaman (tssurya)]

It it true that we do not have a way of archiving records according to the time of deletion, the "delete_at" field could be used along with adding a new tag to nova-manage archive_deleted_rows. The points mentioned by the reporter for wanting this are noted and it would certainly be helpful to delete time range based records.

[Jake Yip (waipengyip)]

@tssurya we have a fix for this already in Newton. I need to rebase it to master. Suggest that you don't waste time on this, I'll send it up soon.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/556751

[melanie witt (melwitt)]

I'm not sure why this was changed from Wishlist => Low, but AFAICT there isn't a defect in the existing functions archive_deleted_rows provides and this is talking about adding an enhancement. Changing this back to Wishlist thusly.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/556751
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=e822360b6696c492bb583240483ee9593d7d24e1
Submitter: Zuul
Branch: master

commit e822360b6696c492bb583240483ee9593d7d24e1
Author: Jake Yip <email address hidden>
Date: Tue Feb 20 16:14:10 2018 +1100

    Add --before to nova-manage db archive_deleted_rows

    Add a parameter to limit the archival of deleted rows by date. That is,
    only rows related to instances deleted before provided date will be
    archived.

    This option works together with --max_rows, if both are specified both
    will take effect.

    Closes-Bug: #1751192
    Change-Id: I408c22d8eada0518ec5d685213f250e8e3dae76e
    Implements: blueprint nova-archive-before

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/nova 20.0.0.0rc1 release candidate.

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/nova/+/796744

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/nova/+/796744
Committed: https://opendev.org/openstack/nova/commit/b84787d26a70ee62cba5b1d9376d1d78da388fa1
Submitter: "Zuul (22348)"
Branch: master

commit b84787d26a70ee62cba5b1d9376d1d78da388fa1
Author: melanie witt <email address hidden>
Date: Thu Jun 17 01:43:17 2021 +0000

    Add func test for nova-manage db archive_deleted_rows --before

    This is inspired by database query filtering that occurs when --before
    is passed to nova-manage db archive_deleted_rows. There is a comparison
    WHERE deleted_at < before and not all database records populate it.

    This verifies that no records are missed due to that comparision when
    archiving with --before.

    Related-Bug: #1751192

    Change-Id: I44b79cfb236e94444740e32b0dd0a2344c29f340