OpenStack Identity (keystone)

N+1 lookups in groups SQL

Bug #1218675 reported by Jamie Lennox
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Identity (keystone)
Fix Released
Medium
Jamie Lennox
OpenStack Identity (keystone) 2013.2 "havana"
Grizzly
Fix Released
Medium
Dirk Mueller
OpenStack Identity (keystone) 2013.1.4

Bug Description

In the SQL for list_users_in_groups we perform

- One lookup to verify that the group exists.
- One lookup to get the users associated with this group
- N lookups to retrieve each of the N associated users

The same pattern is used in list_groups_for_user.

We can replace this with a join query.

I'm not aware if we can drop the initial verify, theoretically if the group doesn't exist then there are no entries in the join table however there may be code specifically relying on this behaviour.

OpenStack Infra (hudson-openstack)
Changed in keystone:
assignee: nobody → Jamie Lennox (jamielennox)
status: New → In Progress
Dolph Mathews (dolph)
Changed in keystone:
importance: Undecided → Medium
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to keystone (master)

Reviewed: https://review.openstack.org/42566
Committed: http://github.com/openstack/keystone/commit/6f9b75e2c3bbbd7dfc5e2d414d7271bb4f8bcf71
Submitter: Jenkins
Branch: master

commit 6f9b75e2c3bbbd7dfc5e2d414d7271bb4f8bcf71
Author: Jamie Lennox <email address hidden>
Date: Mon Aug 19 15:00:03 2013 +1000

    Use joins instead of multiple lookups in groups sql

    Currently to determine a user's groups (or vice versa) we do
    - 1 lookup for the user.
    - 1 lookup for the association table.
    - n lookups for the n groups the user has.

    The association and groups lookup are replaced with a join query.

    Fixes: bug 1218675
    Change-Id: I560659eda1a30635399829e1c86f06734c90f9e2

Changed in keystone:
status: In Progress → Fix Committed
Thierry Carrez (ttx)
Changed in keystone:
milestone: none → havana-3
status: Fix Committed → Fix Released
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to keystone (stable/grizzly)

Fix proposed to branch: stable/grizzly
Review: https://review.openstack.org/45629

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to keystone (stable/grizzly)

Reviewed: https://review.openstack.org/45629
Committed: http://github.com/openstack/keystone/commit/53ff4451c31c888e6cafac9b3a44599601cf6bdc
Submitter: Jenkins
Branch: stable/grizzly

commit 53ff4451c31c888e6cafac9b3a44599601cf6bdc
Author: Jamie Lennox <email address hidden>
Date: Mon Aug 19 15:00:03 2013 +1000

    Use joins instead of multiple lookups in groups sql

    Currently to determine a user's groups (or vice versa) we do
    - 1 lookup for the user.
    - 1 lookup for the association table.
    - n lookups for the n groups the user has.

    The association and groups lookup are replaced with a join query.

    Fixes: bug 1218675
    Change-Id: I560659eda1a30635399829e1c86f06734c90f9e2
    (cherry picked from commit 6f9b75e2c3bbbd7dfc5e2d414d7271bb4f8bcf71)

Thierry Carrez (ttx)
Changed in keystone:
milestone: havana-3 → 2013.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.