yahoo-eng-team team mailing list archive

[Olivier Chaze <2081082@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

Every token checks against table revocation_event table take too much
SQL resources due to missing index.

I tried to submit a patch but I'm not familiar enough to do it on my own :
https://review.opendev.org/c/openstack/keystone/+/929736

After these indexes werw created on our cluster, CPU load dropped from 100% to 25%.
Our table has 60k rows.

Example of query taking advantage of the indexes.

MariaDB [keystonedb]> EXPLAIN SELECT revocation_event.id AS revocation_event_id,         revocation_event.domain_id AS revocation_event_domain_id,         revocation_event.project_id AS revocation_event_project_id,         revocation_event.user_id AS revocation_event_user_id,         revocation_event.role_id AS revocation_event_role_id,         revocation_event.trust_id AS revocation_event_trust_id,         revocation_event.consumer_id AS revocation_event_consumer_id,         revocation_event.access_token_id AS revocation_event_access_token_id,         revocation_event.issued_before AS revocation_event_issued_before,         revocation_event.expires_at AS revocation_event_expires_at,         revocation_event.revoked_at AS revocation_event_revoked_at,         revocation_event.audit_id AS revocation_event_audit_id,         revocation_event.audit_chain_id AS revocation_event_audit_chain_id  FROM revocation_event  WHERE revocation_event.issued_before >= '2024-09-11 18:29:38'  AND (revocation_event.user_id IS NULL OR revocation_event.user_id = '<USER_ID>')  AND (revocation_event.project_id IS NULL OR revocation_event.project_id = '<PROJECT_ID>')  AND (revocation_event.audit_id IS NULL OR revocation_event.audit_id = '<AUDIT_ID>')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: revocation_event
         type: ref_or_null
possible_keys: ix_revocation_event_issued_before,ix_revocation_event_project_id_issued_before,ix_revocation_event_user_id_issued_before,ix_revocation_event_audit_id_issued_before,idx_revocation_event_composite,idx_revocation_event_project_id,idx_revocation_event_project_id_user_id
          key: idx_revocation_event_project_id_user_id
      key_len: 195
          ref: const
         rows: 8
        Extra: Using index condition; Using where
1 row in set (0.001 sec)

** Affects: keystone
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to OpenStack Identity (keystone).
https://bugs.launchpad.net/bugs/2081082

Title:
  Missing indexes on revocation event table

Status in OpenStack Identity (keystone):
  New

Bug description:
  Every token checks against table revocation_event table take too much
  SQL resources due to missing index.

  I tried to submit a patch but I'm not familiar enough to do it on my own :
  https://review.opendev.org/c/openstack/keystone/+/929736

  After these indexes werw created on our cluster, CPU load dropped from 100% to 25%.
  Our table has 60k rows.

  Example of query taking advantage of the indexes.

  MariaDB [keystonedb]> EXPLAIN SELECT revocation_event.id AS revocation_event_id,         revocation_event.domain_id AS revocation_event_domain_id,         revocation_event.project_id AS revocation_event_project_id,         revocation_event.user_id AS revocation_event_user_id,         revocation_event.role_id AS revocation_event_role_id,         revocation_event.trust_id AS revocation_event_trust_id,         revocation_event.consumer_id AS revocation_event_consumer_id,         revocation_event.access_token_id AS revocation_event_access_token_id,         revocation_event.issued_before AS revocation_event_issued_before,         revocation_event.expires_at AS revocation_event_expires_at,         revocation_event.revoked_at AS revocation_event_revoked_at,         revocation_event.audit_id AS revocation_event_audit_id,         revocation_event.audit_chain_id AS revocation_event_audit_chain_id  FROM revocation_event  WHERE revocation_event.issued_before >= '2024-09-11 18:29:38'  AND (revocation_event.user_id IS NULL OR revocation_event.user_id = '<USER_ID>')  AND (revocation_event.project_id IS NULL OR revocation_event.project_id = '<PROJECT_ID>')  AND (revocation_event.audit_id IS NULL OR revocation_event.audit_id = '<AUDIT_ID>')\G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: revocation_event
           type: ref_or_null
  possible_keys: ix_revocation_event_issued_before,ix_revocation_event_project_id_issued_before,ix_revocation_event_user_id_issued_before,ix_revocation_event_audit_id_issued_before,idx_revocation_event_composite,idx_revocation_event_project_id,idx_revocation_event_project_id_user_id
            key: idx_revocation_event_project_id_user_id
        key_len: 195
            ref: const
           rows: 8
          Extra: Using index condition; Using where
  1 row in set (0.001 sec)

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