yahoo-eng-team team mailing list archive

[Haneef Ali <1332666@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

Keystone middleware calls  GET /v2.0/revoked every 10 sec which
generates a query simar to

SELECT token.id AS token_id, token.expires AS token_expires, token.extra
AS token_extra, token.valid AS token_valid, token.user_id AS
token_user_id, token.trust_id AS token_trust_id  FROM token WHERE
token.valid = 1 AND token.expires > '2014-06-19 23:18:48.196884' AND
token.user_id = 'f6d9db238d084998aaef92ce425edff0';

This query most of the time uses the index  "idx_token_expires" which
results in too many rows.    Some times  depending on the load  using
this  index matches more than 50000 rows in our performance run  which
is as good as  full table scan.

As all the quries use "user_id"  in where clause, the above query can be
optimzed by adding index on user_id.  The same performance run  after
adding the index on  user_id doesn't show any degradation.

Can you please consider adding this in upstream?

** Affects: keystone
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to Keystone.
https://bugs.launchpad.net/bugs/1332666

Title:
  Kesytone token poor performance. Need index on user_id

Status in OpenStack Identity (Keystone):
  New

Bug description:
  Keystone middleware calls  GET /v2.0/revoked every 10 sec which
  generates a query simar to

  SELECT token.id AS token_id, token.expires AS token_expires,
  token.extra AS token_extra, token.valid AS token_valid, token.user_id
  AS token_user_id, token.trust_id AS token_trust_id  FROM token WHERE
  token.valid = 1 AND token.expires > '2014-06-19 23:18:48.196884' AND
  token.user_id = 'f6d9db238d084998aaef92ce425edff0';

  This query most of the time uses the index  "idx_token_expires" which
  results in too many rows.    Some times  depending on the load  using
  this  index matches more than 50000 rows in our performance run  which
  is as good as  full table scan.

  As all the quries use "user_id"  in where clause, the above query can
  be optimzed by adding index on user_id.  The same performance run
  after adding the index on  user_id doesn't show any degradation.

  Can you please consider adding this in upstream?

To manage notifications about this bug go to:
https://bugs.launchpad.net/keystone/+bug/1332666/+subscriptions