keystone.token.backends.sql list_revoked_tokens performs very poorly

[Clint Byrum]

The query that it makes use of is extremely inefficient, as it must retrieve the massive 'extra' field when it does not need it. Also there is no index that covers both expires and valid, so we can only do a range query on expires and then filter for valid.

Test situation is a poorly tuned mysql that has a token table with 865000 rows, 35000 of which are revoked (2000 of which are unexpired).

Adding an index on token+valid did speed the query up some, but it still took on average 2 seconds to return all ~2000 revoked token rows. Also changing the query to only query the id and expires columns resulted in the query taking 0.02 seconds to run, leading to a much more responsive experience throughout the cloud.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

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

[Dolph Mathews (dolph)]

This has potential to land as the first migration since stable/havana was cut, so this could be backported.

[Dolph Mathews (dolph)]

This will also be solved by https://blueprints.launchpad.net/keystone/+spec/revocation-events in icehouse, as we can optionally disable the API and it's backend.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/57975
Committed: http://github.com/openstack/keystone/commit/dd2c80c566f20a97a22e0d7d5a514be84772a955
Submitter: Jenkins
Branch: master

commit dd2c80c566f20a97a22e0d7d5a514be84772a955
Author: Clint Byrum <email address hidden>
Date: Thu Nov 21 11:12:17 2013 -0800

    Add index to cover revoked token list

    The individual expires and valid indexes do not fully cover the most
    common query, which is the one that lists revoked tokens.

    Change-Id: I112332b49dac148e5231b76f3399fe60174a702d
    Partial-Bug: #1253755

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/57976
Committed: http://github.com/openstack/keystone/commit/ab7221246af394f24e47484e822b8dcda37411aa
Submitter: Jenkins
Branch: master

commit ab7221246af394f24e47484e822b8dcda37411aa
Author: Clint Byrum <email address hidden>
Date: Fri Nov 22 08:50:39 2013 -0800

    Narrow columns used in list_revoked_tokens sql

    Currently the SQL backend lists revoked tokens by selecting all of the
    columns, including the massive "extra" column. This places a significant
    burden on the client library and wastes resources. We only need the
    id/expired columns to satisfy the API call.

    In tests this query was several orders of magnitude faster with just two
    thousand un-expired revoked tokens.

    Change-Id: I04d62b98d5d760a3fbc3c8db61530f7ebccb0a48
    Closes-Bug: #1253755

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/havana
Review: https://review.openstack.org/69884