DB Migrations missing indexes

[Vipul Sabhaya]

Trove should create indexes within the DB Migration scripts.

11-07-13 DN

I think we need to talk this over before starting. There are a couple of concerns I have.

1) nova doesn't seem to do this in their migrations from what I can tell.
2) if we add indexes to tables that have lots of data, how will this impact the system.

Maybe the better way to go is to just document the indexes somewhere and leave it up to folks to decide if and when to apply them.

02-28-14 DN

These indexes address issues discovered in our production environment. Slow queries and transactions were observed to cause heavy load on our Trove Database.

After adding the indexes we saw an immediate drop in cpu and load.

[Michael Basnight (hubcap)]

Id say before we go down this route, lets ask if _any_ openstack project does it.. I dont want to get in the business of it if no one else does.

Plz post to the mailing list and put a link to the thread here.

[Dan Nguyen (daniel-a-nguyen)]

Agree with hub_cap on this.

On Nov 18, 2013, at 2:55 PM, Michael Basnight <email address hidden> wrote:

> Id say before we go down this route, lets ask if _any_ openstack project
> does it.. I dont want to get in the business of it if no one else does.
>
> Plz post to the mailing list and put a link to the thread here.
>
> ** Changed in: trove
> Status: New => Incomplete
>
> --
> You received this bug notification because you are a bug assignee.
> https://bugs.launchpad.net/bugs/1214188
>
> Title:
> DB Migrations missing indexes
>
> Status in Trove - Database as a Service:
> Incomplete
>
> Bug description:
> Trove should create indexes within the DB Migration scripts.
>
> 11-07-13 DN
>
> I think we need to talk this over before starting. There are a couple
> of concerns I have.
>
> 1) nova doesn't does this in their migrations from what I can tell.
> 2) if we add indexes to tables that have lots of data, how will this impact the system.
>
> Maybe the better way to go is to just document the indexes somewhere
> and leave it up to folks to decide when to if and when to apply them.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/trove/+bug/1214188/+subscriptions

[Vipul Sabhaya (vipuls)]

Nova does create indexes as part of DB migrations. Pull up a devstack and
look at your database.

On Mon, Nov 18, 2013 at 3:07 PM, Dan Nguyen <email address hidden>wrote:

> Agree with hub_cap on this.
>
> On Nov 18, 2013, at 2:55 PM, Michael Basnight
> <email address hidden> wrote:
>
> > Id say before we go down this route, lets ask if _any_ openstack project
> > does it.. I dont want to get in the business of it if no one else does.
> >
> > Plz post to the mailing list and put a link to the thread here.
> >
> > ** Changed in: trove
> > Status: New => Incomplete
> >
> > --
> > You received this bug notification because you are a bug assignee.
> > https://bugs.launchpad.net/bugs/1214188
> >
> > Title:
> > DB Migrations missing indexes
> >
> > Status in Trove - Database as a Service:
> > Incomplete
> >
> > Bug description:
> > Trove should create indexes within the DB Migration scripts.
> >
> > 11-07-13 DN
> >
> > I think we need to talk this over before starting. There are a couple
> > of concerns I have.
> >
> > 1) nova doesn't does this in their migrations from what I can tell.
> > 2) if we add indexes to tables that have lots of data, how will this
> impact the system.
> >
> > Maybe the better way to go is to just document the indexes somewhere
> > and leave it up to folks to decide when to if and when to apply them.
> >
> > To manage notifications about this bug go to:
> > https://bugs.launchpad.net/trove/+bug/1214188/+subscriptions
>
> --
> You received this bug notification because you are a member of RedDwarf
> Core Team, which is subscribed to Trove.
> Matching subscriptions: trove-bugs
> https://bugs.launchpad.net/bugs/1214188
>
> Title:
> DB Migrations missing indexes
>
> Status in Trove - Database as a Service:
> Incomplete
>
> Bug description:
> Trove should create indexes within the DB Migration scripts.
>
> 11-07-13 DN
>
> I think we need to talk this over before starting. There are a couple
> of concerns I have.
>
> 1) nova doesn't does this in their migrations from what I can tell.
> 2) if we add indexes to tables that have lots of data, how will this
> impact the system.
>
> Maybe the better way to go is to just document the indexes somewhere
> and leave it up to folks to decide when to if and when to apply them.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/trove/+bug/1214188/+subscriptions
>

[Michael Basnight (hubcap)]

https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/migrate_repo/versions/133_folsom.py#L931

looks like thsi is a valid bug then

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/72039
Committed: https://git.openstack.org/cgit/openstack/trove/commit/?id=1d0b972377130ad85b6490ee1f40f70f16ff900b
Submitter: Jenkins
Branch: master

commit 1d0b972377130ad85b6490ee1f40f70f16ff900b
Author: daniel-a-nguyen <email address hidden>
Date: Fri Feb 7 17:20:03 2014 -0800

    Adding missing indexes for trove db

    Wraped creates in a try/except to avoid problems if idx exists
    Updated number-naming for scripts

    Change-Id: I81e59f2f54dab73fc794fae0df9e1751e5896530
    Closes-Bug: #1214188