keystone.contrib.revoke.backends.sql contains several glaring performance problems

Bug #1290625 reported by Clint Byrum
10
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Identity (keystone)
Fix Released
Medium
Ron De Rose

Bug Description

* The id column is internal only, and yet, is varchar(64). This should just be an auto incremented int.

* There are no indexes on anything.

* The comments claim that only DB2 has trouble with large deletes. This is false. MySQL will hold gaps open on any indexes, including the primary key, while deleting. This will effectively serialize access to giant chunks of the table. If the ID were an auto-inc int, then this is a non-issue because all writes will either be deletes or incrementing the integer and thus not fall into the gaps. But then if we add indexes where they should be, such as revoked_at, then that index will also have gap locks. note that this is already acknowledged as this bug in tokens: [ https://bugs.launchpad.net/keystone/+bug/1188378 ], which I believe was just cargo-culted into this module.

Dolph Mathews (dolph)
tags: added: performance
Changed in keystone:
status: New → Triaged
importance: Undecided → Medium
Dolph Mathews (dolph)
tags: added: sql
Adam Young (ayoung)
Changed in keystone:
assignee: nobody → Adam Young (ayoung)
Revision history for this message
Lance Bragstad (lbragstad) wrote :

unassigning for now so that others can pick it up

Changed in keystone:
assignee: Adam Young (ayoung) → nobody
Changed in keystone:
assignee: nobody → Ron De Rose (ronald-de-rose)
tags: added: revoke
Revision history for this message
David Stanek (dstanek) wrote :

Is this actively being worked on?

Revision history for this message
Ron De Rose (ronald-de-rose) wrote :

It hasn't been a priority as I've been so focused on shadow users. I can take this on now.

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to keystone (master)

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

Changed in keystone:
status: Triaged → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to keystone (master)

Reviewed: https://review.openstack.org/290841
Committed: https://git.openstack.org/cgit/openstack/keystone/commit/?id=34db9c0e538137eb4622a539de46a8a515e25e9f
Submitter: Jenkins
Branch: master

commit 34db9c0e538137eb4622a539de46a8a515e25e9f
Author: Ronald De Rose <email address hidden>
Date: Wed Mar 9 20:38:45 2016 +0000

    Add auto-increment int primary key to revoke.backends.sql

    Modified the revocation_event id column to be an auto-incremented
    integer. The id column was a varchar(64), however it is only used
    internally and an integer column will help with large deletes.

    Change-Id: Ibbac98ec50583579ca8fcb8f928d4882f571b07c
    Closes-Bug: 1290625

Changed in keystone:
status: In Progress → Fix Released
Changed in keystone:
milestone: none → mitaka-rc1
Revision history for this message
Doug Hellmann (doug-hellmann) wrote : Fix included in openstack/keystone 9.0.0.0rc1

This issue was fixed in the openstack/keystone 9.0.0.0rc1 release candidate.

Changed in keystone:
assignee: Ron De Rose (ronald-de-rose) → nobody
assignee: nobody → Ron De Rose (ronald-de-rose)
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

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