This bug was initially created as a copy of Bug #1721068

I am copying this bug because: 



Description of problem:

nova_api is groving huge, it seems never cleaned.

MariaDB [nova_api]> select count(*) from request_specs;
|  1126585 |

MariaDB [nova_api]> select count(*) from consumers;
|   444235 |

We have "nova-manage db archive_deleted_rows" running daily on each of our clouds, but the following seem to pile up regardless.

I've been looking at similar finding yesterday and today between several clouds and seems there's 2 culprits that leave allocations behind that effectively "consume" cloud resources but are not there:

- migrations -- sometimes the migration makes a reservation, fails, the instance gets back up but the reservation does not get cleared
- upgrades / maintenance / crashes on DB or rabbitmq -- high amount of allocations seem to correlate with clouds recently upgraded from RHOSP11 to 13 (upgrade is with our own installer following upstream guidelines of disabling services, doing offline data migrations and bringing the service back up)

And consumers/request_specs date back all the way to the original creation of the cloud. Seems these never get cleared.


Can we have a query or tool to properly clear the database of unused intried ?

Version-Release number of selected component (if applicable):
OSP13

How reproducible:
unsure

Steps to Reproduce:
unsure

Actual results:
Entries in the database are left there for ever

Expected results:
Entries removed or a way to properly clean it

Additional info: