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 (revoca tion_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 (revo cation_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 -- Mailing list: https://launchpad.net/~yahoo-eng-team Post to : yahoo-eng-team@lists.launchpad.net Unsubscribe : https://launchpad.net/~yahoo-eng-team More help : https://help.launchpad.net/ListHelp
