Skip to content

KEYCLOAK-19052: Optimised (split) the clearExpiredEvents query to reduce execution time - #8674

Merged
hmlnarik merged 1 commit into
keycloak:mainfrom
appvia:optimise-clear-expired-events-query
Nov 30, 2021
Merged

hmlnarik merged 1 commit into
keycloak:mainfrom
appvia:optimise-clear-expired-events-query

Conversation

@KashifSaadat

@KashifSaadat KashifSaadat commented Nov 1, 2021 •

Copy link
Copy Markdown
Contributor

@KashifSaadat

KashifSaadat commented Nov 1, 2021 •

Copy link
Copy Markdown
Contributor Author

Before:

> explain delete from EVENT_ENTITY where REALM_ID in (select realm.id from REALM realm where realm.EVENTS_EXPIRATION > 0) and EVENT_TIME < 1635524216;

+------+--------------------+--------------+-----------------+---------------+---------+---------+------+---------+-------------+
| id   | select_type        | table        | type            | possible_keys | key     | key_len | ref  | rows    | Extra       |
+------+--------------------+--------------+-----------------+---------------+---------+---------+------+---------+-------------+
|    1 | PRIMARY            | EVENT_ENTITY | ALL             | NULL          | NULL    | NULL    | NULL | 2072725 | Using where |
|    2 | DEPENDENT SUBQUERY | realm        | unique_subquery | PRIMARY       | PRIMARY | 38      | func | 1       | Using where |
+------+--------------------+--------------+-----------------+---------------+---------+---------+------+---------+-------------+
2 rows in set (0.001 sec)

After (can use index):

> explain delete from EVENT_ENTITY where REALM_ID in ("realm1", "realm2") and EVENT_TIME < 1635524216;

+------+-------------+--------------+-------+-----------------------------------+--------------------+---------+------+------+-------------+
| id   | select_type | table        | type  | possible_keys                     | key                | key_len | ref  | rows | Extra       |
+------+-------------+--------------+-------+-----------------------------------+--------------------+---------+------+------+-------------+
|    1 | SIMPLE      | EVENT_ENTITY | range | EVENT_ENTITY_INDEX,IDX_EVENT_TIME | EVENT_ENTITY_INDEX | 267     | NULL | 2    | Using where |
+------+-------------+--------------+-------+-----------------------------------+--------------------+---------+------+------+-------------+
1 row in set (0.001 sec)

Reduction in CPU utilisation following the change:
cpu-utilization

@KashifSaadat

Copy link
Copy Markdown
Contributor Author

Hi @stianst, would you be able to review this PR please if possible?

@stianst
stianst requested a review from hmlnarik November 10, 2021 07:10
@KashifSaadat

Copy link
Copy Markdown
Contributor Author

Hi @stianst @hmlnarik, are there any updates on a review for this please? We've been running in production successfully with much better behaviour, and the PR is pretty small and simple to review.

@hmlnarik
hmlnarik requested a review from vramik November 26, 2021 12:09
@vramik

vramik commented Nov 29, 2021

Copy link
Copy Markdown
Contributor

Thank you @KashifSaadat for the PR, changes looks good to me, could you please rebase it to latest main so I could run DB tests?

@hmlnarik could you please approve running GHA?

@KashifSaadat
KashifSaadat force-pushed the optimise-clear-expired-events-query branch from 51e50d0 to 02cfca9 Compare November 29, 2021 11:39
@KashifSaadat

Copy link
Copy Markdown
Contributor Author

Thanks! I've just rebased now.

@vramik vramik left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

@hmlnarik hmlnarik left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Approving per @vramik 's approval

@hmlnarik
hmlnarik merged commit d9bf511 into keycloak:main Nov 30, 2021
@hmlnarik

Copy link
Copy Markdown
Contributor

@KashifSaadat Thank you for your contribution!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

3 participants