Open Stack

On Mon, Apr 08, 2019 at 10:51:47PM -0400, Mohammed Naser wrote:
> Hi everyone.
> 
> In continuing with the trend of interesting database issues, I've
> found the following interesting thing.  It looks like we don't have
> indices for all of our tables that can be large on the deleted field.
> I think it would be beneficial *especially* for cases when the
> archiving code runs.
> 
> The big ones that are huge users in terms of rows without index are
> `instance_system_metadata` and `instance_extra`.
> `instance_action_events` and `instance_actions` don't have any either.
> 
> I believe without this, it results in doing a full table scan during
> the clean up which can become pretty resource intensive.
> 
> I'll defer to the Nova team on the best practice about this (or if
> this is even a bug), but I just wanted to bring the attention to this
> as I've had mild success trying to tame a large database and
> leveraging the archive DB tooling.

I don't think we query shadow tables or it's when we want to purge
them, so basically that is going to be based on datetime.  I may be
wrong but adding an index for a datetime field may not be so
interesting since the index is going to be quite large because of the
hours, minutes, seconds. That needs to be verified the database may
provides feature to index datetime using date only. If so I guess you
raised a good point, if not, indexing such fields is not going to help
and probably going to consum more resources.

s.

> Thanks,
> Mohammed
> 
> -- 
> Mohammed Naser — vexxhost
> -----------------------------------------------------
> D. 514-316-8872
> D. 800-910-1726 ext. 200
> E. mnaser at vexxhost.com
> W. http://vexxhost.com