Large deployments may require additional indexes

[aeva black]

I've compiled the below list of additional indexes from some "large" deployments of openstack diablo and essex. This is a list of indexes that were added in production, presumably to put out fires. I will open a separate bug report to list all missing indexes based on a review of the api and model code.

=== bw_usage_cache ===
 * instance_id
 * start_period

=== compute_nodes ===
 * service_id
 * deleted

=== fixed_ips ===
 * address(15)
    index prefix created because max(length(address)) is only 15.
 * instance_id, updated_at
    yes, there is already an index on (instance_id) but this compound index is faster.

=== instances ===
 * host(30)
 * updated_at
 * deleted_at
 * terminated_at
 * project_id
 * deleted

=== instance_faults ===
 * deleted
 * instance_uuid

=== migrations ===
 * status
 * updated_at
 * instance_uuid

=== networks ===
 * uuid

=== quotas ===
 * project_id
 * deleted

=== floating_ips ===
 * fixed_ip_id

=== security_groups ===
 * deleted

=== security_group_instance_association ===
 * deleted

=== security_group_rules ===
 * group_id
 * parent_group_id
 * deleted

=== services ===
 * deleted, host

=== virtual_interfaces ===
 * uuid

=== volumes ===
 * instance_id

=== volume_metadata ===
 * volume_id

[Sean Dague (sdague)]

Suggested importance: medium (known work arounds, but this should be good in core)

This is probably low hanging fruit, as it is easy to introduce these indexes as migrations.

[Sean Dague (sdague)]

A full listing of queries where this is a problem are found here - http://bit.ly/OxxAKX

[Justin Shepherd (jshepher)]

Sean are you actively working on this issue?

If no.. re-assign to me and i will.

Thanks,
shep

[Justin Shepherd (jshepher)]

Also.. had another

=== virtual_interfaces ===
* uuid

[Sean Dague (sdague)]

Justin, all yours.

[Phil Day (philip-day)]

I would suggest also adding the following to the list:

=== floating_ips ===
 * fixed_ip_id

=== instances ===
 * project_id
 * deleted

=== security_group_instance_association ===
 * deleted

=== security_group_rules ===
 * group_id
 * parent_group_id

=== volumes ===
 * instance_id

=== volume_metadata ===
 * volume_id

[Michael Still (mikal)]

I'm happy to have a swing at this is other people are busy. My only real concern here is that adding these indexes might make this database migration very slow to run. Exactly how big are these tables in your deployment?

[Sean Dague (sdague)]

Justin, is there progress here. We've got another new member that would like to pick this up if it's not being worked already.

[Justin Shepherd (jshepher)]

Sean,

I have 4/5 of it done.. was just trying to figure out the best approach for the reminder.. should have a review in today or tomorrow.

--shep

[OpenStack Infra (hudson-openstack)]

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

[aeva black (tenbrae)]

Justin, did you see the spreadsheet that Sean posted?

It may be a bit out of date, as I haven't been tracking changes in trunk since I started compiling this list, but I think it has a lot of valuable additions to the list I initially posted.

Here's the link again:

http://bit.ly/OxxAKX

[Justin Shepherd (jshepher)]

Implemented rows 1 - 31 from the list of missing indexes (http://bit.ly/OxxAKX)

https://review.openstack.org/#/c/10936/

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/10936
Committed: http://github.com/openstack/nova/commit/1259ed685535e246bf7c0ec8d3a3d330f7df9596
Submitter: Jenkins
Branch: master

commit 1259ed685535e246bf7c0ec8d3a3d330f7df9596
Author: Justin Shepherd <email address hidden>
Date: Tue Aug 7 03:12:28 2012 +0000

    Adding indexes to frequently joined database columns.

    Fixes bug 1006624

    Change-Id: I0f1f6903a29cb8d3573987b88f62cd895a11c100