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@vexxhost.com W. http://vexxhost.com