yahoo-eng-team team mailing list archive

[Clint Byrum <clint@xxxxxxxxxx>]

Public bug reported:

This is because we still have to filter results of an indexed query that
may return _millions_ of rows:

mysql> EXPLAIN SELECT token.id AS token_id FROM token WHERE token.valid = 1 AND token.expires > '2014-02-05 01:28:07.725059' AND token.user_id = '356d68464dc2478992427864dca4ce6a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: token
         type: ref
possible_keys: ix_token_expires,ix_token_valid,ix_token_expires_valid
          key: ix_token_valid
      key_len: 1
          ref: const
         rows: 697205
        Extra: Using where
1 row in set (0.01 sec)


Adding an index on user_id makes this quite a bit faster:

mysql> EXPLAIN SELECT token.id AS token_id FROM token WHERE token.valid = 1 AND token.expires > '2014-02-05 01:28:07.725059' AND token.user_id = '356d68464dc2478992427864dca4ce6a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: token
         type: ref
possible_keys: ix_token_expires,ix_token_valid,ix_token_expires_valid,ix_user_id
          key: ix_user_id
      key_len: 195
          ref: const
         rows: 89
        Extra: Using where
1 row in set (0.00 sec)

Note that memory usage still will go very high if a user one is deleting
has a lot of tokens, because the orm will select all of the rows, when
all that it needs is the id. So there are really two bugs. But the
select is slow even if you just select the id.

** 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/1277298

Title:
  Deleting users takes a long time if there are many tokens

Status in OpenStack Identity (Keystone):
  New

Bug description:
  This is because we still have to filter results of an indexed query
  that may return _millions_ of rows:

  mysql> EXPLAIN SELECT token.id AS token_id FROM token WHERE token.valid = 1 AND token.expires > '2014-02-05 01:28:07.725059' AND token.user_id = '356d68464dc2478992427864dca4ce6a'\G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: token
           type: ref
  possible_keys: ix_token_expires,ix_token_valid,ix_token_expires_valid
            key: ix_token_valid
        key_len: 1
            ref: const
           rows: 697205
          Extra: Using where
  1 row in set (0.01 sec)

  
  Adding an index on user_id makes this quite a bit faster:

  mysql> EXPLAIN SELECT token.id AS token_id FROM token WHERE token.valid = 1 AND token.expires > '2014-02-05 01:28:07.725059' AND token.user_id = '356d68464dc2478992427864dca4ce6a'\G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: token
           type: ref
  possible_keys: ix_token_expires,ix_token_valid,ix_token_expires_valid,ix_user_id
            key: ix_user_id
        key_len: 195
            ref: const
           rows: 89
          Extra: Using where
  1 row in set (0.00 sec)

  Note that memory usage still will go very high if a user one is
  deleting has a lot of tokens, because the orm will select all of the
  rows, when all that it needs is the id. So there are really two bugs.
  But the select is slow even if you just select the id.

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