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

[Clint Byrum]

* 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.

[Lance Bragstad (lbragstad)]

unassigning for now so that others can pick it up

[David Stanek (dstanek)]

Is this actively being worked on?

[Ron De Rose (ronald-de-rose)]

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

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

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

[Doug Hellmann (doug-hellmann)]

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