diff options
author | Clint Byrum <clint@fewbar.com> | 2013-11-22 08:50:39 -0800 |
---|---|---|
committer | Dirk Mueller <dirk@dmllr.de> | 2014-01-30 13:29:59 +0100 |
commit | 2414bab51d134b713b2767435ed29755538d150a (patch) | |
tree | 7e05c8733f391669c15fcbdd4325179b9643b39b | |
parent | 9983fbeaf35a81b6bd3085238b42f3fc42f47d01 (diff) | |
download | keystone-2414bab51d134b713b2767435ed29755538d150a.tar.gz |
list_revoked_tokens sql speedup for havana
This consists of the following 3 patches:
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.
(cherry picked from commit ab7221246af394f24e47484e822b8dcda37411aa)
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.
Because valid is only ever used in conjunction with expires, we do not
need it to have its own index now that there is a covering compound
index for expires and valid.
Note that he expires index is still useful alone for purging old tokens
as we do not filter for valid in that case.
(cherry picked from commit dd2c80c566f20a97a22e0d7d5a514be84772a955)
Remove unused token.valid index
Because valid is only ever used in conjunction with expires, we do not
need it to have its own index now that there is a covering compound
index for expires and valid.
Note that he expires index is still useful alone for purging old tokens
as we do not filter for valid in that case.
(cherry picked from commit 5d8a1a41420aa20d2aa21da6311c9d55b9e373b6)
Change-Id: I04d62b98d5d760a3fbc3c8db61530f7ebccb0a48
Closes-Bug: #1253755
-rw-r--r-- | keystone/common/sql/migrate_repo/versions/035_add_compound_revoked_token_index.py | 33 | ||||
-rw-r--r-- | keystone/common/sql/migrate_repo/versions/036_token_drop_valid_index.py | 33 | ||||
-rw-r--r-- | keystone/tests/test_backend_sql.py | 20 | ||||
-rw-r--r-- | keystone/tests/test_sql_upgrade.py | 15 | ||||
-rw-r--r-- | keystone/token/backends/sql.py | 8 |
5 files changed, 104 insertions, 5 deletions
diff --git a/keystone/common/sql/migrate_repo/versions/035_add_compound_revoked_token_index.py b/keystone/common/sql/migrate_repo/versions/035_add_compound_revoked_token_index.py new file mode 100644 index 000000000..bcce9d4b3 --- /dev/null +++ b/keystone/common/sql/migrate_repo/versions/035_add_compound_revoked_token_index.py @@ -0,0 +1,33 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2013 Hewlett-Packard Development Company, L.P. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +import sqlalchemy as sql + + +def upgrade(migrate_engine): + meta = sql.MetaData() + meta.bind = migrate_engine + token = sql.Table('token', meta, autoload=True) + idx = sql.Index('ix_token_expires_valid', token.c.expires, token.c.valid) + idx.create(migrate_engine) + + +def downgrade(migrate_engine): + meta = sql.MetaData() + meta.bind = migrate_engine + token = sql.Table('token', meta, autoload=True) + idx = sql.Index('ix_token_expires_valid', token.c.expires, token.c.valid) + idx.drop(migrate_engine) diff --git a/keystone/common/sql/migrate_repo/versions/036_token_drop_valid_index.py b/keystone/common/sql/migrate_repo/versions/036_token_drop_valid_index.py new file mode 100644 index 000000000..6bf3c5e93 --- /dev/null +++ b/keystone/common/sql/migrate_repo/versions/036_token_drop_valid_index.py @@ -0,0 +1,33 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2013 Hewlett-Packard Development Company, L.P. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +import sqlalchemy as sql + + +def upgrade(migrate_engine): + meta = sql.MetaData() + meta.bind = migrate_engine + token = sql.Table('token', meta, autoload=True) + idx = sql.Index('ix_token_valid', token.c.valid) + idx.drop(migrate_engine) + + +def downgrade(migrate_engine): + meta = sql.MetaData() + meta.bind = migrate_engine + token = sql.Table('token', meta, autoload=True) + idx = sql.Index('ix_token_valid', token.c.valid) + idx.create(migrate_engine) diff --git a/keystone/tests/test_backend_sql.py b/keystone/tests/test_backend_sql.py index ef246d46b..19b4c424a 100644 --- a/keystone/tests/test_backend_sql.py +++ b/keystone/tests/test_backend_sql.py @@ -22,9 +22,11 @@ from keystone.common import sql from keystone import config from keystone import exception from keystone.identity.backends import sql as identity_sql +from keystone.openstack.common.fixture import moxstubout from keystone import tests from keystone.tests import default_fixtures from keystone.tests import test_backend +from keystone.token.backends import sql as token_sql CONF = config.CONF @@ -352,7 +354,23 @@ class SqlTrust(SqlTests, test_backend.TrustTests): class SqlToken(SqlTests, test_backend.TokenTests): - pass + def test_token_revocation_list_uses_right_columns(self): + # This query used to be heavy with too many columns. We want + # to make sure it is only running with the minimum columns + # necessary. + fixture = self.useFixture(moxstubout.MoxStubout()) + self.mox = fixture.mox + tok = token_sql.Token() + session = tok.get_session() + q = session.query(token_sql.TokenModel.id, + token_sql.TokenModel.expires) + self.mox.StubOutWithMock(session, 'query') + session.query(token_sql.TokenModel.id, + token_sql.TokenModel.expires).AndReturn(q) + self.mox.StubOutWithMock(tok, 'get_session') + tok.get_session().AndReturn(session) + self.mox.ReplayAll() + tok.list_revoked_tokens() class SqlCatalog(SqlTests, test_backend.CatalogTests): diff --git a/keystone/tests/test_sql_upgrade.py b/keystone/tests/test_sql_upgrade.py index 3b763715c..e5def4c30 100644 --- a/keystone/tests/test_sql_upgrade.py +++ b/keystone/tests/test_sql_upgrade.py @@ -1333,6 +1333,21 @@ class SqlUpgradeTests(SqlMigrateBase): else: self.assertEqual(len(index_data), 0) + def test_revoked_token_index(self): + self.upgrade(35) + table = sqlalchemy.Table('token', self.metadata, autoload=True) + index_data = [(idx.name, idx.columns.keys()) + for idx in table.indexes] + self.assertIn(('ix_token_expires_valid', ['expires', 'valid']), + index_data) + + def test_dropped_valid_index(self): + self.upgrade(36) + table = sqlalchemy.Table('token', self.metadata, autoload=True) + index_data = [(idx.name, idx.columns.keys()) + for idx in table.indexes] + self.assertNotIn(('ix_token_valid', ['valid']), index_data) + def test_migrate_ec2_credential(self): user = { 'id': 'foo', diff --git a/keystone/token/backends/sql.py b/keystone/token/backends/sql.py index 455a37b6b..22cfd97fb 100644 --- a/keystone/token/backends/sql.py +++ b/keystone/token/backends/sql.py @@ -33,7 +33,7 @@ class TokenModel(sql.ModelBase, sql.DictBase): trust_id = sql.Column(sql.String(64)) __table_args__ = ( sql.Index('ix_token_expires', 'expires'), - sql.Index('ix_token_valid', 'valid') + sql.Index('ix_token_expires_valid', 'expires', 'valid') ) @@ -181,13 +181,13 @@ class Token(sql.Base, token.Driver): session = self.get_session() tokens = [] now = timeutils.utcnow() - query = session.query(TokenModel) + query = session.query(TokenModel.id, TokenModel.expires) query = query.filter(TokenModel.expires > now) token_references = query.filter_by(valid=False) for token_ref in token_references: record = { - 'id': token_ref['id'], - 'expires': token_ref['expires'], + 'id': token_ref[0], + 'expires': token_ref[1], } tokens.append(record) return tokens |