OpenStack Compute (nova)

Large deployments may require additional indexes

Bug #1006624 reported by aeva black
14
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Compute (nova)
Fix Released
Critical
Justin Shepherd
OpenStack Compute (nova) 2012.2 "folsom"

Bug Description

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

See original description
aeva black (tenbrae)
summary: - Large deployments require additional indexes
+ Large deployments may require additional indexes
Revision history for this message
Sean Dague (sdague) wrote :

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.

tags: added: low-hanging-fruit
Changed in nova:
status: New → Confirmed
Thierry Carrez (ttx)
Changed in nova:
importance: Undecided → Medium
Revision history for this message
Sean Dague (sdague) wrote :

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

Changed in nova:
assignee: nobody → Sean Dague (sdague-b)
Revision history for this message
Justin Shepherd (jshepher) wrote :

Sean are you actively working on this issue?

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

Thanks,
shep

Revision history for this message
Justin Shepherd (jshepher) wrote :

Also.. had another

=== virtual_interfaces ===
* uuid

Revision history for this message
Sean Dague (sdague) wrote :

Justin, all yours.

Changed in nova:
assignee: Sean Dague (sdague-b) → Justin Shepherd (jshepher)
milestone: none → folsom-3
Justin Shepherd (jshepher)
description: updated
description: updated
Revision history for this message
Phil Day (philip-day) wrote :

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)
tags: added: ops
Revision history for this message
Michael Still (mikal) wrote :

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?

Revision history for this message
Sean Dague (sdague) wrote :

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.

Revision history for this message
Justin Shepherd (jshepher) wrote :

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

Justin Shepherd (jshepher)
description: updated
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to nova (master)

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

Changed in nova:
status: Confirmed → In Progress
Revision history for this message
aeva black (tenbrae) wrote :

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

Revision history for this message
Justin Shepherd (jshepher) wrote :

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

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

Vish Ishaya (vishvananda)
Changed in nova:
milestone: folsom-3 → folsom-rc1
importance: Medium → Critical
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to nova (master)

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

Changed in nova:
status: In Progress → Fix Committed
Thierry Carrez (ttx)
Changed in nova:
status: Fix Committed → Fix Released
Thierry Carrez (ttx)
Changed in nova:
milestone: folsom-rc1 → 2012.2
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.